MySQL: 备份和恢复

[lihongjie0209]

mysqldump

备份

mysqldump -u root -pBoolin999@123 --single-transaction --databases employees > /tmp/backup.sql

恢复

mysql -u root -pBoolin999@123 < employees.sql

原理

逻辑备份, 把数据库中的数据查出来然后转换为insert语句导出, 导出文件较大, 占用数据库资源

[lihongjie0209]

xtrabackup

创建备份用户

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO
       'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;

全量备份

xtrabackup --backup --target-dir=/data/backups/

Preparing a backup

xtrabackup 启动一个innodb进程来进行崩溃恢复流程, 因为我们在备份的时候数据库还在写入, 所以我们备份的数据是不完整的, 通过innodb的崩溃恢复流程读取日志使我们的数据完整

After you made a backup with the xtrabackup --backup option, you’ll first need to prepare it in order to restore it. Data files are not point-in-time consistent until they’ve been prepared, because they were copied at different times as the program ran, and they might have been changed while this was happening. If you try to start InnoDB with these data files, it will detect corruption and crash itself to prevent you from running on damaged data. The xtrabackup --prepare step makes the files perfectly consistent at a single instant in time, so you can run InnoDB on them.

You can run the prepare operation on any machine; it does not need to be on the originating server or the server to which you intend to restore. You can copy the backup to a utility server and prepare it there.

During the prepare operation, xtrabackup boots up a kind of modified InnoDB that’s embedded inside it (the libraries it was linked against). The modifications are necessary to disable InnoDB’s standard safety checks, such as complaining that the log file isn’t the right size, which aren’t appropriate for working with backups. These modifications are only for the xtrabackup binary; you don’t need a modified InnoDB to use xtrabackup for your backups.

The prepare step uses this embedded InnoDB to perform crash recovery on the copied data files, using the copied log file. The prepare step is very simple to use: you simply run xtrabackup --prepare option and tell it which directory to prepare, for example, to prepare the previously taken backup run:

xtrabackup --prepare --target-dir=/data/backups/

恢复

恢复就是把我们备份的文件拷贝到相应的目录中去

 xtrabackup --copy-back --target-dir=/data/backups/

rsync -avrP /data/backup/ /var/lib/mysql/

[lihongjie0209]

增量备份

xtrabackup --backup --target-dir=/data/backups/inc1 \
--incremental-basedir=/data/backups/base

Preparing the Incremental Backups

The xtrabackup --prepare step for incremental backups is not the same as for full backups. In full backups, two types of operations are performed to make the database consistent: committed transactions are replayed from the log file against the data files, and uncommitted transactions are rolled back. You must skip the rollback of uncommitted transactions when preparing an incremental backup, because transactions that were uncommitted at the time of your backup may be in progress, and it’s likely that they will be committed in the next incremental backup. You should use the xtrabackup --apply-log-only option to prevent the rollback phase.

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
 xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc1

xtrabackup --prepare --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc2