mysql主从复制和读写分离
2021/12/7 19:16:53
本文主要是介绍mysql主从复制和读写分离，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
目录
一，主从复制架构和原理
1，mysql主从复制原理
2，读写分离的原理
二，配置主从复制与读写分离
1，环境配置
2，初始环境准备
3，搭建mysql主从复制
3，搭建Amoeba 实现读写分离
三，实际问题
1，造成主从不一致的原因
2，主从不一致修复方法
3，如何避免主从不一致
一，主从复制架构和原理
1，mysql主从复制原理
mysql的复制类型：
mysq支持的复制类型
（1）STATEMENT：基于语句的复制。在服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高。
（2）ROW：基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
（3）MIXED：混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。
mysql主从复制的工作过程：
原理工作过程
从库生成两个线程，一个I/O线程，一个SQL线程；
i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log(中继日志) 文件中；
主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；
SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一
致；
复制的作用：数据分布，负载均衡读操作，备份，高可用和故障切换，mysql升级测试
复制架构
主从复制相关线程：
主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重
复制相关功能的文件
master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系
mariadb-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志
mysql主从复制的延迟
master服务器高并发，形成大量事务
网络延迟
主从硬件设备导致 cpu主频、内存io、硬盘io
本来就不是同步复制、而是异步复制 从库优化Mysql参数。比如增大innodb_buffer_pool_size，让更多操作在Mysql内存中完成，减少磁盘操作。 从库使用高性能主机。包括cpu强悍、内存加大。避免使用虚拟云主机，使用物理主机，这样提升了i/o面性。 从库使用SSD磁盘 网络优化，避免跨机房实现同步
2，读写分离的原理
什么是读写分离
读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），
而从数据库处理SELECT查询操作。
数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
读写分离原理
MySQL 读写分离原理
读写分离就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理事务性操作，而从数据库处理 select 查询。数据库复制被用来把主数据库上事务性操作导致的变更同步到集群中的从数据库。
目前较为常见的 MySQL 读写分离分为以下两种
1）基于程序代码内部实现
在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。
优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支；缺点是需要开发人员来实现，运维人员无从下手。
但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。2）基于中间代理层实现
代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有以下代表性程序。
（1）MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行SQL 判断。
（2）Atlas。是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。
（3）Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。
为什么要读写分离
因为数据库的“写”（写10000条数据可能要3分钟）操作是比较耗时的。
但是数据库的“读”（读10000条数据可能只要5秒钟）。
所以读写分离，解决的是，数据库的写入，影响了查询的效率。数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。
利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能
主从复制与读写分离
在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。
二，配置主从复制与读写分离
1，环境配置
master服务器： 192.168.18.100 mysql5.7
slave1服务器： 192.168.18.91 mysql5.7
slave2服务器： 192.168.18.93 mysql5.7
Amoeba服务器： 192.168.18.90 jdk1.6、Amoeba
客户端 服务器： 192.168.18.109 mysql
2，初始环境准备
关闭防火墙和selinux
[root@localhost ~]# systemctl stop firewalld 您在 /var/spool/mail/root 中有新邮件 [root@localhost ~]# setenforce 0 [root@localhost ~]# ###每台机器都需要此操作防止影响
3，搭建mysql主从复制
1，搭建时间同步
主服务配置：
安装时间同步服务器
[root@localhost ~]# yum install ntp -y 已加载插件：fastestmirror, langpacks centos | 3.6 kB 00:00 Loading mirror speeds from cached hostfile 软件包 ntp-4.2.6p5-25.el7.centos.2.x86_64 已安装并且是最新版本
修改配置文件
[root@localhost ~]# vim /etc/ntp.conf
开启服务
[root@localhost ~]# service ntpd start Redirecting to /bin/systemctl start ntpd.service
配置从服务器（两台从服务器同样的操作）：
安装同步服务
[root@localhost ~]# yum install ntpdate -y 已加载插件：fastestmirror, langpacks Loading mirror speeds from cached hostfile 软件包 ntpdate-4.2.6p5-25.el7.centos.2.x86_64 已安装并且是最新版本 无须任何处理
开启服务
[root@localhost ~]# service ntpd start Redirecting to /bin/systemctl start ntpd.service
与主服务器时间同步
[root@localhost ~]# /usr/sbin/ntpdate 192.168.18.100 7 Dec 14:01:57 ntpdate[72726]: the NTP socket is in use, exiting
设置循环任务每30分钟同步一次时间
[root@localhost ~]# crontab -e */30 * * * * /usr/sbin/ntpdate 192.168.18.100 ：wq crontab: installing new crontab #创建任务成功
2，配置主从
主服务器配置
开启二进制日志修改配置文件
重启服务进入数据库
[root@localhost ~]# systemctl restart mysqld.service [root@localhost ~]# mysql -uroot -p123123 mysql: [Warning] Using a password on the command line interface can be insecure.
配置replication主从配置
(root@localhost) [(none)]> grant replication slave on *.* to 'myslave'@'192.168.18.%' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.01 sec) (root@localhost) [(none)]> flush privileges; ##刷新配置 Query OK, 0 rows affected (0.00 sec)
查看主服务器
(root@localhost) [(none)]> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 603 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
配置从服务器（两台从服务器配置相同）：
添加日志配置
[root@localhost ~]# vim /etc/my.cnf
重启服务，进入数据库
[root@localhost ~]# systemctl restart mysqld.service [root@localhost ~]# mysql -uroot -p123123 3mysql: [Warning] Using a password on the command line interface can be insecure.
查看配置格式
mysql> help change master to CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4, MASTER_CONNECT_RETRY=10;
从服务器配置（用户，密码要与主服务配置一致，二进制日志与节点与主服务器查看的要一致）
mysql> change master to
-> master_host='192.168.18.100',master_user='myslave',mmaster_password='123456',master_log_file='master-bin.0000011',master_log_pos=603;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
开启服务，查看开启状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.18.100
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 603
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes ## 两项都是yes就配置成功
Slave_SQL_Running: Yes ## 两项都是yes就配置成功
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 603
Relay_Log_Space: 526
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
验证主从复制功能
在主服务器
(root@localhost) [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bbs | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.01 sec) (root@localhost) [(none)]> create database test; ##创建库 Query OK, 1 row affected (0.00 sec) (root@localhost) [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bbs | | mysql | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.00 sec) (root@localhost) [(none)]> use test; Database changed (root@localhost) [test]> create table test1(id int,name char(20),age char(10)); Query OK, 0 rows affected (0.00 sec) ##创建表 (root@localhost) [test]> show tables; ##查看表 +----------------+ | Tables_in_test | +----------------+ | test1 | +----------------+ 1 row in set (0.00 sec)
在从服务器192.168.18.91上查看
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test1 | +----------------+ 1 row in set (0.00 sec)
在从服务器192.168.18.93上查看
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test1 | +----------------+ 1 row in set (0.00 sec)
在主服务器上创建的库表从服务器上都有了说明主从复制完成了没有问题
3，搭建Amoeba 实现读写分离
安装amoeba
因为 Amoeba 基于是 jdk1.5 开发的，所以官方推荐使用 jdk1.5 或 1.6 版本，高版本不建议使用。
安装Java环境
[root@localhost opt]# cd /opt [root@localhost opt]# ls amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin ##准备好安装包 [root@localhost opt]# cp jdk-6u14-linux-x64.bin /usr/local/ #复制到指定位置 [root@localhost opt]# chmod +x /usr/local/jdk-6u14-linux-x64.bin #赋予执行权限 [root@localhost opt]# cd /usr/local/ #进入文件位置 [root@localhost local]# ls ##查看 bin games jdk-6u14-linux-x64.bin lib64 sbin src etc include lib libexec share [root@localhost local]# ./jdk-6u14-linux-x64.bin ##安装
出现done安装完成
改个名字
[root@localhost local]# mv jdk1.6.0_14/ jdk1.6
修改配置文件在末尾添加变量
[root@localhost local]# vim /etc/profile done unset i unset -f pathmunge export JAVA_HOME=/usr/local/jdk1.6 export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin export AMOEBA_HOME=/usr/local/amoeba export PATH=$PATH:$AMOEBA_HOME/bin "/etc/profile" 82L, 2028C 82,1 底端
刷新配置
[root@localhost local]# source /etc/profile
创建amoeba安装目录，解压安装包
[root@localhost local]# mkdir /usr/local/amoeba [root@localhost local]# cd /opt [root@localhost opt]# ls amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin [root@localhost opt]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba ##解压到指定位置
给文件赋予权限
[root@localhost opt]# chmod -R 755 /usr/local/amoeba/ [root@localhost opt]# /usr/local/amoeba/bin/amoeba amoeba start|stop ##如显示amoeba start|stop说明安装成功
#配置 Amoeba读写分离，两个 Slave 读负载均衡##
#先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问
mysql> grant all on *.* to test@'192.168.18.%' identified by '123123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> ###master和两台slave的mysql都要赋予权限
修改amoeba服务器配置
[root@localhost opt]# cd /usr/local/amoeba/conf/
[root@localhost conf]# cp amoeba.xml amoeba.xml.bak
##修改配置文件一般都要备份配置文件防止出现错误
[root@localhost conf]# vim amoeba.xml ##修改全局配置文件
30 <property name="user">amoeba</property>
##30行设置登录用户名
32 <property name="password">123456</property>
##32行设置密码
115 <property name="defaultPool">master</property>
116 ##设置默认池为master
117 ##117行取消注释
118 <property name="writePool">master</property>
##118行设置写池
119 <property name="readPool">slaves</property>
##119行设置读池
120 ##120行取消注释
:wq
修改配置文件
[root@localhost conf]# vim dbServers.xml
23 <!-- <property name="schema">test</property> -->
###23行注释
26 <property name="user">test</property>
###26行设置登录用户
29 <property name="password">123123</property>
###去除28,30行注释，添加密码
45 <dbServer name="master" parent="abstractServer">
###修改服务池名
48 <property name="ipAddress">192.168.18.100</property>
###添加服务地址
52 <dbServer name="slave1" parent="abstractServer">
53 <factoryConfig>
54 <!-- mysql ip -->
55 <property name="ipAddress">192.168.18.91</property>
56 </factoryConfig>
57 </dbServer>
###添加从服务节点1和地址，复制6行添加另一节点
58 <dbServer name="slave2" parent="abstractServer">
59 <factoryConfig>
60 <!-- mysql ip -->
61 <property name="ipAddress">192.168.18.93</property>
62 </factoryConfig>
63 </dbServer>
65 <dbServer name="slaves" virtual="true">
###定义池名
71 <property name="poolNames">slave1,slave2</property>
###写上节点名
:wq
开启服务
[root@localhost conf]# amoeba start log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml 2021-12-07 16:24:20,788 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0 log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf 2021-12-07 16:24:21,035 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066. 2021-12-07 16:24:21,037 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:50103. ###可以放在后台运行
查看
[root@localhost conf]# netstat -natp|grep java tcp6 0 0 127.0.0.1:60948 :::* LISTEN 3731/java tcp6 0 0 :::8066 :::* LISTEN 3731/java tcp6 0 0 192.168.18.90:52704 192.168.18.91:3306 ESTABLISHED 3731/java tcp6 0 0 192.168.18.90:59826 192.168.18.100:3306 ESTABLISHED 3731/java tcp6 0 0 192.168.18.90:39724 192.168.18.93:3306 ESTABLISHED 3731/java
客户机验证
[root@localhost ~]# mysql -u amoeba -p -h 192.168.18.90 -P8066 Enter password: ##密码123456 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 63243527 Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
mysql> show variables like 'general%'; ##查看全局日志 +------------------+-------------------------------------+ | Variable_name | Value | +------------------+-------------------------------------+ | general_log | OFF | | general_log_file | /usr/local/mysql/data/localhost.log | +------------------+-------------------------------------+ 2 rows in set (0.00 sec)
验证读
在两台从服务器分别添加数据
mysql> select *from test1; +------+------+------+ | id | name | age | +------+------+------+ | 1 | jj | 20 | +------+------+------+ 1 row in set (0.00 sec) mysql> insert into test1 values(2,'qq',24); Query OK, 1 row affected (0.00 sec)
mysql> select *from test1; +------+------+------+ | id | name | age | +------+------+------+ | 1 | jj | 20 | +------+------+------+ 1 row in set (0.00 sec) mysql> insert into test1 values(3,'ww',12); Query OK, 1 row affected (0.01 sec)
在客户机读测试
mysql> select *from test1; +------+------+------+ | id | name | age | +------+------+------+ | 1 | jj | 20 | | 2 | qq | 24 | +------+------+------+ 2 rows in set (0.01 sec) mysql> select *from test1; +------+------+------+ | id | name | age | +------+------+------+ | 1 | jj | 20 | | 3 | ww | 12 | +------+------+------+ 2 rows in set (0.00 sec) mysql> select *from test1; +------+------+------+ | id | name | age | +------+------+------+ | 1 | jj | 20 | | 2 | qq | 24 | +------+------+------+ 2 rows in set (0.00 sec) ##读是轮询在每台从服务器读一次
##读是在从服务器轮询读数据
验证写：
在主从服务器开启全局日志
(root@localhost) [test]> set global general_log=1; Query OK, 0 rows affected (0.01 sec) (root@localhost) [test]> show variables like 'general%'; +------------------+-------------------------------------+ | Variable_name | Value | +------------------+-------------------------------------+ | general_log | ON | | general_log_file | /usr/local/mysql/data/localhost.log | +------------------+-------------------------------------+ 2 rows in set (0.00 sec)
将从服务器从服务关闭
mysql> stop slave; Query OK, 0 rows affected (0.00 sec)
实时查看主从服务器日志文件
[root@localhost ~]# tail -f /usr/local/mysql/data/localhost.log
在客户端测试
mysql> insert into test1 values(4,'gg',3); Query OK, 1 row affected (0.02 sec)
主服务器日志
[root@localhost ~]# tail -f /usr/local/mysql/data/localhost.log /usr/local/mysql/bin/mysqld, Version: 5.7.20-log (Source distribution). started with: Tcp port: 3306 Unix socket: /usr/local/mysql/mysql.sock Time Id Command Argument 2021-12-07T09:32:20.142636Z 3 Query show variables like 'general%' 2021-12-07T09:38:03.533656Z 37 Init DB test 2021-12-07T09:38:03.534453Z 37 Query insert into test1 values(4,'gg',3)
从服务器日志
[root@localhost ~]# tail -f /usr/local/mysql/data/localhost.log /usr/local/mysql/bin/mysqld, Version: 5.7.20 (Source distribution). started with: Tcp port: 3306 Unix socket: /usr/local/mysql/mysql.sock Time Id Command Argument 2021-12-07T09:34:02.027694Z 20 Query stop slave 2021-12-07T09:39:08.872605Z 39 Connect test@192.168.18.90 on using TCP/IP 2021-12-07T09:39:08.873539Z 40 Connect test@192.168.18.90 on using TCP/IP 2021-12-07T09:39:08.874374Z 41 Connect test@192.168.18.90 on using TCP/IP
##主服务器有写操作，从服务器没有写操作
以上操作就可以验证读写分离和读的负载均衡
三，实际问题
1，造成主从不一致的原因
主库binlog格式为Statement，同步到从库执行后可能造成主从不一致。
主库执行更改前有执行set sql_log_bin=0，会使主库不记录binlog，从库也无法变更这部分数据。
从节点未设置只读，误操作写入数据
主库或从库意外宕机，宕机可能会造成binlog或者relaylog文件出现损坏，导致主从不一致
主从实例版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从不支持
数据库上面可能不支持该功能
MySQL自身bug导致
2，主从不一致修复方法
将从库重新实现
虽然这也是一种解决方法，但是这个方案恢复时间比较慢，而且有时候从库也是承担一部分的查询
操作的，不能贸然重建。
使用percona-toolkit工具辅助
PT工具包中包含pt-table-checksum和pt-table-sync两个工具，主要用于检测主从是否一致以及修
复数据不一致情况。这种方案优点是修复速度快，不需要停止主从辅助，缺点是需要知识积累，需要时间去学习，去测试，特别是在生产环境，还是要小心使用
手动重建不一致的表
在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致
这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的
范例：A,B,C这三张表主从数据不一致
3，如何避免主从不一致
主库binlog采用ROW格式
主从实例数据库版本保持一致
主库做好账号权限把控，不可以执行set sql_log_bin=0
从库开启只读，不允许人为写入
定期进行主从一致性检验
这篇关于mysql主从复制和读写分离的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-11-15MySQL教程：初学者必备的MySQL数据库入门指南
- 2024-11-15MySQL教程：初学者必看的MySQL入门指南
- 2024-11-04部署MySQL集群项目实战：新手入门教程
- 2024-11-04如何部署MySQL集群资料：新手入门指南
- 2024-11-02MySQL集群项目实战：新手入门指南
- 2024-11-02初学者指南：部署MySQL集群资料
- 2024-11-01部署MySQL集群教程：新手入门指南
- 2024-11-01如何部署MySQL集群：新手入门教程
- 2024-11-01部署MySQL集群学习：新手入门教程
- 2024-11-01部署MySQL集群入门：新手必读指南
