MySQL 主从复制与读写分离主从同步结构结构类型一主一从一主多从主从从主 -- 从（主）--从互为主从（双

主从同步结构

结构类型

一主一从

一主多从

主从从

主 -- 从（主）--从

互为主从（双主结构）数据可双向同步

主从复制的工作过程

Master节点需要开启二进制日志，Slave节点需要开启中继日志。

（1）Master 节点将数据的改变记录成二进制日志（bin log） ，当Master上的数据发生改变时（增删改），则将其改变写入二进制日志中。

（2）Slave节点会在一定时间间隔内对Master的二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O线程请求Master的二进制事件。（请求二进制数据）

（3）同时Master 节点为每个I/O线程启动一个dump线程，用于通知和向其发送二进制事件，

(4) I/O线程接收到bin-log内容后，将内容保存至slave节点本地的中继日志（Relay log）中，Slave节点将启动SQL线程从中继日志中读取二进制事件，在本地重放，即解析成sql 语句逐一执行，使得其数据和Master节点的保持一致。最后I/O线程和SQL线程将进入睡眠状态，等待下一次被唤醒。

两个日志：二进制日志（bin log） 、中继日志（Relay log）

三个线程：I/O线程、dump线程、SQL线程

注意：

中继日志通常会位于 OS 缓存中，所以中继日志的开销很小。
复制过程有一个很重要的限制，即复制在 Slave上是串行化的，也就是说 Master上的并行更新操作不能在 Slave上并行操作。（当有多个从库，主同时只能给一个从复制）
半同步复制，会多一个ack确认线程（ack collector thread），专门用于接收slave 的反馈信息（收集slave节点返回的ack信息）。

造成主从不一致的原因

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

数据库主从数据不一致解决方案

方法一：忽略错误后，继续同步

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况。

方式二：重新做主从，完全同步

该方法适用于主从库数据相差较大，或者要求数据完全统一的情况。

mysql从服务器挂了恢复后怎么保证数据同步？

物理方法： rsync 磁盘文件同步。使用文件恢复，主节点需要停服务。
主从复制： 将从节点原有库删除，通过偏移量，重新做一次主从复制。

半同步复制什么情况下会降为异步复制？什么时候又会恢复同步复制？

当半同步复制发生超时（由rpl_semi_sync_master_timeout 参数控制，默认为10000ms，即10s），会暂时关闭半同步复制，转而使用异步复制，也就是会自动降为异步工作。
当malster dump 线程发送完一个事务的所有事件之后，如果在rpl_ semi_sync_master_ timeout 内，收到了从库的响应，则主从又重新恢复为半同步复制。

MySQL主从复制延迟原因和优化方法

主从复制延迟原因：

master服务器高并发，形成大量事务。

网络延迟。

主从硬件设备导致（cpu主频、内存IO、硬盘IO）。

是同步复制，而不是异步复制。

优化方法：

从库优化Mysql参数。比如增大innodb_buffer_pool_size，让更多操作在Mysql内存中完成，减少磁盘操作。
从库使用高性能主机。包括cpu强悍、内存加大。避免使用虚拟云主机，使用物理主机，这样提升了I/O方面性。
从库使用SSD磁盘。
网络优化，避免跨机房实现同步。

注意事项

每个master可以有多个slave。

每个slave只能有一个master。

每个slave只能有一个唯一的服务器ID（server-id）。
master一定要开启binlog二进制日志功能；通常为了数据安全，slave也开启binlog功能。

一主一从复制

主服务器的mysql配置

开启二进制日志

vim  /etc/my.cnf
#修改文件
[mysqld]
server_id=106
log-bin=/data/mysql/mysql-bin

建立单独存放二进制日志的文件夹


[root@localhost ~]#mkdir /data/mysql/   -p
#建立文件夹
[root@localhost ~]#chown mysql.mysql /data/ -R
#注意修改权限
[root@localhost ~]#systemctl restart mysqld

