MySQL 案例：主从复制和读写分离

前言

为什么使用主从复制、读写分离

主从复制、读写分离一般是一起使用的。目的很简单，就是为了提高数据库的并发性能。你想，假设是单机，读写都在一台MySQL上面完成，性能肯定不高。如果有三台MySQL，一台mater只负责写操作，两台salve只负责读操作，性能不就能大大提高了吗？

所以主从复制、读写分离就是为了数据库能支持更大的并发。

随着业务量的扩展、如果是单机部署的MySQL，会导致I/O频率过高。采用主从复制、读写分离可以提高数据库的可用性。

一、MySQL主从复制原理

1、MySQL的复制类型

1、基于语句的复制：在主服务器上执行的sql语句，在从服务器上会执行同样的语句。Mysql默认采用基于语句的复制，效率比较高，但是有时不能实现精准复制。
2、基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
3、混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的复制不能精准复制时，就会采用基于行的复制。

2、MySQL主从复制的工作过程

1、在每个事物更新数据完成之前，master在二进制日志记录这些改变，写入二进制日志完成后，master通知存储引擎提交事物。

2、Slave将master的binary log复制到其中的中继日志。首先从mysql服务器开始一个工作线程I/O线程，I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master。他会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

3、Sql从线程处理该过程的最后一步。Sql线程从中继日志中读取事件，并重放其中的事件而更新slave的数据，使其与master的数据一致。

二、MySQL读写分离原理

1、只在主服务器上写，只在从服务器上读;
2、主数据库处理事务性查询，从数据库处理select查询;
3、数据库复制用于将事务性查询的变更同步到集群中的从数据库。

简单的来说，读写分离就是只在mysql主服务器上写，只在mysql从服务器上读。基本原理是让主数据库处理事务性查询，而从数据库处理select查询。数据库复制被用来把事务性查询导致的变更同步到集群中的数据库。

读写分离原理示意图

4、复制的基本过程如下：

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. SlaveSql进程检测到relay-log中新增了内容后，会马上解析relay-log的内容称为在Master端真实执行时候的那些可执行的内容，并在自身执行。

三、配置MySQL读写分离环境

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

三台装有MySQL5.7版本的服务器，角色分配如下：
master：192.168.40.11
slave1：192.168.40.12
slave2：192.168.40.13
一台Amoeba代理服务器
Amoeba：192.168.40.14

四、MySQL读写分离步骤

先把服务器的防火墙和核心防护全部关闭。
systemctl stop firewalld.service
setenforce 0

4.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 ~]# vi /etc/ntp.conf
 17 restrict 192.168.40.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 
 添加下面两行
server 127.127.1.0
fudge 127.127.1.0 stratum 8 	#设置服务器层级是8级，顶级是0

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

• 另外其它服务器可以通过命令ntpdate 192.168.40.11来同步时间日期
• 因为需要定时同步，所以最好使用任务计划来同步时间

crontab -e
*/30 * * * * /usr/sbin/ntpdate ntp.aliyun.com	#在时间同步服务器端上设置
*/30 * * * * /usr/sbin/ntpdate 192.168.40.11    #在时间同步客户端上设置

4.2、主从同步

主服务器配置

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

[root@master ~]# vi /etc/my.cnf
[mysqld]
...省略
server-id=1 		#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]# ls -lh

2. 登陆主服务器，给从服务器授权

[root@master ~]# mysql -uroot -p【密码】
mysql> grant replication slave on *.* to 'myslave'@'192.168.40.%' identified by '123456';	#为所有从服务器授权所有数据库
mysql> flush privileges;
mysql> show grants for myslave@'192.168.40.%';
mysql> show master status; #记下position的值

注意：到这一步后不要再在数据库中进行任何操作，否则读取数据的位置会变。

从服务器配置

先设置任务计划，实现时间同步

crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.40.11

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

[root@slave1 ~]# vi /etc/my.cnf
[mysqld]
...省略
server-id=2 #MySQL服务器的id，需要配置不同数字
relay_log=relay-log-bin  #从主服务器上同步日志文件记录到本地中继日志
relay_log_index=slave-relay-bin.index	#定义中继日志的索引

