mysqldump

mysqldump is a command used to backup MySQL databases.

To take backup, run

mysqldump --opt DB_NAME > DB_NAME.sql

To backup with triggers, routines, and events

mysqldump --opt --triggers --routines --events --single-transaction DB_NAME > DB_NAME.sql

–opt combines many options. It is same as adding –add-drop-table, –add-locks, –create-options, –disable-keys, –extended-insert, –lock-tables, –quick, and –set-charset.

–extended-insert option will group together all INSERT operations for a table. This makes the backup file smaller and makes restoration faster. I restored a mysqldump bakcup, it take me 2 hours to restore. Same database backup with –extended-insert option take only 10 minutes to restore. If you want a separate INSERT for each row, then use –skip-extended-insert or –complete-insert option

Backup All Databases

To backup all databases, run

mysqldump --events --routines --triggers --all-databases | gzip -9 > "$(date +%F-%H%m%S)"-mysql-backup.sql.gz

To backup MySQL databases into separate files, run

mkdir /root/mysqlbackup/
for DB in $(mysql -Be "show databases" | grep -v 'row\|information_schema\|Database\|performance_schema') ; do
    mysqldump --opt --events --routines --triggers ${DB}  > /root/mysqlbackup/${DB}.sql
done

If you need to compress the sql file. use

mkdir /root/mysqlbackup/
for DB in $(mysql -Be "show databases" | grep -v 'row\|information_schema\|Database\|performance_schema') ; do
    mysqldump --skip-lock-tables --events --routines --triggers ${DB} | gzip -9 > /root/mysqlbackup/"$(date +%F-%H%m%S)"-${DB}.sql.gz
done

Backup Database Structure only

mysqldump --no-data DB_NAME > DB_NAME.sql

Backup Only routines

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt DB_NAME > DB_NAME-routines.sql

Related Posts

• How to Backup a Large MySQL Database Table by Table
• mysqldump errno: 24 Can’t open file when using LOCK TABLES
• MySQL Backup using mysqldump
• Extract Backup of one database from mysqldump all databases
• mysqldump packet bigger than max_allowed_packet
• Export MySQL Database table as an XML Document
• mysqldump Lost connection to MySQL server during query when dumping table