3

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?

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.