进入数据库


show master status;
#查看二进制日志位置

grant replication slave on *.* to 用户名@'192.168.91.%' identified by '密码';
#建立复制用户

show slave hosts;  # 再主服务器查看有那些从服务器

从服务器mysql配置

vim  /etc/my.cnf
server-id=108                                                           
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

建立单独存放二进制日志的文件夹


[root@localhost ~]#mkdir /data/mysql/   -p
#建立文件夹
[root@localhost ~]#chown mysql.mysql /data/ -R
#注意修改权限
[root@localhost ~]#systemctl restart mysqld

进入数据

CHANGE MASTER TO
  MASTER_HOST='192.168.91.106',
  MASTER_USER='myslave',
  MASTER_PASSWORD='123123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000004',
  MASTER_LOG_POS=154;


start slave;
#开启线程，开启主从复制


show slave status\G;

Slave_IO_Running: Yes #负责与主机的IO通信  
Slave_SQL_Running: Yes #负责自己的slave mysql进程


##一般 "Slave_IO_Running: No" 的可能原因：  1. 网络不通  2. my.cnf配置有问题（server-id重复）  3. 密码、file文件名、pos偏移量不对  4. 防火墙没有关闭

复制包括开启复制前的数据

主节点

开启日志....
建立日志文件夹....

mysqldump -uroot -p'Admin@123' -A -F --master-data=1 --single-transaction > /data/all.sql
#备份数据库      
-A      --all-databases      
-F      刷新日志
--master-data=1      会将 master 的修改语句写入dump文件中  1是写入   2是写入但是会被注释
--single-transaction  已开启事务的方式备份

查看备份文件会生成  位置


scp /data/all.sql 192.168.91.101:/data/
#将备份文件拷贝过去

grant replication slave on *.* to 用户@'192.168.91.%' identified by '密码';
#建立复制用户

从节点

开启二进制日志....
开启文件夹....



vim all.sql 
#直接在此配置文件中修改就可以了
  22 CHANGE MASTER TO
  23  MASTER_HOST='192.168.91.100',
  24   MASTER_USER='test',
  25   MASTER_PASSWORD='Admin@123',
  26   MASTER_PORT=3306,
  27  MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
  
mysql>source /data/all.sql
mysql> show slave status \G
mysql> start slave;
mysql> show slave status \G

全同步复制

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返

异步复制

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理

半同步复制

介于异步复制和全同步复制之间

当一个人客户端提交事务时，主库执行玩事务后不会立刻响应客户端，而是等待至少一从库将二进制日之后写入中继日志后，最后收到从库的ack响应才返回客户端

主节点

[root@localhost ~]#vim  /etc/my.cnf
#修改文件
[mysqld]
server_id=100
log-bin=/data/mysql/mysql-bin
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000
#修改此行,需要先安装semisync_master.so插件后,再重启,否则无法启动 开启半同步
#设置3s内无法同步，也将返回成功信息给客户端

[root@localhost ~]#mkdir /data/mysql/   -p
#建立文件夹
[root@localhost ~]#chown mysql.mysql /data/ -R
#注意修改权限
[root@localhost ~]#systemctl restart mysqld
[root@localhost ~]#mysql -uroot -pAdmin@123

mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #永久安装插件
mysql>UNINSTALL PLUGIN rpl_semi_sync_master ;
mysql>SHOW PLUGINS; #查看插件
mysql>SET GLOBAL rpl_semi_sync_master_enabled=1; #临时修改变量
mysql>SET GLOBAL rpl_semi_sync_master_timeout = 3000;  #超时长1s,默认值为10s

mysql>SHOW GLOBAL VARIABLES LIKE '%semi%';
#查看半同步状态

show global status like '%semi%';
#查看半同步客户端


grant replication slave on *.* to test@'192.168.91.%' identified by 'Admin@123';
#建立复制用户

从节点

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
#安装插件

