MySQL备份系列--备份方案总结性梳理

mysql数据库备份有多么重要已不需过多赘述了，废话不多说！以下总结了mysql数据库的几种备份方案：

一、binlog二进制日志通常作为备份的重要资源，所以再说备份方案之前先总结一下binlog日志~~
1.binlog日志内容
1）引起mysql服务器改变的任何操作。
2）复制功能依赖于此日志。
3）slave服务器通过复制master服务器的二进制日志完成主从复制，在执行之前保存于中继日志(relay log)中。
4）slave服务器通常可以关闭二进制日志以提升性能。

有些时候备份mysql中的数据，不如说备份二进制日志，客户端对mysql所有的增删改操作，都会被记录到二进制日志当中

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

有关二进制日志的还有mysql主从复制的原理：

MySQL主从复制的核心就是 二进制日志，具体的过程如下：

从上图来看，复制分成三步：

1. Master 主库在事务提交时，会把数据变更写在自己的二进制日志文件中。
2. 从库读取主库的二进制日志文件，写入到从库的中继日志。
3. slave将读取中继日志，将此写到自己的库中。

更准确来说是分为三个进程：

主上有dump线程：负责记录所有对主数据库的增删改查操作，都写入到二进制文件，并通知从上的I/O线程来读取二进制日志

从上的I/O线程：线程负责从主上边读取二进制日志文件，并将二进制日志写入中继日志中

从上的sql线程： 线程负责将中继日志中的数据，在从服务器上重新执行

在操作上就是，主服务器修改/etc/my.conf,从服务器上开启中继日志

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 232-1，默认为1 
server_id=1
###开启二进制日志
log-bin=mysql-bin
#是否只读,1 代表只读, 0 代表读写 
read-only=0 
#重启服务
#创建user1用户，并设置密码，该用户可在任意主机连接该MySQL服务
create user 'user1'@'%' identified with mysql_native_password by 'User@123456';
#################从服务器
#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，和主库不一样即可 
server-id=2 

#是否只读,1 代表只读, 0 代表读写 
read-only=1
然后重启mysql





################
上述是8.0.23中的语法。如果mysql是 8.0.23 之前的版本，执行如下SQL：

CHANGE MASTER TO MASTER_HOST='192.168.83.129', MASTER_USER='user1', MASTER_PASSWORD='User@123456', MASTER_LOG_FILE='binlog.000009', MASTER_LOG_POS=661;

查看效果（怎么测试主从复制）：

 正常来讲只要在主上面写入配置查看从上面是否同步即可

 也可以查看从服务器上的I/O

线程和sql线程是否为yes;####yes表示成功

继续回到备份方面

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

1.重启mysql服务

2.mysql>flush logs； ##刷新log文件

3.根据二进制文件大小来切割  

如何正确删除二进制日志文件？

通过日积月累日志文件会一直增多，造成存储空间的不足等问题，所以要通过删除日志文件来缓解

 二进制日志有一个管理日志的文件：mysql-bin.index

在/etc/my.cof
server-id = 1
####开启二进制日志
log-bin = mysql-bin
然后重启服务
###查找生成的日志文件所在目录
find / -name mysql-bin.000*

当有多个日志的时候就需要删除

mysql -uroot -p密码 -e'flush logs;'
cd到日志文件目录/mysql/data
删除所有二进制文件
rm -rf mysql-bin.00*
####全部删除  !!!!!!!如果不删除，重启服务会报错的
[root@MiWiFi-R4CM-srv data]# vim mysql-bin.index 
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006

#####也有命令可以一键清除
mysql -uroot -p123123 -e"reset master;"
默认只剩下000001

在mysql里查看二进制日志

SHOW BINARY LOGS;
查看  所有   日志

mysql的备份

mysql备份(单个/多个)数据库---还原

mysqldump -uroot -p --databases dgf > /opt/dgf.sql
mysqldump -uroot -p --databases dgf mysql sys > /opt/dgf.sql
mysq -uroot -p < /opt/dgf.sql

数据备份所有库

mysqldump -u root -p --opt --all-databases > /opt/all-data.sql

备份dgf库的users表

mysqldump -u root -p def users > /opt/all-data.sql

#####备份user表和student表
mysqldump -u root -p def user student > /opt/all-data.sql

导出表的结构

###导出库中的所有表结构
mysqldump -urooot  -p -d dgf > /opt/jiegou.sql
###导出库中的单个表结构
mysqldump -urooot  -p -d dgf user > /opt/jiegou.sql

导出表内数据：

####所有表
mysqldump -u -root -p -t dgf > /opt.shuju.sql
####单个表
mysqldump -u -root -p -t dgf users > /opt.shuju.sql

增量备份：

