MySQL备份与主备配置

数据备份类型

• 全量备份：备份整个数据库
• 增量备份：备份自上一次备份以来（增量或完全）以来变化的数据
• 差异备份：备份自上一次完全备份以来变化的数据

全量备份

全量备份的方法有 2 种，一种是利用数据库管理工具提供的备份恢复和导入导出功能。

例如：如果使用 Navicat、PHPMyAdmin 之类的可视化工具，可以直接点击转储 SQL 文件，或者导出 SQL 文件之类的功能。

另一种是利用 mysqldump。

导出：

sudo mysqldump -u root -p student > dir/student_backup.sql

导入：

sudo mysqldump-u root -p student < dir/student_backup.sql
source student_backup.sql #要在数据库操作 use student 之后

增量备份

增量备份的 binlog 是一个二进制格式的文件，用于记录用户对数据库更新的 SQL 语句信息，例如更改数据库表和更改内容的 SQL 语句都会记录到 binlog 里，但是对库表等内容的查询不会记录。

在配置文件中，修改配置打开 binlog。通过 show variables like '%log_bin%'; 查看 binlog 是否打开。

1557456890317

可以看到默认是没有打开的。

默认的配置文件可能在 /etc/mysql/my.cnf，如果是使用 XAMPP 等一键安装的，也可能在 /opt/lampp/etc/my.cnf 等位置。

在配置文件找到 log_bin 所在的位置，取消这一行的注释。

1557458584111

重启服务以后，可以看到启用了 binlog。

1557458635561

binlog 的使用格式

show binary logs;
show binlog events in 'mysql-bin.000001';

1557458736273 1557458749349

