MySQL主从复制与读写分离

一、概述

• 在企业应用中，成熟的业务通常数据量都比较大
• 单台MySQL在安全性、高可用性和高并发方面都无法满足实际的需求
• 配置多台主从数据库服务器以实现读写分离

二、MySQL主从复制原理

• MySQL的复制类型
  • 基于语句的复制
  • 基于行的复制
  • 混合类型的复制
• MySQL主从复制的工作过程
  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读写分离原理

• 只在主服务器上写，只在从服务器上读
• 主数据库处理事务性查询，从数据库处理SELECT查询
• 数据库复制用于将事务性查询的变更同步到集群中的从数据库
• 读写分离方案
  • 基于程序代码内部实现
  • 基于中间代理层实现
    • MySQL-Proxy
    • Amoeba

四、案例部署

4.1 案例环境

amoeba服务器：192.168.10.30
三台MySQL服务器，一主两从
master：192.168.10.40
slave1:192.168.10.50
slave2:192.168.10.60
client：192.168.10.20

4.2时间同步

master服务器：

[root@master ~]# ntpdate ntp.aliyun.com
29 Dec 14:10:29 ntpdate[25523]: step time server 203.107.6.88 offset 0.779129 sec
[root@master ~]# crontab -e
*/30	*	*	*	* /usr/sbin/ntpdate ntp.aliyun.com
[root@master ~]# yum -y install ntp
[root@master ~]# systemctl restart ntpd

slave1和slave2同步：

root@slave1 ~]# ntpdate 192.168.10.40
29 Dec 14:18:48 ntpdate[25578]: step time server 192.168.10.40 offset 0.779788 sec
[root@slave1 ~]# crontab -e
*/30    *       *       *       * /usr/sbin/ntpdate 192.168.10.40

4.3主从同步

4.3.1 master服务器

1）修改主配置文件/etc/my.cnf，在mysqld下发添加

[root@master ~]# vi /etc/my.cnf
server-id=1  	##mysql服务器id，每个服务器不能相同
log_bin=master-bin 	##主服务器日志文件
log_slave_updates=true 	##允许中继日志读取主服务器的二进制日志
[root@master ~]# systemctl restart mysqld

2）为slave授权

[root@master ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 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)

4.3.2 从服务器，slave1和2保持同步

1）修改主配置文件/etc/my.cnf，在mysqld下发添加

[root@slave1 ~]# vi /etc/my.cnf
server-id = 2		##slave1的server-id为2，slave2的server-id修改为3
relay_log=relay-log-bin		##从主服务器上同步日志文件记录到本地中继日志
relay_log_index=slave-relay-bin.index		##定义中继日志的索引

2）启动mysql，与主库保持同步，添加主库的账号，二进制日志文件，开始同步的位置

[root@slave1 ~]# systemctl restart mysqld
[root@slave1 ~]# mysql -uroot -p123456
mysql> change master to master_host='192.168.10.40',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;   ## 启动从服务器
Query OK, 0 rows affected (0.00 sec)


mysql> show slave status \G    ## IO线程和SQL线程需要都处于yes状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.40
                  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

4.4 读写分离

4.4.1 amoeba服务端安装jdk

[root@amoeba ~]tar zxvf jdk-8u91-linux-x64.tar.gz
[root@amoeba ~]# mv jdk1.8.0_91/ /usr/local/java
[root@amoeba ~]# cd /etc/profile.d/
[root@amoeba profile.d]# vim java.sh   ## 指定java的环境变量
export JAVA_HOME=/usr/local/java 
export PATH=$PATH:$JAVA_HOME/bin 
[root@amoeba profile.d]# source java.sh   ##将脚本导入到环境变量中
[root@amoeba profile.d]# java -version   ## 查看java的版本信息
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)

4.4.2 部署amoeba代理

[root@amoeba ~]# 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:MaxPe    rmSize=96m"  
## 32行添加注释，新增33行
 33 JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"

## 编写amoeba管理脚本
[root@amoeba ~]# vi /etc/init.d/amoeba
#!/bin/bash
#chkconfig: 35 62 62

export JAVA_HOME=/usr/local/java
export PATH=$JAVA_HOME/bin:$JAVE_HOME/jre/bin:$PATH
NAME=Amoeba
AMOEBA_BIN=/usr/local/amoeba/bin/launcher
SHUTDOWN=_BIN=/usr/local/amoeba/bin/shutdown
PIDFILE=/usr/local/amoeba/Amoeba-MySQL.pid
SCRIPTNAME=/etc/init.d/amoeba

case "$1" in
start)
echo -n "Starting $NAME... "
$AMOEBA_BIN
echo " done"
;;
stop)
echo -n "Stoping $NAME..."
$SHUTDOWM_BIN
echo " done"
;;
restart)
$SHUTDOWN_BIN
sleep 1
$AMOEBA_BIN
;;
*)
echo "Usage: $SCRIPTNAME {start|stop|restart}"
exit 1
;;
esac

[root@amoeba ~]# chmod +x /etc/init.d/amoeba 
[root@amoeba ~]# chkconfig --add amoeba
[root@amoeba ~]# systemctl start amoeba

4.4.3 在三台MySQL上给amoeba授权

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