有以下四种参数选择： 
--start-datetime       #用来指定二进制日志的起始日期
--stop-datetime        #用来指定二进制日志的结束日期
--start-position         #用来指定二进制日志的起始位置
--stop-position          #用来指定二进制日志的结束位置

增量备份的使用：

以位置进行备份：

1.先使用命令查看二进制日志：

mysqlbinlog /usr/local/mysql/data/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4   参考位置
#220529 14:10:44 server id 1  end_log_pos 123 CRC32 0xf0829e77 	Start: binlog v 4, server v 5.7.12-log created 220529 14:10:44 at startup
ROLLBACK/*!*/;
BINLOG '
ZA6TYg8BAAAAdwAAAHsAAAAAAAQANS43LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABkDpNiEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AXeegvA=
'/*!*/;
# at 123  参考位置
#220529 14:10:44 server id 1  end_log_pos 154 CRC32 0xa460f50b 	Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

也可以使用 -v就会出现自己做的操作 

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220530 18:23:26 server id 1  end_log_pos 123 CRC32 0xed60d12b 	Start: binlog v 4, server v 5.7.12-log created 220530 18:23:26
BINLOG '
HpuUYg8BAAAAdwAAAHsAAAAAAAQANS43LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ASvRYO0=
'/*!*/;
# at 123
#220530 18:23:26 server id 1  end_log_pos 154 CRC32 0x008a1c77 	Previous-GTIDs
# [empty]
# at 154
#220530 18:23:35 server id 1  end_log_pos 219 CRC32 0xdecf7b28 	Anonymous_GTID	last_committed=0	sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#220530 18:23:35 server id 1  end_log_pos 295 CRC32 0x2b9c9bee 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1653906215/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 295
#220530 18:23:35 server id 1  end_log_pos 360 CRC32 0xa6d7fbb9 	Table_map: `zhangsan`.`gongsi` mapped to number 110
# at 360
#220530 18:23:35 server id 1  end_log_pos 512 CRC32 0x66cbf372 	Write_rows: table id 110 flags: STMT_END_F

BINLOG '
J5uUYhMBAAAAQQAAAGgBAAAAAG4AAAAAAAEACHpoYW5nc2FuAAZnb25nc2kABf7+/v4DCP4e/h7+
Wv4eH7n716Y=
J5uUYh4BAAAAmAAAAAACAAAAAG4AAAAAAAEAAgAF/+AP5aSn5pWw5o2u5aSE55CGBuW8oOmjnhIx
MjMzMjExMjMzMjExMjMzMjEG5ZOI5L2bjvZIB+AP5riF6aOO5bGg5a6w5Zy6BuWFs+e+vRIxMjMz
MjExMjMzMjExMjMzMjEP6bq76L6j576K6IKJ5LiyjvZIB3Lzy2Y=
'/*!*/;
### INSERT INTO `zhangsan`.`gongsi`
### SET
###   @1='大数据处理'
###   @2='张飞'
###   @3='123321123321123321'
###   @4='哈佛'
###   @5=122222222
### INSERT INTO `zhangsan`.`gongsi`
### SET
###   @1='清风屠宰场'
###   @2='关羽'
###   @3='123321123321123321'
###   @4='麻辣羊肉串'
###   @5=122222222
#220530 18:23:38 server id 1  end_log_pos 2877 CRC32 0x24c38dad 	Xid = 20
COMMIT/*!*/;
# at 2877
#220530 18:23:59 server id 1  end_log_pos 2924 CRC32 0xd3589f4f 	Rotate to mysql-bin.000003  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2.使用命令

mysqlbinlog --start-position 4 --stop-position 123 mysql-bin.000001
             开始位置                      结束位置   文件名称

以时间进行备份：

1.先使用命令查看二进制日志（一般也不用参考时间位置）：

mysqlbinlog /usr/local/mysql/data/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4   参考位置
#220529 14:10:44 server id 1  end_log_pos 123 CRC32 0xf0829e77 	Start: binlog v 4, server v 5.7.12-log created 220529 14:10:44 at startup ###参考时间
ROLLBACK/*!*/;
BINLOG '
ZA6TYg8BAAAAdwAAAHsAAAAAAAQANS43LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABkDpNiEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AXeegvA=
'/*!*/;
# at 123  参考位置
#220529 14:10:44 server id 1  end_log_pos 154 CRC32 0xa460f50b 	Previous-GTIDs ###参考时间
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2.使用命令

mysqlbinlog --start-datetime '2022-05-29 00:00:00' --stop-datetime '2022-05-30 00:00:00' mysql-bin.000001

进行备份与恢复练习：

新建几行数据：

