MySQL主从复制、读写分离（利用Amoeba和Mycat）、完全同步

MySQL主从复制、读写分离（利用Amoeba和Mycat）、完全同步

mysql的主从复制和读写分离

主从复制 面试必问 主从复制的原理 读写分离 mha

一、主从复制

1、概念

主从复制：主MySQL上的数据，新增、修改库、表、表里的数据都会同步到从MySQL服务器上主从复制是单向的，只能从主复制到从服务器

1.1主从复制延迟问题

1. 网络延迟
2. 主从硬件设备（CPU主频、内存的读写性能IO、硬件IO）
3. 同步复制而不是异步复制

解决方案：

1.硬件方面：主库一般来说不需要动的太多，从库的硬件配置要更好。提升随机写的性能。硬盘可以考虑缓存固态的、升级CPU的核数、扩展一下内存。尽量使用物理机（不要用云服务器）
2.网络层面，主从服务器都配置在一个局域网内，尽量避免跨网段和跨机房
3.架构方面：做读写分离，把写入控制在主，从库负责读，减轻从库的压力
4.配置MySQL方面：从配置文件的角度实现性能最大化

配置文件当中进行设置的方式提高数据的而安全性

数据库的存储引擎要是innodb

1.2、MySQL安全和性能配置：

**追求安全性配置：**双一设置

innodb_flush_log_at_trx_commit=1

#每次事务提交时都会刷新事务日志。以确保持久性，最高级别的数据安全性，但是会影响性能，默认就是1

0就是事务提交时不会立刻刷新，而是每秒刷新一次，可以提高性能，但是发生故障会导致数据丢失。

2表示事务提交时，事务日志不会写入硬盘而是保存在系统缓存，同时也不会进行刷新。有一定的安全性和性能。但是对内存要求比较高。生成中一般都是默认1

sync binlog=1

#1也是默认值，每次提交事务之后，直接把二进制日志刷新到磁盘，以确保日志的持久性，占用性能比较高，但是安全性高

0表示二进制日志写入到缓存，也不会刷新日志，故障发生也会丢失数据，对内存的要求也提高了

自定义数字N：表示每N个事务才执行一次刷新到磁盘。提高性能，但是一旦崩溃数据也会大量丢失。

追求性能化配置：

sync binlog=0 sync_binlog=10 提交10次刷新一次

innodb_flush_log_at_trx_commit=2 每次更新都保存在内存中，不进行刷新

logs-slave-updates=0

#从库的更新不会写入二进制日志（不建议）

innodb buffer_pool_size 300M 500G

#控制innodb存储引擎缓冲池的大小，设置的数值越高，可以提高他的innodb的性能，更多的数据和索引都可以缓存在内存中。减少磁盘的访问次数。对系统内存要求比较高。

1.3、主从复制的工作过程：

1. 主节点的数据记录发生变化都会记录在二进制日志
2. Slave节点会在一定时间内对主库的二进制文件进行探测，看其是否发生变化，如果有变化，从库会开启一个IO的线程，请求master主库的二进制事件
3. 主库会给每一个IO读写的线程启动一个dump线程，用于发送二进制事件给从库，从库通过IO读写线程获取更新，Slave_sql 负责将更新写入到从库本地。实现主从一致

1.4、mysql主从复制注意点：

1. 只能在主库上发生变化，然后同步到从
2. 复制过程是串行化过程，在从库上复制是串行的，主库的并行更新不能在slave从库上并行操作
3. 主从复制的设计目的就是为了在主库上写，在从库上查。读写分离，实现高可用。
4. 若主从版本不一致，从的版本一定要高于主，保证可以向下兼容
5. 因为若主的版本更新，低版本的从无法兼容的。

流程图：

1.5、MySQL的主从复制的模式：

1. 异步复制：MySQL的默认复制方法就是异步复制。

异步：只要执行完之后。客户端提交事务，主MySQL会立即把结果返回给从服务器，主MySQL并不关心MySQL从MySQL是否已经接收，并且处理。主MySQL一旦崩溃，主MySQL的事务可能没有传到从MySQL，这个时候强行把从提升为主，可能到新的主MySQL，数据不完整。（很少见，工作中都是异步复制）

