Partial copy of huge database I have started working in a project which has a big database, around 300GB. For security reasons I can not access database from my local web app. So I need to copy last 100,000 from each table.

To copy from a table to another, I know I can do:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;


But how I can handle connecting to the other database?

One idea I have, it is to create a table, same structure, and use query above to move records and then dump those tables.

Is there a better way?
mysqldump -h source_server... --order-by-primary --where='id &gt; ...' src_db, tbl  |
    mysql -h dest_server ...


But that assumes


id is the PRIMARY KEY
You can get the id 100K (or so) rows from the end:  SELECT id FROM tbl ORDER BY id DESC LIMIT 100000, 1
You can access both servers from wherever you run the pipeline.


Since you need 2 connections, there is no 'simple' way to do it from the mysql commandline tool or from client code (PHP, Java, etc).  Copying one row at a time would quite slow.  Employing LOAD DATA will be no better than mysqldump + mysql.