mysqldump
mysqldump is a command used to backup MySQL databases.
To take backup, run
|
1 |
mysqldump --opt DB_NAME > DB_NAME.sql |
To backup with triggers, routines, and events
|
1 |
mysqldump --opt --triggers --routines --events 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
|
1 |
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
|
1 2 3 4 |
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} > /root/mysqlbackup/"$(date +%F-%H%m%S)"-${DB}.sql done |
If you need to compress the sql file. use
|
1 2 3 4 |
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
|
1 |
mysqldump --no-data DB_NAME > DB_NAME.sql |
Backup Only routines
|
1 |
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt DB_NAME > DB_NAME-routines.sql |
Related Posts
- 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