MySQL备份--xtrabackup与mysqldump工具使用

MySQL备份----xtrabackup与mysqldump工具的使用

一、Xtrabackup8.0:

一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。

备份原理：

Percona XtraBackup的工作原理是在启动时记住日志序列号(LSN)，然后复制数据文件。同时，Percona XtraBackup运行一个后台检测进程，该进程监视mysq redo事务日志文件，并从其中复制更改，记入后台日志文件xtrabackup_log中。之后复制innodb的数据文件一系统表空间文件ibdatax，复制结束后，将执行flush tables with readlock,然后复制.frm MYI MYD等文件，最后执行unlock tables,最终停止xtrabackup_log

Xtrabackup（centos/redhat）安装:

方法一（官方推荐）：
1.下载相应的rpm安装包
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-8.0.4/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.4-1.el7.x86_6 4.rpm

2.安装已下载的rpm安装包
yum localinstall percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm

3.查看是否已安装成功
rpm -qa | grep -i xtrabackup
相关文件查询
rpm -ql percona-xtrabackup-80

4.创建备份目录

5.修改mysql配置文件
开启bin-log日志，添加或修改数据库目录

方法二（官方推荐）：

1. 安装Percona yum存储库
   yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

2. 安装Percona XtraBackup
   yum install percona-xtrabackup-80

方法三：

1. 下载二进制包
   wget
   https://www.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0-7/binary/tarball/percona-xtrabackup-8.0.7-Linux-x86_64.libgcrypt20.tar.gz

2. 解压
   tar zxvf percona-xtrabackup-8.0.7-Linux-x86_64.libgcrypt20.tar.gz
   mv percona-xtrabackup-8.0.7-Linux-x86_64 xtrabackup
   cp -r xtrabackup /usr/local/xtrabackup

3. 添加环境变量
   echo "export PATH=$PATH:/usr/local/xtrabackup/bin" >
   /etc/profile.d/xtrabackup.sh

4. 让环境变量生效
   source /etc/profile.d/xtrabackup.sh

Xtrabackup优点：

1. 备份速度快，物理备份可靠
2. 备份过程不会打断正在执行的事务（无需锁表）
3. 能够基于压缩等功能节约磁盘空间和流量
4. 自动备份校验
5. 还原速度快(不需要执行任何的mysql语句，不需要构建索引，innoDB表无需完全缓存到内存)
6. 可以流传将备份传输到另外一台机器上
7. 在不增加服务器负载的情况备份数据

Xtrabackup缺点：

1. 文件大
2. 不总是可以跨平台、操作系统和MySQL版本

注意新版本8.0：

（1）移除了innobackupex命令；
（2）Percona XtraBackup 8.0 支持MySQL8.0的版本；
（3）mysql8.0之前的版本需要使用xtrabackup2.4备份和恢复.

Xtrabackup权限：

Xtrabackup一个以完全备份所需的最低特权创建数据库用户:
mysql> CREATE USER ‘bkpuser'@'localhost' IDENTIFIED BY 's3cr%T';
mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON . TO 'bkpuser'@'localhost';
mysql> GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;

注意：

一旦连接到服务器，在服务器执行备份你对datadir需要READ和EXECUTE的文件系统级权限。
如果有多个服务器实例，则必须指定正确的连接参数（port, socket, host），以便xtrabackup与正确的服务器通信。

Xtrabackup全量备份：

xtrabackup --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password=xxxyyy --port=3306 --backup --target-dir=/backup/full/

选项优化：

--rsync
使用rsync实用程序优化本地文件传输。指定此选项后，xtrabackup将使用rsync复制所有非InnoDB文件，而不是为每个文件生成单独的cp，这对于具有大量数据库或表的服务器而言可以更快。此选项不能与--stream一起使用。
--use-memory
此选项会影响分配多少内存以使用进行备份
--parallel=#
此选项指定创建备份时用于同时复制多个数据文件的线程数。默认值为1（即无并发传输）。

