重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
MySQL主从复制与读写分离的原理
这篇文章主要为大家分享MySQL主从复制与读写分离的原理。文中还介绍了如何配置和验证主从复制与读写分离的实验，希望大家通过这篇文章能有所收获。
成都创新互联坚实的技术研发基础赢得了行业内的良好口碑，公司成立10余年来，为上千家企业提供过网站建设、软件开发、搜索引擎优化技术、互联网大数据整合营销服务，多年的技术服务成功经验、众多的客户使我们能懂得更多，做得更好。"让您的网站跑起来"是我们一直追求的目标！
MySQL主从复制与读写分离
一、前言
前面我们已经对MySQL数据库的安装、命令、备份、索引、事务以及存储引擎等各个方面有了初步的理解，而今天我们将从宏观的角度来理解什么是MySQL数据库的主从复制与读写分离。
二、场景描述
在实际的生产环境中，如果对MySQL数据库的读与写都在一台服务器上进行操作，无论是在安全性，高可用性、还是高并发性等诸多方面都是无法满足的；而这就需要对MySQL数据库进行主从复制与读写分离。
我们使用一个架构图来引出MySQL数据库主从复制的原理以及其作用，请看下图：
服务器作用：
MySQL主服务器：负责数据的写入；
MySQL从服务器：同步主服务器的数据并进行数据的轮循读取；
那么这三台具备相同服务的MySQL服务器就称为“MySQL群集”。我们可以从上图中看出，这样的安排实现数据同步的基础上，实现数据的读写分离，从而在保证数据的可靠性的同时也大大减轻了主服务器的压力。
下面我们对MySQL的主从复制和读写分离进行逐一介绍并给出配置实例。
三、MySQL主从复制原理
MySQL主从复制与读写分离有着紧密的联系，可以这么说，MySQL的读写分离的实现需要基于主从复制的基础之上。
3.1MySQL支持的复制类型
- 基于语句的复制；——基于SQL语言命令的复制形式，使用SQL命令执行复制，效率高
- 基于行的复制；——复制数据库变化的内容，不是执行命令
- 混合类型的复制；——默认采用语句类型的复制，如果发现有不确定问题等其他原因造成无法复制的情况再进行基于行的复制
3.2MySQL复制的工作过程
我们通过下图来理解MySQL主、从服务器是如何进行复制同步的过程的。
1）首先，MySQL主服务器在更新数据库或其他进行数据库相关操作时，会在二进制日志文件中记录这些改变（这我们在前面的增量备份与恢复的文章中进行了讲述，log-bin的设置以及如何使用mysqladmin命令刷新该日志。）当写入日志完成后，主服务器会告知存储引擎提交事务；
2）MySQL从服务器会将主服务器的二进制日志文件（Binary log）复制到其中继日志（Relay log）中。中继日志通常存放在系统缓存中，因此中继日志的开销很小；
3）从服务器通过自身线程从中继日志中读取事件，更新自身的日志文件使其与主服务器中的数据一致。
ps:复制过程中有一个很重要的限制，即在从服务器上复制是串行化的，这就表明主服务器上的并行更新操作不能在从服务器上并行操作。
四、MySQL主从复制配置流程与实际操作
4.1主从复制所需环境
所需设备（我们在虚拟机上模拟进行配置）清单：
Centos7一台：作为主服务器——master，ip地址为192.168.68.133
Centos7两台：作为从服务器——slave1、slave2IP地址分别为192.168.68.129、192.168.68.132
所需安装服务：
安装并且配置ntp服务、都安装了MySQL5.7版本的数据库
4.2主从复制具体流程步骤
4.2.1准备工作
首先，我们联想一下增量备份的恢复操作，我们都是依赖于data目录下的二进制日志文件，通过两种方法实现的，其中一种就是根据时间节点进行备份恢复操作的。那么我们需要进行MySQL主从服务器复制，就需要先同步所有MySQL服务器的系统时间。
4.2.1.1主服务器准备工作的配置
1）服务器名称设置以区别
hostnamectl set-hostname master
su2）安装配置ntp服务
yum install ntp -y
#修改ntp服务配置文件，添加下面的两句
vim /etc/ntp.conf
server 127.127.68.0 #服务器本地网段，127.127表示的就是192.168
fudge 127.127.68.0 stratum 8 #时区设置（东八区）
#保存退出
systemctl start ntpd
#可以使用netstat -nutp | grep ntpd 命令查看服务开启状态3）关闭防火墙和SELinux功能
systemctl stop firewalld
setenforce 04.2.1.2两台从服务器的准备工作的配置（二者步骤一致）
1）服务器名称设置
hostnamectl set-hostname slave1（slave2）
su2）两台从服务器上安装ntp和ntpdate服务并开启服务
yum install ntp ntpdate -y
systemctl start ntpd3）两台从服务器上关闭防火墙和SELinux功能
systemctl stop firewalld
setenforce 04）使用ntpdate命令进行时间同步
[root@slave1 ~]# /usr/sbin/ntpdate 192.168.68.133
9 Jan 15:35:13 ntpdate[67450]: the NTP socket is in use, exiting4.2.2MySQL服务的配置
4.2.2.1主服务器上MySQL的修改与配置
1）修改mysql的主配置文件并重启mysql服务
vim /etc/my.cnf
#配置如下：
log-bin = master-bin #二进制日志文件 master-bin可以自己设置
server-id = 1 #服务器的id号，用于区别
log-slave-updates=true #开启从服务器更新日志功能（结合复制流程连接）
systemctl restart mysqld.service2）进入数据库进行权限设置（授权）与刷新
[root@lokott ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17-log Source distribution
Copyright (c) 2000, 2016, 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> grant replication slave on *.* to 'myslave'@'192.168.68.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;#刷新系统权限表
Query OK, 0 rows affected (0.00 sec)
mysql> 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)注意核心命令：授权命令的解释，还有以上位置Position 记录数据603
命令格式：
grant replication slave on *.* to 'myslave'@'192.168.68.%' identified by '123456';
#grant ——授权命令 replication slave 从服务器复制操作 *.*表示的是所有数据库所有表其具体含义是：赋予192.168.68.0网段的主机（服务器）复制（同步）主服务器的所有数据库数据；
4.2.2.2从服务器上MySQL的修改与配置
1）修改主配置文件并重启服务
vim /etc/my.cnf
#配置如下
log-bin = mysql-bin
server-id = 2
relay-log = relay-log-bin #中继日志
relay-log-index = slave-relay-log-bin.index #索引文件
#保存退出重启
systemctl restart mysqld.service2)进入数据库配置同步
[root@slave1 ~]# mysql -uroot -p
...#省略部分内容
mysql> change master to master_host='192.168.68.133',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',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.68.133
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
Slave_SQL_Running: Yes
...#省略部分内容 上面的两行表示从服务器给出的IO线程和数据库SQL语句都在运行状态
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)4.2.3进行主从复制的测试与验证
在主服务器上创建一个新的数据库，新建表和数据；在服务器上进行查看；
主服务器：
create database test;
Query OK, 1 row affected (0.00 sec)从服务上slave1查询：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> exit
Bye
[root@slave1 ~]#
从服务器slave2上查询：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> exit
Bye
[root@slave2 ~]#
4.3主从复制小结
该配置实现的是从服务器通过主服务器给予的权限进行复制操作，我们需要通过实验配置理解上述的主从复制的原理以及其复制的过程。只有在理解掌握了如何进行MySQL数据库的主从复制，才能理解和进行MySQL读写分离的配置操作。
五、MySQL读写分离原理
读写分离的原理，简单来说就是实现上图中，在主服务器上写数据，使用从服务器轮循读取数据的功能。
我们结合下图来理解读写分离的过程
基于代理层实现：代理一般位于客户端与服务器之间，代理服务器接到客户端请求通过判断后转发到后端数据库，有两个代表性程序。
1）MySQL_Proxy：通过自带的lua脚本进行SQL判断；
2）Amoeba：Java语言开发，阿里巴巴将其用于生产环境，不支持事务与存储过程；
六、MySQL读写分离配置流程与实际操作
环境：基于上面主从复制的流程进行后续陪配置
添加设备：Centos7两台：其中一台作为Amoeba代理服务器，另一台作为客户端测试服务器。
所需软件包：在Amoeba代理服务器上需要安装jdk和amoeba相关环境及应用（amoeba使用Java开发）
1）安装jdk环境和ameoba环境
[root@amoeba ~]# cd tar/
[root@amoeba tar]# ls
amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin cd tar/
ls
cp jdk-6u14-linux-x64.bin /usr/local/
cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
mv jdk1.6.0_14/ /usr/local/jdk1.6
vim /etc/profile
#添加如下几行
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
source /etc/profile
mkdir /usr/local/amoeba
#回到压缩包目录下解压amoeba
tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
cd /usr/local/2）配置amoeba相关参数
配置路径在/usr/local/amoeba/conf中，配置文件为ameoba.xml,dbServers.xml。前者为amoeba主配置文件（还记得tomcat吗？），后者是数据库服务器的相关配置文件。
1、更改ameoba.xml
30 amoeba
31
32 123456
#user是我们在client端登录amoeba代理服务器的身份名称，password是登录密码，待会我们将会在client端上登录
15 master
116
117 master
118 slaves
119
#defaultPool表示默认的服务器 writePool表示指定写服务器（组），readPool表示指定读服务器（组），slaves将在dbServers.xml文件中配置2、更改dbServers.xml
26 test
27
28
29 123123
45
46
47
48 192.168.68.133
49
50
51
52
53
54
55 192.168.68.129
56
57
58
59
60
61
62 192.168.68.132
63
64
65
66
67
68
69 1
70
71
72 slave1,slave2
73
74 3、开启amoeba（可以在另一个terminal查看端口netstat -natp | grep 8066）
[root@amoeba bin]# amoeba start &
[1] 121608
[root@amoeba bin]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2020-01-10 08:20:03,413 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
2020-01-10 08:20:03,566 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2020-01-10 08:20:03,567 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:39466.4、客户机client端上安装mysql数据库，登录查看
[root@localhost ~]# mysql -u amoeba -p123456 -h 192.168.68.144 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2023306452
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution
Copyright (c) 2000, 2016, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.01 sec)
#因为此时我们还没有创建如何数据表和数据，所以在客户端上是查看不到任何数据的。5、我们在主服务器上创建一个表并且在从服务器上查看是否存在这个表，存在则主从复制正常
主服务器：
mysql> use test;
Database changed
mysql> create table info (id int(5) not null primary key auto_increment,name varchar(10) not null);
Query OK, 0 rows affected (0.02 sec)
mysql> desc info;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(5) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> select * from info;
Empty set (0.00 sec)从服务器：
[root@slave1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.17-log Source distribution
Copyright (c) 2000, 2016, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.01 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 |
+----------------+
| info |
+----------------+
1 row in set (0.00 sec)6、在client端上查看这个表
mysql> show tables;
Empty set (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| info |
+----------------+
1 row in set (0.00 sec)7、那么我们如何验证读写分离呢？还记得我们在从服务器上的start slave命令吗？我们可以停止主从复制,使用stop slave即可
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)8、我们在client端插入新的数据，分别在主从服务器上进行查看
client端：
mysql> insert into info (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> select * from info;
+----+----------+
| id | name |
+----+----------+
| 2 | zhangsan |
+----+----------+
1 row in set (0.00 sec)主服务器：
mysql> select * from info;
+----+----------+
| id | name |
+----+----------+
| 2 | zhangsan |
+----+----------+
1 row in set (0.00 sec)从服务器：
mysql> select * from info;
Empty set (0.00 sec)主从服务器写分离小结
从以上的实验中，我们可以得出结论，经过主从复制与写分离的配置，我们可以将写数据的任务在主服务器上进行，主服务器写入完成后，由从服务器经过主从复制的过程进行（start slave）复制，因为在生产环境中我们不能stop slave（停止主从复制）的！！！。但是为了验证读写分离原理，我们只能先stop slave了。
下面验证读分离。
我们在从服务器上分别写入一些数据（两台从服务器上写的不一样）
slave1：
mysql> insert into info (name) values('lisi');
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+------+
| id | name |
+----+------+
| 1 | lisi |
+----+------+
1 row in set (0.00 sec)slave2：
mysql> insert into info (name) values('wangwu');
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+--------+
| id | name |
+----+--------+
| 1 | wangwu |
+----+--------+
1 row in set (0.00 sec)client 查询：（可能会有问题，因为是采用了auto_increment自增列）
因此我们在从服务器上开启复制功能，在主从服务器上修改字段，然后在从服务器上关闭复制功能，并且重新写入不同的数据，在client端查看。
两台从服务器：
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)主服务器：
mysql> alter table info modify id int(5) not null;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc info;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(5) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> delete from info;
Query OK, 1 row affected (0.00 sec)从服务器：
mysql> alter table info modify id int(5) not null;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc info;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(5) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> delete from info;
Query OK, 1 row affected (0.00 sec)client ：重新写入数据
mysql> insert into info (id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)主服务器：
mysql> select * from info;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
#此时需要重新查看状态记录position，此时如下：
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 2955 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)从服务器：
mysql> change master to master_host='192.168.68.133',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=2955;
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.
mysql> STOP SLAVE IO_THREAD FOR CHANNEL '';
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.68.133',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=2955;
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.68.133
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 2955
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: 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: 2955
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:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 5bb93767-328a-11ea-820a-000c290bd936
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)算了，我们将三个服务器上的数据表的数据都删了重新进行主从复制吧；按照主从复制执行之后继续进行读分离的操作；
关闭从服务器主从复制：
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)从服务器上写入不同数据：
slave1：
mysql> insert into info values(2,'lisi');
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
+----+----------+
2 rows in set (0.00 sec)slave2：
mysql> insert into info values(3,'wangwu');
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 3 | wangwu |
+----+----------+
2 rows in set (0.00 sec)client：
mysql> select * from info;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
+----+----------+
2 rows in set (0.00 sec)
mysql> select * from info;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 3 | wangwu |
+----+----------+
2 rows in set (0.00 sec)读分离小结
MySQL读分离则是在MySQL的从服务器上进行数据读取操作。其中两台做负载均衡，轮循读取，减轻压力，提高并发访问。
关于MySQL主从复制与读写分离就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
文章标题：MySQL主从复制与读写分离的原理
文章出自：http://cqcxhl.com/article/psdgpj.html