Mysql 备份方案和总结

Mysql的备份，其中有一种方法就是对Mysql的二进制日志进行备份。客户端对Mysql所有的增删改操作，都会被记录到二进制日志当中。

一、binlog 二进制日志通常作为备份的重要资源

1.binlog日志内容
1）引起mysql服务器改变的任何操作(增  删  改 没查)。
2）复制功能依赖于此日志。Mysql主从
3）slave服务器通过复制master服务器的二进制日志完成主从复制，在执行之前保存于中继日志(relay log)中。 
4）slave服务器通常可以关闭二进制日志以提升性能。

2.binlog日志文件的文件表现形式
1）默认在安装目录下，存在mysql-bin.00001, mysql-bin.00002的二进制文件（binlog日志文件名依据my.cnf配置中的log-bin参数后面的设置为准）

 

什么时候二进制日志会重新生成？

1. 重启mysql服务    systemctl   restart  mysqld
2. mysql> flush logs;
3. 根据二进制文件的大小来切割

）还有mysql-bin.index用来记录被mysql管理的二进制文件列表
3）如果需要删除二进制日志时，切勿直接删除二进制文件，这样会使得mysql管理混乱。

 3.binlog日志文件查看相关mysql命令
 

1）SHOW MASTER STATUS ; 查看正在使用的二进制文件 
MariaDB [(none)]> SHOW MASTER STATUS ; 
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 245 | | |

2）FLUSH LOGS; 手动滚动二进制日志
MariaDB [(none)]> FLUSH LOGS; 
MariaDB [(none)]> SHOW MASTER STATUS ; 
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 245 | | |
+------------------+----------+--------------+------------------+
滚动以后，mysql重新创建一个新的日志mysql-bin.000004

3）SHOW BINARY LOGS 显示所有的二进制日志文件
MariaDB [(none)]> SHOW BINARY LOGS ; 
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 30373 |
| mysql-bin.000002 | 1038814 |
| mysql-bin.000003 | 288 |
| mysql-bin.000004 | 245 |

4.MySQL二进制文件读取工具mysqlbinlog 

命令格式：mysqlbinlog [参数] log-files 
有以下四种参数选择： 
--start-datetime       #用来指定二进制日志的起始日期
--stop-datetime        #用来指定二进制日志的结束日期
--start-position         #用来指定二进制日志的起始位置
--stop-position          #用来指定二进制日志的结束位置
Mysqlbinlog可以完成mysql的增量备份
[root@test-huanqiu ~]# mysqlbinlog --start-position 30225 --stop-position 30254 mysql-bin.000001 
截取一下结果： 
# at 30225
#151130 12:43:35 server id 1 end_log_pos 30354 Querythread_id=1exec_time=0error_code=0
use `mysql`/*!*/;
SET TIMESTAMP=1448858615/*!*/;
SET @@session.pseudo_thread_id=1/*!*/
根据以上截取结果第二行，进行解释二进制日志内容 
1）时间点： 151130 12:43:35
2）服务器ID： server id 1
服务器ID主要用于标记日志产生的服务器，主要用于双主模型中，互为主从，确保二进制文件不会被相互循环复制
3）记录类型： Query 
4) 线程号： thread_id = 1 
5) 语句的时间戳和写入二进制日志文件的时间差； exec_time=0 
6) 事件内容
7）事件位置 #at 30225 
8) 错误代码 error_code=0 
9) 事件结束位置 end_log_pos也就是下一事件开始的位置

1. 二进制日志格式
   由bin_log_format={statement|row|mixed}定义 
   1）statement: 基于语句，记录生成数据的语句 
   缺点在于如果当时插入信息为函数生成，有可能不同时间点执行结果不一样，
   例如： INSERT INTO t1 VALUE (CURRENT_DATE());
   2）row: 基于行数据  缺点在于，有时候数据量会过大
   3）mixed： 混合模式，由mysql自行决定何时使用statement, 何时使用row 模式

查看当前二进制日志记录格式

mysql> show variables like 'binlog_format';

mysql> set session binlog_format=statement;

5.7之前是statement，5.7之后是基于行的

建议：切勿将二进制日志与数据文件放在一同设备；可以将binlog日志实时备份到远程设备上，以防出现机器故障进行数据恢复；