###刷新日志
mysql> flush logs;
###插入数据
insert gongsi values('大数据处理','张飞',123321123321123321,'哈佛',122222222),('清风屠宰场','关羽',123321123321123321,'麻辣羊肉串',1,122222222);
###再次刷新日志
mysql> flush logs;
mysql>exit退出
查看日志就会刷新出来一个新日志000002，但是之前的操作都是在000001所以只需要恢复000001上就可以

删除表中的新数据（模拟丢失）：

mysql> delete from gongsi where 姓名='张飞';
Query OK, 1 row affected (0.00 sec)

mysql> delete from gongsi where 姓名='关羽';
Query OK, 1 row affected (0.00 sec)

使用mysqlbinlog进行恢复：

mysqlbinlog --start-position 4 --start-datetime '2022-5-29 00:00:01' --stop-datetime '2022-5-29 00:00:00'  /usr/local/mysql/data/mysql-bin.000002 > /opt/zeng1.sql
###以位置进行备份
mysqlbinlog --start-position 4 --stop-position 1088 /usr/local/mysql/data/mysql-bin.000002 > /opt/zeng1.sql
###恢复数据
mysql -uroot  -p666666 < /opt/zeng1.sql
或者
mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p

然后查看自己的表格,就可以发现表格恢复成功

二进制日志格式（默认为ROW）

由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;

设置了row或statement就可以看懂二进制文件了，但各有优缺点
mysql5.7之前是statement，5.7之后是基于行的
建议：切勿将二进制日志与数据文件放在一同设备；可以将binlog日志实时备份到远程设备上，以防出现机器故障进行数据恢复；

备份类型： 
 

（1）根据备份时，mysql服务是否在线

	冷备(cold backup)：		停掉mysql这个服务，读写操作都不能进行
	温备份：		全局施加共享锁，只能读不能写
	热备(hot backup)：		不停掉mysql这个服务，读写照样进行

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

2）逻辑备份(logical backup)： 把数据抽取出来保存在sql脚本中，mysqldump就属于逻辑备份

增量备份和差异备份的区别

增量备份 incremental backup （只需要备份一天的量）

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

 差异备份 differential backup （需要备份从上次备份开始到现在之间所有的量）

备份那些自从上次完全备份之后被修改过的所有文件，备份的时间起点是从上次完整备份起，备份数据量越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。

Mysql最常用的三种备份工具：
 

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

两者对比如下

除了Xtrebackup之外其他都很简单，所以今天只做Xtrebackup.

Xtrebackup工具

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 之上做了一层封装。

原理

通信方式

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

[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	不回滚未提交事务（如果未提交就回滚，如果提交就返回OK）
--copy-back	恢复备份目录

完全备份

1.进行备份一个数据库文件

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

#查看备份数据

 

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

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

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

xtrabackup_logfile　　　　#备份的日志文件

（2）恢复

[root@localhost ~]# innobackupex --apply-log /backups/2022-05-31_08-15-27/ ###合并数据，使数据文件处于一致性的状态
rm -rf /usr/local/mysql/data/*　　#删除原有的数据
[root@localhost ~]# innobackupex --copy-back /backups/2022-05-31_08-15-27/  ####对数据进行恢复

修改属主属组

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

增量备份

1.进行增量备份之前建议来一个全量备份，但我已经做过全量了，这里就不演示了

2.创建一个增量备份：

innobackupex --user=root --password=666666 --incremaental /backups/ -incremental-
basedir=/backups/2022-05-31_08-15-27/
 指定用户和密码  指定创建增量备份的目录   指定完全备份的路径

进入文件查看此文件信息

cat xtrabackup_checkpoints backup_type = incremental
from_lsn = 815001943   ###从这里开始的页数
to_lsn = 815002161
last_lsn = 815002161   ###到这里结束的页数
compact = 0
recover_binlog_info = 0

模拟mysql故障

[root@localhost ~]# rm -rf /usr/local/mysql/data/*
####先还原全备的
innobackupex --apply-log     --redo-only    /baclups/2022-05-31_08-15-27/
                           不回滚未提交事务
####
innobackupex --apply-log --redo-only /baclups/2022-05-31_08-15-27/ 
                                      指定全备路径
--incremental-dir=/baclups/2022-05-31_09-56-51/
指定包含增量备份的目录
############大概意思就是将增量备份的页数保存到全量备份中

进行恢复实验

innobackupex --copy-back /baclups/2022-05-31_08-15-27/
直接恢复全量备份就可以


查看全量备份的页数文件
[root@cpe-66-66-66-188 2022-05-31_09-56-51]# cat ../2022-05-31_08-15-27/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 815002161
last_lsn = 815002161
compact = 0
recover_binlog_info = 0
！！！！！！！！！！！就可以发现，全量备份的最大页数跟增量备份的最大页数一致了

mysql的函数有：
 

Count()         max()         sum()         min()        avg()
                最大值         总和          最小值       平均值