Using mysqldump: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
|||
| (7 intermediate revisions by the same user not shown) | |||
| Line 23: | Line 23: | ||
Put the table names in a file with: | Put the table names in a file with: | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash" enclose="div"> | ||
$ mysql -u [username] -p [password] -N information_schema -e " | $ 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 | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 35: | Line 35: | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
$ mysqldump | $ mysqldump --user=[username] --password=[password] [source_schema] `cat tables.txt` > dump_file.sql | ||
</syntaxhighlight> | |||
Optionally, edit `dump_file.sql` to change table names: | |||
:Search & replace with `vim`: | |||
:<kbd>:s\foo\bar\g</kbd> will change occurrences of "foo" to "bar" on the current line only. | |||
:<kbd>:%s\foo\bar\g</kbd> will change all occurrences of "foo" to "bar" | |||
:<kbd>:%s\foo\bar\gc</kbd> 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: | |||
<syntaxhighlight lang="bash"> | |||
$ mysql --user=[username] --password=[password] [destination_schema] | |||
mysql> source dump_file.sql | |||
</syntaxhighlight> | |||
[http://zetcode.com/databases/mysqltutorial/exportimport/ Exporting and Importing MySQL Data] documents some alternative methods for importing and exporting. | |||
== Dump specific rows from a table == | |||
<syntaxhighlight lang="sql"> | |||
mysqldump my_db_name my_table_name --where="id > 500" > my_backup.sql | |||
</syntaxhighlight> | </syntaxhighlight> | ||
[[Category:MySQL]] [[Category:Web Development]] | [[Category:MySQL]][[Category:MariaDB]][[Category:Databases]][[Category:Web Development]] | ||
Latest revision as of 18:58, 24 October 2024
Overview[edit]
Quick references for mysqldump tasks.
Common tasks[edit]
Dump everything from a database[edit]
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[edit]
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[edit]
mysqldump my_db_name my_table_name --where="id > 500" > my_backup.sql