MySQL备份与主备配置 - 好文

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.sqlsource student_backup.sql
#要在数据库操作 use student 之后
增量备份
增量备份的 binlog 是一个二进制格式的文件，用于记录用户对数据库更新的 SQL 语句信息，例如更改数据库表和更改内容的 SQL 语句都会记录到
binlog 里，但是对库表等内容的查询不会记录。
在配置文件中，修改配置打开 binlog。通过 show variables like '%log_bin%'; 查看 binlog 是否打开。

可以看到默认是没有打开的。
默认的配置文件可能在 /etc/mysql/my.cnf，如果是使用 XAMPP 等一键安装的，也可能在 /opt/lampp/etc/my.cnf 等位置。
在配置文件找到 log_bin 所在的位置，取消这一行的注释。

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

binlog 的使用格式 show binary logs; show binlog events in 'mysql-bin.000001';

GTID 的全称是Global Transaction
Identifier，也就是全局事务ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由两部分组成，格式是：`gtid=server-uuid:gno
。
server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值。
gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。
在 GTID 模式下，每个事务都会跟一个 GTID 一一对应。
这样，每个MySQL 实例都维护了一个GTID 集合，用来对应“这个实例执行过的所有事务”。
下面来测试一下 binlog。

先导入测试数据，然后执行以下语句。 update student set birth = 2019 where id = '100'; insert
into student values(200, 'jxtxzzw', '男', 2019, '计算机系', '上海'); delete from
student where id = 200; 查看 binlog。

通过以下两条语句可以生成新的 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 找到了误删的两条数据。
mysqlbinlog --start-position=4183 --stop-position=4592
/opt/lampp/var/mysql/mysql-bin.000001 | mysql -u root -p 在上面这条语句中，首先是设置了起点为
4183、终点为 4592，并指定了 binlog 的文件为 mysql-bin.000001。

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

可以看到恢复成功。
如果想要删除 binlog，删除 binlog 的方法是：
* 关闭 MYSQL 主从，关闭 binlog。
* 开启 MYSQL 主从，设置 expire_logs_days。
* 手动清除 binlog 文件，PURGE MASTER LOGS to ‘mysqld-bin.00001’;(before ‘date’)。
* reset master。
练习 1

* 删掉 student 库，通过全量备份和 binlog 对其进行恢复。
* 尝试了解 binlog 的三种格式。
删掉 student 库的过程非常简单，而通过全量备份恢复只需要导入即可，从略。
下面重点说一下从 binlog 恢复的过程。

需要特别说明的是，命令行下可能不允许使用 delete，这时候可以用 drop table 替换。
删除所有数据以后再次打开数据库，看到表已经是空的了。

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

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

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

binlog 的三种格式：
直接转载卜算的《使用mysql的binlog恢复误操作(update|delete)的数据》（
https://blog.csdn.net/Aeroleo/article/details/77929917
<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';

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

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

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

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

进入数据库，执行：
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;

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

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

主库成功添加了这条记录。
从库也出现了这条记录。
特别需要指出的是，从库和主库的同步只能是从同步开始设置的那一刻之后的操作才能同步。
举个例子，主库有 100、200、300 这三个用户，从库有 200、300、500 这三个用户：

* 在主库添加 400 号用户，则从库自动添加 400 号用户
* 在主库删除 300 号用户，则从库自动删除 300 号用户
* 在主库将 200 号用户的编号修改为 233 号，则从库自动将 200 号用户的编号设置为 233。
* 在主库修改 100 号用户的编号修改为 101，从库没有响应。
* 在主库修改 100 号用户的编号修改为 500，进一步修改姓名为张三，由于在第一步操作之后，主库的 100 号用户的编号已经修改为 500
了，之后如果用主键来判断修改了哪一条记录，那么将会是“把 500 号用户的姓名修改为张三”，于是，这一修改会体现在从库上，从库的 500 号用户也被改成了张三。
因此，需要说明的是，如果从库一开始就没有 student 这个库，或者没有表，那么，主从备份是不会起作用的。

MySQL 双主结构
目的：A 和 B 双向同步。
刚才的是主从备份，只有主机的修改会被同步到从机，从机的修改不会被同步到主机。

修改两边的配置文件。
在 A 和 B 重复上面主从备份的时候创建 repl 用户的过程，并赋予权限。 create user repl; GRANT REPLICATION
SLAVE ON *.* TO 'repl'@‘%' IDENTIFIED BY '123456'; FLUSH PRIVILEGES; 测试是否可以连接。

自己连自己、自己连对方，都可以正常访问。
之后的步骤与主从备份是类似的，只是相当于对两台主机都做了一次主从备份，互相做对方的从机。
于是，需要先查看 binlog 的 index 和 pos。
这是在 A 查到的结果，A 的 IP 是 192.168.23.129。

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

注意这个操作需要在 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;
之后分别在 A 和 B 上通过 start slave 来运行。
通过 show slave status 可以看到 IO 和 SQL 都是 Running 的。

测试。 INSERT INTO `student` (`id`, `name`, `sex`, `birth`, `department`,
`address`) VALUES ('11', 'Added From Desktop', NULL, NULL, NULL, NULL); 之后可以看到
Server 版的数据库出现了这一记录。

在 Server 版修改。 UPDATE `student` SET `address` = 'Modified From Server' WHERE
`student`.`id` = 11; 可以看到双向同步了。

主备延迟
最后需要说明的是，主备之间存在一个延迟。
* 主库 A 执行完成一个事务，写入 binlog，我们把这个时间记为 T1。
* 之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2。
* 备库 B 执行完成这个事务，我们把这个时刻记为 T3。
主备延迟即 T3 - T1 的差。
可以在备库上执行 show slave status 查看 seconds_behind_master。

但是在我们的测试中，几乎所有的主备延迟都是 0。
这是因为，主备延迟的来源有：
* 备库的性能更差
* 备库压力较大
* 大事务必须执行完才会写入 binlog，然后传给备库
在试验中并没有遇到这样的情况。
当然可以手动构造大量的数据来做个测试。
练习 2
尝试配置MySQL一主一备及双主结构。
上文已详述。

热门工具换一换