MySQL主从复制、读写分离

MySQL主从复制

binlog日志

• bin log ：记录所有数据的更改，可用于本机数据恢复和主从同步

bin log一共有三种模式：【STATEMENT 、 ROW 、 MIXED 】

statement level模式

• 每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。

• 优点：statement level下的优点，首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约io，提高性能。因为他只需要记录在master上所执行的语句的细节，以及执行语句时候的上下文的信息。

• 缺点：由于它是记录的执行语句，所以为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是,由于mysql现在发展比较快，很多的新功能加入，使mysql的复制遇到了不小的挑战,自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement level下，目前已经发现的就有不少情况会造成mysql的复制问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如sleep()在有些版本就不能正确复制

rowlevel模式

• 日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改

• 优点：bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以row level的日志的内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function,以及trigger的调用和触发无法被正确复制的问题。

• 缺点：row level下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改记录，这样可能会产生大量的日志内容，比如有这样一条update语句：update test set flag ='d' where other_id='a',执行之后，日志中记录的不是这条update语句所对应的事件(mysql是以事件的形式来记录bin-log日志)，而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的很多事件。自然，bin-log日志的量会很大

mixed模式

• 实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选一种。新版本中的statement level还是和以前一样，仅仅记录执行的语句。而新版本的mysql中对row level模式被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete 等修改数据的语句，那么还是会记录所有行的变更。

查看binlog的日志模式

• show variables like 'binlog_format';

调整binlog的日志模式：

• set binlog_format=[STATEMENT / ROW / MIXED] ;

relay log 中继日志

• Mysql 主节点将binlog写入本地，从节点定时请求增量binlog，主节点将binlog同步到从节点

• 从节点单独进程会将binlog 拷贝至本地 relaylog中 .

• 从节点定时重放relay log

查看bin log 和relay log 日志

因为binlog日志文件：mysql-bin.000005是二进制文件，没法用vi等打开，这时就需要mysql的自带的mysqlbinlog工具进行解码，执行： mysqlbinlog mysql-bin.000005 可以将二进制文件转为可阅读的sql语句

• mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.000058 > binlog

基于binlog的主从复制

首先我们说说主从复制的原理，这个是必须要理解的玩意儿：

　　

理解：

• MySQL之间的数据复制的基础就是二进制日志文件bin log ，

• Master的所有操作都会纪录在二进制日志文件中，

• 其他MySQL通过一个IO线程与其进行通信，监控这个日志文件的变化，并将变化赋值到Slave的中继日志relay中，

• 然后SQL线程会执行中继日志中的相关操作，以此实现主从数据库的一致性，也就是主从复制

配置 Master

准备材料如下：

• 分别装在两台Linux服务器上的数据库

• master：192.168.159.159

• slave ： 192.168.159.169

master数据库配置文件修改：my.cnf

• 在[mysqld]段下添加如下内容

　　

• 然后重启Mysql： service mysqld restart

• 登录到MySQL建立账户并授权给Slave

  • 设置账户的密码长度最低位数：set global validate_password_length=4;

  • 设置账户的密码强度级别标识：set global validate_password_policy=0;

  • 一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，加强安全

mysql> CREATE USER 'chen'@'192.168.159.169' IDENTIFIED BY 'chen';  #创建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.159.169'; #为其权限
mysql>flush privileges;   #刷新权限

　　

• 然后查看master状态，记录二进制文件名( mysql-bin.000002 ) 和位置(787)

　　

配置 Slave

• 第一步修改从服务器的配置文件 ： my.conf

　　

• 如果你的虚拟机上的MySQL服务是克隆而来的，则删除UUID文件：

  • rm /var/lib/mysql/auto.cnf

  • 从新启动MySQL服务

• 第三步登入MySQL，进行从服务器的配置 ，并开启从服务器复制功能

  • change master to 
    master_host='192.168.159.159',
    master_port=3306,
    master_user='chen',
    master_password='chen',
    master_log_file='mysql-bin.000002',
    master_log_pos=787；

  • 第四步：开启从服务器的复制功能 ： start slave;.

    • 

  • 第五步：检查从服务器的复制功能状态：show slave status\G

  • 　　

• 相关的命令：

  • stop slave 可以停止 从服务器

  • reset slave 清空从服务器的规则

  • start slave 启动从服务器

  • show slave status 查看从服务器状态

  • show master status 查看主服务器状态

  • 注意：当我们已经完成了主从后，在不关闭的情况下，在创立新的主从关系会报错

