Mysql bin-log日志恢复数据与物理备份-xtrabackup

主打一个数据备份与恢复

bin-log日志恢复

bin-log 日志，就记录对数据库进行的操作，什么增删改的操作全都在 bin-log 日志中，首先需要开启bin-log日志文件

开启bin-log配置

• 如图：在mysql的配置文件中的 [mysqld] 添加下面的配置

vim /etc/my.cnf   # 不同的操作系统的配置文件不一样

server_id=10   # 不指定，可随意写一个数
log-bin=/var/lib/mysql/mysql-bin.log
1
2
3
4

• 添加上面的配置之后需要重启一下

systemctl restart mysqld
1

• 重启之后在/var/lib/mysql 目录下面有一个 mysql_bin.00001 文件
• 在mysql中可以刷新bin-log日志

mysql> flush logs;
1

• 可以使用下面的查看当前使用的是哪一个 bin-log 日志文件

mysql> show master status

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      777 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

bin-log日志恢复

• 查看bin-log日志

mysql> select * from school.student;
+----+--------+------+
| id | name   | sex  |
+----+--------+------+
|  1 | 小温   | 男   |
|  2 | 小成   | 女   |
+----+--------+------+
2 rows in set (0.00 sec)

mysql> delete from school.student;
Query OK, 2 rows affected (0.00 sec)
#  上面是删除表中的两条记录
# 查看 bin-log 日志文件中对应的操作
mysqlbinlog --base64-output=decode-rows /var/lib/mysql/mysql-bin.000003 -vv
BEGIN
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 这个图片是课件里面的，只供展示
  

• 里面有好多，我只截取上面对应的一部分的日志

/*!*/;
# at 927
#230926  0:48:58 server id 10  end_log_pos 991 CRC32 0x55632c6b         Table_map: `school`.`student` mapped to number 83
# has_generated_invisible_primary_key=0
# at 991
#230926  0:48:58 server id 10  end_log_pos 1052 CRC32 0x67d51427        Delete_rows: table id 83 flags: STMT_END_F
### DELETE FROM `school`.`student`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='小温' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
### DELETE FROM `school`.`student`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='小成' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
# at 1052
#230926  0:48:58 server id 10  end_log_pos 1083 CRC32 0x6eb05b5b        Xid = 132
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

• 如果要恢复对应的数据，必须找到对应的添加语句

BEGIN
/*!*/;
# at 634
#230925 19:52:16 server id 10  end_log_pos 698 CRC32 0x26fba24f         Table_map: `school`.`student` mapped to number 83
# has_generated_invisible_primary_key=0
# at 698
#230925 19:52:16 server id 10  end_log_pos 746 CRC32 0x88535582         Write_rows: table id 83 flags: STMT_END_F
### INSERT INTO `school`.`student`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='小温' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0  */
# at 746
#230925 19:52:16 server id 10  end_log_pos 777 CRC32 0xd1f2f433         Xid = 96
COMMIT/*!*/;
# at 777
#230926  0:48:58 server id 10  end_log_pos 856 CRC32 0x842fc928         Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes    original_committed_timestamp=1695660538233698     immediate_commit_timestamp=1695660538233698     transaction_length=306
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1695660538233698 (2023-09-26 00:48:58.233698 CST)
# immediate_commit_timestamp=1695660538233698 (2023-09-26 00:48:58.233698 CST)
/*!80001 SET @@session.original_commit_timestamp=1695660538233698*//*!*/;
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 856
#230926  0:48:58 server id 10  end_log_pos 927 CRC32 0xf9ee8b28         Query   thread_id=18    exec_time=0     error_code=0
SET TIMESTAMP=1695660538/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
BEGIN
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

• 上面是添加数据对应的日志
• 要恢复的话需要使用下面的这条语句

mysqlbinlog --start-position 634 --stop-position 927 /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p1

# 其中 634 是上面日志文件有个 at 634 相当于从这个时刻开始到 927 这个时刻
# 需要注意的是，在这个里面必须将 COMMITTED 事务提交语句也得包含起来
1
2
3
4

• 当然，还可以使用时间来恢复数据

mysqlbinlog --start-datetime='2019-8-21 23:55:54' --stop-datetime='2019-8-21 23:56:541' mylog.000001 | mysql -u root -p1
1

物理备份-xtrabackup

物理备份： 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

三种备份方式

• 全量备份-----完整备份：

每次都将所有数据（不管自第一次备份以来有没有修改过），进行一次完整的复制，备份后会清除文件的存档属性，方便日后增量备份或者差异备份进行版本比较。
特点：占用空间大，备份速度慢，但恢复时一次恢复到位，恢复速度快。

• 增量备份: 每次备份上一次备份到现在产生的新数据

