1

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?

0
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.

-1

Copy the create script for those tables an copy onlyt the rows that you want

mysqldump --opt --user=username --password=password database table --limit 1000 > file.sql

At the new server create the table and charge

mysqldump --opt --user=username --password=password database new_table --limit 1000 < file.sql

I think it's the best way for your

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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