MySQL Backup

MySQL Enterprise Backup

To manually flush logs

# mysqladmin -u root -p flush-logs

To list all binary log files.

mysql> SHOW BINARY LOGS;

To display the current binary log file.

mysql> SHOW MASTER STATUS;

Compressed copy of InnoDB data files, ibbackup_logfile, Copy of MyISAM, frm, .mrg files.

$ mysqlbackup --defaults-file=/usr/my.cnf -u dba -p --with-timestamp --compress backup

$ mysqlbackup --defaults-file=/usr/my.cnf -u dba -p --with-timestamp --compress  --backup-image= backup-all.bki backup-to-image

MEB copy back and apply-log

Uncompressed InnoDB data files to datadir, Recreates InnoDB log files, Applies log so InnoDB files are consistent, Restore MyISAM and other flles.

$ mysqlbackup --backup-dir=backup_img --datadir=/data/datadir --innodb_log_group_home_dir=. --innodb_log_file_in_group=8 --innodb_log_size=5242880 --innodb_data_file_path=”ibdata1:12M:autoextend” -backup-image=- copy-back-and-apply-log

Validate – Ensure that checksums are consistent

List the contents of the image

$ mysqlbackup --backup-image=/backup/backup-all.bki list-image

 Extract files from the image

$ mysqlbackup --backup-image=/backup/backup-my.bki extract

Image-to-backup-dir – Extract the full image into a directory

Convert the backup directory into the image

$ mysqlbackup --backup-image=/backup/backup-all.bki --backup-dir=/backup backup-dir-to-image

MySQL Dump

Backup all the databases

# mysqldump -u root -p --all-databases --single-transaction --flush-logs --master-data=2 > dump.sql

--single-transaction will dump InnoDB tables in a consistent state

 --flush-logs will close current logs and open a new one

 --master-data=2 will write binary log coordinates in SQL comment in the dump file

Backup a single database

# mysqldump -u root -p --single-transaction --verbose dbname  > dbname.sql

Backup multiple databases

# mysqldump -u root -p --single-transaction --verbose --databases dbname1 dbname2 > dbname.sql

Backup a specific table

# mysqldump -u root –p dbname t1 > dbname_t1.sql

Restore all the databases

# mysql  -u root -p < dump.sql

Restore a database

# mysql -u root -p dbname < dbname.sql

# mysqlbinlog mysql-bin.000002 mysql-bin.000003 | mysql –u root –p dbname

Restore a table from the specific table backup

# mysql -u root -p dbname < dump_t1.sql

Purge old binary logs

mysql> PURGE MASTER LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);