Xtrabackup全量恢复：

1. 在恢复备份之前，datadir必须为空。同样重要的是，在执行恢复之前，MySQL服务器需要关闭。不能还原到正在运行的mysqld实例的datadir(导入部分备份时除外)。

2. 准备一个全量备份，主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使用得数据文件处于一致性状态。
   xtrabackup --defaults-file=/etc/my.cnf --user=root --password=xxxyyy --prepare （--apply-log-only）--target-dir=/backup/full/

3. 当数据恢复至数据目录以后，您应该检查恢复的文件是否具有正确的所有权和权限。由于文件属性将被保留，在大多数情况下，在启动数据库服务器之前，您需要将文件的所有权更改为mysql，因为它们将由创建备份的用户拥有:
   xtrabackup --defaults-file=/etc/my.cnf --user=root --password=xxxyyy --copy-back --target-dir=/backup/full/
   chown -R mysql:mysql /var/lib/mysql

4. 重新启动mysql服务

注意：

如果您打算将备份作为进一步增量备份的基础，那么在准备备份时应该使用—apply-log-only选项，否则将无法对其应用增量备份

Xtrabackup参数介绍：

--defaults-file
指定mysql配置文件：只能从给定的文件中读取默认选项。 且必须作为命令行上的第一个选项；必须是一个真实的文件，它不能是一个符号链接。

--target-dir
指定备份数据目录

--prepare
数据文件在准备好之前不是时间点一致的，因为它们是在程序运行的不同时间复制的，并且在此过程中可能已经被更改,此步骤使文件在同一时刻完全一致

--backup
做一个备份，放入--target-dir

--copy-back
拷贝先前备份所有文件到它们的原始路径。但原路径下不能有任何文件或目录，除非指定 --force-non-empty-directories 选项。

指定数据库备份：

xtrabackup --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password=xxxyyy --port=3306 --databases=”xxx” --backup --target-dir=/part/single

指定数据库恢复：

prepare部分备份的过程类似于导出表的过程，要使用--export选项进行，还原部分备份的过程跟导入表的过程相同。当然，也可以通过直接复制prepared状态的备份直接至数据目录中实现还原，不要此时要求数据目录处于一致状态。

1. xtrabackup --defaults-file=/etc/my.cnf --prepare --export --target-dir=/part/single
2. 创建好对应的数据库和表结构
3. 丢弃表空间
   alter table dbname.tbname discard tablespace
