• MySQL主从复制与读写分离

    引言

    在企业应用中，成熟的业务通常数据量都比较大。单台MySQL在安全性、高可用性和高并发方面都无法满足实际的需求，配置多台主从数据库服务器以实现读写分离。

    一、MySQL主从复制的原理

    1、MySQL主从复制原理

    （1）MySQL的复制类型

    基于语句的复制

    基于行的复制

    混合类型的复制

    2、MySQL主从复制的工作过程

    两日志、三线程

    在每个事务更新数据完成之前，master在二进制日志（Binary log）记录这些改变。写入二进制日志完成后，Master通知存储引擎提交事务。

    Slave将Master的复制到其中继日志（Relay log）。首先slave开始一个工作线程（I/O),I/O线程在Master上打开一个普通的连接，然后开始Binlog dump process。从Master的二进制日志中读取事件，如果已经跟上Master，它会睡眠并等待Master产生新的事件，I/O线程将这些事件写入中继日志。

    SQL slave thread(SQL从线程）处理该过程的最后一步，SQL线程从中继日志读取事件，并重放其中的事件而更新slave数据，使其与Master中的数据一致，只要该线程与I/O线程保持一致，中继日志通常会位于OS缓存中，所以中继日志的开销很小。复制过程中有一个很重要的限制，即复制在Slave上是串行化的，也就是说Master上的并行更新操作不能在Slave上并行操作

    3、主从复制简化内容

    主从复制核心部分就是两个日志三个线程（高版本的mysql以及异步复制、半同步复制、全同步复制）

    二个日志：二进制和中继日志

    三个线程：master的dump和slave的I/O、SQL

    主要原理：master将数据保存在二进制日志中。I/O向dump发出同步请求，dump把数据发送给I/O线程，I/O写入本地的中继日志SQL线程读取本地的中继日志数据，同步到自己数据库中，完成同步。

    4、MySQL四种同步方式

    （1)异步复制(AsyncReplication)

    主库将更新写入Binlog日志文件后，不需要等待数据更新是否已经复制到从库中，就可以继续处理更多的请求。Master将事件写入binlog，但并不知道SIave是否或何时已经接收且已处理。在异步复制的机制的情况下，如果Master宕机，事务在Master上已提交，但很可能这些事务没有传到任何的Slave上。假设有Master->Salve故障转移的机制，此时Slave也可能会丢失事务。MySQL复制默认是异步复制，异步复制提供了最佳性能。

    (2)同步复制(SyncReplication)

    主库将更新写入Binlog日志文件后，需要等待数据更新已经复制到从库中，并目已经在从库执行成功，然后才能返回继律处理其它的请求。同步复制提供了最佳安全性，保证数据安全，数据不会丢失，但对性能有一定的影响。

    (3)半同步复制(Semi-SyncReplication)

    主库提交更新写入二进制日志文件后，等待数据更新写入了从服务器中继日志中，然后才能再继续处理其它请求。该功能确保至少有1个从库接收完主库传递过来的binlog内容已经写入到自己的relaylog里面了，才会通知主库上面的等待线程，该操作完毕。半同步复制，是最佳安全性与最佳性能之间的一个折中。 MySQL
    5.5版木之后引入了半同步复制功能，主从服务器必须安装半同步复制插件，才能开启该复制功能。如果等待超时，超过rpl semi sync master timeout参数设置时间(默认值为10000，表示10秒)，则关闭半同步复制，并自动转换为异步复制模式。当master
    dump线程发送完一个事务的所有事件之后，如果在rplsemi syncmastertimeout内，收到了从库的响应，则主从又重新恢复为增强半同步复制。 ACK(Acknowledaecharacter)即是确认字符。

    (4)增强半同步复制(lossless Semi-Sync Replication、无损复制)

    增强半同步是在MySQL5.7引入，其实半同步可以看成是一个过渡功能，因为默认的配置就是增强半同步，所以，大家一般说的半同步复制其实就是增强的半同步复制，也就是无损复制。
    增强半同步和半同步不同S的是，等待ACK时间不同
    rpl semi sync master wait point=AFTER SYNC(默认)
    半同步的问题是因为等待Ack的点是Commit之后，此时Master已经完成数据变更，用户已经可以看到最新数据，当Binlog还未同步到slave时，发生主从切换，那么此时从库是没有这个最新数据的，用户看到的是老数据。
    曾强半同步将等待ACK的点放在提交Commit之前，此时数据还未被提交，外界看不到数据变事，此时如果发送主从切换，新库依然还是老数据，不存在新据不一致的问题。

    5、MySQL主从复制的延迟

    （1）master服务器高并发，形成大量事务

    （2）网络延迟

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

      (4) 本来就不是同步复制、而是异步复制

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

    二、MySQL读写分离原理

    mysql数据库

    主要的性能是读和写，一般场景来说读请求更多。

    根据主从复制可用演变成读写分离，因为读写分离基于主从复制，使用读写分离而解决高并发的问题。

    1、读写分离原理

       (1)只在主服务器上写，只在从服务器上读

    （2) 主数据库处理事务性查询，从数据库处理SELECT查询

    （3）数据库复制用于将事务性查询的变更同步到集中的从数据库

    2、读写分离方案

    （1）基于程序代码内部实现（开发自研）

    （2）基于中间件代理实现(MySQL-Proxy、Amoeba、Mycat等中间件）

    3、MySQL架构的演变方向

    （1）单台M有SQL有单点故障

    集群-->>  主从复制

    （2）主从复制读和写的压力不均衡

    读写分离

    （3）读写分离的基础是主从复制

    Mysql的高可用架构MHA（master HA高可用）MGR MMM

    4、架构分类

    （1）一主多备

    一般来说做读写分离，master写其他slave读，这种价格最大问题是I/O压力集中在Master上多台同步会影响IO

    （2）Master -slave中继-slave

    slave为中继分担Master压力，slave中继需要开启bin-log，并配置log-slave-updates

    slave中继可使用black-hole存储引擎，不会把数据存储到磁盘，只记录二进制日志。

    （3）Master-Master 互为主从

    很多人误以为这样可用做到MySQL负载均衡，实际没什么好处，每个服务器需要做同样的同步更新，破坏了事务的隔离性和数据一致性

     （4）Monitor-Master-Master

    天生的缺陷：复制延迟，slave上同步要慢于master，如果大并发的情况延迟更严重 mysql5.6已经自身可以实现fialover故障切换5.7.20

     三、主从复制实验

    实验准备

    master服务器：192.168.154.19    mysql5.7

    slave1服务器： 192.168.154.20   mysql5.7

    slave2服务器： 192.168.154.21    mysql5.7

    Amoeba服务器+客户端服务器： 192.168.154.22 jdk1.6、Amoeba

    客户端服务

    1、先创建主从数据库

    使用master服务器和slave1服务器

    （1）先关闭防火墙，关闭增强功能（master和slave服务器都需要）

    1. systemctl stop firewalld
    2. setenforce 0

    （2）在master服务器和slave1服务器上安装ntp服务并开启

    master服务器

     yum install -y ntp

     编辑配置文件

    1. vim /etc/ntp.conf
    2.  
    3.  末尾加入
    4. server 127.127.154.0
    5. fudge 127.127.154.0 startum 8

     开启ntp服务

    systemctl start ntpd

    slave1服务器

    yum install -y ntp

     开启服务

     （3）在slave1服务器上设置与master时间同步

    /usr/sbin/ntpdate 192.168.154.19

     （4）编辑master服务器中的mysql主配置文件

    vim /etc/my.cnf
    1. [client]
    2. port = 3306
    3. default-character-set=utf8
    4. socket=/usr/local/mysql/mysql.sock
    5.  
    6. [mysql]
    7. port = 3306
    8. default-character-set=utf8
    9. socket=/usr/local/mysql/mysql.sock
    10. auto-rehash
    11.  
    12. [mysqld]
    13. user = mysql
    14. basedir=/usr/local/mysql
    15. datadir=/usr/local/mysql/data
    16. port = 3306
    17. character-set-server=utf8
    18. pid-file = /usr/local/mysql/mysqld.pid
    19. socket=/usr/local/mysql/mysql.sock
    20. bind-address = 0.0.0.0
    21. skip-name-resolve
    22. max_connections=2048
    23. default-storage-engine=INNODB
    24. max_allowed_packet=16M
    25. server-id = 1
    26. log-bin=master-bin         
    27. binlog_format = MIXED
    28. log-slave-updates=true
    29.  
    30. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

     重启服务

    systemctl restart mysqld

    （5）加入mysql服务器给服务器授权

    1. mysql -uroot -p密码
    2.  
    3. GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.154.%' IDENTIFIED BY '123456';
    4. #给从服务器授权
    5.  
    6. flush privileges; ##刷新
    7.  
    8. show master status  #查看主服务器数据

     （6）配置slave1服务器的配置文件

    1. [client]
    2. port = 3306
    3. default-character-set=utf8
    4. socket=/usr/local/mysql/mysql.sock
    5.  
    6. [mysql]
    7. port = 3306
    8. default-character-set=utf8
    9. socket=/usr/local/mysql/mysql.sock
    10. auto-rehash
    11.  
    12. [mysqld]
    13. user = mysql
    14. basedir=/usr/local/mysql
    15. datadir=/usr/local/mysql/data
    16. port = 3306
    17. character-set-server=utf8
    18. pid-file = /usr/local/mysql/mysqld.pid
    19. socket=/usr/local/mysql/mysql.sock
    20. bind-address = 0.0.0.0
    21. skip-name-resolve
    22. max_connections=2048
    23. default-storage-engine=INNODB
    24. max_allowed_packet=16M
    25. server-id = 2
    26. relay-log=relay-log-bin
    27. relay-log-index=slave-relay-bin.index
    28. relay_log_recovery = 1
    29. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

    重启mysql服务

    systemctl restart mysqld

    （7）slave1服务器配置主从同步

    1. mysql -u root -p123456  #进去从数据库
    2.  
    3. CHANGE master to master_host='192.168.154.19',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;   
    4. #配置同步，注意master_log_file和master_log_pos 的值要与Master查询的一致
    5.  
    6. start slave;     #启动同步
    7.  
    8. show slave status\G;    #查看slave状态

    (8) 验证结果

    在slave1主机查看mysql中的库

     在master主机创建新的库FENGBANGCHANG

     再次从slave1主机查看mysql中的库master中创建的库被同步到slave1中了

    一主一备成功实现

    2、添加从服务器slave2

    前提：slave2上有mysql

    （1）先修改slave2上的mysql配置文件

    vim /etc/my.cnf
    1. [client]
    2. port = 3306
    3. default-character-set=utf8
    4. socket=/usr/local/mysql/mysql.sock
    5.  
    6. [mysql]
    7. port = 3306
    8. default-character-set=utf8
    9. socket=/usr/local/mysql/mysql.sock
    10. auto-rehash
    11.  
    12. [mysqld]
    13. user = mysql
    14. basedir=/usr/local/mysql
    15. datadir=/usr/local/mysql/data
    16. port = 3306
    17. character-set-server=utf8
    18. pid-file = /usr/local/mysql/mysqld.pid
    19. socket=/usr/local/mysql/mysql.sock
    20. bind-address = 0.0.0.0
    21. skip-name-resolve
    22. max_connections=2048
    23. default-storage-engine=INNODB
    24. max_allowed_packet=16M
    25. server-id = 3
    26. relay-log=relay-log-bin
    27. relay-log-index=slave-relay-bin.index
    28. relay_log_recovery = 1
    29.  
    30. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

     重启服务

    systemctl restart mysqld

    （2）slave2服务器配置主从同步

    1. mysql -u root -p 123456
    2.  
    3. CHANGE master to master_host='192.168.154.19',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;      #设置主从同步
    4.  
    5.  

    1. start slave;     #启动同步
    2.  
    3. show slave status\G;    #查看slave状态

     （3）验证结果

    先查看slave2中的库

    在master库中创建新库guxin

     在slave2中查看库备份了刚刚创建的guxin库

    在slave1中查看也库备份了刚刚创建的guxin库

     一主两从配置完成

    四、读写分离实验

    1、先配置Amoeba服务器

    （1）上次安装包到opt

    1. cd /opt
    2.  
    3. rz-E   #上传包amoeba-mysql-binary-2.2.0.tar.gz
    4. rz-E   #上传包jdk-6u14-linux-x64

    （2）编译文件jdk-6u14-linux-x64

    1. chmod +x jdk-6u14-linux-x64
    2. ./jdk-6u14-linux-x64
    3. 跳出交互页面选择yes
    4. 按enter键
    5.  
    6. mv jdk1.6.0_14/ /usr/local/jdk1.6
    7.

     编辑配置文件

    1. vim /etc/profile   # 大G到行尾
    2. export JAVA_HOME=/usr/local/jdk1.6
    3. export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
    4. export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
    5. export AMOEBA_HOME=/usr/local/amoeba
    6. export PATH=$PATH:$AMOEBA_HOME/bin

     重载配置文件并查看java版本

    1. source /etc/profile
    2.  
    3. java -version

     （3）安装amoeba软件

    1. mkdir /usr/local/amoeba
    2.  
    3. tar zxvf amoeba-mysql-binary-2.2.0.tar.gz  -C /usr/local/amoeba/

    1. ls /usr/local/amoeba/
    2. chmod -R 755 /usr/local/amoeba/
    3. cd /usr/local/amoeba/bin/
    4. ls

       尝试启动amoeba服务（可省略）

    ./amoeba start

     2、配置Amoeba读写分离，两个slave读负载均衡

    （1）先在master、slave1、slave2的mysql上开发权限给192.168.154.%网段

    1. grant all on *.* to test@'192.168.154.%' identified by '123456';
    2.  
    3. #三台主机都要配置

     （2）创建amoeba.xml、dbServers.xml的备份文件

    1. cd /usr/local/amoeba/conf
    2. cp amoeba.xml amoeba.xml.bak
    3. cp dbServers.xml dbServers.xml.bak

     （3）编辑置文件amoeba.xml

    1. vim amoeba.xml
    2.

     修改内容如下

     (4)编辑配置文件dbServers.xml

    1. 23行注释掉 作用默认进入test库
    2. <!--                    <property name="schema">test</property>-->
    3. 26修改
    4.    <property name="user">test</property>
    5. 28-30去掉注释
    6.  <property name="password">123456</property>
    7.                  </factoryConfig>
    8. 45行修改，设置主服务器的名Master
    9.   <dbServer name="master"  parent="abstractServer">
    10. 48行修改设置主服务器的地址
    11.    <property name="ipAddress">192.168.154.19</property>
    12. 52行修改设置从服务器的名slave1
    13. <dbServer name="slave1"  parent="abstractServer">
    14. 55行修改设置从服务器1的地址
    15.  <property name="ipAddress">192.168.154.20</property>
    16. 58复制上面6行粘贴，设置从服务器2的名称和地址
    17. <dbServer name="slave2"  parent="abstractServer">
    18.                  <factoryConfig>
    19.                          <!-- mysql ip -->
    20.                         <property name="ipAddress">192.168.154.21</property>
    21.                 </factoryConfig>
    22.          </dbServer>
    23. 65行修改
    24.  <dbServer name="slaves" virtual="true">
    25. 71行修改
    26.  <property name="poolNames">slave1,slave2</property>

     （5）启动amoeba软件

    /usr/local/amoeba/bin/amoeba start &

     （6）查看8066端口是否开启，默认端口为tcp8066

     3、测试读写分离

    (1)在客户端安装mariadb-server mariadb

    1. yum install -y mariadb-server mariadb
    2. systemctl start mariadb.service

     （2）在主服务器上测试

    1. mysql -u amoeba -p123456 -h 192.168.154.22 -P8066
    2.  
    3. #通过amoeba服务器代理服务mysql，在通过客户端连接mysql后写入的数据只有主服务器会记录，然后同步给从———从服务器在主服务器上

     （3）在master服务器上创建表并查看

     在slave1 slave2上查看都有了test表

     （4）在两台从服务器上

    slave1上

    1. stop slave；
    2. use guxin；
    3. insert into test values('1','zhangsan','hello');

     slave2上

    1. stop slave；
    2. use guxin；
    3. insert into test values('2','lisi','world');

     在master服务器上

     insert into test values('3','wangerma','nihao');

     在客户端服务器上

    1. select * from test
    2.  
    3. select * from test   查看两次test

    （5）验证读写分离的写操作

     在客户端写入一条数据

    insert into test values('4','liuer','good boy');

     在master服务器上查看一下

    select * from test

    在slave1，slave2上查看

     只有master服务器能看到刚刚在客户端加入的数据，说明数据的写入是由master来处理的

    再在两个从服务器上执行 start slave; 即可实现同步在主服务器和客户机上添加的数据

    start slave；

    读写分离成功实现

    总结

    1、主从同步原理

        首先client端（tomcat）将数据写入到master节点的数据库中，master节点会通知存储引擎提交事务，同时会将数据以（基于行、基于sql、基于混合）的方式保存在二进制日志中
        SLAVE节点会开启I/O线程，用于监听master的二进制日志的更新，一旦发生更新内容，则向master的dump线程发出同步请求
        master的dump线程在接收到SLAVE的I/O请求后，会读取二进制文件中更新的数据，并发送给SLAVE的I/O线程
        SLAVE的I/O线程接收到数据后，会保存在SLAVE节点的中继日志中
        同时，SLAVE节点钟的SQL线程，会读取中继日志钟的熟，更新在本地的mysql数据库中
        最终，完成slave——>复制master数据，达到主从同步的效果

    2、如何查看主从同步状态是否成功

    在从服务器内输入命令 show slave status\G，查看主从信息进行查看，里面有IO线程的状态信息，还有master服务器的IP地址、端口、事务开始号，

    当 slave_io_running 和 slave_sql_running 都显示为yes时，表示主从同步状态成功

    3、如果I/O和SQL不是yes呢,你是如何排查的

    首先排除网络问题，使用ping命令查看从服务是否能与主服务器通信

    再者查看防火墙和核心防护是否关闭

    接着查看从服务器内的slave是否开启

    两个从服务器的 server-id 是否相同导致只能连上一台

    master_log_file 和 master_log_pos 的值要是否与Master查询的一致

    4、show slave status能看到哪些信息(比较重要的)

    IO线程的状态信息

    master服务器的IP地址、端口、事务开始位置

    最近一次的报错信息和报错位置等

    5、主从复制慢(延迟)有哪些可能

    主服务器的负载过大，被多个睡眠或者僵尸线程占用，导致系统负载过大

    从库硬件比主库差，导致复制延迟

    主从复制单线程，如果主库写并发太大，来不及传送到从库，就会导致延迟。

    慢SQL语句过多

    网络延迟
     

  • 相关阅读:
    【路径规划】基于FMM快速行进法实现船舶路径规划附matlab代码
    物联网智慧大屏
    【Python人工智能】Python全栈体系（二十一）
    网络之以太网
    andriodstudio创建不了项目，如何解决？
    python http.server 的测试和常见问题解决方法
    Java 线程的几种状态
    java-python+vue社区防疫服务管理系统网站
    AI 视频 | 文本生视频工具又迎来重大更新，Runway Gen-2 到底有多强？Gen-2 怎么用（保姆级教程）
    算法通关村第十六关：白银挑战-滑动窗口经典问题
  • 原文地址：https://blog.csdn.net/m0_58292366/article/details/125482692