Use mysqldump to backup table with a new table name

Question

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.

Answer 1

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 !!!

Answer 2

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.