FSSE.INFO: MySQL Dump

MySQL Dump

MySqlDump lets you backup or dump your database(s)

Backup all databases

$ mysqldump --host mydbhost --user=myuser --password --all-databases  > mybackup.sql
Password:

Backup a single database

$ mysqldump -h mydbip  -u myuser -p --add-drop-table mydatabase > mydbbackup.sql
Password:

Backup a single table

$ mysqldump --user=myuser --password \
--complete-insert --skip-lock-tables --skip-extended-insert \
mydatabase mytable > mybackup.sql
Password:

Backup a single table (with where clause)

mysqldump --user=myuser --password \
--complete-insert --compatible=ansi  --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 \
--skip-extended-insert --skip-quote-names --complete-insert --compatible=ansi \
mydbname
$(echo "use mydbname ; 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 mydbname \
--skip-add-locks --skip-quote-names --compatible=ansi --skip-extended-insert > 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
MySqlDump
MySqlRestore
MySqlUtilities

FSSE.INFO : MySqlDump