下面就可以开始测试 ，测试就不写具体得了，在主服务器中增加或者修改一个数据，然后我们在从服务器中查询看是否做到数据一致即可。

主从同步延迟的原因及解决办法

mysql 用主从同步的方法进行读写分离，减轻主服务器的压力的做法现在在业内做的非常普遍。 主从同步基本上能做到实时同步

　　

• 主从同步配置好以后，主服务器会把更新语句写入binlog

• 从服务器的IO 线程(这里要注意，5.6.3 之前的IO线程仅有一个，5.6.3之后的有多线程去读了，速度自然也就加快了)会去读取主服务器的binlog 并且写到从服务器的Relay log 里面

• 然后从服务器的 的SQL thread 会一个一个执行 relay log里面的sql ， 进行数据恢复

主从同步的延迟的原因

我们知道， 一个服务器开放Ｎ个连接给客户端来连接的， 这样有会有大并发的更新操作, 但是从服务器的里面读取binlog 的线程仅有一个， 当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表就会导致，主服务器的SQL大量积压，未被同步到从服务器里数据自然而然就多了起来。这就导致了主从不一致， 也就是主从延迟

主从同步延迟的解决方案

实际上没有什么彻底的方法可以杜绝这种延迟的发生，生产 > 消费 = 消息必然堆积

• 我们知道因为主服务器要负责更新操作， 他对安全性的要求比从服务器高， 所有有些设置配置的比较需要性能，比如,但是我的Slave并不需要这些耗费大量性能来保证安全的配置，可以考虑关闭一些

  • sync_binlog=1

  • innodb_flush_log_at_trx_commit = 1

  • ......点我去my.cnf解读

• 主从同步，从节能性能，不对外提供查询服务，作为备份存在，此时性能很高，可以有效缓解改问题

• 增加从服务器数量，分散客户端来的读取请求的压力，从而降低服务器负载

MySQL读写分离

目前比较流行的中间件是MySQL-Proxy和MySQL Router这两种方案

MySQL强烈建议使用Router 8与MySQL Server 8和5.7一起使用

目前的读写分离就是基于主从同步 + 中间件

• 中间件实现写操作发送给Master数据库

• 读操作发送给Slave数据库

• 我们客户端直接访问 中间件即可，中间件实现路由的分发

服务器环境：

• Master：192.168.159.159 【MySQL Proxy 、MySQL Router】

• Slave1：192.168.159.169

   

MySQL Proxy实现读写分离

主从的作用主要体现在备份数据上，要想做到MySQL支持高可用和高并发还需要集群，完成主从复制和读写分离

读写分离环境：

• 服务器都关闭防火墙 iptables -L service iptablesstop

• 服务器关闭selinux 采用临时关闭，

  • 长久关闭修改 : /etc/selinux/config 将SELINUX设置为disabled

  • 临时关闭 ：setenforce 0

  • 查看状态： getenforce

  • 

安装MySQL-Proxy

https://downloads.mysql.com/archives/proxy/

我是上传到 /usr/local/ 下的

tar -zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz  #解压文件
mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy   #更改目录名

创建主配置文件：mysql-proxy.cnf

cd /usr/local/mysql-proxy
mkdir lua           #创建脚本存放目录
mkdir logs          #创建日志目录
cp ./share/doc/mysql-proxy/rw-splitting.lua ./lua  #复制读写分离配置文件
cp ./share/doc/mysql-proxy/admin-sql.lua ./lua     #复制管理脚本
vi /etc/mysql-proxy.cnf                         #创建配置文件,并将下面内容写入
​
[mysql-proxy]
user=root             
admin-username=root   #主从mysql共有的用户
admin-password=123456 #用户的密码
proxy-address=192.168.159.159:4040                #mysql-proxy运行ip和端口，不加端口，默认4040
proxy-read-only-backend-addresses=192.168.159.169 #指定后端从slave读取数据
proxy-backend-addresses=192.168.159.159           #指定后端主master写入数据
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定读写分离配置文件位置
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua    #指定管理脚本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log         #日志位置
log-level=debug    #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true       #以守护进程方式运行
keepalive=true    #mysql-proxy崩溃时，尝试重启
​
#保存退出后 给权限
chmod 660 /etc/mysql-porxy.cnf

修改读写分离配置文件：rw-splitting.lua

vim /usr/local/mysql-proxy/lua/rw-splitting.lua

vim /usr/local/mysql-proxy/lua/rw-splitting.lua
​
if not proxy.global.config.rwsplit then
 proxy.global.config.rwsplit = {
  min_idle_connections = 1,     #默认超过4个连接数时，才开始读写分离，改为1
  max_idle_connections = 1,     #默认8，改为1
  is_debug = false
 }
