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