1. **全同步复制: **主库执行完成一个事务，要等所有的从库都执行了该事务之后才会返回客户端，因为需要等待所有从库全部执行完成，性能必然下降。（适用于对数据一致性和数据完整性要求非常高的场景）

2. **半同步复制：**介于异步复制和全同步复制之间。主库执行完一个客户端提交的事务之后，至少等待一个从库接收并处理完成之后才会返回非客户端。半同步在一定程度上提高了数据的安全性。夜壶有一定的延迟。这个延迟的时间一般是一个tcp-ip的往返时间（从发送到接受的时间，单位是毫秒。RTT），半同步复制最好是在低延迟的网络中使用。

总结

异步效率高

全同步安全

半同步安全效率均衡

2、主从复制实验：

实现是基于mysql的二进制日志，根据主库的二进制文件的标志位，实现主和从的同步 。主从服务器之间，服务器的时间要同步。

架构：

三台mysql服务器

230 主 240从 250从

ntpdate ntp.aliyun.com 时间同步

date查看时间是否同步

关防火墙 安全机制

yum -y install ntp

时间同步之后开始配置：

主MySQL：

vim /etc/my.cnf

log-bin=master-bin

binlog_format = MIXED

log-slave-updates=true ——允许从服务器复制数据时，可以从主的二进制日志写到自己的二进制日志中

重启MySQL

进入MySQL

给从服务器新建用户和授权：

CREATE USER ‘myslave’@‘192.168.183.%’ IDENTIFIED WITH mysql_native_password BY ‘123456’;创建用户并给权限

GRANT REPLICATION SLAVE ON . TO ‘myslave’@‘192.168.183.%’;

FLUSH PRIVILEGES; 刷新

show master status;靠位置点同步

从MySQL：

vim /etc/my.cnf

server-id不能一样，不能重复

添加：

relay-log=relay-log-bin

relay-log-index=slave-relay-bin.index

relay_log_recovery=1 ——不加默认是0，设1表示开启中继日志的恢复。从服务器出现异常，或者崩溃时，从服务器会从主服务器的二进制日志的正确读取和应用中继日志。同步。

relay-log=relay-log-bin:

指定了从服务器上二进制日志的基本文件名。在这个例子中，

二进制日志的文件名将以 “relay-log-bin” 开头。

relay-log-index=slave-relay-bin.index:

指定了中级日志索引文件的名称。二进制日志索引文件用于记录二进制日志文件的顺序和位置。

在这个例子中，索引文件名为 “slave-relay-bin.index”。

relay_log_recovery=1:

用于配置从服务器在启动时是否执行中继日志的恢复操作。设置为 1 表示启用中继日志的恢复，

通常在从服务器出现异常或崩溃后重启时使用。

这有助于确保从服务器能够从主服务器的二进制日志中正确地读取和应用中级日志，以保持数据一致性

重启MySQL

另一台从MySQL一样配置

show master status;

根据住的二进制文件名和位置点对两个从MySQL进行同步：

主：

两个从MySQL同步：

CHANGE master to master_host=‘192.168.183.230’,master_user=‘myslave’,master_password=‘123456’,master_log_file=‘master-bin.000001’,master_log_pos=857;

配置同步，注意 master_log_file 和 master_log_pos 的值要与Master查询的一致

start slave; #启动同步，如有报错执行 reset slave;

*查看slave状态：*

show slave status\G;

slave_IO_Running:Yes：负责和主库的IO通信

Slave_SQL_Running:Yes：负责自己的Slave MySQL进程

若slave_IO_Running:No怎么解决？

1. 网络问题

2. vim /etc/my.cnf配置文件错了

3. CHANGE master to master_host=‘192.168.233.21’,master_user=‘myslave’,master_password=‘123456’,master_log_file=‘master-bin.000001’,master_log_pos=604;

   要么文件名写错了，要么位置偏移量不对

4. 防火墙和安全机制问题（面试问，不能说这个）

若slave_SQL_Running:No怎么解决？

uuid

到此主从复制结束

打开navicat验证：

主MySQL新建查询：

创建一个新库、新表，看从有没有同步

二、读写分离：

要实现读写分离，第一步必须先实现读写分离

主从架构当中，主库只负责写，从库只负责读

读写分离的方式：

1.代码 开发人员纯靠代码完成，涉及到数据库的二次开发。性能好，不需要额外的硬件设备

