十、 MySQL的主从复制
一.主从复制简介
2015年5月28日11时，12小时后恢复，损失：平均每小时106.48W$
1）高可用
2）辅助备份
3）分担负载
复制是 MySQL 的一项功能，允许服务器将更改从一个实例复制到另一个实例。
1）主服务器将所有数据和结构更改记录到二进制日志中。DDL DML DCL
2）从属服务器从主服务器请求该二进制日志并在本地应用其内容。
3）IO：请求主库，获取上一次执行过的新的事件，并存放到relaylog
4）SQL：从relaylog中将sql语句翻译给从库执行
二.主从复制原理
主从复制的前提
1）两台或两台以上的数据库实例
2）主库要开启二进制日志
3）主库要有复制用户
4）主库的server_id和从库不同
5）从库需要在开启复制功能前，要获取到主库之前的数据（主库备份，并且记录binlog当时位置）
6）从库在第一次开启主从复制时，时必须获知主库：ip，port，user，password，logfile，pos
IP：10.0.0.51
Port：3306
User：rep
Password：123
logFile：mysql-bin.000002
Pos：120
7）从库要开启相关线程：IO、SQL
8）从库需要记录复制相关用户信息，还应该记录到上次已经从主库请求到哪个二进制日志
9）从库请求过来的binlog，首先要存下来，并且执行binlog，执行过的信息保存下来
主从复制涉及到的文件和线程
主库：
1）主库binlog：记录主库发生过的修改事件
2）dump thread：给从库传送（TP）二进制日志线程
从库：
1）relay-log（中继日志）：存储所有主库TP过来的binlog事件
2）master.info：存储复制用户信息，上次请求到的主库binlog位置点
3）IO thread：接收主库发来的binlog日志，也是从库请求主库的线程
4）SQL thread：执行主库TP过来的日志
原理
1）通过change master to语句告诉从库主库的ip，port，user，password，file，pos
2）从库通过start slave命令开启复制必要的IO线程和SQL线程
3）从库通过IO线程拿着change master to用户密码相关信息，连接主库，验证合法性
4）从库连接成功后，会根据binlog的pos问主库，有没有比这个更新的
5）主库接收到从库请求后，比较一下binlog信息，如果有就将最新数据通过dump线程给从库IO线程
6）从库通过IO线程接收到主库发来的binlog事件，存储到TCP/IP缓存中，并返回ACK更新master.info
7）将TCP/IP缓存中的内容存到relay-log中
8）SQL线程读取relay-log.info，读取到上次已经执行过的relay-log位置点，继续执行后续的relay-log日志，执行完成后，更新relay-log.info
大前提条件：做主从复制之前，一定要保证主库和从库之间数据一致性
主从复制搭建实战
主库操作:
1）修改配置文件
#编辑mysql配置文件
[root@db01 ~]# vim /etc/my.cnf
#在mysqld标签下配置
[mysqld]
#主库server-id为1，从库不等于1
server_id =1
#开启binlog日志
log_bin=mysql-bin
2）创建主从复制用户
#登录数据库
[root@db01 ~]# mysql -uroot -p1
#创建rep用户
mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by '123';
#记录主库binlog及位置点
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 317 | | | |
+------------------+----------+--------------+------------------+-------------------+
从库操作:
1）修改配置文件
#修改db02配置文件
[root@db02 ~]# vim /etc/my.cnf
#在mysqld标签下配置
[mysqld]
#主库server-id为1，从库不等于1
server_id =5
#重启mysql
[root@db02 ~]# /etc/init.d/mysqld restart
#登陆数据库
[root@db02 ~]# mysql -uroot -p1
#执行change master to 语句
mysql> change master to
master_host='10.0.0.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000002',
master_log_pos=317,
master_port=3306;
mysql> start slave;
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
四.主从复制基本故障处理
IO线程报错：
连接主库
1）user password ip port
2）网络：不通，延时高，防火墙
请求binlog
1）binlog不存在或者损坏
更新relay-log和master.info
1.网络
[root@db02 ~]# ping 10.0.0.51
1）硬件层，路由，交换机，网络设备
2）网线
3）安全组规则
4）插错网线口
2.端口
[root@db02 ~]# telnet 10.0.0.51 3306
systemctl stop firewalld
firewalld-cmd --add-service=mysql
--add-port=3306/tcp
3.用户名
mysql> grant replication slave on *.* to rep@'%' identified by '123';
change master to
master_host='10.0.0.51',#1
master_user='rep',#2
master_password='123',#3
master_log_file='mysql-bin.000003',
master_log_pos=169853,
master_port=3306;
4.密码
[root@db03 data]# mysql -urep -p123 -h10.0.0.51
#rep@'db03' 反向解析
解决：
vim /etc/my.cnf
skip_name_resolve
SQL线程报错：
1）relay-log出现问题
2）从库做写入了
- 操作对象已存在（create）
- 操作对象不存在（insert update delete drop truncate alter）
- 约束问题、数据类型、列属性
处理方法一：
#临时停止同步
mysql> stop slave;
#将同步指针向下移动一个（可重复操作）
mysql> set global sql_slave_skip_counter=1;
#开启同步
mysql> start slave;
处理方法二：
#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加以下参数
slave-skip-errors=1032,1062,1007...
但是以上操作都是有风险存在的
处理方法三：
1）重新备份数据库，恢复到从库
2）给从库设置为只读
#在命令行临时设置
set global read_only=1;
#在配置文件中永久生效
read_only=1
五.延时从库
普通的主从复制可能存在不足
1）逻辑损坏怎么办？
2）不能保证主库的操作，从库一定能做
3）高可用？自动failover？
4）过滤复制
企业中一般会延时3-6小时
在SQL线程上做手脚，不影响IO线程的工作
延时从库配置方法
#停止主从
mysql> stop slave;
#设置延时为180秒
mysql> change master to master_delay=180;
#执行change master to 语句时配置
change master to
master_host='10.0.0.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000003',
master_log_pos=169853,
master_port=3306,
master_delay=180; #设置延时为180秒
#开启主从
mysql> start slave;
#查看状态
mysql> show slave status \G
SQL_Delay: 60
#延时从库停止方法
#停止主从
mysql> stop slave;
#设置延时为0
mysql> change master to master_delay=0;
#开启主从
mysql> start slave;
思考问题：
总数据量级500G，正常备份去恢复需要1.5-2小时
1）配置延时3600秒
mysql> change master to master_delay=3600;
2）主库
drop database db;
3）怎么利用延时从库，恢复数据？
提示：
1、从库relaylog存放在datadir目录下
2、mysqlbinlog 可以截取relaylog内容
3、show relay log events in 'db01-relay-bin.000001';
思路：
操作步骤：
1）从库配置延时3600秒
[root@db04 ~]# mysql
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 3600;
mysql> start slave;
2）主库上模拟数据
[root@db01 ~]# mysql -uroot -p1
mysql> create database delay;
mysql> use delay
mysql> create table delay(id int);
mysql> insert into delay values(1);
#模拟删除
mysql> select count(*) from delay;
+----------+
| count(*) |
+----------+
| 143 |
+----------+
mysql> drop database delay;
#在从库上查看已经删除，而延时从库还没有过来
3）在延时从库上停止sql线程
mysql> stop slave sql_thread;
4）准备恢复的数据
#备份数据
[root@db04 ~]# mysqldump -B delay > /tmp/delay.sql
#或[root@db04 ~]# mysqldump -A > /tmp/delay.sql
# 进入/data找新增的bin_log
[root@db04 ~]# cd /opt/mysql/data
[root@db04 data]# mysqlbinlog --base64-output=decode-rows -vvv db04-relay-bin.000002
找到drop database 之前 找到结束位置
# at 27555
#191117 22:48:19 server id 1 end_log_pos 27888 CRC32 0x453a73e2 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1574002099/*!*/;
drop database delay
#cat relay-log.info 找到起点
[root@db04 data]# cat relay-log.info
7
./db04-relay-bin.000002
283
#截取数据
mysqlbinlog --start-position=283 --stop-position=27555 db04-relay-bin.000002 > /tmp/delay2.sql
5）恢复数据
[root@db04 data]# scp /tmp/delay* 172.16.1.51:/tmp/
[root@db01 ~]# mysql -uroot -p1 < /tmp/delay.sql
[root@db01 ~]# mysql -uroot -p1 < /tmp/delay2.sql
#查看主库
mysql> select count(*) from delay.delay;
+----------+
| count(*) |
+----------+
| 143 |
+----------+
6）延时从库开启sql线程
mysql> start slave sql_thread;
mysql> show slave status\G
六.半同步复制
从MYSQL5.5开始，支持半自动复制。之前版本的MySQL Replication都是异步（asynchronous）的，主库在执行完一些事务后，是不会管备库的进度的。如果备库不幸落后，而更不幸的是主库此时又出现Crash（例如宕机），这时备库中的数据就是不完整的。简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务了。
半同步复制（Semi synchronous Replication）则一定程度上保证提交的事务已经传给了至少一个备库。
出发点是保证主从数据一致性问题，安全的考虑。
5.5 出现概念，但是不建议使用，性能太差
5.6出现group commit 组提交功能，来提升开启半同步复制的性能
5.7更加完善了，在group commit基础上出现了MGR
5.7的增强半同步复制的新特性：after commit； after sync；
半同步复制开启方法
开启半同步前提条件，必须做完主从复制
1）安装（主库）
#登录数据库
[root@db01 ~]# mysql -uroot -poldboy123
#查看是否有动态支持
mysql> show global variables like 'have_dynamic_loading';
#安装自带插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';
#启动插件
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
#设置超时
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
#修改配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加如下内容（不用重启库）
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
检查安装：
mysql> show variables like'rpl%';
mysql> show global status like 'rpl_semi%';
2）安装（从库）
#登录数据库
[root@mysql-db02 ~]# mysql -uroot -poldboy123
#安装slave半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';
#启动插件
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
#重启io线程使其生效
mysql> stop slave io_thread;
mysql> start slave io_thread;
#编辑配置文件（不需要重启数据库）
[root@mysql-db02 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加如下内容
[mysqld]
rpl_semi_sync_slave_enabled =1
注：相关参数说明
rpl_semi_sync_master_timeout=milliseconds
设置此参数值（ms）,为了防止半同步复制在没有收到确认的情况下发生堵塞，如果Master在超时之前没有收到任何确认，将恢复到正常的异步复制，并继续执行没有半同步的复制操作。
rpl_semi_sync_master_wait_no_slave={ON|OFF}
如果一个事务被提交,但Master没有任何Slave的连接，这时不可能将事务发送到其它地方保护起来。默认情况下，Master会在时间限制范围内继续等待Slave的连接，并确认该事务已经被正确的写到磁盘上。
可以使用此参数选项关闭这种行为，在这种情况下，如果没有Slave连接，Master就会恢复到异步复制。
测试半同步
#创建两个数据库，test1和test2
mysql> create database test1;
Query OK, 1 row affected (0.04 sec)
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
#查看复制状态
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
#此行显示2，表示刚才创建的两个库执行了半同步
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.06 sec)
#从库查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test1 |
| test2 |
+--------------------+
#关闭半同步（1:开启 0:关闭）
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 0;
#查看半同步状态
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF | #状态为关闭
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
#再一次创建两个库
mysql> create database test3;
Query OK, 1 row affected (0.00 sec)
mysql> create database test4;
Query OK, 1 row affected (0.00 sec)
#再一次查看半同步状态
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
#此行还是显示2，则证明，刚才的那两条并没有执行半同步否则应该是4
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
注:不难发现，在查询半同步状态是，开启半同步，查询会有延迟时间，关闭之后则没有
七.过滤复制
主库：
白名单，do :只记录白名单中列出的库的二进制日志
- binlog-do-db
黑名单，Ignore：被记录到黑名单的库，执行的所有SQL语句不计入binlog
- binlog-ignore-db
从库：
白名单：被记录到白名单的库，从库只取该库的binlog数据 mysqlbinlog -d
- --replicate-do-db=test
- --replicate-do-table=test.t1
- --replicate-wild-do-table=test.t2
黑名单：被记录到黑名单的库，从库不取该库的binlog数据
- --replicate-ignore-db
- --replicate-ignore-table
- --replicate-wild-ignore-table
复制过滤配置：
[root@db01 data]# vim /data/3307/my.cnf
#在[mysqld]标签下添加
replicate-do-db=world
#关闭MySQL
mysqladmin -S /data/3307/mysql.sock shutdown
#启动MySQL
mysqld_safe --defaults-file=/data/3307/my.cnf &
测试复制过滤：
第一次测试：
1）主库：
[root@db02 ~]# mysql -uroot -p123 -S /data/3308/mysql.sock
mysql> use world
mysql> create table t1(id int);
2）从库查看结果：
[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock
mysql> use world
mysql> show tables;
第二次测试：
1）主库：
[root@db02 ~]# mysql -uroot -p123 -S /data/3308/mysql.sock
mysql> use test
mysql> create table tb1(id int);
2）从库查看结果：
[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock
mysql> use test
mysql> show tables;