[root@slave1 ~]#vim /etc/my.cnf
[mysqld]
server-id=101
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动


CHANGE MASTER TO
  MASTER_HOST='192.168.91.100',
  MASTER_USER='test',
  MASTER_PASSWORD='Admin@123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=154;
  注意最后分号
  
  
  
show global status like '%semi%';
#查看状态  主从状态


mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql>SET GLOBAL rpl_semi_sync_slave_enabled=1; #临时修改变量
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';

主从复制错误解决方法

方法1：临时跳过错误 stop slave; set global sql_slave_skip_counter=1; start slave;

#指定跳过复制事件的个数

方法2：永久跳过错误修改配置文件 vim /etc/my.cnf.d/mariadb-server.cnf 

[mysqld]

slave_skip_errors=1007|ALL
#指定跳过事件的ID
重启数据库

级联主从复制

需要在中间的从服务器启用以下配置，实现中间slave节点能将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制

vim  /etc/my.cnf

[mysqld]
log_slave_updates(此条为开启log转发更新即将一级主的binlog转发给从服务器)

MySQL读写分离原理

读写分离就是只在主服务器上写，只在从服务器上读。
让master（主数据库）来响应事务性操作（insert，update，delete， create，drop）

让slave（从数据库）来响应select非事务性操作
数据库复制被用来把主数据库上事务性操作导致的变更，同步到集群中的从数据库。

目前较为常见的MysQL读写分离分为以下两种

1）基于程序代码内部实现

在代码中根据select、insert进行路由分类，这类方法也是目前生产环境应用最广泛的。

优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支；缺点是需要开发人员来实现，运维人员无从下手。

但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。

2）基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有以下代表性程序。

（1）MySQL-Proxy。MySQL-Proxy为MysQL开源项目，通过其自带的1ua脚本进行sQL判断。

（2）Atlas。是由奇虎360的Web平台部基础架构团队开发维护的一个基于MysQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用atlas运行的mysql业务，每天承载的读写请求数达几干保条。支持事物以及存储过程。

（3）Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。

（4）Mycat。是一款流行的基于Java语言编写的数据库中间件，是一个实现了MySq1协议的服务器，其核心功能是分库分表。配合数据库的主从模式还可以实现读写分离。

由于使用MysQLProxy需要写大量的ua脚本，这些Lua并不是现成的，而是需要自己去写。这对于并不熟悉MysQLProxy 内置变量和MySQL Protocol的人来说是非常困难的。

Amoeba是一个非常容易使用、可移植性非常强的软件。因此它在生产环境中被广泛应用于数据库的代理层。

mycat

在业务代码中，数据库的操作，不直接连接数据库，而是先请求到中间件服务器（代理）

由代理服务器，判断是读操作去从数据服务器，写操作去主数据服务器

mycat安装目录结构说明

bin：mycat命令，启动、重启、停止等运行目录
catlet： catlet为Mycat的一个扩展功能
conf ：mycat 配置信息,重点关注
lib ：mycat引用的jar包，Mycat是java开发的
logs：日志文件，包括Mycat启动的日志和运行的日志
version.txt：mycat版本说明

mycat的常用配置文件

Mycat的配置文件都在conf目录里面，这里介绍几个常用的文件：

server.xml ：Mycat软件本身相关的配置文件，设置账号、参数等
schema.xml：Mycat对应的物理数据库和数据库表的配置,读写分离、高可用、分布式策略定制、节点控制
rule.xml：Mycat分片（分库分表）规则配置文件,记录分片规则列表、使用方法等

mycat日志

Mycat的日志文件都在logs目录里面

wrapper.log：mycat启动日志
mycat.log：mycat详细工作日志

mycat 实现读写分离

#每台服务器上都初始化，关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
setenforce 0

MySQL主从配置

安装mycat

