3

Is there a way to back up a table using mysqldump so the table that is being backed up can have a new name in the sql script created by mysqldump.

So that If I back up the table 'test_table' like so.

mysqldump -u user -p db_name test_table > backup.sql

and I want 'test_table' to now be called 'test_table_backup' in the backup.sql that is created.

CREATE TABLE `test_table_backup` (

...

);

I know i can just dump the file then craft some way to do a search and replace on the table name but I was hoping there was a safer way using mysqldump itself.

  • mysqldump is backup/dumping program, not cloning one. The result it produces is oriented to restore, not to make copy. So dump editing is the only way to solve the task in question. – Akina May 25 '18 at 6:16
3

You have to use sed to transform the table name

mysqldump -u user -p db_name test_table | sed 's/test_table/test_table_backup/g' > backup.sql

Give it a Try !!!

  • Ad what happens if you happen to have the string test_table somewhere in the actual rows' data...? – JonBrave Nov 14 '18 at 11:11
  • is this safe for big size dumps (more than 4GB)?? or will be too slow and consume all memory? – Enrique Jul 18 '19 at 18:22
0

sed is a simple way to to change the table name (as mentioned in the other Answer). If you are worried that table_name may appear as part of the table data, you can add backtick back spaces to be more precise with your sed statement. This works because mysqldump always surrounds the table name with backtick and spaces.

mysqldump -u username -p db_name table_name | sed -e 's/ `table_name` / `table_name_new` /' > dump_table_new.sql

Of course, if you already have a dump file, you don't need to run mysqldump again.

sed -e 's/ `table_name` / `table_name_new` /' < dump_table_name.sql > dump_table_new.sql

If you want to copy a table without mysqldump, then I would suggest pt-table-sync in the Percona toolkit.

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.