GTID 的全称是Global Transaction Identifier，也就是全局事务ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由两部分组成，格式是：``gtid=server-uuid:gno`。

server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值。

gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。

在 GTID 模式下，每个事务都会跟一个 GTID 一一对应。

这样，每个MySQL 实例都维护了一个GTID 集合，用来对应“这个实例执行过的所有事务”。

下面来测试一下 binlog。

1557458853180

先导入测试数据，然后执行以下语句。

update student set birth = 2019 where id = '100';
insert into student values(200, 'jxtxzzw', '男', 2019, '计算机系', '上海');
delete from student where id = 200;

查看 binlog。

1557458950530

通过以下两条语句可以生成新的 binlog。

flush logs;
show binary logs;

除了 flush logs;，重启 MySQL 服务以及 mysqlbinlog 也可以生成新的 binlog。

通过 binlog 恢复数据

构造场景：

insert into student values(907,'李七','男',1991,'计算机系','上海');
insert into student values(908,'李八','男',1992,'音乐系','上海');
delete from student where id=907;//误删
delete from student where id=908;//误删

如何通过 binlog 恢复这两条数据？

通过查看 binlog 找到了误删的两条数据。

1557459125508

mysqlbinlog --start-position=4183 --stop-position=4592 /opt/lampp/var/mysql/mysql-bin.000001 | mysql -u root -p

在上面这条语句中，首先是设置了起点为 4183、终点为 4592，并指定了 binlog 的文件为 mysql-bin.000001。

1557459226725 1557459678141

输入管理员密码之后，可以重新打开数据库看一下是不是成功。

1557459751519

可以看到恢复成功。

如果想要删除 binlog，删除 binlog 的方法是：

1. 关闭 MYSQL 主从，关闭 binlog。
2. 开启 MYSQL 主从，设置 expire_logs_days。
3. 手动清除 binlog 文件，PURGE MASTER LOGS to ‘mysqld-bin.00001’;(before ‘date’)。
4. reset master。

练习 1

1. 删掉 student 库，通过全量备份和 binlog 对其进行恢复。
2. 尝试了解 binlog 的三种格式。

删掉 student 库的过程非常简单，而通过全量备份恢复只需要导入即可，从略。

下面重点说一下从 binlog 恢复的过程。

1557459828077

需要特别说明的是，命令行下可能不允许使用 delete，这时候可以用 drop table 替换。

删除所有数据以后再次打开数据库，看到表已经是空的了。

1557459890412

然后打开 binlog 看一眼，找到 start position 和 stop position。

1557461599133

然后从起点位置到结束位置执行一次恢复。

1557461570784

可以看到数据已经恢复了。

1557461639511

binlog 的三种格式：

直接转载 卜算 的《使用mysql的binlog恢复误操作(update|delete)的数据》（https://blog.csdn.net/Aeroleo/article/details/77929917）中的内容：

MYSQL binlog复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

② ROW模式（RBR）

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

③ MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

MySQL 主备配置

在主库上创建用户 repl，并给他权限。

CREATE USER repl;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '123456';

1557463740318

检查在主库 my.cnf 中配置 server-id。发现已经配置了。

1557463809046

然后进配置文件把所有 bind-address=127.0.0.1 的配置行注释掉。

1557464115585

在主库执行 show master status; 记录 binlog 和 pos。

1557464220695

打开 Ubuntu-Server（从库），修改配置文件（/opt/lampp/etc/my.cnf）中的 server-id 为 2，重启 MySQL 服务。

1557464381264

进入数据库，执行：

change master to master_host='主库IP（这里是192.168.23.129）', master_user='主库用户（这里是repl）', master_password='主库用户密码（这里是123456）', master_log_file="主库的binlog（这里是mysql-bin.000003）", master_log_pos=主库的binlog的pos（这里是327）;
start slave;
show slave status\G;

1557464651425

然后在 status 可以看到 slave 的状态是 YES。

1557466491120 1557466508070

测试主备。在主库添加一条记录，然后在从库看一下是不是有这条记录。

1557464970121 1557464987238

主库成功添加了这条记录。

从库也出现了这条记录。

特别需要指出的是，从库和主库的同步只能是从同步开始设置的那一刻之后的操作才能同步。

举个例子，主库有 100、200、300 这三个用户，从库有 200、300、500 这三个用户：

• 在主库添加 400 号用户，则从库自动添加 400 号用户
• 在主库删除 300 号用户，则从库自动删除 300 号用户
• 在主库将 200 号用户的编号修改为 233 号，则从库自动将 200 号用户的编号设置为 233。
• 在主库修改 100 号用户的编号修改为 101，从库没有响应。
• 在主库修改 100 号用户的编号修改为 500，进一步修改姓名为张三，由于在第一步操作之后，主库的 100 号用户的编号已经修改为 500 了，之后如果用主键来判断修改了哪一条记录，那么将会是“把 500 号用户的姓名修改为张三”，于是，这一修改会体现在从库上，从库的 500 号用户也被改成了张三。

因此，需要说明的是，如果从库一开始就没有 student 这个库，或者没有表，那么，主从备份是不会起作用的。

1557467029314

MySQL 双主结构

目的：A 和 B 双向同步。

刚才的是主从备份，只有主机的修改会被同步到从机，从机的修改不会被同步到主机。

1557467095234

修改两边的配置文件。

在 A 和 B 重复上面主从备份的时候创建 repl 用户的过程，并赋予权限。

create user repl;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@‘%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

测试是否可以连接。

1557467728717 1557467761322

自己连自己、自己连对方，都可以正常访问。

之后的步骤与主从备份是类似的，只是相当于对两台主机都做了一次主从备份，互相做对方的从机。

于是，需要先查看 binlog 的 index 和 pos。

这是在 A 查到的结果，A 的 IP 是 192.168.23.129。

1557467898254

这是在 B 查到的结果， B 的 IP 是 192.168.23.128。

1557468134222

注意这个操作需要在 root 权限下运行，repl 运行不了。

然后在 A 执行：

change master to master_host='192.168.23.128', master_user='repl', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=313;

类似的，在 B 运行：

change master to master_host='192.168.23.129', master_user='repl', master_password='123456', master_log_file='mysql-bin.000005', master_log_pos=327;

1557468531570

之后分别在 A 和 B 上通过 start slave 来运行。

通过 show slave status 可以看到 IO 和 SQL 都是 Running 的。

1557468640189 1557468671526

测试。

INSERT INTO `student` (`id`, `name`, `sex`, `birth`, `department`, `address`) VALUES ('11', 'Added From Desktop', NULL, NULL, NULL, NULL);

之后可以看到 Server 版的数据库出现了这一记录。

1557468735519

在 Server 版修改。

UPDATE `student` SET `address` = 'Modified From Server' WHERE `student`.`id` = 11;

可以看到双向同步了。

1557468786687

主备延迟

最后需要说明的是，主备之间存在一个延迟。

• 主库 A 执行完成一个事务，写入 binlog，我们把这个时间记为 T1。
• 之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2。
• 备库 B 执行完成这个事务，我们把这个时刻记为 T3。

主备延迟即 T3 - T1 的差。

可以在备库上执行 show slave status 查看 seconds_behind_master。

1557468868124

但是在我们的测试中，几乎所有的主备延迟都是 0。

这是因为，主备延迟的来源有：

1. 备库的性能更差
2. 备库压力较大
3. 大事务 必须执行完才会写入 binlog，然后传给备库

在试验中并没有遇到这样的情况。

当然可以手动构造大量的数据来做个测试。

练习 2

尝试配置MySQL一主一备及双主结构。

上文已详述。