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.

0

2 Answers 2

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

1
  • The sed command won't catch DROP TABLE IF EXISTS ...; line, because it doesn't have space after the ending tick. You can remove the space at the end of the sed command, but I would still be extra careful with this solution.
    – iTK98
    Apr 5, 2020 at 9:40
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.

1
  • The spaces inside your sed statement will cause it not to match the table names as intended, as the backticks do not always have a space before/after them.
    – Brandon
    Feb 5, 2023 at 6:12

Your Answer

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

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