end

启动MySQL-proxy

• 这儿可以考虑配置环境变量，或者为mysql-proxy脚本配置软连接，避免全路径

nohup /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf > /usr/local/mysql-proxy/bin/mysql-proxy.out 2>&1 &

测试读写分离：在其他客户端，通过mysql命令去连接MySQL Proxy机器

mysql -uroot -p123456 -h192.168.159.159 -P4040

MySQLProxy虽然可以实现读写分离的操作，但是MySQLProxy官方并没有推出稳定版，其中的坑还是 挺多的，并不推荐在生产环境使用 ，官方推荐使用MySQLRouter，所以关于MySQLProxy的使用大家做 为了解内容即可

MySQL Router 实现读写分离

没有其他介绍，就一句话：

• MySQL强烈建议使用Router 8与MySQL Server 8和5.7一起使用

• 下载 ：mysql-router-8.0.23-linux-glibc2.17-x86_64-minimal.tar.xz

• 解压，先解压xz，再解压tar

  • xz -d mysql-router-8.0.23-linux-glibc2.17-x86_64-minimal.tar.xz

  • tar -xvf mysql-router-8.0.23-linux-glibc2.17-x86_64-minimal.tar

• j进入解压目录，创建Mysql Router的启动配置文件：mysqlrouter.conf

  • [logger]
    level = INFO
    [routing:secondary]
    bind_address = localhost
    bind_port = 7001
    destinations = 192.168.159.159:3306,192.168.159.169:3306
    routing_strategy = round-robin
    [routing:primary]
    bind_address = localhost
    bind_port = 7002
    destinations =  192.168.159.159:3306,192.168.159.169:3306
    routing_strategy = first-available

  • 这里设置了两个路由策略：【routing:secondary 、routing:primary 】

    • 通过本地7001端口，循环连接到192.168.159.159:3306、192.168.159.19:3306两个MySQL实例，由round-robin路由策略所定义

    • 通过本地7002端口配置了MySQL写入实例，并设置首个可用策略

      • 首个可用策略使用目标列表中的第一个可用服务器

      • 即当192.168.159.159:3306可用时，所有7002端口的连接都转发到它，否则才转发到后面的服务器

    • 因此应用程序可以据此确定将读写请求发送到不同的服务器

      • 本例中可将读请求发送到本地7001端口 ，负载均衡到 192.168.159.159、192.168.159.169

      • 同时将写请求发送到本地7002 ，路由到192.168.159.159机器

• 启动Mysql Router

  • ./bin/mysqlrouter -c ./cdmysqlrouter.conf &

  • 可以执行测试，看是否OK

    • mysql -uroot -proot -P7001 --protocol=tcp -e "select@@hostname"

      • 多访问几次，负载均衡效果

    • mysql -uroot -proot -P7002 --protocol=tcp -e "select@@hostname"

      • 访问第一个机器：192.168.159.159:3306

在上面的配置文件中，我们配置了两个路由策略：routing_strategy = xx

• routing_strategy是MySQL Router的核心选项，从8.0.4版本开始引入

• 该选项实际控制路由策略，即客户端请求最终连接到哪个MySQL服务器实例

• 该设置时可选的，缺省使用round-robin策略

  • round-robin：每个新连接都以循环方式连接到下一个可用的服务器，以实现负载平衡

  • round-robin-with-fallback ：用于InnoDB Cluster。每个新的连接都以循环方式连接到下一个可用的secondary服务器。如果secondary服务器不可用，则以循环方式使用primary服务器

  • first-available ： 新连接从目标列表路由到第一个可用服务器。如果失败，则使用下一个可用的服务器，如此循环，直到所有服务器都不可用为止

  • next-available ：与first-available类似，新连接从目标列表路由到第一个可用服务器。与firstavailable不同的是如果一个服务器被标记为不可访问，那么它将被丢弃，并且永远不会再次用作目标。重启Router后，所有被丢弃服务器将再次可选。此策略向后兼容MySQL Router 2.x中mode为read-write的行为

基于主从复制的高可用方案（）

• 双节点主从 + keepalived/heartbeat方案，中小型规模的时候，采用这种架构是最省事的。

• 两个节点可以采用简单的一主一从模式，或者双主模式，并且放置于同一个VLAN中，

• 在master节点发生故障后，利用keepalived/heartbeat的高可用机制实现快速切换到slave节点

至于更多细节，暂时不补充了，以上的方案可做了解

.