2.中间层大地里 代理服务器。在客户端和主从架构之间有一个代理服务器。代理服务器收到客户端的请求之后 通过客户端的sql语句来进行判断，读转到从，写转主

1、概念：

1.1、为什么要有读写分离？

1. 数据库在写入数据时，比较耗时（MySQL写1万条数据，3分钟）
2. 数据库在读的时候速度很快（读1万条数据，5秒）

读写分离之后，数据库的写入和读取是分开的，哪怕写入的数据量比较大，但是不影响查询的效率。

1.2、什么场景下需要读写分离：

数据库不是一定需要读写分离的。只有在某些程序在使用数据库，更新少，但是查询较多，这种情况可以考虑读写分离

若读和差的需求差不多，也可以考虑读写分离

生产库一般都会做读写分离，测试库一般不管

在工作中，数据库的读写不可能在同一个库中完成。既不安全，也不能满足高可用，也不能实现高并发。工作中都会做读写分离

1.3、MySQL读写分离的原理：

1. 根据脚本实现：在代码中实现路由分类。在select和insert中进行路由分类。这种方法是最多的。

特点：性能好，在代码中就可以实现，不需要额外的硬件设备

缺点：开发实现的，根我们无关。如果说大型的复杂的应用，设计改动的代码非常多

1. 基于中间层代理实现：mysql-proxy，是MySQL自带的开源项目，基于自带的lua脚本。这些lua脚本不是现成的，要自己写，不熟悉他的内置变量写不出来的
2. atlas：360内部自己使用的代理工具，不对外公开。每天读写请求承载量可以到几十亿条。而且还支持事务和存储过程
3. Amoeba：是基于java开发的开源软件。不支持事务和存储过程。但是Amoeba还是用的最多的，功能比较强大的软件。基于jdk1.5开发的，官方推荐1.5-1.6（今天实现读写分离的方式）

2、实验：用Amoeba来实现读写分离：

3台MySQL：

MySQL31（主）、32（从）、33（从）

test1（读写分离客户服务器，部署Amoeba）、test2（客户端）

安装Amoeba软件和jdk环境

test1（读写分离客户服务器，部署Amoeba）、test2（客户端）

安装Amoeba软件和jdk环境

export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAV{A}_{H}OME/jre/libexportPATH=$JAVA_HOME/lib:$JAV{A}_{H}OME/jre/bin/:$PATH:$HOME/binexportAMOEB{A}_{H}OME=/usr/local/amoebaexportPATH=$PATH:$AMOEBA_HOME/bin

安装Amoeba

先mkdir /usr/local/amoeba

tar -xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/

三台MySQL设置权限，允许Amoeba可以访问

CREATE USER ‘amoeba’@‘192.168.183.%’ IDENTIFIED WITH mysql_native_password BY ‘123456’;

GRANT REPLICATION SLAVE ON . TO ‘amoeba’@‘192.168.183.%’;

GRANT ALL PRIVILEGES ON . TO ‘amoeba’@‘192.168.183.%’;

flush privileges;

##配置 Amoeba读写分离，两个 Slave 读负载均衡##
#先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问

CREATE USER ‘amoeba’@‘192.168.233.%’ IDENTIFIED WITH mysql_native_password BY ‘123456’;

GRANT REPLICATION SLAVE ON . TO ‘amoeba’@‘192.168.233.%’;

GRANT ALL PRIVILEGES ON . TO ‘amoeba’@‘192.168.233.%’;

flush privileges;

#再回到amoeba服务器配置amoeba服务：
cd /usr/local/amoeba/conf/

修改amoeba.xml 用户信息配置文件

确定库名 确定主从

先备份：

cp amoeba.xml amoeba.xml.bak

改配置文件：vim amoeba.xml

30行用户改成amoeba

31行修改密码123456

115行修改

修改完毕之后保存退出

修改dbservers.xml 数据库信息文件

先备份：

cp dbServers.xml dbServers.xml.bak.2024.07.23

23行，更改注释范围

26行修改：

45行修改：

52行修改：

65行修改：

71行修改：

修改完成后

启动Amoeba+后台运行

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

查看java启没启动

netstat -antp | grep java

测试读写分离：

客户端安装MySQL测试：（epel源）

yum -y install epel-release