3.备份类型： 
（1）根据备份时，mysql服务是否在线
1）冷备(cold backup)：  停掉mysql这个服务，读写操作都不能进行 
2）温备份： 全局施加共享锁，只能读不能写
3）热备(hot backup)：不停掉mysql这个服务，读写照样进行 

（2）根据备份时的数据集分类 
1）完全备份(full backup)
2）增量备份(partial backup)
（3）根据备份时的接口
1）物理备份（physical backup）：直接复制数据文件 ，打包归档，就是对mysql的数据存放路径进行打包
特点： 
不需要额外工具，直接归档命令即可，但是跨平台能力比较差；如果数据量超过几十个G，则适用于物理备份

2）逻辑备份(logical backup)： 把数据抽取出来保存在sql脚本中，mysqldump就属于逻辑备份
特点： 
可以使用文本编辑器编辑；导入方便，直接读取sql语句即可；逻辑备份恢复时间慢，占据空间大；无法保证浮点数的精度；恢复完数据库后需要重建索引。

（4）根据备份整个数据还是变化数据 
1） 全量备份 full backup

完全备份：每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。

优点：备份与恢复操作简单方便

缺点：数据存在大量的重复；占用大量的空间；备份与恢复时间长

2） 增量备份 incremental backup 
只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份起到最后一次增量备份依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。
3） 差异备份 differential backup 
备份那些自从上次完全备份之后被修改过的所有文件，备份的时间起点是从上次完整备份起，备份数据量越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。

（5）备份策略，需要考虑因素如下
备份方式 
备份实践
备份成本
锁时间
时长
性能开销
恢复成本
恢复时长
所能够容忍丢失的数据量
（6）备份内容 
1）数据库中的数据
2）配置文件 
3）mysql中的代码： 存储过程，存储函数，触发器
4）OS 相关的配置文件，crontab 中的备份策略脚本
5）如果是主从复制的场景中： 跟复制相关的信息
6）二进制日志文件需要定期备份，一旦发现二进制文件出现问题，需马上对数据进行完全备份

(7)Mysql最常用的三种备份工具：
1）mysqldump：
通常为小数据情况下的备份
innodb： 热备，温备
MyISAM, Aria: 温备 
单线程备份恢复比较慢
2）Xtrabackup（通常用innobackupex工具）：
备份mysql大数据
InnoDB热备，增量备份；
MyISAM温备，不支持增量，只有完全备份 
属于物理备份，速度快；
3）lvm-snapshot：
接近于热备的工具：因为要先请求全局锁，而后创建快照，并在创建快照完成后释放全局锁；
使用cp、tar等工具进行物理备份；
备份和恢复速度较快；
很难实现增量备份，并且请求全局需要等待一段时间，在繁忙的服务器上尤其如此；

两者对比如下

 

Percona XtraBackup（简称PXB）是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具，支持 MySQl（Oracle）、Percona Server 和 MariaDB，并且全部开源

工具集

软件包安装完后一共有4个可执行文件，如下：

 

其中最主要的是 innobackupex 和 xtrabackup，前者是一个 perl 脚本，后者是 C/C++ 编译的二进制。

xtrabackup 是用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 mysqld server 没有交互；

innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，还会和 mysqld server 发送命令进行交互，如加读锁（FTWRL）、获取位点（SHOW SLAVE STATUS）等。简单来说，innobackupex 在 xtrabackup 之上做了一层封装。

一般情况下，我们是希望能备份 MyISAM 表的，虽然我们可能自己不用 MyISAM 表，但是 mysql 库下的系统表是 MyISAM 的，因此备份基本都通过 innobackupex 命令进行；另外一个原因是我们可能需要保存位点信息。

另外2个工具相对小众些，xbcrypt 是加解密用的；xbstream 类似于tar，是 Percona 自己实现的一种支持并发写的流文件格式。两都在备份和解压时都会用到（如果备份用了加密和并发）

原理

通信方式

2个工具之间的交互和协调是通过控制文件的创建和删除来实现的，主要文件有：

• xtrabackup_suspended_1
• xtrabackup_suspended_2
• xtrabackup_log_copied

举个栗子，我们来看备份时 xtrabackup_suspended_2 是怎么来协调2个工具进程的

