一看就懂的MySQL主从复制与读写分离

一、前言

MySQL使用主从复制与读写分离的原因：
● 在企业应用中，成熟的业务通常数据量都比较大
● 单台MySQL在安全性、高可用性和高并发方面都无法满足实际的需求
（myisam基于表级锁定；innodb基于行级锁定 --存储引擎不能满足并发读、写的需求）
● 配置多台主从数据库服务器以实现读写分离

二、MySQL主从复制原理

● MySQL的主从复制和MySQL的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离

2.1 MySQL支持的复制类型

● 基于语句的复制。在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。
● 基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
● 混合类型的复制。默认采用基于语句的复制，一旦发生基于语句无法精准复制时，就会采用基于行的复制。

三、MySQL读写分离原理

● 只在主服务器上写，只在从服务器上读
● 主数据库处理事务性查询，从数据库处理SELECT查询
● 数据库复制用于将事务性查询的变更同步到集群中的从数据库
● 读写分离方案

1. 基于程序代码内部实现
2. 基于中间代理层实现
   

3.1 复制的基本过程

1. Master将用户对数据库更新的操作以二进制格式保存到Binary Log日志文件中
2. Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容
3. Master接受到到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志制定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置
4. Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到Master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”
5. Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的 那些可执行的内容，并在自身执行

四、MySQL主从复制读写分离搭建

三台MySQL5.7版本的服务器：
master：192.168.111.10
slave1：192.168.111.20
slave2：192.168.111.30
一台Amoeba代理服务器：
Amoeba：192.168.111.100

4.1 MySQL读写分离步骤

先把服务器的防火墙和核心防护全部关闭。

[root@master ~]# systemctl stop firewalld
[root@master ~]# setenforce 0

4.1.1 时间同步

● 主服务器：

