MySQL Dump
MySqlDump lets you backup or dump your database(s)Backup a single database
$ mysqldump \ --single-transaction \ --add-drop-table \ --compatible=ansi --complete-insert \ --skip-lock-tables --skip-extended-insert --skip-quote-names \ -u myuser -p -h mydbhost mydatabase > mydbbackup.sql Password:
Backup all databases
$ mysqldump \ --single-transaction \ --add-drop-table \ --compatible=ansi --complete-insert \ --skip-lock-tables --skip-extended-insert --skip-quote-names \ --all-databases \ --user=myuser --password > mybackup.sql Password:
remotehost
If you db if not on localhost you'll need to specify a remote hosthost
-h mydbip
host
--host mydbhost
Backup a single table
$ mysqldump --user=myuser --password \ --single-transaction \ --add-drop-table \ --complete-insert --compatible=ansi \ --skip-lock-tables --skip-extended-insert --skip-quote-names \ mydatabase mytable > mybackup.sql Password:
Backup a single table (with where clause)
mysqldump --user=myuser --password \
--add-drop-table \
--complete-insert --compatible=ansi \
--skip-lock-tables --skip-extended-insert --skip-quote-names \
mydatabase mytable -w "mycolumn in ('IC','GG','GJ','IM')" > mybackup.sql
Password:Backup multiple tables (with wildcards !)
$ mysqldump --user=myuser --password \ --single-transaction \ --add-drop-table \ --complete-insert --compatible=ansi \ --skip-lock-tables --skip-extended-insert --skip-quote-names \ mydatabase $(echo "use mydatabase ; show tables like 'mytblprefix%'" | mysql --skip-column-names) > mybackup.sql Password:
Backup database into a single file
structure only
$ mysqldump -h myhost -u myuser -p \ --skip-add-locks --skip-quote-names --no-data \ mydbname > mydb.sql Password:
structure with data
$ mysqldump -h myhost -u myuser -p \ --complete-insert --compatible=ansi \ --skip-add-locks --skip-quote-names --skip-extended-insert \ mydbname > mydb.sql Password:
Backup structure into multiple files
Backup structure of all tables to separate .sql files ready for adding to subversion$ mysqldump -h myhost -u myuser -p \ --no-data --skip-lock-tables --compact \ --tab=mydir mydb Password:
Backup table into XML
Backup table to XML$ mysqldump -h myhost -u myuser -p --skip-lock-tables --compact \ --xml mydb mytable Password:
Also see
MySqlRestore
REFERRERS
MySqlAdministration
MySqlBackup
MySqlDesignQuery
MySqlRestore
MySqlUtilities