1. innobackupex 在启动 xtrabackup 进程后，会一直等 xtrabackup 备份完 InnoDB 文件，方式就是等待 xtrabackup_suspended_2 这个文件被创建出来；
2. xtrabackup 在备完 InnoDB 数据后，就在指定目录下创建出这个文件，然后等这个文件被 innobackupex 删除；
3. innobackupex 检测到文件 xtrabackup_suspended_2 被创建出来后，就继续往下走；
4. innobackupex 在备份完非 InnoDB 表后，删除 xtrabackup_suspended_2 这个文件，这样就通知 xtrabackup 可以继续了，然后等 xtrabackup_log_copied 被创建；
5. xtrabackup 检测到 xtrabackup_suspended_2 文件删除后，就可以继续往下了。

备份过程

整个备份过程如下图：

Xtrabackup   innodb(redo文件（日志）   +   Ibd 文件(数据和索引))

Redo文件:是 存储引擎层(innodb)生成的日志，主要为了保证数据的可靠性；

Ibd文件：Inodb引擎开启的表空间，用来存储表的数据和索引

 

增量备份

PXB 是支持增量备份的，但是只能对 InnoDB 做增量，InnoDB 每个 page 有个 LSN 号，LSN 是全局递增的，page 被更改时会记录当前的 LSN 号，page中的 LSN 越大，说明当前page越新（最近被更新）。每次备份会记录当前备份到的LSN（xtrabackup_checkpoints 文件中），增量备份就是只拷贝LSN大于上次备份的page，比上次备份小的跳过，每个 ibd 文件最终备份出来的是增量 delta 文件。

MyISAM 是没有增量的机制的，每次增量备份都是全部拷贝的。

增量备份过程和全量备份一样，只是在 ibd 文件拷贝上有不同。

二、innobackupex针对mysql数据库的备份环境部署

安装xtrabackup

[root@localhost ~]# tar xf percona-xtrabackup2.4.5-Linux-x86-_64.tar.gz -C /usr/src/
[root@localhost ~]# cd /usr/src/percona-xtrabackup-2.4.5-Linux-x86_64/
[root@localhost percona-xtrabackup-2.4.5-Linux-x86_64]# cp bin/* /usr/bin/
[root@localhost ~]# yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5
[root@localhost ~]# rpm -ivh percona-toolkit-2.2.19-1.noarch.rpm

Xtrabackup完全备份+binlog增量备份

Xtrabackup中主要包含两个工具：

xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；

innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。

常用选项:  

   --host     指定主机

   --user     指定用户名

   --password    指定密码

   --port     指定端口

   --databases     指定数据库

   --incremental    创建增量备份

   --incremental-basedir   指定包含完全备份的目录

   --incremental-dir      指定包含增量备份的目录   

   --apply-log        对备份进行预处理操作             

     一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

   --redo-only      不回滚未提交事务

   --copy-back     恢复备份目录

1. 完全备份

[root@master backups]# innobackupex --user=root --password=123123  /backups/　　#在mysql上进行全库备份#

语法解释说明：

#--user=root 指定备份用户

#--password=123456  指定备份用户密码

#--host　　指定主机

#/backups　　指定备份目录

2、#查看备份数据

 

 

backup-my.cnf　　#备份用到的配置选项信息文件

xtrabackup_binlog_info　　#mysql服务器当前正在使用的二进制日志文件和此时二进制日志时间的位置信息文件

xtrabackup_checkpoints　　#备份的类型、状态和LSN状态信息文件

xtrabackup_logfile　　　　#备份的日志文件

（2）恢复

[root@localhost ~]# innobackupex --apply-log /backups/2019-11-28_17-13-01/ #合并数据，使数据文件处于一致性的状态