（1）主机上安装java（mycat基于java）
#yum安装java
[root@localhost ~]#yum install java -y
#确认安装成功
[root@localhost ~]#java -version
openjdk version "1.8.0_131"
OpenJDK Runtime Environment (build 1.8.0_131-b12)
OpenJDK 64-Bit Server VM (build 25.131-b12, mixed mode)

（2）切换至opt目录，下载mycat安装包
[root@localhost ~]#cd /opt
[root@localhost ~]#wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz

（3）创建/apps文件夹，并解压mycat包至/apps下
[root@localhost ~]#mkdir /apps
[root@localhost ~]#tar zxvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/

（4）设置变量环境
[root@localhost ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@localhost ~]#source /etc/profile.d/mycat.sh

（5）启动mycat，查看日志文件，最后可以看到启动成功
[root@localhost ~]#mycat start
#注意内存小于2G 起不来
Starting Mycat-server...

[root@localhost ~]#tail -f /apps/mycat/logs/wrapper.log
#启动成功日志末尾会出现successfully
STATUS | wrapper  | 2021/12/09 21:04:10 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2021/12/09 21:04:10 | Launching a JVM...
INFO   | jvm 1    | 2021/12/09 21:04:11 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2021/12/09 21:04:11 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2021/12/09 21:04:11 | 
INFO   | jvm 1    | 2021/12/09 21:04:12 | MyCAT Server startup successfully. see logs in logs/mycat.log

修改 mycat 配置文件 /apps/mycat/conf/server.xml

[root@localhost ~]#vim /apps/mycat/conf/server.xml

#去掉44行行注释，对应的在51行行末注释，删除50行行末注释，5 * 60 * 1000L; //连接空>    闲检查

#修改45行端口号为3306
45 <property name="serverPort">3306</property>

#配置Mycat的连接信息(账号密码)，在110 和111行， 可以修改，这边不修改了


##注意
1.#server.xml文件里登录mycat的用户名和密码可以任意定义，这个账号和密码是为客户机登录mycat时使用的账号信息
2.#逻辑库名(如上面的TESTDB，也就是登录mycat后显示的库名，切换这个库之后，显示的就是代理的真实mysql数据库的表)要在schema.xml里面也定义，否则会导致mycat服务启动失败！这里只定义了一个标签，所以把多余的都注释了。如果定义多个标签，即设置多个连接mycat的用户名和密码，那么就需要在schema.xml文件中定义多个对应的库！

修改 mycat 配置文件 /apps/mycat/conf/schema.xml

schema.xml是最主要的配置项，此文件关联mysql读写分离策略，读写分离、分库分表策略、分片节点都是在此文件中配置的.MyCat作为中间件，它只是一个代理，本身并不进行数据存储，需要连接后端的MySQL物理服务器，此文件就是用来连接MySQL服务器的。

<?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.91.100:3306" user="root" password="Admin@123">
                 <readHost host="host2" url="192.168.91.101:3306" user="root" password="Admin@123"/>
                </writeHost>
        </dataHost>
</mycat:schema>



在dataNode节点中，指定database真实的数据库（重要）
设置writeHost与readHost（读写两台服务器信息）

主服务器上授权

[root@localhost ~]#mysql -uroot -p123123
#授权
GRANT ALL ON *.* TO 'root'@'192.168.91.%' IDENTIFIED BY '123456';

#查看创建成功
use mysql;
select user,host from user;

重启mycat服务，客户机连接mycat

（1）在mycat服务器上，重启mycat服务,查看启动日志，文末出现successfully
 [root@localhost ~]#mycat restart  
 [root@localhost ~]#tail -f /apps/mycat/logs/wrapper.log
 
 
 (2)查看3306端口，可以监听到主从服务器
 [root@localhost ~]# ss -antp|grep 3306
 
 
 （3）在客户机上登录mycat，这时可以不加端口直接进入数据库了
[root@localhost ~]#mysql -uroot -p123456 -h 192.168.59.114

MySQL 主从复制与读写分离