第17章 使用MySQL主从复制技术实现数据库热备

什么是 MySQL Replication

replication 可以实现将数据从一台数据库服务器(master)复制到一台或多台数据库服务器(slave)
默认情况下属于异步复制，无需维持长连接
通过配置，可以复制所有库或几个库，甚至库中的一些表

DML: SQL操作语句，update，insert，delete
Relay log：中继日志

replication的作用
1.Fail Over 故障切换
2.backup server 备份服务，无法对sql语句执行产生故障恢复，有限的备份
3.high performance 高性能，可以多台slave，实现读写分离

replication工作原理
1.master将改变记录到二进制日志（binary log）中（这些记录叫做二进制日志事件，binary log events）
2.slave将master的binary log events 拷贝到它的中继日志（relay log）
3.slave 重做中继日志的事件，修改salve上的数据

1：master 记录二进制日志。在每个事务更新数据完成之前，master 在二进制日志记录这些改变。MySQL 将事务写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master 通知存储引擎提交事务。
2：slave 将 master 的 binary log 拷贝到它自己的中继日志。首先，slave 开始一个工作线程——I/O 线程。I/O 线程在 master 上打开一个普通的连接，然后开始 binlog dump process。Binlog dump process 从 master 的二进制日志中读取事件，如果已经执行完master 产生的所有文件，它会睡眠并等待 master 产生新的事件。I/O 线程将这些事件写入中继日志。
3：SQL slave thread（SQL 从线程）处理该过程的最后一步。SQL 线程从中继日志读取事件，并重新执行其中的事件而更新 slave 的数据，使其与 master 中的数据一致。

Replication常见方案

1.one master and Muti salve ，一主多从

一般用来做读写分离的，master写，其它slave读，这种架构最大问题I/O压力集中在Master上<多台同步影响IO>

2.M-S-S,一主两从

使用一台slave作为中继，分担Master的压力，slave中继需要开启bin-log，并配置log-slave-updates，Slave中继可使用Black-hole存储引擎，不会把数据存储到磁盘，只记录二进制日志。

 

3.M-M，双主互备（互为主从）

很多人误以为这样可以做到MySQL负载均衡，实际没什么好处，每个服务器需要做同样的同步更新，破坏了事务隔离性和数据的一致性。

4.M-M-M，多主

监控三台机器互相作为对方的master

天生的缺陷：复制延迟，slave上同步要慢于master，如果大并发的情况那延迟更严重

5.one slave Muti master,一从对多主

好处：节省成本，将多个master数据自动化整合。
缺陷：对库和表数据的修改较多。

部署MySQL主从同步 M-S

主机名	IP	系统/mysql版本	角色
centos7-64	192.168.0.64	CentOS Linux release 7.6.1810 (Core)/Server version: 5.7.25	Master
centos7-70	192.168.0.70	CentOS Linux release 7.6.1810 (Core)/Server version: 5.7.25	slave

上传 mysql-5.7.tar.gz 到master
上传到slave scp mysql-5.7.tar.gz root@192.168.0.70:/root 
tar xvf mysql-5.7.tar.gz
yum -y install ./mysql-community-* 
systemctl start mysqld
关闭密码强度检查 echo "validate-password=OFF">> /etc/my.cnf
systemctl restart mysqld
grep "password" /var/log/mysqld.log

mysql -uroot -p'8NYjnl1;hg;y'  密码用单引号包含
set password for root@localhost=password('123456');
flush privileges;

systemctl stop mysqld
vim /etc/my.cnf
log-bin=mysql-bin-master  #启用二进制日志
server-id=1                          #本机数据库ID唯一标识符
binlog-do-db=book2          #需要同步的数据库名，没有此条设置，就会同步所有库，重复此选项即可添加多个库。
binlog-ignore-db=mysql     #不需要同步的数据库名，重复此选项可添加多个。

systemc restart mysqld

mysql -uroot -p123456
主服务器进行授权
grant replication slave on *.* to slave@192.168.0.70 identified by "slave123456";
flush privileges;
这里slave是主服务器上的用户，允许192.168.0.70这个地址使用slave用户来登录主服务器
如果提示密码简单虚执行以下两条命令，再执行授权
set global validate_password_policy=0;
set global validate_password_length=1;
flush privileges;
select user,host from mysql.user;
update user set host="192.168.0.70" where mysql.user="slave";

show master status;

主库日志名       ，同步位置，同步谁，忽略谁 
show binlog events\G

pos 操作后的位置记录
ls /var/lib/mysql 查看下mysql-bin-master二进制文件

 复制前要保证同步的数据库一致
mysqldump -uroot -p123456 -B book2>book2.sql
导出数据库
scp book2.sql 192.168.0.70:/root
传给从服务器

