Using mysqldump: Difference between revisions

Revision as of 15:40, 4 March 2013

Quick references for mysqldump tasks.

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.

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

Put the table names in a file with:

$ mysql -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

@@ Line 16: / Line 16: @@
 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:
+<syntaxhighlight lang="bash">
+$ mysql -N information_schema -e "select table_name from tables where table_name like 'bak_%'" > tables.txt
+</syntaxhighlight>
+Edit the file to put all the table names into a single line.
+* Edit the file with `vim`.
+* In `vim`, <kbd>J</kbd> will join the current line with the next line. <kbd>[n]J</kbd> joins the next ''n'' lines together, e.g. <kbd>3J</kbd>.
+Then the file can be used to specify the table names:
+<syntaxhighlight lang="bash">
+$ mysqldump dbname `cat tables.txt` > dump_file.sql
+</syntaxhighlight>
 [[Category:MySQL]] [[Category:Web Development]]