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_name like 'bak_%'" > 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 dbname `cat tables.txt` > dump_file.sql