[root@master ~]# ntpdate ntp.aliyun.com
[root@master ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate ntp.aliyun.com
[root@master ~]# yum -y install ntp
[root@master ~]# vim /etc/ntp.conf
17 restrict 192.168.111.0 mask 255.255.255.0 nomodify notrap
21 #server 0.centos.pool.ntp.org iburst#注释
22 #server 1.centos.pool.ntp.org iburst
23 #server 2.centos.pool.ntp.org iburst
24 #server 3.centos.pool.ntp.org iburst
25 server 127.127.1.0#本地是时钟源
26 fudge 127.127.1.0 stratum 8#设置服务器层级是8级，顶级是0
[root@master ~]# systemctl restart ntpd
[root@master ~]# systemctl stop firewalld
[root@master ~]# setenforce 0

4.1.2 主从同步

● 主服务器配置：

1. 修改主服务器的主配置文件参数

[root@master ~]# vim /etc/my.cnf
server-id=10#mysql服务器指定id，服务器的唯一标识，不能相同
log_bin=master-bin#主服务器日志文件
log_slave_updates=true#允许中继日志读取主服务器的二进制日志
[root@master ~]# systemctl restart mysqld
[root@master ~]# cd /usr/local/mysql/data/
[root@master data]# ll
总用量 122928
-rw-r-----. 1 mysql mysql       56 11月  5 20:08 auto.cnf
-rw-r-----. 1 mysql mysql      308 11月 14 09:03 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 11月 14 09:03 ibdata1
-rw-r-----. 1 mysql mysql 50331648 11月 14 09:03 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 11月  5 20:08 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 11月 14 09:03 ibtmp1
-rw-r-----. 1 mysql mysql      177 11月 14 09:03 master_bin.000001
-rw-r-----. 1 mysql mysql      154 11月 14 09:03 master_bin.000002
-rw-r-----. 1 mysql mysql       40 11月 14 09:03 master_bin.index
drwxr-x---. 2 mysql mysql     4096 11月  5 20:08 mysql
drwxr-x---. 2 mysql mysql     8192 11月  5 20:08 performance_schema
drwxr-x---. 2 mysql mysql     8192 11月  5 20:08 sys
#登录主服务器，给从服务器授权
[root@master ~]# mysql -u root -p
mysql> grant replication slave on *.* to 'myslave'@'192.168.111.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show grants for myslave@'192.168.111.%';
+-------------------------------------------------------------+
| Grants for myslave@192.168.111.%                            |
+-------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.111.%' |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master_bin.000002 |      756 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
# 特别注意File和Position对应的master-bin.000002以及756两个参数，从服务器同步时需要用到

4.1.3 从服务器配置

● 设置时间同步：

[root@slave1 ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.111.10
no crontab for root - using an empty one
crontab: installing new crontab

1. 修改从服务器主配置文件参数

[root@slave1 ~]# vi /etc/my.cnf
server-id = 20#mysql服务器的id，需要配置不同数字
relay_log=relay-log-bin#从主服务器上同步日志文件记录到本地中继日志
relay_log_index=slave-relay-bin.index#定义relay-log的位置和名称
[root@slave1 ~]# systemctl stop firewalld
[root@slave1 ~]# setenforce 0
[root@slave1 ~]# systemctl restart mysqld

2. 登录mysql服务器，添加要同步数据的master服务器的账号、二进制日志文件、开始同步的位置（position）

[root@slave1 ~]# mysql -u root -p
mysql> change master to master_host='192.168.111.10',master_user='myslave',master_password='123456',master_log_file='master-bin.000002',master_log_pos=756;
mysql> start slave;#开启从服务器
mysql> show slave status\G#查看从服务器状态
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes#这两项需要为YES
#如果Slave_IO_Running: No重启mysql从服务器即可（需要联网）

第二台配置与第一台相同

4.1.4 读写分离

1. 安装JDK环境

[root@amoeba opt]# tar zxf jdk-8u91-linux-x64.tar.gz
[root@amoeba opt]# mv jdk1.8.0_91/ /usr/local/java
[root@amoeba opt]# cd /etc/profile.d/
[root@amoeba profile.d]# vim java.sh
export JAVA_HOME=/usr/local/java#设置java根目录
export PATH=$PATH:$JAVA_HOME/bin#在PATH环境变量中添加JAVA根目录下的bin子目录
[root@amoeba profile.d]# source java.sh#java.sh脚本导入到环境变量中，使其生效
[root@amoeba ~]# java -version
openjdk version "1.8.0_131"
OpenJDK Runtime Environment (build 1.8.0_131-b12)
OpenJDK 64-Bit Server VM (build 25.131-b12, mixed mode)

2. 部署amoeba代理

[root@amoeba ~]# cd /opt
[root@amoeba opt]# unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local/
[root@amoeba ~]# mv /usr/local/amoeba-mysql-3.0.5-RC/ /usr/local/amoeba
[root@amoeba ~]# chmod -R 755 /usr/local/amoeba/
[root@amoeba ~]# vim /usr/local/amoeba/jvm.properties 

32 #JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"
33 JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"

3. 在三台MySQL上添加权限开放给amoeba访问

mysql> grant all on *.* to 'test'@'192.168.111.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

4. 修改amoeba主配置文件

[root@amoeba ~]# vim /usr/local/amoeba/conf/amoeba.xml
#28~30行修改如下，修改客户端连接amoeba前端服务器时使用的用户名和密码
28                                         <property name="user">amoeba</property>
29 
30                                         <property name="password">123456</property>
#83行修改如下（去掉注释<!--和-->）
 83                 <property name="defaultPool">master</property>
 84 
 85                 <property name="writePool">master</property>
 86                 <property name="readPool">slaves</property>

5. 修改dbServers.xml 文件

[root@amoeba ~]# vi /usr/local/amoeba/conf/dbServers.xml
#指定为amoeba创建的允许读取数据库的用户名和密码
22                         <!-- mysql schema -->
23                         <property name="schema">mysql</property>#5.7版本没有默认的test数据库
24 
25                         <!-- mysql user -->
26                         <property name="user">test</property>
27                         <!-- mysql password -->
28                         <property name="password">123456</property>
#配置三个服务器主机名和地址
43         <dbServer name="server1"  parent="abstractServer">
44                 <factoryConfig>
45                         <!-- mysql ip -->
46                         <property name="ipAddress">192.168.111.10</property>
47                 </factoryConfig>
48         </dbServer>
49 
50         <dbServer name="server2"  parent="abstractServer">
51                 <factoryConfig>
52                         <!-- mysql ip -->
53                         <property name="ipAddress">192.168.111.20</property>
54                 </factoryConfig>
55         </dbServer>
56 
57         <dbServer name="server3"  parent="abstractServer">
58                  <factoryConfig>
59                          <!-- mysql ip -->
60                          <property name="ipAddress">192.168.111.30</property>
61                  </factoryConfig>
62          </dbServer>
#指定名为slaves的poolNames中pools的主机名
64         <dbServer name="slaves" virtual="true">
70                         <property name="poolNames">server2,server3</property>

6. 启动amoeba服务器准备测试

[root@amoeba ~]# /usr/local/amoeba/bin/launcher
[root@amoeba ~]# netstat -anpt | grep 8066#查看amoeba服务是否开启
tcp6       0      0 :::8066                 :::*                    LISTEN      66823/java          
[root@amoeba ~]# yum -y install mysql#安装测试用客户机
[root@amoeba ~]# mysql -uamoeba -p123456 -h 192.168.111.100 -P 8066#amoeba地址

4.1.5 验证

1. 在master新建库和表：

mysql> create database test;
mysql> use test;
mysql> create table aaa(id int(2) not null primary key, name varchar(48), score char(48));
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| aaa            |
+----------------+
1 row in set (0.00 sec)
mysql> insert into aaa(id,name,score)values(1,'zhangsan',88);
Query OK, 1 row affected (0.02 sec)
mysql> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
+----+----------+-------+
1 row in set (0.00 sec)

2. 在两台从服务器上查看是否有库及表生成

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 |
+----------------+
| aaa            |
+----------------+
1 row in set (0.00 sec)
mysql> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
+----+----------+-------+
1 row in set (0.00 sec)

3. 在两台上关闭同步

mysql> stop slave;

4. 在主服务上插入内容不会同步（通过amoeba操作）
● 主服务器：

mysql> insert into aaa values(2,'lisi',89);
Query OK, 1 row affected (0.01 sec)

● 从服务器：

mysql> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
+----+----------+-------+
1 row in set (0.00 sec)

5. 在从服务器1上直接插入内容

mysql> insert into aaa values(3,'wangwu',60);
Query OK, 1 row affected (0.01 sec)

mysql> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
|  3 | wangwu   | 60    |
+----+----------+-------+
2 rows in set (0.00 sec)

6. 在从服务器2上直接插入内容

mysql> insert into aaa values(4,'zhaoliu',68);
Query OK, 1 row affected (0.01 sec)

mysql> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
|  4 | zhaoliu  | 68    |
+----+----------+-------+
2 rows in set (0.00 sec)

7. 在客户端amoeba上测试（第一次向从服务器1读数据，第二次向从服务器2读数据）

MySQL [(none)]> use test;
MySQL [test]> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
|  3 | wangwu   | 60    |
+----+----------+-------+
2 rows in set (0.00 sec)

MySQL [test]> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
|  4 | zhaoliu  | 68    |
+----+----------+-------+
2 rows in set (0.01 sec)

8. 在通过客户端连接数据库后写入的数据只有主服务器会记录，然后同步给从服务器，从而实现读写分离

● amoeba：

MySQL [test]> insert into aaa values(5,'sunqi',90);
Query OK, 1 row affected (0.01 sec)

● 主：

mysql> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
|  2 | lisi     | 89    |
|  5 | sunqi    | 90    |
+----+----------+-------+
3 rows in set (0.00 sec)

● 从1：

mysql> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
|  3 | wangwu   | 60    |
+----+----------+-------+
2 rows in set (0.00 sec)

● 从2：

mysql> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
|  4 | zhaoliu  | 68    |
+----+----------+-------+
2 rows in set (0.00 sec)

9. 在客户端amoeba上看不到新插入的数据，因为同步没有开启，只有主服务器上可以看到数据

MySQL [test]> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
|  3 | wangwu   | 60    |
+----+----------+-------+
2 rows in set (0.00 sec)

MySQL [test]> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
|  4 | zhaoliu  | 68    |
+----+----------+-------+
2 rows in set (0.01 sec)

10. 在开启同步后，主服务器上的数据会同步到各从服务器上中，但从服务器上的自己增加的数据不会同步，只有本地保存

● 从1：

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
|  2 | lisi     | 89    |
|  5 | sunqi    | 90    |
+----+----------+-------+
3 rows in set (0.00 sec)

● 从2：

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
|  2 | lisi     | 89    |
|  5 | sunqi    | 90    |
+----+----------+-------+
3 rows in set (0.00 sec)

● amoeba：

MySQL [test]> select * from aaa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88    |
|  2 | lisi     | 89    |
|  5 | sunqi    | 90    |
+----+----------+-------+
3 rows in set (0.00 sec)

在客户端上会看到主上同步过来的数据，以及自己本地增加的数据，但是看不到从上增加的数据