13-mysql半同步复制-02

半同步复制

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。
这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失

半同步复制实现

#官方文档
https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html
https://dev.mysql.com/doc/refman/5.7/en/replication-semisync.html
https://mariadb.com/kb/en/library/semisynchronous-replication/

CentOS8 在MySQL8.0 实现半同步复制

#1、查看插件文件
[root@centos8 ~]#rpm -ql mysql-server |grep semisync
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
#2、master服务器配置
[root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=8
log-bin
rpl_semi_sync_master_enabled=ON #修改此行,需要先安装semisync_master.so插件后,再重启,否则无法启动
rpl_semi_sync_master_timeout=3000   #设置3s内无法同步，也将返回成功信息给客户端

#3、slave服务器配置
[root@slave1 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=18
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动
[root@slave2 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=28
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动

#4、主服务器配置:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #永久安装插件
UNINSTALL PLUGIN rpl_semi_sync_master ;
SHOW PLUGINS;
#查看插件
SET GLOBAL rpl_semi_sync_master_enabled=1; #临时修改变量
SET GLOBAL rpl_semi_sync_master_timeout = 3000;  #超时长1s,默认值为10s
SHOW GLOBAL VARIABLES LIKE '%semi%';

#5、从服务器配置:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled=1; #临时修改变量
SHOW GLOBAL VARIABLES LIKE '%semi%';
#注意:如果已经实现主从复制,需要stop slave;start slave;
stop slave;
start slave;
SHOW GLOBAL STATUS LIKE '%semi%';

复制过滤器

复制过滤器：让从节点仅复制指定的数据库，或指定数据库的指定表
复制过滤器两种实现方式：
- (1) 服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件
  - 缺点：基于二进制还原将无法实现；不建议使用
  - 优点: 只需要在主节点配置一次即可
  - 注意：此项和 binlog_format相关
  - 参看：https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-db

vim /etc/my.cnf
binlog-do-db=db1 #数据库白名单列表，不支持同时指定多个值，如果想实现多个数据库需多行实现
binlog-do-db=db2 
binlog-ignore-db= #数据库黑名单列表

- (2) 从服务器SQL_THREAD在relay log中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地
  - 缺点：会造成网络及磁盘IO浪费,在所有从节点都要配置
  - 优点: 不影响二进制备份还原
  - 从服务器上的复制过滤器相关变量

replicate_do_db="db1,db2,db3" #指定复制库的白名单，变量可以指定逗号分隔的多个值，选
项不支持多值,只能分别写多行实现
replicate_ignore_db= #指定复制库黑名单
replicate_do_table= #指定复制表的白名单
replicate_ignore_table= #指定复制表的黑名单
replicate_wild_do_table= foo%.bar%    #支持通配符
replicate_wild_ignore_table=

注意：跨库的更新将无法同步

范例: 通过二进制日志服务器选项实现过滤器

[mysqld]
server-id=8
log-bin
binlog-do-db=db1
binlog-do-db=db2
binlog-do-db=hellodb
binlog-ignore=testdb1

范例: 通过系统变量实现过滤器

stop slave;
set global replicate_do_db='db1,hellodb';
select @@replicate_do_db;
start slave;

复制的监控和维护

#清理日志
PURGE { BINARY | MASTER } LOGS   { TO 'log_name' | BEFORE datetime_expr }
RESET MASTER TO # #mysql 不支持
RESET SLAVE [ALL]

#复制监控
SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW SLAVE STATUS
SHOW PROCESSLIST

#从服务器是否落后于主服务
Seconds_Behind_Master：0

#如何确定主从节点数据是否一致
percona-toolkit

MySQL 主从数据不一致

造成主从不一致的原因

主库binlog格式为Statement，同步到从库执行后可能造成主从不一致。
主库执行更改前有执行set sql_log_bin=0，会使主库不记录binlog，从库也无法变更这部分数据。
从节点未设置只读，误操作写入数据
主库或从库意外宕机，宕机可能会造成binlog或者relaylog文件出现损坏，导致主从不一致
主从实例版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面可能不支持该功能
MySQL自身bug导致

主从不一致修复方法

将从库重新实现
- 虽然这也是一种解决方法，但是这个方案恢复时间比较慢，而且有时候从库也是承担一部分的查询操作的，不能贸然重建。
使用percona-toolkit工具辅助
- PT工具包中包含pt-table-checksum和pt-table-sync两个工具，主要用于检测主从是否一致以及修复数据不一致情况。这种方案优点是修复速度快，不需要停止主从辅助，缺点是需要知识积累，需要时间去学习，去测试，特别是在生产环境，还是要小心使用
- 关于使用方法，可以参考下面链接：https://www.cnblogs.com/feiren/p/7777218.html
手动重建不一致的表
- 在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致
- 这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的

范例：A,B,C这三张表主从数据不一致

1、从库停止Slave复制
mysql>stop slave;

2、在主库上dump这三张表，并记录下同步的binlog和POS点
mysqldump -uroot -pzhongguo -q --single-transaction --master-data=2 testdb A B C >/backup/A_B_C.sql

3、查看A_B_C.sql文件，找出记录的binlog和POS点
head A_B_C.sql
例如:MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=666666;

#以下指令是为了保障其他表的数据不丢失，一直同步直到那个点结束，A,B,C表的数据在之前的备份已经生成了一份快照，只需要导入进去，然后开启同步即可
4、把A_B_C.sql拷贝到Slave机器上，并做指向新位置
mysql>start slave until MASTERLOGFILE='mysql-bin.888888',MASTERLOGPOS=666666;

5、在Slave机器上导入A_B_C.sql
mysql -uroot -pzhongguo testdb 
mysql>set sql_log_bin=0;
mysql>source /backup/A_B_C.sql
mysql>set sql_log_bin=1;

6、导入完毕后，从库开启同步即可。
mysql>start slave;

如何避免主从不一致

主库binlog采用ROW格式
主从实例数据库版本保持一致
主库做好账号权限把控，不可以执行set sql_log_bin=0
从库开启只读，不允许人为写入
定期进行主从一致性检验

mycat读写分离

案例：利用 Mycat 实现 MySQL 的读写分离

环境要求

#所有主机的系统版本： 
cat /etc/centos-release
CentOS Linux release 8.0.1905 (Core)  
#服务器共三台 
mycat-server 192.168.56.12 #内存建议2G以上
mysql-master 192.168.56.11 MySQL 8.0 或者Mariadb 10.3.17
mysql-slave  192.168.56.13   MySQL 8.0 或者Mariadb 10.3.17

关闭SELinux和防火墙

systemctl stop firewalld
setenforce 0
时间同步

1、创建 MySQL 主从数据库

yum -y install mysql-server
#或者
yum -y install mariadb-server

1) 修改master和slave上的配置文件

#master1上的my.cnf
[root@master1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id = 11
log-bin

#slave1上的my.cnf
[mysqld]
server-id = 13

systemctl start mariadb

2) Master1上创建复制用户

mysql -uroot -p
#登录数据库后执行如下操作
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.56.%' IDENTIFIED BY 'replpass';
FLUSH PRIVILEGES;    
show master status;

3) Slave1上执行

mysql -uroot -p
#登录数据库后执行如下操作
CHANGE MASTER TO
MASTER_HOST='192.168.56.%',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=403;

start slave;
show slave status\G;

2、在MySQL代理服务器192.168.56.12安装mycat并启动

#确认安装并成功
yum -y install java
java -version

#下载并安装
wget -c http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
#wget -c http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
mkdir /apps && tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/
#mkdir /apps && tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/
#配置环境变量
echo 'PATH=/apps/mycat/bin:$PATH' >/etc/profile.d/mycat.shsource /etc/profile.d/mycat.sh
#查看端口
ss -ntl

#启动mycat
file /apps/mycat/bin/mycat 
mycat start
#注意: 此步启动较慢,需要等一会儿,另外如果内存太小,会导致无法启动

#可以看到打开多个端口,其中8066端口用于连接MyCAT
ss -ntlp
#查看日志，确定成功，可能需要等一会儿才能看到成功的提示
tail -f /apps/mycat/logs/wrapper.log
#用默认密码123456来连接mycat
mysql -uroot -pzhongguo -h 192.168.56.12 -P8066

4、在mycat 服务器上修改server.xml文件配置Mycat的连接信息

[root@mysql-proxy ~]#vim /apps/mycat/conf/server.xml
...省略...
#修改下面行的8066改为3306复制到到独立非注释行
<property name="serverPort">3306</property>
<property name="handlelDistributedTransactions">0</property> #将上面行放在此行前面
#或者删除注释,并修改下面行的8066改为3306
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
<property name="idleTimeout">300000</property>
<property name="authTimeout">15000</property>
<property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property> #5 * 60 * 1000L; //连接空闲检查 删除#后面此部分
<property name="frontWriteQueueSize">4096</property> <property 
name="processors">32</property> #--> 删除#后面此部分
 .....
<user name="root">                                       #连接Mycat的用户名
   <property name="password">zhongguo</property>          #连接Mycat的密码
   <property name="schemas">TESTDB</property>           #数据库名要和schema.xml相
对应
</user>
</mycat:server>

这里使用的是root，密码为magedu,逻辑数据库为TESTDB，这些信息都可以自己随意定义,读写权限都有，没有针对表做任何特殊的权限。重点关注上面这段配置，其他默认即可

5、修改schema.xml实现读写分离策略

[root@mysql-proxy ~]#vim /apps/mycat/conf/schema.xml 
#最终文件内容
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"
dataNode="dn1">
 </schema>
 <dataNode name="dn1" dataHost="localhost1" database="hellodb" />
 <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
  writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
 <heartbeat>select user()</heartbeat>
 <writeHost host="host1" url="192.168.56.11:3306" user="root" password="zhongguo"><readHost host="host2" url="192.168.56.13:3306" user="root" password="zhongguo" />
 </writeHost>
   </dataHost>
</mycat:schema>
#重新启动mycat
[root@mysql-proxy ~]#mycat restart
#以上***部分表示原配置文件中需要修改的内容，注意大小写

上面配置中，balance改为1，表示读写分离
注意：要保证能使用root/123456权限成功登录10.0.0.18和10.0.0.28机器上面的mysql数据库。同时，也一定要授权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库！！这很重要，否则会导致登录mycat后，对库和表操作失败！

6、在后端主服务器创建用户并对mycat授权

[root@master1 ~]#mysql -uroot -p
create database mycat;
GRANT ALL ON *.* TO 'root'@'192.168.56.%' IDENTIFIED BY 'zhongguo' ;
flush privileg

7、在Mycat服务器上连接并测试

[root@mysql-proxy ~]#mysql -uroot -pzhongguo -h127.0.0.1 TESTDB
#登录数据库后执行
show databases;
use TESTDB;
create table t1(id int);
select @@server_id;
select @@hostname;

8、通过通用日志确认实现读写分离

在mysql中查看通用日志

show variables like 'general_log';  #查看日志是否开启
set global general_log=on;    #开启日志功能
show variables like 'general_log_file'; #查看日志文件保存位置
set global general_log_file='tmp/general.log'; #设置日志文件保存位置

在主和从服务器分别启用通用日志，查看读写分离

[root@master1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
general_log=ON
[root@master1 ~]#systemctl restart mariadb
[root@master1 ~]#tail -f /var/lib/mysql/centos8.log

9、停止从节点，MyCAT自动调度读请求至主节点

[root@slave1 ~]#systemctl stop mariadb
[root@client ~]#mysql -uroot -pzhongguo-h192.168.56.12 -P8066

select @@server_id;
#停止主节点，MyCAT不会自动调度写请求至从节点
insert teachers values(5,'wang',30,'M');

10、MyCAT对后端服务器的健康性检查方法select user()

#开启通用日志
[root@master1 ~]#mysql
set global general_log=1;
[root@slave1 ~]#mysql
set global general_log=1;

#查看通用日志
tail -f /var/lib/mysql/master.log
tail -f /var/lib/mysql/slave.log

利用 ProxySQL 实现读写分离

posted @ 2022-08-07 22:15 西瓜的春天阅读(65) 评论(0) 收藏举报

刷新页面返回顶部

西瓜的春天

斯人若彩虹，遇上方知有

13-mysql半同步复制-02

半同步复制

CentOS8 在MySQL8.0 实现半同步复制

复制过滤器

范例: 通过二进制日志服务器选项实现过滤器

范例: 通过系统变量实现过滤器

复制的监控和维护

MySQL 主从数据不一致

造成主从不一致的原因

主从不一致修复方法

如何避免主从不一致

mycat读写分离

案例：利用 Mycat 实现 MySQL 的读写分离

环境要求

关闭SELinux和防火墙

1、创建 MySQL 主从数据库

2、在MySQL代理服务器192.168.56.12安装mycat并启动

4、在mycat 服务器上修改server.xml文件配置Mycat的连接信息

5、修改schema.xml实现读写分离策略

6、在后端主服务器创建用户并对mycat授权

7、在Mycat服务器上连接并测试

8、通过通用日志确认实现读写分离

9、停止从节点，MyCAT自动调度读请求至主节点

10、MyCAT对后端服务器的健康性检查方法select user()

利用 ProxySQL 实现读写分离