MySQL：主从复制、读写分离万字详解
目录案例概述
案例前置知识点
MySQL主从复制原理
MySQL复制范例
MySQL主从复制的过程
I/O线程怎么知道有新数据增加的？
主从复制的缺点
办理的方法
案例步调
主从复制
开端设置
MySQL数据库
防火墙
时间设置
ntp服务
捏造机设置时间同步
开启二进制日志记录功能
授权复制权
创建连接
验证测试
读写分离
读写分离原理
主机结构
操作步调
根本设置
安装jdk
安装amoeba
修改设置文件
修改连接客户端设置文件
修改连接后端数据库设置文件
测试
数据同步测试
数据查询测试
案例概述
[*]在企业应用中，成熟的业务通常数据量都比较大
[*]单台MySQL在安全性、高可用性和高并发方面都无法满足现实需求
[*]设置多台主从数据库服务器来实现读写分离
案例前置知识点
主服务器可以有多个从服务器，一对多
MySQL主从复制原理
[*] MySQL复制范例
[*]基于语句（SQL语句）的复制：STATMENT
[*]要颠末很多环节，大概会出错。效率高
[*]基于行的复制：ROW
[*]复制记录本身，而不是产生记录的语句。效率低
[*]肴杂范例的复制：MIXED
[*]把上面两个归并就是肴杂范例复制
[*]复制时优先用基于语句的复制，然后再用基于行的复制
[*] MySQL主从复制的过程
https://i-blog.csdnimg.cn/direct/3382cbf77bdc402f95e82e3bb940712e.png
[*]当主服务器数据改动后，有dump线程将实行的事件或语句写入二进制日志中
[*]只要有新数据，从服务器会通过的I/O线程去主服务器读取二进制日志文件
[*]从服务器将新增加的数据写入到本地的中继日志，这个日志只保存主服务器同步过来的新日志内容
[*]只要中继日志内新增了日志信息，再被从服务器的SQL线程进行重放（重新实行新增数据的SQL语句）
[*]如许就可以实现主从复制，主节点和从节点的服务器数据就可以到达一致
当从服务器的数据和主服务器的数据实现同步的时间，此时主服务器的二进制日志和从服务器的中继日志两个文件内的position信息是一致的
I/O线程怎么知道有新数据增加的？
当从服务器的中继日志内的信息和主服务器的二进制日志文件不一致了，那么I/O线程就会感知到主服务器有新数据增加，就会去读取
主从复制的缺点
构建主从复制时，最幸亏业务数据生成之前就构建好主从复制
如果一个主服务器已经运行了一段时间了，主服务器内存储的有业务数据了，这时间如果要实现主从复制的话，只能同步添加从服务器之后新增的数据，而构建主从复制之前的数据就同步不了了
办理的方法
对于上面的场景，可以在实现主从复制之前，把主服务器的数据通过mysqldump导出出来，再拷贝到从服务器导入进去，这个时间两个服务器的数据就一致了，然后再构建主从复制数据就同步了
导出的时间，最好把步调和数据库隔脱离，不然在导出的时间，步调添加了新的数据，又要重新导出
案例步调
本案例的目的是实现主从复制和读写分离，所以必要三台捏造机来实现本案例
主机脚色
IP地址
主服务器
192.168.10.101
从服务器1
192.168.10.102
从服务器2
192.168.10.103
主从复制
开端设置
MySQL数据库
本案例必要三台主机都安装了MySQL数据库，这里就略过不再演示安装步调，之前的文章有写：http://t.csdnimg.cn/5jnIy
防火墙
把三台主机的防火墙都关闭
# systemctl stop firewalld 时间设置
首先要包管三台主机的时间一致，在主从复制时，如果时间偏差太大，接收方会认为接收的数据和自己的时间不一致，就不会担当数据了
# 101
# date
2024年 07月 30日 星期二 20:36:19 CST
# 102
# date
2024年 07月 30日 星期二 20:36:21 CST
# 103
# date
2024年 07月 30日 星期二 20:36:23 CST
如果时间不一致的话，要么手动修改，要么使用ntp服务来确保主机上的时间和指定服务器时间一致
ntp服务
通过ntp服务将一台主机设置为时间服务器，然后让别的两台主机和该时间服务器同步
在101主机上安装ntp服务的软件包，打开ntp服务的设置文件，在server 3.centos.pool.ntp.org iburst下方添加以下内容，末了重启该服务并设为开机自启
或者让三台主机使用网络上的时间服务器，比如阿里云、腾讯云、百度云的时间服务器
# yum -y install ntp
# vim /etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8
# systemctl restart ntpd
# systemctl enable ntpd 在别的的主机上也安装ntp服务，而且进行时间同步
# yum -y install ntp
# ntpdate 192.168.10.101 捏造机设置时间同步
或者在捏造机设置中打开VMware tools功能，开启捏造机与宿主机时间的同步
这里的图片只是演示步调，并不代表最终设置的结果，请自行设置
https://i-blog.csdnimg.cn/direct/8eb8f8a29a43490e9620ef0743326ec3.png
开启二进制日志记录功能
因为从服务器要通过主服务器的二进制日志文件来进行数据的复制更新，所以在设置文件开启这个功能（默认是关闭的）
首先打开mysql的设置文件
# vim /etc/my.cnf 在单元追加以下内容
# 主从复制必要参数
server-id=101
log-bin=master-bin
binlog-format=MIXED
# 附加优化参数，可写可不写
binlog-ignore-db=test # 忽略该库的日志记录
binlog-cache-size=1M # 日志缓存大小
expire-logs-days=3 # 自动清理过期日志的天数
log-slave-updates=true # 接收从服务器的更改
[*]server-id：表现指定服务器的标识，如果要构建一个主从架构的环境，设备之间的标识不能雷同，不然会出错
[*]log-bin=master-bin：指定日志文件生成的前缀
[*]binlog-format：指定复制方式
[*]
[*]STATMENT：基于语句的复制
[*]ROW：基于行的复制
[*]MIXED：肴杂复制
log-slave-updates
这个选项开启后，允许一个MySQL服务器接收上游的数据并扩展给下游的服务器，可以使用这个选项实现主主架构
修改完设置文件后，必要重启服务
# systemctl restart mysqld 授权复制权
要实现主从复制，101必要授权给102和103允许复制的权限
授权所有来自192.168.10.0网段的主机，使用myslave用户允许访问所有库的所有表，密码为123456。
然后刷新权限
# mysql -uroot -ppwd123
mysql> grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123456';
mysql> flush privileges; 再使用show下令查看主节点的当前状态
当前位置日志文件名称，和最新位置603
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 603 | | test | |
+-------------------+----------+--------------+------------------+-------------------+ 此时再来到102主机
打开设置文件，在单元内添加以下内容，注意唯一标识不要重复，然后保存并退出，重启服务
# vim /etc/my.cnf
server-id=102
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
replicate-ignore-db=test # 忽略复制的数据库
# systemctl restart mysqld 再来到103主机
打开设置文件，在单元内添加以下内容，注意唯一标识不要重复，然后保存并退出，重启服务
# vim /etc/my.cnf
server-id=103
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
replicate-ignore-db=test # 忽略复制的数据库
# systemctl restart mysqld 创建连接
此时再来到102主机
在102主机的XShell终端内的任意位置右键选择下图选项，开启输入同步
https://i-blog.csdnimg.cn/direct/92000f6bd2d84bb685eb5fe85679ce32.png
必要在101主机关闭输入同步，确保只有102和103开启
https://i-blog.csdnimg.cn/direct/61c58d4213bf47c196c4cc72452ad921.png
首先在102主机登录进mysql
# mysql -uroot -ppwd123 使用change语句，修改主服务器的IP为101主机，用来连接的用户和密码改为刚刚在101设置的'myslave'和'123456'
要读取的日志文件指定为刚刚在101使用show master status下令表现的master-bin.000001，日志的位置也指定为刚刚使用show下令表现的603
mysql> change master to master_host='192.168.10.101', master_user='myslave', master_password='123456', master_log_file='master-bin.000001', master_log_pos=603; 然后开启从节点的相干进程，然后使用show slave status;下令查看从节点当前状态
确保IO进程和SQL进程是开启状态（YES），还可以看到其他关于主从节点的信息
只要两个进程都是YES状态，说明主从架构已经构建完成了
如果表现：Slave_IO_Running: Connecting 必要去change语句检查看看是不是密码错了什么的
如果找到问题必要实行change语句，必要先stop slave，然后使用change语句，末了再start slave，再show slave status;检查状态信息
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
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.000001
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes # IO进程状态
Slave_SQL_Running: Yes # SQL进程状态
Replicate_Do_DB:
Replicate_Ignore_DB: test # 不进行复制的库
# ... 关闭输入同步
此时可以关闭输入同步的功能了
https://i-blog.csdnimg.cn/direct/191da3431f62490f9ae70c3355d739b8.png
验证测试
来到101主机
先创建一个auth库，然后使用show下令查看一下，可以看到列出的数据库里有auth库
mysql> create database auth;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth |
| mysql |
| performance_schema |
| sys |
+--------------------+
102和103主机
在102和103主机直接使用show下令查看所有数据库，在没有创建过的环境下也可以看到auth库了
mysql> show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth |
| mysql |
| performance_schema |
| sys |
+--------------------+ 再来到101主机
在auth库内创建一个表，然后插入一条数据
mysql> select * from auth.users;
+------+------+
| id | name |
+------+------+
| 1 | tom|
+------+------+ 102主机和103主机
102和103主机此时应该都能查询到这一条数据
mysql> select * from auth.users;
+------+------+
| id | name |
+------+------+
| 1 | tom|
+------+------+ 但是如果在从服务器写入数据，除了写入数据的从服务器，其他不管是主服务器还是从服务器都不会同步从服务器写入的数据
所以在主从架构中，只有主服务器写入的数据会被其他从服务器同步
再来到101主机
再创建一个test库，然后进入该库，创建一个表和一条数据
mysql> create database test;
mysql> use test
mysql> create table t1 (id int(10), name char(20));
mysql> insert into t1 values (1, 'tom'); 102主机和103主机
102和103主机此时还都能查询到这一条数据
mysql> select * from test.t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
以为我们在设置102和103主机的MySQL设置文件的时间，设置了：replicate-ignore-db=test 选项，忽略复制了test的库，也就是差别步这个库的数据
读写分离
在上面的主从复制的底子上再构建读写分离，增强性能
比如主服务器只负责数据的写入，两个从服务器只负责数据的读取，分担压力
读写分离原理
[*]只在主服务器上写，只在从服务器上读
[*]主数据库处理事件性查询，从数据库处理select查询
[*]数据库复制用于将事件性查询的变更同步到集群中的从数据库
[*]读写分离方案
[*]基于步调代码内部实现
[*]基于中间署理层实现
[*]MySQL-Proxy
[*]Amoeba
署理的作用：把接收到的SQL语句分类，哪些是读取的哪些是写入的，再把读取的语句转发给用于读取的服务器处理，将写入的语句转发给用于写入的服务器处理
主机结构
除了三台主从服务器，还必要一台amoeba主机作为署理服务器和一台客户端主机作为测试机
这里再打开两台捏造机，一共就是5台主机
https://i-blog.csdnimg.cn/direct/315ad4cec69b4b8e9f22e5c6de930539.png
操作步调
根本设置
安装jdk
来到104主机
因为amoeba是java开发的，所以导入amoeba的tar包和jdk的二进制文件
https://i-blog.csdnimg.cn/direct/8612f565628f4d788ecae4f5b44c16bf.png
由于这里的jdk二进制文件是编译过后的代码，必要实行该文件来安装，所以必要使用chmod下令添加实行权
实行jdk文件，同意条款，按回车继承，安装完成
# chmod +x jdk-6u14-linux-x64.bin
# ./jdk-6u14-linux-x64.bin
Do you agree to the above license terms?
yes
Press Enter to continue..... # 按回车键继续 然后将安装出的目录移动到/usr/local/下 重定名为jdk1.6
# mv jdk1.6.0_14/ /usr/local/jdk1.6 为了能够方便地使用jdk，必要在系统变量中参加jdk的路径，由于jdk不仅仅是提供给系统使用，还有其他步调基于jdk运行，所以不创建软链接，而是直接在变量中写入路径
这里打开全局变量设置文件，在末尾添加以下内容，保存退出然后使用source重新加载该文件
末了可以使用java -version下令来检测jdk是否安装乐成
# vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/jre/bin
# source /etc/profile
# java -version
java version "1.6.0_14"
安装amoeba
来到104主机
amoeba是一个免安装的软件，中间不必要编译，直接解压就可以使用
# mkdir /usr/local/amoeba
# chmod -R 755 /usr/local/amoeba/
# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ 添加amoeba的系统变量和下令路径
这里打开全局变量设置文件，在末尾添加以下内容，保存退出然后使用source重新加载该文件
末了使用amoeba下令，查看变量是否设置正常
# vim /etc/profile
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin
# source /etc/profile
# amoeba
amoeba start|stop
来到101主机（主服务器）
amoeba在署理时，访问数据库的不肯定是用户，也大概是管理员或开发人员，因此amoeba的访问权限要大一点，除了grant语句的权限其他的语句都能实行
先登录进MySQL，授权给amoeba主机all权限
这里的test用户是提供给署理服务器，前端的用户只能通过署理服务器来连接数据库，而署理服务器只能通过这里的test用户来连接数据库
# mysql -uroot -ppwd123
mysql> grant all on *.* to 'test'@'192.168.10.%' identified by '123.com';
mysql> flush privileges; 修改设置文件
来到104主机
因为amoeba有两个连接，一个连接前端的用户端，一个连接后端的数据库，所以amoeba针对两个连接也提供了两个对应设置文件
[*]amoeba.xml
[*]连接前端客户端的设置文件
[*]dbServers.xml
[*]连接后端数据库的设置文件
修改连接客户端设置文件
打开前端设置文件
# vim /usr/local/amoeba/conf/amoeba.xml 在第30行和32行，修改客户端用于连接署理服务器时的账号和密码
这里的用户和密码只是前端的用户连接amoeba时用到的账户和密码，而不是连接数据库的
<property name="user">amoeba</property>
<property name="password">123456</property> 在第115行修改默认连接的主机
此时这里指定的仅仅是一个名字，master到底是哪个主机？我们还没有界说
<property name="defaultPool">master</property> 在第118和119行，修改负责写入数据的主机和负责读取数据的主机，并把这两行的表明去掉
这里也仅仅是指定的主机名，最终连接的主机我们还没有界说
<property name="writePool">master</property>
<property name="readPool">slaves</property> 保存并退出
修改连接后端数据库设置文件
打开连接后端数据库的设置文件
# vim /usr/local/amoeba/conf/dbServers.xml 先修改第23行的选项，指定连接amoeba默认登录MySQL连接的库为auth（必须已存在的库）
主服务器的数据库里必须要有这个auth库，否则会出错
如果没有这个库，去101主服务器数据库使用create database test;创建该库
<property name="schema">test</property> 再修改第26行和29行的账号密码，根据在101主服务器数据库中授权时创建的用户和密码来修改
29行四周的表明要去掉，而且使用的账号和密码都是必须存在的
<property name="user">test</property>
<property name="password">123.com</property> 在第45行，界说前面编写连接前端客户端设置文件里的master主机和slaves主机，分别修改对应的IP地址
但是我们的架构是2个从服务器，也就是两个slave，所以这里要复制一个dbServer块，再添加一个slave2，然后再组合定名为slaves
<dbServer name="master"parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.10.101</property> # 修改对应IP
</factoryConfig>
</dbServer>
<dbServer name="slave1"parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.10.102</property> # 修改对应IP
</factoryConfig>
</dbServer>
# 添加的slave2 ↓↓↓
<dbServer name="slave2"parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.10.103</property> # 修改对应IP
</factoryConfig>
</dbServer>
# 把slave1和slave2共同命名为slaves
<dbServer name="slaves" virtual="true">
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
<property name="loadbalance">1</property>
<!-- Separated by commas,such as: server1,server2,server1 -->
<property name="poolNames">slave1,slave2</property>
</poolConfig>
</dbServer> 注意，必要关闭防火墙或设置相干策略来允许流量通过，这里为了实行方便直接关闭防火墙
保存并退出，开启amoeba
下令末端加的&符号表现在背景运行
背景运行：开启的步调或服务，在背景运行不会占用前台终端，如果在前台运行的话，终端就不能输入下令了，一直被该步调占用
# systemctl stop firewalld
# amoeba start&
# 回车
然后使用netstat下令查看该步调的状态，如果表现出主从服务器的3个连接，而且表现ESTABLISHED（已创建连接），就说明amoeba启动正常了
如果没有表现主从服务器的3个连接，只表现两个，或少了某几个，就说明设置文件有错误，大概有错别字、表明没删除
# netstat -anpt | grep java
tcp6 0 0 127.0.0.1:1449 :::* LISTEN 1610/java
tcp6 0 0 :::8066 :::* LISTEN 1610/java
tcp6 0 0 192.168.10.104:49672 192.168.10.102:3306 ESTABLISHED 1610/java
tcp6 0 0 192.168.10.104:33674 192.168.10.103:3306 ESTABLISHED 1610/java
tcp6 0 0 192.168.10.104:60556 192.168.10.101:3306 ESTABLISHED 1610/java 这里的8066端口是提供给客户端连接amoeba署理服务器的端口，别的的4个端口都是随机端口
确保正确启动后就可以去105主机进行测试了
测试
数据同步测试
来到105主机（客户端测试机）
因为必要登录MySQL，所以必要先安装一个MySQL的客户端
然后使用mysql下令登录MySQL
这里使用的账户和密码都是刚才在104署理服务器的amoeba连接前端客户端的设置文件中界说的账户和密码，如下图↓
https://i-blog.csdnimg.cn/direct/53803351ed4f4f05ac052ef869819763.png
-h：指定连接的IP为192.168.10.104，因为我们要通过署理服务器来访问数据库，而不是直接连接MySQL数据库
-P 8066：如果要连接MySQL数据库本身，就要指定3306，但是我们要通过署理来连接MySQL，所以这里要写成署理的端口号8066（之前在104署理服务器的设置文件中查看 或者 使用netstat查询）
# yum -y install mysql
# mysql -uamoeba -p123456 -h 192.168.10.104 -P 8066 通过署理服务器登录进数据库后，进入auth库，创建一个test表用来测试，然后添加一条数据
MySQL [(none)]> use auth MySQL > create table test (id int(10), name char(20)); MySQL > insert into test values (1, 'tom');
在101、102、103主机查询
此时测试机通过署理服务器增加的表和数据都能被3台主从服务器查询到了，数据都被同步了
mysql> use auth
mysql> show tables;
+----------------+
| Tables_in_auth |
+----------------+
| test |
| users |
+----------------+
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | tom|
+------+------+ 在102主机添加
来到102主机添加一条数据
mysql> insert into test values (2, '102'); 在103主机添加
再来到103主机添加一条数据
mysql> insert into test values (3, '103'); 在105测试机查询
此时查询test表，可以看到表内的信息，是轮番被测试机查询到的
这说明：select的读取操作是轮番发送给2个从服务器的，这种行为可以被称为轮询负载均衡
MySQL > select * from test;
+------+------+
| id | name |
+------+------+
| 1 | tom|
| 2 | 102|
+------+------+
MySQL > select * from test;
+------+------+
| id | name |
+------+------+
| 1 | tom|
| 3 | 103|
+------+------+ 数据查询测试
在102、103从服务器操作
如果2个从服务器发生故障了，这里使用stop slave;下令将两个从服务器停止支持
mysql> stop slave; 在105测试机查询
此时在105测试机查询该表的内容，还是可以查询到102和103两个从服务器插入的数据
这是因为Amoeba通常具有负载均衡功能，即使你停止了从服务器的复制支持，署理服务器仍然会将查询哀求发送到多个数据库实例，如许可以避免单点故障并提高响应速度。
MySQL > select * from test;
+------+------+
| id | name |
+------+------+
| 1 | tom|
| 2 | 102|
+------+------+
2 rows in set (0.00 sec)
MySQL > select * from test;
+------+------+
| id | name |
+------+------+
| 1 | tom|
| 3 | 103|
+------+------+ 在101主服务器添加
如果此时在101主服务器添加一条数据然后再去105测试机查询会怎么样呢？
mysql> insert into test values (4, '101'); 在105测试机查询
再次使用select语句查询test表的所有内容，可以发现查询的结果还是两个从服务器刚才添加的数据
因为在关闭了两个从服务器的复制支持后，主服务器的新增数据无法再同步给两个从服务器，而从服务器是被我们用来读取数据的服务器，所以只能查询到从服务器同步主服务器数据之前的数据
MySQL > select * from test;
+------+------+
| id | name |
+------+------+
| 1 | tom|
| 2 | 102|
+------+------+
2 rows in set (0.02 sec)
MySQL > select * from test;
+------+------+
| id | name |
+------+------+
| 1 | tom|
| 3 | 103|
+------+------+
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]