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