Using mysqldump

Overview

Quick references for mysqldump tasks.

Common tasks

Dump everything from a database

Includes both structure and data.

$ mysqldump --user[=username] --password[=password] database [tables]

Add -t or --no-create-info option to dump only data.

Add -d or --no-data option to dump only table structure.

Workaround to select tables with a wildcard

Wildcards aren't supported by mysqldump. Each table has to be listed.

Put the table names in a file with:

$ mysql -u [username] -p [password] -N information_schema -e "SELECT table_name FROM tables WHERE table_schema LIKE '[source_schema]' AND table_name LIKE '[table_pattern_]%'" > tables.txt

Edit the file to put all the table names into a single line.

• Edit the file with vim.
• In vim, J will join the current line with the next line. [n]J joins the next n lines together, e.g. 3J.

Then the file can be used to specify the table names:

$ mysqldump --user=[username] --password=[password] [source_schema] `cat tables.txt` > dump_file.sql

Optionally, edit dump_file.sql to change table names:

Search & replace with vim:

:s\foo\bar\g will change occurrences of "foo" to "bar" on the current line only.

:%s\foo\bar\g will change all occurrences of "foo" to "bar"

:%s\foo\bar\gc will change all occurrences of "foo" to "bar" with confirmation for each occurrence.

Connect to the schema and import the data from the dump file:

$ mysql --user=[username] --password=[password] [destination_schema]

mysql> source dump_file.sql

Exporting and Importing MySQL Data documents some alternative methods for importing and exporting.

Dump specific rows from a table

mysqldump my_db_name my_table_name --where="id > 500" > my_backup.sql