记一次Mysql8主从复制和读写分离实验的踩坑之旅
简介
随着系统使用，数据量增多，关系型数据库的瓶颈开始凸显，即使进行了建立索引、优化sql，也还是存在性能下降，查询大表等待时间过长等问题。
基于此，进行对数据库集群部署、主从复制、读写分离，从而实现数据热备，数据库故障主备切换；提升查询效率、用户体验。
选择任意一种即可
基于二进制日志文件偏移量（Binary Log File Position）
配置
主库my.conf或my.ini
server-id=1 # 数据库中的id号，主从mysql数据库不能相同
log-bin=mysql-bin # 允许slave同步自己的二进制文件
binlog-format=ROW #选择row模式
binlog-ignore-db=mysql #不同步mysql系统数据库 多个多写
从库my.conf或my.ini
server-id=2 # 数据库中的id号，主从mysql数据库不能相同
log-bin=mysql-bin # 允许slave同步自己的二进制文件
binlog-format=ROW #选择row模式
binlog-ignore-db=mysql #不同步mysql系统数据库 多个多写
注意：可能是个例，mysql 8.02发现从库配置需要放在【default-character-set=utf8】 配置上测才能生效。
binlog-format参数
binlog-format包含三种类型分别为Statement,MiXED,以及ROW。
**ROW Level**
• 记录的方式是行，即如果批量修改数据，记录的不是批量修改的SQL语句事件，而是每条记录被更改的SQL语句，因此，ROW模式的binlog日志文件会变得很“重”。
优点：row level的binlog日志内容会非常清楚的记录下每一行数据被修改的细节。而且不会出现某些特定情况下存储过程或function，以及trigger的调用和触发器无法被正确复制的问题。
缺点：row level下，所有执行的语句当记录到日志中的时候，都以每行记录的修改来记录，这样可能会产生大量的日志内容，产生的binlog日志量是惊人的。批量修改几百万条数据，那么记录几百万行。
**Statement level(默认)**
• 记录每一条修改数据的SQL语句（批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件）。
优点：statement模式记录的更改的SQ语句事件，并非每条更改记录，所以大大减少了binlog日志量，节约磁盘IO，提高性能。
缺点：statement level下对一些特殊功能的复制效果不是很好，比如：函数、存储过程的复制。由于row level是基于每一行的变化来记录的，所以不会出现类似问题。
**Mixed**
• 实际上就是前两种模式的结合。在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。
更多参数参见官方文档。
**企业场景如何选择binlog的模式**
1、 如果生产中使用MySQL的特殊功能相对少（存储过程、触发器、函数）。选择默认的语句模式，Statement Level。
2、 如果生产中使用MySQL的特殊功能较多的，可以选择Mixed模式。
3、 如果生产中使用MySQL的特殊功能较多，又希望数据最大化一致，此时最好Row level模式；但是要注意，该模式的binlog非常“沉重”。
操作
主库操作
查看MASTER_LOG_FILE 与 MASTER_LOG_POS
show master status;
从库操作
CHANGE MASTER TO
MASTER_HOST='192.168.1.2',
MASTER_USER='root',
MASTER_port=3307,
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='binlog.000026',
MASTER_LOG_POS=891;
START SLAVE;//开启从库复制
SHOW SLAVE STATUS;
STOP slave;//关闭从库复制
参数： MASTER_HOST： 主库物理地址 MASTER_USER：用户名 MASTER_port：主库端口 MASTER_PASSWORD：密码 MASTER_LOG_POS：从库复制位 MASTER_LOG_FILE: 从库起始复制文件（主库最新日志文件）
半同步复制开启
点位复制默认为异步复制。并不关注slave是否成功接受数据，如果脱机会导致slave数据丢失；有必要可以开启半从不复制，即确认一台slave同步成功，才继续操作，但会影响性能。
基于 GTID（Global Transaction Identifiers）
Master:
show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 2a09ee6e-645d-11e7-a96c-000c2953a1cb |
+---------------+--------------------------------------+
Slave
show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 8ce853fc-6f8a-11e7-8940-000c29e3f5ab |
+---------------+--------------------------------------+
参数讲解
Retrieved_Gtid_Set : 从库已经接收到主库的事务编号
Executed_Gtid_Set : 从库自身已经执行的事务编号
Master Slave my.inf修改
#开启全局事务ID，GTID能够保证让一个从服务器到其他的从服务器那里实现数据复制而且能够实现数据整合的
gtid_mode = on
#开启gtid，必须主从全开
enforce_gtid_consistency = 1
change master to
master_host='master1', #这边master1是master1的主机名，也可以是IP或者域名
master_user='master2', #这边master2是用户名而已
master_password='Root@123',
master_auto_position=1;
start slave;
show slave STATUS;
GTID （Global Transaction Identifiers）是对于一个已提交事务的编号，事务的唯一编号，并且是一个全局唯一的编号。GTID 和事务会记录到 binlog 中，用来标识事务。GTID 是用来替代以前 传统的复制方法，MySQL-5.6.2 开始支持 GTID，在 MySQL-5.6.10 后完善。有了 GTID，一个事务在集群中就不再孤单，在每一个节点中，都存在具有相同标识符的兄弟们和它作伴，可以避免同一个事务，在同一个节点中出现多次的情况。GTID 的出现，最直接的效果就是，每一个事务在集群中具有了唯一性的意义，这在运维方面具有更大的意义，因为使用 GTID 后再也不需要为了不断地找点而烦恼了，给 DBA 带来了很大的便利性。
GTID的优点： 1.根据GTID可以知道事务最初是在哪个实例上提交的 2.GTID的存在方便了Replication的Failover
GTID的缺点: 1.GTID 模式实例和非GTID模式实例是不能进行复制的，要求非常严格，要么都是GTID，要么都不是 2.gtid_mode 是只读的，要改变状态必须1)关闭实例、2)修改配置文件、3) 重启实例
读写分离
读写分离使用了阿里的中间件Mycat实现，由于Mycat1主要用于支持Mysql5，需要对源码进行修改进行重新打包，参见[Mycat1注意]。
团队项目：https://github.com/MyCATApache
源码地址：https://github.com/MyCATApache/Mycat-Server
安装部署Mycat
在源码地址下载源码，按照[Mycat1注意]修改后打包生成Mycat-server-1.6.7.6-release-20210128213924-win.tar.gz，解压缩后执行启动脚本mycat.bat即可。
Mycat1配置
简单配置读写分离只需要修改schema.xml 和server.xml 即可。
server.xml用于配置Mycat用户权限。schema.xml用于配置物理数据库，是否读写分离、主备切换、心跳检测等。
如下xml配置了两个关系：
数据库代理(mycat-water)-----→ dataNode（dn_water）--------→ dataHost（dh_conf）--------→ 物理层数据库（water-dev）----→ 物理地址 读配置 写配置
DB_proxy(mycat-water)-----→ dataNode（dn_water）--------→ dataHost（dh_conf）--------→ database（water-dev）----→ 物理地址 读配置 写配置
DB_proxy(mycat)-----→ dataNode（dn_water）--------→ dataHost（dh_conf）--------→ database（xxl_job）----→ 物理地址 读配置 写配置
其中root对 mycat拥有读写权限，test对mycat-water有读写权限。
schema.xml
server.xml