在第一次完整备份之后，第二次开始每次都将添加了存档属性的文件进行备份，并且在备份之后再把这些存档属性清除。为什么要清除存档属性呢？这就是为了下一次备份的时候判断是否有文件变化，因为用户在每次备份以后修改这些被清除存档属性的文件，存档属性就会自动加上，相当于用户告诉系统，这些文件有变化，你下一次就备份这些文件，其他没有存档属性的就不需要备份，这就是增量备份的工作机制。

特点：因每次仅备份自上一次备份（注意是上一次，不是第一次）以来有变化的文件，所 以备份体积小，备份速度快，但是恢复的时候，需要按备份时间顺序，逐个备份版本进行恢 复，恢复时间长。

• 差异备份:只备份跟完整备份不一样的

在第一次完整备份之后，第二次开始每次都将所有文件与第一次完整备份的文件做比较，把自第一次完整备份以来所有修改过的文件进行备份，且以后每次备份都是和第一次完整备份进行比较（注意是第一次，不是上一次），备份自第一次完整备份以来所有的修改过的文件。
特点：占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间。

---

简单的讲，完整备份就是不管三七二十一，每次都把指定的备份目录完整的复制一遍，不管目录下的文件有没有变化；增量备份就是每次将之前（第一次、第二次、直到前一次）做过备份之后有变化的文件进行备份；差异备份就是每次都将第一次完整备份以来有变化的文件进行备份。

安装xtrabackup

官网：www.percona.com

• 下载
  
  
  点击这个下载，然后复制到linux系统中解压
• 安装

# 解压后进入对应文件夹里面
yum install -y *.npm  # 安装
1
2

备份

全量备份

• 备份

# 创建备份目录
mkdir /xtrabackup/full -p
# 全量备份 
innobakeupex --user=root --password='1' /xtrabackup/full
1
2
3
4

• 恢复

# 1.关闭数据库
systemctl stop mysqld
# 2.清理数据 
rm -rf /var/lib/mysql/*
# 3.恢复之前验证数据
innobackupex -apply-log /xtrabackup/full/对应的文件
# 4.恢复数据 恢复数据前需要将 bin-log 配置修改 将bin-log的文件目录修改为其他地方，
# 也可以将 bin-log 配置项注释掉
innobackupex --copy-back /xtrabackup/full/文件
# 5.修改权限
chown mysql.mysql  /var/lib/mysql -R
1
2
3
4
5
6
7
8
9
10
11

增量备份

原理：每次备份上一次备份到现在产生的新数据

• 全量备份第一天的

innobackupex --user=root --password='1' /xtrabackup/full
1

• 增量备份第二天的

innobackupex --user=root --password='1' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/第一天备份的文件夹
1

• 增量备份第三天的

innobackupex --user=root --password='1' --incremental /xtarbackup/ --incremental-basedir=/xtrabackup/第二天备份的文件
1

• 恢复文件

# 1.关闭mysql
systemctl stop mysqld
# 2.清除数据
rm -rf /var/lib/mysql/*
# 3.依次重演备份文件
innobackupex --apply-log --redo-only /xtarbackup/第一天备份文件
# 之后的重演相当于将数据放到了第一天备份文件上
innobackupex --apply-log --redo-only /xtrabackup/第一天备份文件 --incremental-dir=/xtrabackup/第二天备份文件
innobackupex --apply-log --redo-only /xtrabackup/第一天备份文件 --incremental-dir=/xtrabackup/第三天备份文件
# 4.恢复数据
innobackupex --copy-back /xtarbackup/第一天备份文件
# 5.修改权限
chown mysql.mysql -R /var/lib/mysql/*
# 6.启动数据库
systemctl start mysqld
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

差异备份

• 全量备份第一天的

innobackupex --user=root --password='1' /xtarbackup/
1

• 第二到四天全不备份 第五天备份前面全部的数据，除外第一天

innobackupex --user=root --password='1' --incremental /xtarbackup/ --incremental-basedir=/xtarbackup/第一天备份的文件夹
1

• 恢复数据

# 1.关闭mysql
systemctl stop mysqld
# 2.清除数据
rm -rf /var/lib/mysql/*
# 3.依次重演备份文件
innobackupex --apply-log --redo-only /xtarbackup/第一天备份文件
innobackupex --apply-log --redo-only /xtarbackup/第一天备份文件 --incremental-dir=/xtarbackup/第五天备份文件
# 4.恢复数据
innobackupex --copy-back /xtarbackup/第一天备份文件
# 5.修改权限
chown mysql.mysql -R /var/lib/mysql/*
# 6.启动数据库
systemctl start mysqld
1
2
3
4
5
6
7
8
9
10
11
12
13