文章目录
- 四、过滤复制
- 3、过滤复制
MySQL主从
一、主从复制介绍
1、主从复制是什么
将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态，称之为主从复制。
一句话表示就是，主数据库做什么，从数据库就跟着做什么
2、为何要做主从
1>为实现服务器负载均衡/读写分离做铺垫，提升访问速度
2>通过复制实现数据的异地备份，保障数据安全
3>提高数据库系统的可用性
###################### 实现服务器负载均衡/读写分离 #######################
1》#什么是读写分离
有了主从保持数据一致作为大前提，我们便可以可以分离读写操作，其中Master负责写操作的负载，也就是说一切写的操作都在Master上进行，而读的操作则分摊到Slave上进行。那么读写分离的作用是什么呢
2》#读写分离的作用
读写分离可以大大提高读取的效率。
在一般的互联网应用中，经过一些数据调查得出结论，读/写的比例大概在 10：1左右 ，也就说写操作非常少，大量的数据操作是集中在读的操作（如某些应用，像基金净值预测的网站。其数据的更新都是有管理员更新的，即更新的用户比较少。而查询的用户数 量会非常的多。）
此时我们可以制作一主多从，因为写操作很少，所以由一个主库负责即可，而大量的读操作则分配给多个从库，这样占据比例最大的读操作的压力就被负载均衡了，因此读效率会得到了极大的提升，
另外，还有一个原因就是：熟悉DB的研发人员都知道，写操作涉及到锁的问题，不管是行锁还是表锁还是块锁，都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上，而读操作其其他的N个节点上进行，这从另一个方面有效的提高了读的效率，保证了系统的性能及高可用性
3》#具体做法
方案一：
就是主库写，从库读
方案二：
主库负责写，还有部分读，从库只负责读，而且是读操作的主力
即当主服务器比较忙时，部分查询请求会自动发送到从服务器中，以降低主服务器的工作负荷
####################### 复制实现数据的异地备份 #######################
可以定期的将数据从主服务器上复制到从服务器上，这实现了数据的异地备份。
在传统的备份体制下，是将数据备份在本地。此时备份作业与数据库服务器运行在同一台设备上，当备份作业运行时就会影响到服务器的正常运行。有时候会明显的降低服务器的性能。
同时，将备份数据存放在本地，也 不是很安全。如硬盘因为电压等原因被损坏或者服务器被失窃，此时由于备份文件仍然存放在硬盘上，数据库管理员无法使用备份文件来恢复数据。这显然会给企业 带来比较大的损失
####################### 据库系统的可用性 #######################
3、主从复制的原理
主从复制的三个步骤：
(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events） (2) slave的io线程将master的binary log events拷贝到它的中继日志(relay log)； (3) slave的sql线程解析中继日志中的事件并在从库执行，保持与主库一致。
复制过程有一个很重要的限制
复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作
# 从库准备
（1）从库change master to 时，ip port user password binlog position写入到master.info进行记录
（2）从库 start slave 时，会启动IO线程和SQL线程
# 同步的过程
1.从库的IO线程，读取master.info信息，获取主库信息并连接主库
2.主库接收从库的链接请求后，会生成一个准备binlog DUMP的线程，来响应从库
3.主库一旦有新的日志生成，会发送“信号”给主库的binlog dump线程，然后binlog dump线程会读取binlog日志的更新
4.TP(传送)给从从库的IO线程
5.IO线程将收到的日志存储到了TCP/IP 缓存
6.写入TCP/IP缓存后，立即返回ACK给主库 ，此时主库工作完成
7.IO线程更新master.info文件binlog 文件名和postion
8.IO线程将缓存中的数据，存储到relay-log日志文件,此时io线程工作完成
9.从库SQL线程读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点
10.从库SQL线程基于从步骤9中获取到的起点，去中继日志relay-log.000001获取后续操作，在从库回放relay-log
11.SQL线程回放完成之后，会更新relay-log.info文件，把当前操作的位置记入，作为下一次操作的起点。
12. relay-log会有自动清理的功能
4、主从复制的日志格式
主从复制的日志格式是二进制格式
【二进制日志】常见主从复制的二进制的三种格式：
binlog_format=statement
binlog_format=row
binlog_format=mixed注：row的复制方式更能保证主从库数据的一致性，但日志量较大，在设置时考虑磁盘的空间问题
5、主从复制的方式
mysql 主从复制的两种复制方式：
1>异步复制
2>半同步复制
二、异步复制（Asynchronous replication）
MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整
1、异步复制原理介绍
MySQL 主从复制默认是异步的模式：
2、mysql异步复制的部署
注：使用的主从数据库版本一致（mysql5.7）
【主库异步主从制作】（停服时）
########################### 主库 IP：192.168.15.51 #########################
1）#（账号创建，用于主从复制，只授予予replication slave权限，这里必须 *.* 不能指定库授权，因为 replication slave 是全局的）
mysql> grant replication slave on *.* to 'hzl'@'%' identified by 'Hzl@20144'; #创建用户，并指定用户只授予复制权限
mysql> flush privileges; #刷新权限
2) #修改主库配置文件
开启主库的Binlog，并设置server-id（设置服务非id号主库与从库不能一致）
[mysqld]
#节点ID，确保唯一
server-id = 1
#开启mysql的binlog日志功能
log-bin = mysql-bin
#控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，
性能最差，最安全
sync_binlog = 1
#binlog日志格式
binlog_format = row
#binlog过期清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size= 512m
#不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制黏贴下述配置项，写多行
binlog-ignore-db=mysql
# 表中自增字段每次的偏移量
auto-increment-offset = 1
# 表中自增字段每次的自增量
auto-increment-increment = 1
#跳过从库错误
slave-skip-errors = all
3）#重启数据库
systemctl restart mysqld
4）#主库备份 （备份时，先锁表，保证备份数据一致）
mysql > flush tables with read lock; #先进行锁表操作
mysql> show master status; #查看状态，获取二进制日志的信息
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysqldump -uroot -pHzl@20144 -A -E -R --triggers --master-data=2 --single-transaction > /tmp/all.sql #对数据库的数据进行备份
#注：
-A或--all-databases #表示所有库
-E或--events #备份事件调度器
-R #备份存储过程和函数数据（如果开发写了函数和存储过程，就备，没写就不备）
--master-data=1 #备份文件中 change， master语句是没有注释的，默认为1
--single-transaction： #快照备份 （搭配--master-data可以做到热备）该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态
mysql > unlock tables; #对数据库解锁，恢复对主数据库的操作
5）#将备份文件发送给从库
scp /tmp/all.sql root@192.168.15.52:/tmp #同步数据
########################### 从库 IP：192.168.15.52 #########################
1）#测试复制账号
mysql -uhzl -pHzl@20144 -h 192.168.15.51 #远程账号测试
2）#导入初始数据
mysql -uroot -pHzl@20144 < /tmp/all.sql
3）#修改从库配置文件（增加server-id，这个值是唯一的，要区别于主库和其他从库)
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.% #表示使用过程中忽略mysql库下所有
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
#从库也可以开启binlog，但通常关闭
#log-bin=mysql-bin
4）#重启从库
systemctl restart mysqld
5）#配置复制 （主库 查看一下binlog日志名与位置）
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 680 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
6）#从库进行配置
[root@db02 ~]# mysql -uroot -pHzl@20144 # 登录然后执行以下命令
change master to
master_host='192.168.15.51', -- 主库服务器的IP
master_port=3306, -- 主库端口
master_user='hzl', -- 主库用于复制的用户
master_password='Hzl@20144', -- 主库密码
master_log_file='mysql-bin.000003', -- 主库日志名
master_log_pos=680; -- 主库日志偏移量,即从何处开始复制
7）#从库启动slave线程，并检查
#方式一：
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
#检查线程
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.15.51
Master_User: hzl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 680
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes #负责从库去主库读取二进制日志，并写入从库的中继日志
Slave_SQL_Running: Yes #负责将中继日志转换成sql语句执行
Replicate_Do_DB:
Replicate_Ignore_DB:
········
·····
ps ： #主要看注释的位置状态是否开启，两个状态显示yes表示配置成功
#方式二：
mysql> show processlist\G
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 2. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 599
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 599
State: Slave has read all relay log; waiting for more updates #这个表状态开启成功
Info: NULL
3 rows in set (0.00 sec)
【主库异步主从制作】（运行时）
1)#准备纯净的从库环境
主库：192.168.15.51
从库：192.168.15.52
2)#修改主库配置文件
开启主库的Binlog，并设置server-id（设置服务非id号主库与从库不能一致）
[mysqld]
#节点ID，确保唯一
server-id = 1
#开启mysql的binlog日志功能
log-bin = mysql-bin
#控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，
性能最差，最安全
sync_binlog = 1
#binlog日志格式
binlog_format = row
#binlog过期清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size= 512m
#不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制黏贴下述配置项，写多行
binlog-ignore-db=mysql
# 表中自增字段每次的偏移量
auto-increment-offset = 1
# 表中自增字段每次的自增量
auto-increment-increment = 1
#跳过从库错误
slave-skip-errors = all
3)#重启主库
systemctl restart mysqld
4)#主库创建账号
mysql> grant replication slave on *.* to 'hzl'@'%' identified by 'Hzl@20144'; #创建用户，并指定用户只授予复制权限
mysql> flush privileges; #刷新权限
#创建测试库与表
create database db1;
create table t1(id int);
5)#模拟主库运行，有数据写入
for i in `seq 1 1000000`
do
mysql -uroot -p"Hzl@20144" -e "insert db1.t1 values($i)";
sleep 1;
done
6)#主库全备数据
#不打点备份(no)
mysqldump -uroot -p"Hzl@20144" -A -R --triggers > /tmp/all.sql
#打点备份(yes)
mysqldump -uroot -p"Hzl@20144" -A -R --triggers --master-data=2 --single-transaction > /tmp/all.sql
7)#将热备数据传达从库
scp /tmp/all.sql 192.168.15.52:/tmp
8)#修改从库配置文件
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.% #表示使用过程中忽略mysql库下所有
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
#从库也可以开启binlog，但通常关闭
#log-bin=mysql-bin
9.重启从库
systemctl restart mysqld
10)#在从库导入全备数据(导入打点备份，原因如下)
#导入不打点的数据的话，接下来主从，位置点只能去主库查看 show master status; 而数据在不停地往主库里插入，该命令查看的只是那一刻binlog的记录的位置，基于该位置同步数据会丢失所获得位置点往前一直到全备的数据，以及操作从库过程中新写入主库的数据
#如果导入的是打点的数据，那么全量备份的起始点可以从备份文件中获得，从库可以以此为同步的基准点，去主库获取全量备份之后的数据
11)#查看sql文件中的位置点（如果是打点备份的话）该位置即主库刚刚做完全量备份时，主库binlog日志所处的位置
[root@db01 ~]# head -50 /tmp/all.sql|grep 'MASTER_LOG_POS' #使用打点备份才能看到log pos
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=129005;
#导入数据
mysql -uroot -pHzl@20144 < /tmp/all.sql
12)#从库配置同步
[root@db02 ~]# mysql -uroot -pHzl@20144 # 登录然后执行
change master to
master_host='192.168.15.51',
master_port=3306,
master_user='hzl',
master_password='Hzl@20144',
master_log_file='mysql-bin.000003',
master_log_pos=129005;
13）#开启SQL线程和IO线程
start slave;
14）#查看主从状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.15.51
Master_User: hzl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 99700
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 99340
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
.........
......
ps ： #主要看注释的位置状态是否开启，两个状态显示yes表示配置成功
3、mysql常见的主从故障
【主从复制基本故障处理】
#清空主从配置：
reset slave all;
############################ (1) IO线程问题 ##########################
1)#检测网络:
[root@db01 ~]# ping 172.16.1.51
2)#检测端口:
[root@db01 ~]# telnet 172.16.1.51 3307
#阿里云机器有的禁ping
[root@db01 ~]# tcping 172.16.1.51 3306
3)#检测账号,密码:
#根据授权命令
grant replication slave on *.* to hzl@'%' identified by 'Hzl@20144';
#连接测试
mysql -uhzl -pHzl@20144 -h127.0.0.1 -P 3307
4)#查看binlog是否存在
show master status;
5）#反向解析
#没有跳过反向解析
ERROR 1045 (28000): Access denied for user 'root'@'db01' (using password: YES)
#配置
skip_name_resolve
############################ （2）SQL线程问题 ##########################
1)#主库有的数据，从库没有
主库:a库
从库:没有a库
2)#从库有的数据，主库没有
从库:a库
主库:要创建a库
3)#主库与从库数据库结构有差别
#处理方法一：
#临时停止同步
mysql> stop slave;
#将同步指针向下移动一个（可重复操作）解决不了根本问题
mysql> set sql_slave_skip_counter=1;
#开启同步
mysql> start slave;
#处理方法二：
#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加以下参数
slave-skip-errors=1032,1062,1007
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突
#处理方法三：
1.要求,主从复制之前,主库和从库的数据保证一致.
2.在从库上设置 只读:set read-only=1;
【延迟从库】
用延时从库可以做备份：
主库执行删除的时候，从库还没有删除，可以把表数据拿出来恢复回去,企业中一般会延时3-6小时
#########################（1）延时从库配置 ###########################
1）#已经有主从的情况下
1.停止主从
mysql> stop slave;
2.设置延时为180秒
mysql> CHANGE MASTER TO MASTER_DELAY = 180;
3.开启主从
mysql> start slave;
4.查看状态
mysql> show slave status \G
SQL_Delay: 180
5.主库创建数据，会看到从库值变化，创建的库没有创建
SQL_Remaining_Delay: 170
2）#没有主从复制的情况下
1.修改主库，从库配置文件
server_id
开启binlog
2.保证从库和主库的数据一致
3.执行change语句
change master to
master_host='172.16.1.50',
master_user='hzl',
master_password='Hzl@20144',
master_log_file='mysql-bin.000001',
master_log_pos=2752,
master_delay=180;
3）#延时从库停止方法
1.停止主从
mysql> stop slave;
2.设置延时为0
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
3.开启主从
mysql> start slave;
#注：做延时从库只是为了备份，不提供服务
#########################（2）思考：延时到底是在哪里延时的 ###########################
思考：IO线程还是SQL线程做的手脚？
1）#去主库创建一个库
create database ttt;
2）#查看从库的relaylog看看有没有内容
mysqlbinlog --base64-output=decode-rows -vvv db03-relay-bin.000002
3）#总结：
延时从库是在SQL线程做的手脚，IO线程已经把数据放到relay-log里了，SQL线程在执行的时候，会延迟你设定的时间长度.
#########################（3）使用延时从库恢复数据###########################
1#场景
总数据量级500G，正常备份去恢复需要1.5-2小时
1）配置延时3600秒
mysql> CHANGE MASTER TO MASTER_DELAY = 3600;
2）主库
drop database db;
3）怎么利用延时从库，恢复数据？
2.#环境准备
1.进行每日的全备
mysqldump -uroot -p123 -A -R --triggers --master-data=2 –single-transaction > /backup/full.sql
2.调整延时从库延时时间为60分钟
stop slave;
CHANGE MASTER TO MASTER_DELAY = 3600;
start slave;
3.主库写入新数据
create database yanshi;
use yanshi;
create table yanshi(id int);
insert into yanshi values(1),(2),(3),(4);
create database yanshi2;
use yanshi2;
create table yanshi2(id int);
insert into yanshi2 values(1),(2),(3),(4);
3.#模拟删除数据
1.删除一个库，可以是之前的，也可以是刚创建的
#因为刚我们看了，只要我执行了，从库已经写到了relay-log，跟我数据库里的数据关系不大
drop database world;
4.#使用延时从库恢复数据
1.停止从库sql线程
stop slave sql_thread;
2.查看状态
show slave status;
3.备份从库数据
mysqldump -uroot -p123 -B world > /backup/congku.sql
4.截取一下relay-log
1）确认起点，查看relay-log.info即可
[root@db02 data]# cat relay-log.info
./db02-relay-bin.000005283
2）确认终点，找到drop语句之前
[root@db02 data]# mysqlbinlog --base64-output=decode-rows -vvv db02-relay-bin.000005
3）截取数据
[root@db02 data]# mysqlbinlog --start-position=283 --stop-position=1112 db02-relay-bin.000005 > /tmp/yanshi.sql
5.#将从库全备的数据与relaylog截取数据拷贝到主库
scp /tmp/yanshi.sql 172.16.1.51:/tmp/
scp /backup/congku.sql 172.16.1.51:/tmp/
6.#将数据导入主库
#导入前不要停掉binlog，
mysql < /tmp/yanshi.sql
mysql < /tmp/congku.sql
7.#开启从库的sql线程
start slave sql_thread;
ps ：#主库那边执行到删除的时候没关系，因为还有创建的部分，他会再次把数据创建回来
4、异步复制的问题
MYSQL5.5之前版本的主从复制都是异步（asynchronous）的，而在异步复制中，主库执行完操作后，写入binlog日志后，就返回客户端，这一动作就结束了，主库并不会验证从库有没有收到binlog日志、以及收到的binlog是否完整，那如果主库提交一个事务并写入Binlog中后，当从库还没有从主库得到Binlog时，主库宕机了或因磁盘损坏等故障导致该事务的Binlog丢失了，那从库就不会得到这个事务，也就造成了从库与主库数据不一致的问题，我们也就无法使用备库来继续提供数据一致的服务了，半同步复制可以解决该问题
三、半同步复制（Semisynchronous replication）
1、半同步复制原理介绍
从MYSQL5.5开始，支持半同步复制（Semi synchronous Replication）在一定程度上保证提交的事务已经传给了至少一个备库
半同步复制介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用半同步复制原理图：
1)#一个事务操作的完成需要记完两份日志，即主从的binlog是同步的
半同步复制，当主库每提交一个事务后，不会立即返回，而是等待其中一个从库接收到Binlog并成功写入Relay-log中才返回客户端，
所以这样就保证了一个事务至少有两份日志，一份保存在主库的Binlog，另一份保存在其中一个从库的Relay-log中，从而保证了数据的安全性和一致性。
2)#半同步即并非完全同步
半同步复制的“半”体现在，虽然主从库的Binlog是同步的，但主库不会等待从库的sql线程执行完Relay-log后才返回，而是确认从库的io线程接收到Binlog，达到主从Binlog同步的目的后就返回了
所以从库的数据对于主库来说还是有延时的，这个延时就是从库sql线程执行Relay-log的时间。所以只能称为半同步。
3)#半同步复制超时则会切换回异步复制，正常后则切回半同步复制
2、半同步复制的开启方法
半同步模式是作为MySQL5.5的一个插件来实现的，主从库使用的插件不一样
1)#检查主从mysql是否支持动态添加插件 （查看主库与从库是否支持，mysql5.5之上默认支持）
mysql> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES |
+------------------------+
1 row in set (0.00 sec)
2）#分别在主从库上安装对用插件
#插件一般默认在MySQL安装目录/lib/plugin下，可以去查看一下是否存在
[root@localhost ~]# ls /usr/lib64/mysql/plugin/ | grep semisync
semisync_master.so #主库的插件
semisync_slave.so #从库插件
#安装插件
主库:mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
从库:mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
#安装完成后，在plugin表（系统表）中查看一下
#方式一：
mysql> select * from mysql.plugin; #查看是否安装成功
+----------------------+----------------------+
| name | dl |
+----------------------+----------------------+
| rpl_semi_sync_master | semisync_master.so |
| validate_password | validate_password.so |
+----------------------+----------------------+
2 rows in set (0.00 sec)
#方式二：
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
#方式三：查看mysql所有的插件
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
3）#在主从库开启半同步复制
################################# 主库 ############################
#启动插件 （临时开启）
mysql> set global rpl_semi_sync_master_enabled=1;
#设置超时时间
mysql> set global rpl_semi_sync_master_timeout=30000;
#修改配置文件 (永久开启)
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加如下内容（不用重启库，因为上面已经开启了）
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
################################# 从库 ############################
#启动插件 （临时开启）
mysql> set global rpl_semi_sync_slave_enabled=1;
#重启io线程使其生效
mysql> stop slave io_thread; #停止io线程
mysql> start slave io_thread; #启动io线程，使插件生效
#编辑配置文件 (永久开启)
[root@mysql-db02 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加如下内容 （不需要重启数据库）
[mysqld]
rpl_semi_sync_slave_enabled =1
4）#在主库上查看半同步复制的状态
#方式一：(过滤查询)
mysql> show status like 'Rpl_semi_sync_master_status%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)
#方式二：（查询全部）
mysql> show status like '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| 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 | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
###### 注释： 在输出信息中，重点关注三个参数：
#ON表示半同步复制打开，OFF表示关闭
rpl_semi_sync_master_status OFF/ON
#这个数字表示主库当前有几个事务说通过半同步复制到从库的
rpl_semi_sync_master_yes_tx [number]
#表示有几个事务不是通过半同步复制到从库的
rpl_semi_sync_master_no_tx [number]
3、半同步复制的测试
事实上，半同步复制并不是严格意义上的半同步复制
1》当半同步复制发生超时时（由rpl_semi_sync_master_timeout参数控制，单位是毫秒，默认为10000，即10s），会暂时关闭半同步复制，转而使用异步复制。
2》当master dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主从又重新恢复为半同步复制实验测试：
#验证分为三个阶段
1)#在Slave执行stop slave之前，主的insert操作很快就能返回。
2)#在Slave执行stop slave后，主的insert操作需要10.01s才返回，而这与rpl_semi_sync_master_timeout参数的时间相吻合。
这时，查看两个状态的值，均为“OFF”了。同时，主的error.log中打印如下信息：
2021-07-23T16:51:49.855452Z 6 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000003, pos: 1447), semi-sync up to file mysql-bin.000003, position 1196.
2021-07-23T16:51:49.855742Z 6 [Note] Semi-sync replication switched OFF.
3)#在Slave执行start slave后，主的insert操作很快就能返回，此时，两个状态的值也变为“ON”了。
同时，主的error.log中会打印如下信息
2021-07-23T16:52:40.477098Z 7 [Note] Start binlog_dump to master_thread_id(7) slave_server(2), pos(mysql-bin.000003, 1196)
2021-07-23T16:52:40.477168Z 7 [Note] Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000003, 1196)
2021-07-23T16:52:40.523475Z 0 [Note] Semi-sync replication switched ON at (mysql-bin.000003, 1447)
#环境变量
mysql> show variables like '%Rpl%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 30000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
7 rows in set (0.00 sec)
四、过滤复制
1、企业中的多种环境
1)# 开发环境
1>开发完自己测试
2)# 测试环境
1>性能测试
2>功能测试
3)# 预发布环境（beta，内测不删档）
1>只是一台服务器
2>没有真实的流量
3>连的是线上数据库
### 疑问：如果有一个待上线需求，需要改动数据库表结构，怎么处理？
1>先把预发布环境使用的数据库切换为测试环境使用的数据库
2>然后有针对性的测试下数据库的变更是否会影响线上当前代码程序的运行，测试通过后再上线
4）# 灰度环境
1>1台或多台线上主机
2>链接的是线上数据库
3>真实流量
#灰度发布，又称金丝雀发布
1>金丝雀发布这一术语源于煤矿工人把笼养的金丝雀带入矿井的传统。矿工通过金丝雀来了解矿井中一氧化碳的浓度，如果一氧化碳的浓度过高，金丝雀就会中毒，从而使矿工知道应该立刻撤离
2>灰度发布发生在预发布环境之后，生产环境之前对应到软件开中，则是指在发布新的产品特性时通过少量的用户试点确认新特性没有问题，确保无误后推广到更大的用户使用群体。
3>生产环境一般会部署在多台机器上，以防某台机器出现故障，这样其他机器可以继续运行，不影响用户使用。灰度发布会发布到其中的几台机器上，验证新功能是否正常。如果失败，只需回滚这几台机器即可
5)# 沙盒环境
1>沙盒环境又称测试环境和开发环境，是提供给开发者开发和测试用的环境。
2>沙盒通常严格控制其中的程序所能访问的资源
比如：沙盒可以提供用后即回收的磁盘及内存空间。在沙盒中，网络访问、对真实系统的访问、对输入设备的读取通常被禁止或是严格限制。也就是说所谓的沙盒测试就是在产品未上线前在内部环境或网络下进行的测试，此时在正常的线上环境是无法看到或查询到该产品或项目的，只有产品在测试环境下无问题上传到生产环境之后，用户才能看到该产品或功能
6)# 生产环境
2、过滤复制的两种方式
【黑名单】
不记录黑名单列出的库的二进制日志
#参数
replicate-ignore-db=test #忽略test库及以下的所有表
replicate-ignore-table=test.t1
replicate-wild-ignore-table=test.t% #支持通配符,t开头的表
#注意：
replicate-ignore-table依赖参数replicate-ignore-db：
#即如果想忽略某个库下的某张表，需要一起配置
replicate-ignore-db=test
replicate-ignore-table=test.t1
【白名单】
只执行白名单中列出的库或者表的中继日志
#参数：
replicate-do-db=test #只看test库及以下的所有表
replicate-do-table=test.t1
replicate-wild-do-table=test.t% #支持通配符,t开头的表
注意：
replicate-do-table依赖参数replicate-do-db
#即如果想只接收某个库下的某张表，需要一起配置
replicate-do-db=test
replicate-do-table=test.t1
【设置黑白名单的影响】（主从库）
1)#黑白名单对**主库**的影响是：是否记录binlog日志
在主库上设置白名单:只记录白名单设置的库或者表、相关的SQL语句到binlog中
在主库上设置黑名单:不记录黑名单设置的库或者表、相关的SQL语句到binlog中
2)#黑白名单对**从库**的影响是：sql线程是否执行io线程拿到的binlog,IO线程一定会拿到所有的binlog，但
3、过滤复制
1)#做好一主两从
#如果查看从库show slave status\G报错
A slave with the same server_uuid/server_id as this slave has connected to the master;
#解决方案如下：
如果server_id相同，请修改/etc/my.cnf中server_id的配置
如果server_uuid相同，请删除auto.cnf文件(auto.cnf文件在/etc/my.cnf中datadir配置的目录下)，然后重启数据库，数据库会重新生成server_uuid和auto.cnf文件
2）#从库1配置
[root@db02 ~]# vim /etc/my.cnf #添加配置文件
[mysqld]
replicate-do-db=big_hzl
[root@db02 ~]# systemctl restart mysqld #重启服务
#查看主从状态
show slave status;
3）#从库2配置
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
replicate-wild-do-table=big_hzl.t%
replicate-do-db=small_hzl
replicate-do-table=small_hzl.t1
[root@db03 ~]# systemctl restart mysqld
#查看主从状态
show slave status;
4）#主库创建表测试
create database big_hzl;
create database small_hzl;
use big_hzl;
create table t1(id int); -- 表名以t开头
create table t2(id int); -- 表名以t开头
create table x3(id int);
use small_hzl;
create table t1(id int);
create table t2(id int);
create table t3(id int);
5）#从库查看表
从库1上只能看到big_hzl库下的所有表：t1、t2、x3
从库2上只能看到big_hzl库下t开头的那两张表，看不到表x3，small_hzl库下的t1表
6）#测试：尝试把白名单配置到主库
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
replicate-do-db=db1
[root@db01 ~]# systemctl restart mysql
#测试同步
五、主从复制架构
【主备架构】
只有主库提供读写服务，备库仅留作备用
jdbc:mysql://vip:3306/xxdb
1）#高可用分析：高可用，主库挂了，keepalive（只是一种工具）会自动切换到备库。这个过程对业务层是透明的，无需修改代码或配置。
2）#高性能分析：读写都操作主库，很容易产生瓶颈。大部分互联网应用读多写少，读会先成为瓶颈，进而影响写性能。另外，备库只是单纯的备份，资源利用率50%，这点方案二可解决。
3）#一致性分析：读写都操作主库，不存在数据一致性问题。
4）#扩展性分析：无法通过加从库来扩展读性能，进而提高整体性能。
5)#可落地分析：两点影响落地使用，
【双主架构】
两个主库同时提供服务，负载均衡
jdbc:mysql://vip:3306/xxdb
1)#高可用分析：高可用，一个主库挂了，不影响另一台主库提供服务。这个过程对业务层是透明的，无需修改代码或配置。
2)#高性能分析：读写性能相比于方案一都得到提升，提升一倍。
3)#一致性分析：存在数据一致性问题。请看，一致性解决方案。
4)#扩展性分析：当然可以扩展成三主循环，但笔者不建议（会多一层数据同步，这样同步的时间会更长），如果非得在数据库架构层面扩展的话，扩展为方案四
5）#可落地分析：两点影响落地使用
【主从架构】
一主多从，读写分离
写：jdbc:mysql://master-ip:3306/xxdb
读：
jdbc:mysql://slave1-ip:3306/xxdb
jdbc:mysql://slave2-ip:3306/xxdb
1)#高可用分析：主库单点，从库高可用。一旦主库挂了，写服务也就无法提供。
2)#高性能分析：大部分互联网应用读多写少，读会先成为瓶颈，进而影响整体性能。读的性能提高了，整体性能也提高了。另外，主库可以不用索引，线上从库和线下从库也可以建立不同的索引（线上从库如果有多个还是要建立相同的索引，不然得不偿失；线下从库是平时开发人员排查线上问题时查的库，可以建更多的索引）
3）#一致性分析：存在数据一致性问题。请看，一致性解决方案。
4）#扩展性分析：可以通过加从库来扩展读性能，进而提高整体性能。（带来的问题是，从库越多需要从主库拉取binlog日志的端就越多，进而影响主库的性能，并且数据同步完成的时间也会更长）
5）#可落地分析：两点影响落地使用
第一，数据一致性问题，一致性解决方案可解决问题。
第二，主库单点问题，笔者暂时没想到很好的解决方案。
ps
【级联复制】（Master –Slaves - Slaves）
多级复制架构只是在一主多从的基础上，再主库和各个从库之间增加了一层二级主库Master2，这层二级主库仅仅用来将一级主库推送给它的BInlog日志再推送给各个从库，以此来减轻一级主库的推送压力
缺点就是Binlog日志要经过两次复制才能到达从库，增加了复制的延时 可以通过在二级从库上应用Blackhol存储引擎（黑洞引擎）来解决这一问题，降低多级复制的延时 “黑洞引擎”就是写入Blackhole表中数据并不会写到磁盘上，所以这个Blackhole表永远是个空表，对数据的插入/更新/删除操作仅在Binlog中记录，并复制到从库中去
【双主+主从架构】
看似完美，但也一样有弊端
写：
jdbc:mysql://vip:3306/xxdb
读：
jdbc:mysql://slave1-ip:3306/xxdb
jdbc:mysql://slave2-ip:3306/xxdb
1)#高可用分析：高可用。
2)#高性能分析：高性能。
3)#一致性分析：存在数据一致性问题。请看，一致性解决方案 。
4)#扩展性分析：可以通过加从库来扩展读性能，进而提高整体性能。（带来的问题同方案二）
5)#可落地分析：同方案二，但数据同步又多了一层，数据延迟更严重
六、数据一致性解决方案
【导致主从数据延时因素】
注：图中圈出的位置是数据同步的地方
数据同步（从主库把binlog日志推送给从库，然后在从库再执行一遍）是需要时间的，这个同步时间内主库和从库的数据会存在不一致的情况，遇到网络延迟，或者主库并发事务过多的情况（主库可以并行
事务，但从库的是SQL线程串行回放的，如果并发事务过大，从库必然忙活不过来，造成延迟），都会
加剧不一致的情况
果同步过程中有读请求，那么读到的就是从库中的老数据,如图所示：
【主从延迟】
1)#延时指标1：查看从库获取到主库binlog的时间
show slave status;
查看延时时间
Seconds_Behind_Master: 0
2)#延时指标2：查看从库回放的日志量与主库日量的差
- 主库： show master status ;
【主从延迟优化】
1）硬件方面
#总结，硬件强劲，延迟自然会变小。一句话，缩小延迟的解决方案就是花钱和花时间。2）文件系统方面
#master端修改linux、Unix文件系统中文件的etime属性
由于每当读文件时OS都会将读取操作发生的时间回写到磁盘上，对于读操作频繁的数据库文件来说这是没必要的，只会增加磁盘系统的负担影响I/O性能。
可以通过设置文件系统的mount属性，组织操作系统写atime信息
在linux上的操作为：打开/etc/fstab，加上noatime参数/dev/sdb1 /data reiserfs noatime 1 2然后重新mount文件系统
#mount -o remount /data
3）优化mysql参数
1》logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。
2》sync_binlog在slave端设置为0或禁用slave端的binlog
3》slave端，如果使用的存储引擎是innodb，innodb_flush_log_at_trx_commit =2
#对数据安全性较高，需要设置
sync_binlog=1
innodb_flush_log_at_trx_commit = 1
#而对于slave则不需要这么高的数据安全，完全可以设置为
sync_binlog=0或者关闭binlog
innodb_flush_log_at_trx_commit =0或2
4）开启GTID复制模式（主从）
1》#5.6版本加入GTID复制模式，DUMP在传输日志时可以并发，需要手工配置。开启GTID后，可以有多个SQL线程，只能针对不同的库的事务进行并行SQL恢复、
2》#5.7版本GTID做了增强，不手工开启也自动维护匿名的GTID信息
5.7 版本的从库并发配置方法
gtid_mode=ON #开启GTID复制模式
enforce_gtid_consistency=ON #强制GTID一致性
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE #将master-info信息记录到表中
relay_log_info_repository=TABLE #将relay-info信息记录到表中
relay_log_recovery=ON
5）架构方面的优化
1、第一类架构思路：主从一致性解决方案
1》强制读主，采用主备架构方案，读写都走主库。用缓存来扩展数据库读性能 。有一点需要知道：如果缓存挂了，可能会产生雪崩现象，不过一般分布式缓存都是高可用的
2》选择读主，写操作时根据库+表+业务特征生成一个key放到Cache里，并设置超时时间（大于等于主从数据同步时间） 读请求时，同样的方式生成key先去查Cache，再判断是否命中,若命中，则读缓存，未命中，则读从库，代价是多了一次缓存读写，有一定的加速查询的意思
3》半同步复制，等主从同步完成，写请求才返回。就是大家常说的“半同步复制”semi-sync。这可以利
用数据库原生功能，实现比较简单。代价是写请求时延增长，吞吐量降低4》数据库中间件，引入开源（mycat等）或自研的数据库中间层，数据库中间件的成本比较高，并且还多引入了一层，进一步减缓的主从同步
2、第二类架构思路：数据库与缓存一致性解决方案
如图所示：
常用的缓存使用方式：
第一步：淘汰缓存—>写操作、delete key；
第二步：写入数据库；
第三步：读取缓存，命中则返回，否则去数据库中读；
第四步：读取数据库后写入缓存
ps ：图一，不会产生DB和缓存不一致问题；图二，会产生DB和缓存不一致问题，即r2.read先于w3.sync执行。如果不做处理，缓存里的数据可能一直是脏数据
设置缓存时，一定要加上有效时间，以防延时淘汰缓存失败的情况！
6）架构的演练
#多种架构的演变方式：
1、架构演变一：方案一 -> 方案一+分库分表 -> 方案二+分库分表 -> 方案四+分库分表；
2、架构演变二：方案一 -> 方案一+分库分表 -> 方案三+分库分表 -> 方案四+分库分表；
3、架构演变三：方案一 -> 方案二 -> 方案四 -> 方案四+分库分表；
4、架构演变四：方案一 -> 方案三 -> 方案四 -> 方案四+分库分表；
#总结