4.4.4 在amoeba服务端修改主配置文件

[root@amoeba ~]# vi /usr/local/amoeba/conf/amoeba.xml 
   28：  <property name="user">amoeba</property>

   29：  <property name="password">123456</property>
   83：  <property name="defaultPool">master</property>
          ## 去掉这里的注释
         <property name="writePool">master</property>
         <property name="readPool">slaves</property>

         <property name="needParse">true</property>

4.4.5 修改dbServers.xml 文件

[root@amoeba ~]# vi /usr/local/amoeba/conf/dbServers.xml
## 设置数据库的用户和密码
22:		<property name="schema">mysql</property>

		<!-- mysql user -->
26:		property name="user">test</property>

28:		<property name="password">123456</property>

## 配置三台MySQL服务器
     43         <dbServer name="master"  parent="abstractServer">
     44                 <factoryConfig>
     45                         <!-- mysql ip -->
     46                         <property name="ipAddress">192.168.10.40</property>
     47                 </factoryConfig>
     48         </dbServer>
     49 
     50         <dbServer name="slave1"  parent="abstractServer">
     51                 <factoryConfig>
     52                         <!-- mysql ip -->
     53                         <property name="ipAddress">192.168.10.50</property>
     54                 </factoryConfig>
     55         </dbServer>
     56         <dbServer name="slave2"  parent="abstractServer">
     57                 <factoryConfig>
     58                         <!-- mysql ip -->
     59                         <property name="ipAddress">192.168.10.60</property>
     60                 </factoryConfig>
     61         </dbServer>
	
	 64         <dbServer name="slaves" virtual="true">

	 70         <property name="poolNames">slave1,slave2</property>

4.4.6 启动amoeba服务

两种启动方式，可以使用我们编写的管理脚本启动，也可以通过服务自带的launcher脚本启动服务

[root@amoeba ~]# systemctl start amoeba

[root@amoeba ~]# /usr/local/amoeba/bin/launcher

注意在启动的时候，如出现

2020-12-29 21:18:48,135 ERROR net.ServerableConnectionManager - Failure listening to socket on port '8066'.

表示8066端口已经被占用，通过netstat我们可以查到占用端口的进程号，再通过kill命令杀死进程即可正常启动服务

4.5 验证

4.5.1 客户端配置

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

[root@server2 ~]# yum install -y mariadb*
[root@server2 ~]# mysql -uamoeba -p123456 -h 192.168.10.30 -P 8066

4.5.2 验证同步

1）在client上面新建库test，查看三台数据库是否生成库和表

主库：

MySQL [(none)]> create database test;
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> use test;
Database changed
MySQL [test]> create table t1(id int(2) PRIMARY KEY, name varchar(48),score int(16));
Query OK, 0 rows affected (0.01 sec)
MySQL [test]> insert into t1(id,name,score)value(1,'xiaobai',100);
Query OK, 1 row affected (0.01 sec)

主库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+----+---------+-------+
| id | name    | score |
+----+---------+-------+
|  1 | xiaobai |   100 |
+----+---------+-------+
1 row in set (0.00 sec)

从库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from t1;
+----+---------+-------+
| id | name    | score |
+----+---------+-------+
|  1 | xiaobai |   100 |
+----+---------+-------+
1 row in set (0.00 sec)

成功开启同步

2）两台从服务器停止服务

mysql> stop slave;
## 在主服务器上插入的内容不会被同步-通过amoeba操作的
mysql> insert into t1 values(10,'ll',70);
Query OK, 1 row affected (0.00 sec)



## 在从服务器slave1上新建内容————直接在从上操作
mysql> insert into t1 values(3,'xiaotian',60);
Query OK, 1 row affected (0.00 sec)
## 在从服务器2上新建内容
mysql> insert into t1 values(4,'xiaoli',80);
Query OK, 1 row affected (0.01 sec)


## 客户端amoeba上测试————第一次会向从服务器slave1读取数据，第二次会向从服务器slave2读取，轮询模式
MySQL [test]> select * from t1;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaobai  |   100 |
|  3 | xiaotian |    60 |
+----+----------+-------+
2 rows in set (0.01 sec)

MySQL [test]> select * from t1;
+----+---------+-------+
| id | name    | score |
+----+---------+-------+
|  1 | xiaobai |   100 |
|  4 | xiaoli  |    80 |
+----+---------+-------+
2 rows in set (0.00 sec)

## 通过客户端连接数据库后写入的数据只有主master会有记录，然后同步给从，从服务器不会记录，从而实现了读写分离。
MySQL [test]> insert into t1(id,name,score)value(2,'xiaoming',10);
Query OK, 1 row affected (0.01 sec)


## 在客户端amoeba上看不到新插入的数据是应为没有开始主从，只有主master服务器上可以看到数据
mysql> select * from t1;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaobai  |   100 |
|  2 | xiaoming |    10 |
| 10 | ll       |    70 |
+----+----------+-------+
3 rows in set (0.00 sec)


## 在客户端开启同步后，主服务器上数据会同步到各个从服务器中，但是从服务器上自己增加的数据不会同步，只有本地会保存
## 客户端查看会看到主上面同步过来的数据，已经自己本地新增的数据，但是看不到其他从服务器上新增的数据