4. 将导出的表文件复制到目标服务器的对应数据库目录的子目录中
   cp /part/single/dbname/*.ibd /var/bin/mysql/dbname/
5. 将文件重新授权
   chown -R mysql:mysql /var/lib/mysql
6. 加载表空间
   alter table dbname.tbname import tablespace;

--export
创建导出表所需的文件

注意：

要将innodb_file_per_table开启，数据要是innodb存储引擎，如果是delete数据的话，datadir目录下还是会有.ibd、.frm文件，如果是drop的数据，那么我们的手动执行要恢复表的建表语句，.cfg文件是用来和数据字典进行匹配的。

Xtrabackup增量备份：

增量备份主要是通过拷贝innodb中有变更的页(指的是LSN大于xtrabackup_checkpoints中的LSN号)。文件xtrabackup_checkpoints会记录备份完成时检查点的LSN，增量备份是基于全备的，第一次增量备份的数据是基于上一次全备，之后的每一次增备都是基于上一次的增备，最终达到一致性的增备，增备的过程中，和全备很类似，区别在于第二步

第一次增量备份：

xtrabackup --defaults-file=/etc/my.cnf --host=127.0.0.1 --port=3306 --user=root --password=123456 --backup --target-dir=/incr/incr1
--incremental-basedir=/data/backup/

第二次增量备份：

xtrabackup --defaults-file=/etc/my.cnf --host=127.0.0.1 --port=3306 --user=root --password=123456 --backup --target-dir=/part/incr2
--incremental-basedir=/incr/incr1

注意：

需要注意的是，增量备份仅能应用于InooDB或XtraDB表，对于MyISAM表，增量与全备相同.

Xtrabackup增量恢复：

1. 准备完全备份,确保全备数据目录数据的一致性
   xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup

2. 恢复增量备份到完全备份(开始恢复的增量备份要添加--apply-log-only参数，到最后一次的增量备份要去掉--apply-log-only)，即使--apply-log-only在最后一次的增量备份中使用了，备份仍将保持一致，但在这种情况下，服务器将执行回滚阶段。
   xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/ --incremental-dir=/incr/incr1
   xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/ --incremental-dir=/incr/incr2

3. 最终数据会在/data/backup/上，对应用了增量备份的新备份进行恢复
   xtrabackup --user=root --password=xxxyyy --copy-back --target-dir=/data/backup/
   chown -R mysql:mysql /var/lib/mysql

注意：

--prepare增量备份的步骤与完全备份的步骤不同。在完全备份中，执行两种类型的操作以使数据库保持一致：已提交的事务相对于数据文件从日志文件中重放，未提交的事务被回滚。在准备增量备份时，必须跳过未提交事务的回滚，因为在备份时未提交的事务可能正在进行中，并且很有可能将在下一个增量备份中提交。您应该使用该 --apply-log-only选项来防止回滚阶段。

Xtrabackup参数介绍：

--apply-log-only
此选项只导致在准备备份时执行重做阶段。这对于增量备份非常重要。

--target-dir
备份：增量备份目录
恢复：在准备增量备份时，这是增量备份与完整备份相结合以创建新的完整备份的目录。

--incremental-basedir
在创建增量备份时，此目录包含完整备份，即增量备份的基本数据集。

—apply-log-only
防止事务回滚阶段

备份目录各文件说明：

(1)xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息。
每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
(3)xtrabackup_info —— 记录的是你备份的时候的一些参数，脚本版本，数据库版本，备份时间，binlog，是否压缩，备份增量还是全量等信息。
(4)backup-my.cnf —— 包含了my.cnf中备份时需要的信息。
(5)xtrabackup_logfile——记录了备份过程中的日志，在对数据进行prepare时需要通过日志将数据还原成一致的可用的数据。
(6)其他一些数据库文件信息
有时您可以看到to_lsn （最后一个检查点LSN）和last_lsn（最后一个复制的LSN）之间存在差异，这意味着在备份过程中服务器上有一些流量

xtrabackup全备份过程:

1. 实时备份redo log

• xtrabackup开启xtrabackup_log监控线程
• 记录开始的LSN，并实时监测redo log文件的变化
• 将备份过程中新写入到事务日志中的日志页拷贝至xtrabackup_logfile文件中；

2. 拷贝innodb文件

• 开启redo log备份后，同时开启xtrabackup拷贝线程，开始拷贝innodb文件。
• 拷贝顺序：
• ibdata1, sys_config.ibd, mysql.ibd,以及其他datadir目录的ibd文件
• 数据库目录下的ibd，test库、employees、sakila等
• undo文件

3. 复制innodb文件结束后，LOCK TABLES FOR BACKUP

• 锁定非innodb表

4. 拷贝非innodb文件

• 拷贝mysql、p_s和其他库下的.frm,.MRG,.MYD,.MYI,.ARM,.ARZ,.CSM,.CSV,.sdi 和.par等类型的文件。

5. 获取LSN和binary log position

• 此时文件备份结束，需要获取LSN和binlog位置
• LOCK BINLOG FOR BACKUP
• 用于阻止所有可能更改binlog位置的操作
• 然后完成复制REDO日志文件并获取binlog坐标
• 从p_s.log_status中获取信息
• binlog记录到xtrabackup_binlog_info文件中
• LSN记录到xtrabackup_checkpoints

6. FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS

• 关闭并重新打开，已安装存储引擎的任何可刷新日志，不记录到binlog。
• 这会导致InnoDB将其日志刷新到磁盘。

7. 停止xtrabackup_log线程
8. 解锁tables、binlog
9. 备份其他信息

• Copying ib_buffer_pool
• Writing backup-my.cnf
• Writing xtrabackup_info

10. binlog位置将打印到STDERR，如果一切正常，xtrabackup将退出返回0。

二、mysqldump:

备份原理：

导出SQLy语句文件

mysqldump优点：

1. 恢复简单，可以使用管道将他们输入到mysql
2. 与存储引擎无关，因为是从MySQL服务器中提取数据而生成的，所以消除了底层数据存储的不同
3. 有助于避免数据损坏。若磁盘驱动器有故障而要复制原始文件时，此时将得到一个损坏的备份

mysqldump缺点：

1. 必须有数据库服务器完成逻辑工作，需要更多地cpu周期
2. 逻辑备份还原速度慢：需要MySQL加载和解释语句、转化存储格式、重建引擎

mysqldump全量备份：

mysqldump命令必须带上--flush-logs选项以生成新的二进制日志文件
mysqldump -h127.0.0.1 -P3306 -uroot -p --single-transaction --flush-logs --master-data=2 --all-database > /backup/all.sql

参数介绍

对于MyISAM将–single-transaction替换为–lock-all-tables
--flush-logs为结束当前日志，生成新日志文件；
--master-data=2 选项将会在输出SQL中记录下完全备份后新日志文件的名称；以及其开始的pos位置
--all-database 所有数据库
--master-data=[0|1|2]
0 : 不记录
1 : 记录为CHANGE MASTER语句
2 : 记录为注释的CHANGE MASTER语句
--single-transaction 该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB

mysqldump全量恢复：

mysql -h127.0.0.1 -P3306 -uroot -p < /backup/all.sql

mysqldump指定库备份：

mysqldump -h127.0.0.1 -P3306 -uroot -p --single-transaction --flush-logs --master-data=2 dbname > /backup/part.sql

mysqldump指定库恢复：

mysql -h127.0.0.1 -P3306 -uroot -pxxxx < /backup/part.sql

mysqldump增量备份：

增量备份的数据是从上次全量备份之后更新的数据，对mysql来说binlog日志文件存储着增量备份的数据。binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息。

mysqladmin -uroot -pxxxx flush-logs

注意：

执行增量备份的前提条件是MySQL打开binlog日志功能，在mysql配置文件my.cnf中加入log-bin=mysql-bin

mysqldump增量恢复：
mysqlbinlog mysql-bin.0000xx | mysql -uroot -p

mysqldump数据恢复指定的时间点或位置：
--start-datetime=YYYY-mm-dd HH:MM:SS
--stop-datetime=YYYY-mm-dd HH:MM:SS
上边一组参数用于指定恢复开始时间点和结束时间点，可以一起或单独给出

--start-position=xxxxx
--stop-position=xxxxx
上边一组参数用于指定恢复开始位置和结束位置，可以一起或单独给出

两组参数也可以结合使用

三、mysqldump与xtrabackup对比

四、全量备份与恢复性能测试对比：

工具	mysqldump	xtrabackup
数据量	1G左右	1G左右
全备时间	平均1m15s	平均20s
恢复全备时间	平均8m	平均18s
占用空间	617MB	930MB

工具	mysqldump	xtrabackup
数据量	5G左右	5G左右
全备时间	平均3m50s	平均1m10s
恢复全备时间	平均45m	平均1m5s
占用空间	3.3GB	4G

五、备份时期：

一周做一次全量备份，其余时间增量备份或者一个月做一次全量备份，一周一次增量备份，依靠系统定时任务，在业务压力比较小的时间点凌晨(0--5点)，定时执行备份脚本

六、备份文件维护

若全量备份，可以天为单位进行删除
若全量+增量备份，定期删除(建议周期一个月)已过时备份文件