配置从服务器
tar xvf mysql-5.7.tar.gz
yum -y install ./mysql-community-* 
systemctl start mysqld
关闭密码强度检查 echo "validate-password=OFF">> /etc/my.cnf
systemctl restart mysqld
grep "password" /var/log/mysqld.log
mysql -uroot -pylYOSp=81ozE
set password for root@localhost=password('slave123456');
flush privileges;
exit


查看主从服务器版本是否一致，如果不能一致确保主服务器版本高于从服务器，主服务器5.7与从服务器至少保持一致
show variables like '%version%';


mysql -h 192.168.0.64 -u slave -pslave123456
可以远程连接到主服务器

 mysql -uroot -pslave123456 < book2.sql
直接导入数据库，因为使用-B 带有建库语句，所以直接导入即可。

vim /etc/my.cnf
server-id=2
从服务器的唯一id号，就像身份证号是唯一的，不能相同。

systemctl restart mysqld
mysql -uroot -pslave123456
stop slave;
change master to master_host='192.168.0.64',master_user='slave',master_password='slave123456',master_port=3306;
这里填写的是主服务器的ip地址，主服务器上授权允许远程登录的用户名和密码
start slave；
show slave status\G

Slave_IO_Running 一个负责与主机的IO通信
Slave_SQL_Running 负责自己的slave mysql进程
都必须为yes代表成功。

主服务器
查看状态
show processlist \G

command：Binlog Dump 二进制日志导出
state： 主程序已经将所有的binlog发送到从程序，等待更多的更新

插入数据测试同步
主服务器
create table test2 (id int(10),name varchar(20));

从服务器

排错
1.二进制日志没有开启
2.iptables 没有开端口
3.对应的主机ip地址写错了，端口号错了，用户名密码错了
主服务器
show master status;

show binlog events\G
从服务器
stop slave;
change master to master_log_file='mysql-bin-master.000004',master_log_pos=1675;
start slave;
show slave status\G

SQL线程出错主要原因:
主从服务器数据结构不统一：出错后，如果数据量比较少，可以手动解决创建插入，在更新slave状态。
如果数据量大，直接mysqldump 导出数据库传给从服务器，然后再slave。
如果主服务器误删除了，从服务器上也会同步删除，所以主服务器要定期做mysqldump备份。

部署MySQL主从从 M-S-S 模型

主机名	IP	系统/mysql版本	角色
centos7-64	192.168.0.64	CentOS Linux release 7.6.1810 (Core)/Server version: 5.7.25	Master
centos7-70	192.168.0.70	CentOS Linux release 7.6.1810 (Core)/Server version: 5.7.25	slave中继
centos7-80	192.168.0.80	CentOS Linux release 7.6.1810 (Core)/Server version: 5.7.25	slave

部署 主服务器

上传 mysql-5.7.tar.gz 到master
上传到slave
scp mysql-5.7.tar.gz root@192.168.0.70:/root 
scp mysql-5.7.tar.gz root@192.168.0.80:/root 
tar xvf mysql-5.7.tar.gz
yum -y install ./mysql-community-* 
systemctl start mysqld
关闭密码强度检查 echo "validate-password=OFF">> /etc/my.cnf
systemctl restart mysqld
grep "password" /var/log/mysqld.log

mysql -uroot -p'8NYjnl1;hg;y'  密码用单引号包含
set password for root@localhost=password('123456');
flush privileges;
exit

在主服务器上授权用户 repl用户 可以被192.168.0.1-254的用户访问，%代表所有。
mysql -uroot -p123456
grant replication slave on *.* to repl@'192.168.0.%' identified by '123456';
create database HA;
use HA;
create table T1(id int,name varchar(20));
exit
vim /etc/my.cnf
validate-password=OFF
log-bin=mysql-bin-master
server-id=1
binlog-do-db=HA
binlog-ignore-db=mysql
sync-binlog=1                
binlog-format=row

Mysql开启bin-log日志使用bin-log时，默认情况下，并不是每次执行写入就与硬盘同步，这样在服务器崩溃是，就可能导致bin-log最后的语句丢失。可以通过这个参数来调节，sync_binlog=N，使执行N次写入后，与硬盘同步。1是最安全的，但是也是最慢的。
binlog-format 是记录binlog的方式，row模式最安全，效率最低。

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

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

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

systemctl restart mysqld
mysql -uroot -p123456
show master status;

mysqldump -uroot -p123456 -B HA>HA.sql
scp HA.sql root@192.168.0.70:/root
scp HA.sql root@192.168.0.80:/root

部署中继从服务器

