Partial copy of huge database

Question

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?

Answer 1

mysqldump -h source_server... --order-by-primary --where='id > ...' 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.