rm -rf /usr/local/mysql/data/*　　#删除原有的数据
[root@localhost ~]# innobackupex --copy-back /backups/2019-11-28_17-13-01/ 

 

修改属主属组

[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql/data/

总结全库备份与恢复三步曲：

a.　innobackupex全量备份，并指定备份目录路径；

b.　在恢复前，需要使用--apply-log参数先进行合并数据文件，确保数据的一致性要求；

c.　恢复时，直接使用--copy-back参数进行恢复，需要注意的是，在my.cnf中要指定数据文件目录的路径。

xtrabackup增量备份与恢复

使用innobackupex进行增量备份，每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。在进行增量备份时，首先要进行一次全量备份，第一次增量备份是基于全备的，之后的增量备份都是基于上一次的增量备份的，以此类推。

要实现第一次增量备份，可以使用下面的命令进行

1. 增量备份演示

[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/   #全备数据

[root@master ~]# mysql -uroot -p　　#在master上创建student库并创建testtb表插入若干数据

Enter password: 

mysql> create database student;

Query OK, 1 row affected (0.03 sec)

mysql> use student;

Database changed

mysql> create table testtb(id int);

Query OK, 0 rows affected (0.07 sec)

mysql> insert into testtb values(1),(10),(99);

Query OK, 3 rows affected (0.04 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from testtb;

+------+

| id   |

+------+

|    1 |

|   10 |

|   99 |

+------+3 rows in set (0.00 sec)

mysql> quit;

Bye

#使用innobackupex进行增量备份

[root@master backups]# innobackupex --user=root --password=123456  --incremental /backups/ --incremental-basedir=/backups/2018-07-30_11-01-37/

......180730 13:51:50 Executing UNLOCK TABLES180730 13:51:50 All tables unlocked180730 13:51:50 Backup created in directory '/backups/2018-07-30_13-51-47/'

MySQL binlog position: filename 'mysql-bin.000005', position '664'180730 13:51:50 [00] Writing /backups/2018-07-30_13-51-47/backup-my.cnf180730 13:51:50 [00]        ...done180730 13:51:50 [00] Writing /backups/2018-07-30_13-51-47/xtrabackup_info180730 13:51:50 [00]        ...done

xtrabackup: Transaction log of lsn (3158741) to (3158741) was copied.180730 13:51:50 completed OK!

[root@master backups]# ll　　#查看备份数据

total 0

drwxr-x--- 7 root root 232 Jul 30 11:01 2018-07-30_11-01-37　　#全量备份数据目录

drwxr-x--- 8 root root 273 Jul 30 13:51 2018-07-30_13-51-47　　#增量备份数据目录

[root@master 2018-07-30_11-01-37]# cat xtrabackup_checkpoints #查看全量备份的xtrabackup_checkpoints

backup_type = full-backuped　　#备份类型为全量备份

from_lsn = 0　　#lsn从0开始

to_lsn = 3127097　　#lsn到3127097结束

last_lsn = 3127097

compact = 0

recover_binlog_info = 0

[root@master 2018-07-30_13-51-47]# cat xtrabackup_checkpoints 　　#查看增量备份的xtrabackup_checkpoints

backup_type = incremental　　#备份类型为增量备份

from_lsn = 3127097　　#lsn从3127097开始

to_lsn = 3158741　　  #lsn到啊3158741结束

last_lsn = 3158741　　

compact = 0

recover_binlog_info = 0

1. 增量备份后数据恢复演示

1. 模拟mysql故障，删除数据目录所有数据

[root@localhost ~]# rm -rf /usr/local/mysql/data/*



（2）合并全备数据目录，确保数据的一致性

[root@master ~]# innobackupex --apply-log --redo-only /backups/2019-11-29_14-58-49/

（3）将增量备份数据合并到全备数据目录当中

[root@master ~]# innobackupex --apply-log --redo-only /backups/2019-11-29_14-58-49/ --incremental-dir=/backups/2019-11-29_15-07-21/

[root@master ~]# cat /backups/2019-11-29_14-58-49/xtrabackup_checkpoints 

backup_type = log-applied　　#查看到数据备份类型是增加

from_lsn = 0　　#lsn从0开始

to_lsn = 3158741　　#lsn结束号为最新的lsn

last_lsn = 3158741

compact = 0

recover_binlog_info = 0

（4）恢复数据

[root@master ~]# innobackupex --copy-back /backups/2019-11-29_14-58-49/

[root@master ~]# chown -R mysql.mysql /usr/local/mysql/data　　#更改数据的属主属组

[root@master ~]# mysql -uroot -p -e "show databases;"　　#查看数据是否恢复

总结：

（1）增量备份需要使用参数--incremental指定需要备份到哪个目录，使用incremental-dir指定全备目录；

（2）进行数据备份时，需要使用参数--apply-log redo-only先合并全备数据目录数据，确保全备数据目录数据的一致性；

（3）再将增量备份数据使用参数--incremental-dir合并到全备数据当中；

（4）最后通过最后的全备数据进行恢复数据，注意，如果有多个增量备份，需要逐一合并到全备数据当中，再进行恢复。

#1.  --user=root 指定备份的用户

#2.  --password=root指定备份用户的密码

#3.  --defaults-file=/etc/my.cnf 指定的备份数据的配置文件

#4.  /opt/ 指定备份后的数据保存路径