yum -y install mariadb-server mariadb

安装好了之后重启mariadb，查看端口是否启动

在所有三台主从MySQL上配置文件上加入：

开启日志：

vim /etc/my.cnf

general_log=ON

general_log_file=/usr/local/mysql/data/mysql_general.log

重启MySQL

打开三台MySQL的日志

从客户端连接Amoeba服务器进行测试：

三、面试题

1、主从同步复制原理
2、读写分离你们使用什么方式？
3、如何查看主从同步状态是否成功
4、如果I/O不是yes呢，你如何排查？
5、show slave status能看到哪些信息（比较重要）
6、主从复制慢（延迟）会有哪些可能？怎么解决？

总结+面试题
1、主从同步复制原理

（1）Master节点将数据的改变记录成二进制日志（bin log），当Master上的数据发生改变时，

则将其改变写入二进制日志中。

（2）Slave节点会在一定时间间隔内对Master的二进制日志进行探测其是否发生改变，如果发生改变，

则开始一个I/O线程请求 Master的二进制事件。

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

并保存至Slave节点本地的中继日志（Relay log）中，Slave节点将启动SQL线程从中继日志中读取二进制日志，

在本地重放，即解析成 sql 语句逐一执行，使得其数据和 Master节点的保持一致，最后I/O线程和SQL线程将进入睡眠状态，

等待下一次被唤醒。

2、读写分离你们使用什么方式？ amoeba 代理 mycat 代码 sql_proxy
通过amoeba代理服务器，实现只在主服务器上写，只在从服务器上读；
主数据库处理事务性查询，从数据库处理select 查询；
数据库复制被用来把事务查询导致的变更同步的集群中的从数据库

3、如何查看主从同步状态是否成功
在从服务器上内输入 show slave status\G 查看主从信息查看里面有IO线程的状态信息，还有master服务器的IP地址、端口事务开始号。
当 Slave_IO_Running和Slave_SQL_Running都是YES时 ，表示主从同步状态成功

4、如果I/O不是yes呢，你如何排查？
首先排查网络问题，使用ping 命令查看从服务器是否能与主服务器通信
再查看防火墙和核心防护是否关闭（增强功能）
接着查看从服务slave是否开启
两个从服务器的server-id 是否相同导致只能连接一台
master_log_file master_log_pos的值跟master值是否一致

5、show slave status能看到哪些信息（比较重要）
IO线程的状态信息
master服务器的IP地址、端口、事务开始的位置
最近一次的错误信息和错误位置
最近一次的I/O报错信息和ID
最近一次的SQL报错信息和id

6、主从复制慢（延迟）会有哪些可能？怎么解决？
主服务器的负载过大，被多个睡眠或 僵尸线程占用 导致系统负载过大，从库硬件比主库差，导致复制延迟
主从复制单线程，如果主库写作并发太大，来不及传送到从库，就会到导致延迟
慢sql语句过多
网络延迟

mysql主从复制

若主从版本不一致，从的版本一定要高于主，保证可以向下兼容
因为若主的版本更新，低版本的从无法兼容的。

器的IP地址、端口事务开始号。
当 Slave_IO_Running和Slave_SQL_Running都是YES时 ，表示主从同步状态成功

4、如果I/O不是yes呢，你如何排查？
首先排查网络问题，使用ping 命令查看从服务器是否能与主服务器通信
再查看防火墙和核心防护是否关闭（增强功能）
接着查看从服务slave是否开启
两个从服务器的server-id 是否相同导致只能连接一台
master_log_file master_log_pos的值跟master值是否一致

5、show slave status能看到哪些信息（比较重要）
IO线程的状态信息
master服务器的IP地址、端口、事务开始的位置
最近一次的错误信息和错误位置
最近一次的I/O报错信息和ID
最近一次的SQL报错信息和id

6、主从复制慢（延迟）会有哪些可能？怎么解决？
主服务器的负载过大，被多个睡眠或 僵尸线程占用 导致系统负载过大，从库硬件比主库差，导致复制延迟
主从复制单线程，如果主库写作并发太大，来不及传送到从库，就会到导致延迟
慢sql语句过多
网络延迟

mysql主从复制

若主从版本不一致，从的版本一定要高于主，保证可以向下兼容
因为若主的版本更新，低版本的从无法兼容的。