[root@slave1 ~]# systemctl restart mysqld

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

[root@slave1 ~]# mysql -uroot -p【密码】
mysql> change master to master_host='192.168.40.11',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;
mysql> start slave;	#启动从服务器
mysql> show slave status\G;	#查看从服务器状态
...省略
 Slave_IO_Running: Yes 
 Slave_SQL_Running: Yes	#这两项需要为YES

在主服务器上验证同步

[root@master ~]# mysql -uroot -p【密码】

主服务器创建好数据库或数据表，在从服务器上查看，从服务器上也会同步。

4.3、amoeba读写分离

设置任务计划

crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.40.11

1. 部署java环境

[root@amoeba ~]# tar -zvxf jdk-8u91-linux-x64.tar.gz
[root@amoeba ~]# cp -rv jdk1.8.0_91/ /usr/local/java	#拷贝

[root@amoeba ~]# vi /etc/profile
尾部添加
export JAVA_HOME=/usr/local/java	#设置java根目录
export JRE_HOME=/usr/locla/java/jre	
export PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin	#在PATH环境变量中添加JAVA根目录下的bin子目录
export CLASSPATH=./:$JAVA_HOME/lib:$JRE_HOME/lib

[root@amoeba ~]# source /etc/profile	#将脚本导入到环境变量中，使其生效
[root@amoeba ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/java/bin:/usr/locla/java/jre/bin

[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 ~]# unzip amoeba-mysql-3.0.5-RC-distribution.zip 
[root@amoeba ~]# mv amoeba-mysql-3.0.5-RC/ /usr/local/amoeba
[root@amoeba ~]# chmod -R 755 /usr/local/amoeba/
[root@amoeba ~]# vi /usr/local/amoeba/jvm.properties		#缓存优化
32行下新增：
32 #JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPe    rmSize=96m"
33 JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"

3. 在三台MySQL上给amoeba赋予权限

mysql> grant all privileges on *.* to 'test'@'192.168.40.%' identified by 'abc123';
mysql> flush privileges;

4. 修改amoeba主配置文件

[root@amoeba ~]# vi /usr/local/amoeba/conf/amoeba.xml
#28，30行修改如下 修改客户端连接amoeba前端服务器时使用的用户名和密码
	:28        <property name="user">amoeba</property>
	:29
	:30        <property name="password">abc123</property>

:83行修改如下（去掉注释）
	<property name="defaultPool">master</property>
	<!--	# 为注释，需要删除行
	<property name="writePool">master</property>
	<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          <!-- mysql user -->
	:25          <property name="user">test</property>
	:27          <property name="password">abc123</property>

配置三个服务器主机名和地址
	:43         <dbServer name="master"  parent="abstractServer">
	:44                <factoryConfig>
	:45                      <!-- mysql ip -->
	:46                      <property name="ipAddress">192.168.40.11</property>
	:47                </factoryConfig>
	:48       </dbServer>
	:49         <dbServer name="slave1"  parent="abstractServer">
	:50                <factoryConfig>
	:51                      <!-- mysql ip -->
	:52                      <property name="ipAddress">192.168.40.12</property>
	:53                </factoryConfig>
	:54        </dbServer>
	:55        <dbServer name="slave2"  parent="abstractServer">
	:56                <factoryConfig>
	:57                      <!-- mysql ip -->
	:58                      <property name="ipAddress">192.168.40.13</property>
	:59                </factoryConfig>
	:60        </dbServer>


指定名为slaves的poolNames中pools的主机名
	:62        <dbServer name="slaves" virtual="true">
	:68        <property name="poolNames">slave1,slave2</property>

6. 启动amoeba服务器进行测试

[root@amoeba ~]# /usr/local/amoeba/bin/launcher
[root@ameoba ~]# netstat -anpt | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      14503/java

[root@amoeba ~]# yum -y install mysql	#安装测试用客户机
[root@ameoba ~]# mysql -uamoeba -pabc123 -h 192.168.40.14 -P 8066		#amoeba地址

4.4、案例验证

1）在master新建库和表

mysql> create database ttt;
Query OK, 1 row affected (0.02 sec)

mysql> use ttt;
Database changed
mysql> create table aa(id int(2) not null primary key, name varchar(48), score char(
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------+
| Tables_in_ttt |
+---------------+
| aa            |
+---------------+
1 row in set (0.00 sec)

mysql> insert into aa values(1,'zhangsan',99);
Query OK, 1 row affected (0.01 sec)

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

mysql> use ttt;
Database changed
mysql> show tables;
+---------------+
| Tables_in_ttt |
+---------------+
| aa            |
+---------------+
1 row in set (0.00 sec)

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

3）在两台上停止服务

mysql> stop slave;

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

mysql> insert into aa values(2,'lisi',88);

从：

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

因为从服务器上已经关闭了slave,所以无法执行复制。

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

mysql> insert into aa values(3,'wangxin',70);
Query OK, 1 row affected (0.01 sec)

mysql> select * from aa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 99    |
|  3 | wangxin  | 70    |
+----+----------+-------+
2 rows in set (0.00 sec)

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

mysql> insert into aa values(4,'xy',66);
Query OK, 1 row affected (0.01 sec)

mysql> select * from aa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 99    |
|  4 | xy       | 66    |
+----+----------+-------+
2 rows in set (0.00 sec)

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

MySQL [ttt]> select * from aa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 99    |
|  3 | wangxin  | 70    |
+----+----------+-------+
2 rows in set (0.00 sec)

MySQL [ttt]> select * from aa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 99    |
|  4 | xy       | 66    |
+----+----------+-------+
2 rows in set (0.00 sec)

可以看出同样的命令得到不同的结果，类似于轮询的读取策略。

8）在amoeba服务器上面添加的表，是写入到主服务器上的，不会写到从服务器上面；

MySQL [ttt]> insert into aa values(5,'bbb',90);
Query OK, 1 row affected (0.04 sec)

主服务器：
mysql> select * from aa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 99    |
|  2 | lisi     | 88    |
|  5 | bbb      | 90    |
+----+----------+-------+
3 rows in set (0.00 sec)

从服务器1：
mysql> select * from aa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 99    |
|  3 | wangxin  | 70    |
+----+----------+-------+
2 rows in set (0.00 sec)

从服务器2：
mysql> select * from aa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 99    |
|  4 | xy       | 66    |
+----+----------+-------+
2 rows in set (0.00 sec)

9）在客户端amoeba上看不到新插入的数据，因为同步没有开启，只有主服务器上可以看到数据，但是因为主从复制的原因，所以如果从服务器上面开启了slave，则可以在上面读取到数据，从而实现读写分离

MySQL [ttt]> select * from aa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 99    |
|  3 | wangxin  | 70    |
+----+----------+-------+
2 rows in set (0.00 sec)

MySQL [ttt]> select * from aa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 99    |
|  4 | xy       | 66    |
+----+----------+-------+
2 rows in set (0.00 sec)

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

从服务器1：
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from aa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 99    |
|  2 | lisi     | 88    |
|  3 | wangxin  | 70    |
|  5 | bbb      | 90    |
+----+----------+-------+
4 rows in set (0.00 sec)

从服务器2：
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from aa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 99    |
|  2 | lisi     | 88    |
|  4 | xy       | 66    |
|  5 | bbb      | 90    |
+----+----------+-------+
4 rows in set (0.00 sec)

客户端：
MySQL [ttt]> select * from aa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 99    |
|  2 | lisi     | 88    |
|  3 | wangxin  | 70    |
|  5 | bbb      | 90    |
+----+----------+-------+
4 rows in set (0.00 sec)

MySQL [ttt]> select * from aa;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 99    |
|  2 | lisi     | 88    |
|  4 | xy       | 66    |
|  5 | bbb      | 90    |
+----+----------+-------+
4 rows in set (0.00 sec)