tar xvf mysql-5.7.tar.gz
yum -y install ./mysql-community-* 
systemctl start mysqld
关闭密码强度检查 echo "validate-password=OFF">> /etc/my.cnf
systemctl restart mysqld
grep "password" /var/log/mysqld.log
mysql -uroot -p'8NYjnl1;hg;y'  密码用单引号包含
set password for root@localhost=password('123456');
flush privileges;
exit
导入数据库HA.sql
mysql -uroot -p123456 < HA.sql
测试远程登录mysql -h 192.168.0.64 -urepl -p123456
配置my.cnf
vim /etc/my.cnf
validate-password=OFF
log-bin=mysql-bin-slave1
server-id=2
log-slave-updates=1 
binlog-format=row

log-slave-updates的含义是，把它从 relay-log 当中读取出来的二进制日志和本机上执行的操作也记录到自己的二进制日志里面，这样才能使第三台 slave 通过中继 slave 读取到相应数据变化 

systemctl restart mysqld
mysql -uroot -p123456
stop slave;
change master to master_host='192.168.0.64',master_user='repl',master_password='123456';
这里仍填写主授权的可以远程登录的账号
start slave;
show slave status \G

再授权一个用户给slave（centos7-80）
grant replication slave on *.* to 'repl2'@'192.168.0.80' identified by '123456';
flush privileges;

部署slave

tar xvf mysql-5.7.tar.gz
yum -y install ./mysql-community-* 
systemctl start mysqld
关闭密码强度检查 echo "validate-password=OFF">> /etc/my.cnf
systemctl restart mysqld
grep "password" /var/log/mysqld.log
mysql -uroot -p'8NYjnl1;hg;y'  密码用单引号包含
set password for root@localhost=password('123456');
flush privileges;
exit
导入数据库HA.sql
mysql -uroot -p123456 < HA.sql
测试远程登录 mysql -h 192.168.0.70 -urepl2 -p123456
配置my.cnf
validate-password=OFF
server-id=3  #这里因为是最后一台从，所以只添这一项即可，如果不是最后一台从，这台也可以当中继，添加log-bin=mysql-bin-slave2 和 binlog-format=row
systemctl restart mysqld
mysql -uroot -p123456
stop slave;
change master to master_host='192.168.0.70',master_user='repl2',master_password='123456';
start slave;
show slave status \G

进行测试

如何让中继服务器查询不到数据
set sql_log_bin=off; 关闭日志
alter table HA.T1 ENGINE=blackhole; 修改中继表的引擎为blackhole
使用blackhole引擎的原因是它不占硬盘空 间，作为一个中转，只负责记日志、传日志
在主从之间添加一个分布式master（中继服务器），配置blackhole存储引擎，他起到一个中继的作用，他接收数据但丢弃（类似/dev/null），而不是存储，只是会把master的二进制日志供下层的slave来读取。

模拟故障：
Mysql主从库的表结构不一致，主库的某个表tableA 比从库表tableA少了一个字段。
当尝试再主库上更改表结构，这行alter语句会binlog同步到从库，从库执行这行语句时出错，主从同步线程会自动停止，只能人为手动处理错误，然后再启动slave上的主从同步线程。
譬如
从库添加字段
alter table HA.T1 add age int default 0 after name;
再在主库添加这个字段
alter table HA.T1 add age int default 0 after name;
从库会同步主库，但是从库已经存在了这个字段。
查看slave状态
show slave status\G

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
解决方法：mysql> reset slave;
解决方法1：跳过错误的事务
从库执行
stop slave;
set global sql_slave_skip_counter=1;
start slave;
show slave status\G

很多slave数据库的时候这样改太麻烦了。
解决方法2：
避免产生这种情况，我们不要让主库将alter语句记录到binlog中就行。
set sql_log_bin=off; 先关闭日志记录 binlog
alter table HA.T1 add age int default 0 after name; 然后执行alter语句
set sql_log_bin=on; 然后再开启日志记录

还有一种错误，就是从服务器上的日志文件和位置与主服务器不相同。

从服务器

主服务器

这种只需要再从服务器上具体指定即可。
从服务器操作
stop slave;
change master to master_host='192.168.0.64',master_user='repl',master_password='123456',master_port=3306,master_log_file='mysql-bin-master.000005',master_log_pos=856;
start slave;
show slave status\G

show binlog events in 'mysql-bin-master.000001'; 
查看binlog中的中的文件

mysql的binlog占用大量磁盘

查看当前日志保存天数：
show variables like '%expire_logs_days%';
临时生效
set global expire_logs_days=7;

配置文件可以配置只保留n天的binlog
vim /etc/my.cnf
expire_logs_days = 7

slave同步错误时候的解决方案

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
解决方法：mysql> reset slave;
解决方法1：跳过错误的事务
从库执行
stop slave;
set global sql_slave_skip_counter=1;
start slave;

purge master logs before '2020-09-23 19:33:00'; 删除之前同步过来的日志。 慎用
mysql> reset slave;  清除