MySQL主从复制与读写分离数据库必须掌握的知识点

在实际的生产环境中，如果对mysql数据库的读和写都在一台数据库服务器中操作，无论是在安全性、高可用性，还是高并发等各个方面都是不能满足实际需求的。因此，一般通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。

一、什么是主从复制

将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

1.1:MySQL主从复制原理

1.11:MySQL的复制类型

• 基于语句的复制

• 基于行的复制

• 混合类型的复制

1.12:主从复制的作用

• 主数据库出现的问题，可以切换到从数据库

• 可以进行数据层层面的读写分离

• 可以在从数据库上进行日程备份

1.2：MySQL主从复制的工作过程

Binary log：主数据库的二进制日志
Relay log：从服务器的中继日志

第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。
第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。
第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

二：案列实施：MySQL主从复制搭建

• 配置环境

操作系统：五台Centos 7.6的Linux系统

• 数据库版本：MySQL 5.7

一台做主服务器（master）IP：20.0.0.41）

两台做从服务器（shuai01 IP:20.0.0.42）（shuai02 IP: 20.0.0.43)

一台客户端client（20.0.0.45）

2.1：配置前提

1.需要保证3306端口开启或关闭防火墙

2.三台服务器之间可以互相pingtong

#在主服务器ping从服务器shuai01 shuai02
[root@master ~]# ping 20.0.0.42
[root@master ~]# ping 20.0.0.43
#在shuai01ping主服务器跟另一个从服务器
[root@shuai01 ~]# ping 20.0.0.41
[root@shuai01 ~]# ping 20.0.0.43
#从服务器shuai02一样

2.2：实验目的：

• 通过配置，实现主从配置

• 首先配置主服务器（master）

[root@localhost ~]# iptables -F
[root@localhost ~]# setenforce 0

• 配置时间同步环境

• 在主服务器上安装NTP时间同步服务器

#安装NTP服务
[root@localhost ~]# yum install ntp -y

• 修改ntp.conf 设置主服务器为时间同步

[root@master ~]# vim /etc/ntp.conf
...省略内容
server 0.centos.pool.ntp.org iburst
server 1.centos.pool.ntp.org iburst
server 2.centos.pool.ntp.org iburst
server 3.centos.pool.ntp.org iburst
'//添加一下两段'
server 127.127.195.0 //本地是时钟源// 
fudge 127.127.195.0 stratum 8  //设置时间层级为8//
...省略内容
#重启服务
[root@master ~]# systemctl start ntpd
#进行时间同步
[root@master ~]# /usr/sbin/ntpdate 20.0.0.41

• 配置主服务器（master）

• 开启二进制日志选项，增加服务器id

vim /etc/my.cnf

#在[mysqld]选项中添加
server-id = 11            #标识唯一id（必填） 注意不能与从服务器id一样
log-bin=master-bin        #主服务器日志文件
log-slave-updates=true    #从服务器更新二进制日志
#重启服务
[root@master ~]# systemctl restart mysqld

如果你按照上面的正确安装mysql了，这里是可以正常重启的。如果启动不正常出现如下报错：

The server quit without updating PID file…

你需要使用如下命令查看是否还存在mysqld进程

ps -ef|grep mysqld

如果有，可以使用命令：kill -9 mysqld的进程号 结束它，然后重新启动mysqld

• 登录MySQL数据库允许从库获得主服务器日志

[root@master ~]# mysql -uroot -p

• 进入后做如下配置

#给从库放权限
replication：复制 slave：从服务器 *.*:所有数据库所有表 'mysql':身份
mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by'123456';
#刷新权限
mysql> flush privileges;

• 重启MySQL服务，登录MySQL，查看主库信息

[root@master ~]# systemctl restart mysqld
[root@master ~]# mysql  -uroot -p
mysql> show master status;

• 显示内容如下

+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000004 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#位置是154 从服务器要定位这个位置

注：如果执行这个步骤始终为Empty set（0.00 sec），那说明前面的my.cf没有配对，请回去重新检查步骤。

2.3：配置shuai01从服务器

#关闭防火墙跟防护功能
[root@shuai01 ~]# iptables -F
[root@shuai01 ~]# setenforce 0

• 修改从库的数据配置文件

[root@shuai01 ~]# vim /etc/my.cnf
[mysqld]添加以下内容
##
server-id = 22                          #不能跟主服务器一样
relay-log=relay-log-bin                 #开启中继日志 从主服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index   #定义relay-log的位置和名称

• 重启服务MySQL服务，登录MySQL

[root@shuai01 ~]# systemctl restart mysqld
m[root@shuai01 ~]# mysql -uroot -p

• 并做如下修改

mysql> change master to master_host='20.0.0.41',master_user='myslave',master_password='123456',master_log_file='master-bin.000004',master_log_pos=154;

#开启从服务器功能
mysql> start slave;

注：上面的mastr_log_file是在配置master的时候File字段

，master_log_pos是在配置master的Position字段.一定要一一对应。

• 查看从库显示信息：

mysql> show slave status \G;

Slave_IO_Runind线程：负责同步master的二进制日志文件把他记载到工作日志中

Slave_SQL_Running线程：负责数据库直接读取中继日志更新里面数据

2.4：配置从服务器shuai02

[root@shuai02 ~]# iptables -F
[root@shuai02 ~]# setenforce 0

#修改shuai02配置文件
[root@shuai02 ~]# vim /etc/my.cnf

#添加以下内容 跟shuai01一样  区别是server-id
server-id = 23
relay-log=relay-log-bin
relay-log-index=slave-relay-bin,index
#重启服务

• 登录数据库

[root@shuai02 ~]# mysql -uroot -p

#设置权限
mysql> change master to master_host='20.0.0.41',master_user='myslave',master_password='123456',master_log_file='master-bin.000004',master_log_pos=154;

#重启功能
mysql> start slave;

#查看从库显示信息
mysql> show slave status\G;
#已经开启同步功能了
  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

• 测试是否同步

#在主服务器创建数据库跟数据

#创建niu数据据
mysql> create database niu;

#使用niu数据库
mysql> use niu;

#创建tt表
mysql> create table tt (id int(3),name char(10));

#插入表结构数据
mysql> insert into tt (id,name) values (1,'zhangsan'),(3,'lisi');

#查询表信息
mysql> select * from tt;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    3 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

• 从服务器shuai01查看数据库

#显示所有数据库  可以看到有niu库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| niu                |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

#进入数据库niu
mysql> use niu;

#可以看到数据都同步成功
mysql> select * from tt;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    3 | lisi     |
+------+----------+
rows in set (0.00 sec)

• 从服务器shuai02查看

mysql> select * from tt;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    3 | lisi     |
+------+----------+
2 rows in set (0.00 sec)
#数据库主从复制成功

三：MySQL读写分离

• 实现MySQL读写分离的前提是我们已经将MySQL主从复制配置完毕

• Mysql读写分离基本原理是让master数据库处理操作，slave数据库处理读操作。master将写操作的变更同步到各个slave节点。

3.1：MySQL读写分离原理

• 读写分离就是只在主服务器上写，只在从服务器上读
• 主数据库处理事务性査询，而从数据库处理 select査询
• 数据库复制被用来把事务性査询导致的变更同步到集群中的从数据库

3.11：MySQL读写能提高系统性能的原因：

• 物理服务器增加，机器处理能力提升，拿硬件换性能。

• master直接写的是并发的，slave通过主库发来的binlog恢复是异步

• salve可以单独设置一些参数来提升其读的性能

• 增加冗余，提高可用性。

四：MySQL读写分离搭建

4.1：配置amobeba服务器（20.0.0.44)

• 读写分离安装java环境（amoeba软件基于java平台运行）

• 关闭防火墙

[root@amoeba opt]# iptables -F

[root@amoeba opt]# setenforce ::0

4.11：读写分离安装java环境

[root@amoeba opt]# rz -E
rz waiting to receive.
[root@amoeba opt]# ls
amoeba-mysql-binary-2.2.0.tar.gz  mysql-5.7.20               rh
jdk-6u14-linux-x64.bin            mysql-boost-5.7.20.tar.gz
#复制到/usr/local
[root@amoeba opt]# cp jdk-6u14-linux-x64.bin /usr/local/
[root@amoeba opt]# cd /usr/local/
[root@amoeba local]# ls
bin  games    jdk-6u14-linux-x64.bin  lib64    mysql  share
etc  include  lib 
#设置权限
[root@amoeba local]# chmod +x jdk-6u14-linux-x64.bin 
[root@amoeba local]# ./jdk-6u14-linux-x64.bin 
一直回车就行  直到出现
Do you agree to the above license terms? [yes or no]
#输入yes

#重命名为jdk1.6
[root@amoeba local]# mv jdk1.6.0_14/ jdk1.6

4.12：设置环境变量

[root@amoeba local]# vim /etc/profile
#末行添加内容
export PATH=$PATH:$AMOEBA_HOME/bin
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

#使环境变量生效
[root@amoeba local]# source /etc/profile
创建文件目录   
[root@amoeba local]# mkdir /usr/local/amoeba
#把压缩包解压到amoeba目录
[root@amoeba local]# cd /opt
[root@amoeba opt]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
#设置权限
[root@amoeba local]# chmod -R 755 /usr/local/amoeba/
#显示说明安装成功
[root@amoeba local]# /usr/local/amoeba/bin/amoeba
amoeba start|stop

4.13：配置Amoeba读写分离，两个slave读负载均衡

• 在三台mysql上添加权限给amoeba访问

mysql> grant all on *.* to test@'20.0.0.%' identified by '123.com';

#刷新数据库
mysql> flush privileges;

#通过20.0.0.0段的test用户拥有所有权限对于所有的库和表都可以使用123.com来访问'
#三台mysql服务器都要设置'

4.14：配置amoeba

1.定义访问的用户

2.定义具体读写分离对象

30行 客户端访问到amoeba

[root@amoeba mnt]# cd /usr/local/amoeba/conf/
[root@amoeba conf]# vim amoeba.xml 	    #编辑amoeba主配置文件
#此段设置的是客户端通过amoeba用户和123456密码访问amoeba服务器
<property name="user">amoeba</property>	#来到第30行开始修改用户名'
    
<property name="password">123456</property>	 #使用123456密码访问amoeba服务器'

#移动到117行，开启读写功能池设定'
<property name="defaultPool">master</property>	#115行master名称
<!-- -->	#117行取消注释'
<property name="writePool">master</property>  #118行修改找到master去写入
<property name="readPool">slaves</property>	  #119行修改找到salves去写 s：不止一台
#120行删除-->注释符号
    
[root@amoeba conf]# vim dbServers.xml 	#编辑数据库配置文件#
<property name="schema">mysql</property>	#23行test修改为mysql#

'//设置amoeba访问mysql数据库的用户和密码'
<property name="user">test</property>	        #访问三个数据库的用户
    
<!--  mysql password-->	'//28行-30行取消注释'
<property name="password">123.com</property>	#访问三个数据库的密码

<dbServer name="master"  parent="abstractServer">	#45行主mysql服务器名称修改为master
 <property name="ipAddress">20.0.0.41</property>	#48行//修改主服务器IP'

<dbServer name="slave1"  parent="abstractServer">	#52行修改从服务器名称'
<property name="ipAddress">20.0.0.42</property>	355行修改从服务器IP
    '//第一个从服务器段后插入第二个从服务器配置'
<dbServer name="slave2"  parent="abstractServer">
        <factoryConfig>
                <!-- mysql ip -->
	    <propertyname="ipAddress">20.0.0.44</property>
      </factoryConfig>
</dbServer>
'//修改数据库从服务器池'
<dbServer name="slaves" virtual="true">	            #66行修改服务器吃名称为slaves'
<property name="poolNames">slave1,slave2</property>	#72行添加两个从服务器名称slave1,slave2'

4.13：启动Ambeba软件

• 开启服务，后台运行

[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start&
[5] 112123
[root@amoeba conf]# log4j:WARN log4j config load completed from file:/usr/local/amoeba4j.xml
2020-08-27 10:09:36,227 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current v1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_lis
2020-08-27 10:09:36,432 INFO  net.ServerableConnectionManager - Amoeba for Mysql liste.0.0.0/0.0.0.0:8066.
2020-08-27 10:09:36,434 INFO  net.ServerableConnectionManager - Amoeba Monitor Server  on /127.0.0.1:49429.

4.2：测试客户client

20.0.0.45

#安装mysql用来验证
[root@client ~]# yum -y install mysqld
[root@client ~]# iptables -F
[root@client ~]# setenforce 0
#连接amoeba服务器
[root@client ~]# mysql -u amoeba -p123456 -h 20.0.0.44 -P8066
#显示数据库

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| niu                |
| performance_schema |
| sys                |
+--------------------+

4.21：测试读写分离，关闭主从复制

#在client创建shuai数据库
MySQL [(none)]> create database shuai;
#进入主从服务器查看，发现主服务器跟从服务器都已经自动同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| niu                |
| performance_schema |
| shuai              |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

#三个都已经同步 但是你不知道三个哪个是写那个是读？

• 测试读写分离

#主服务器创建数据表
mysql> use shuai;
#创建数据表
mysql> create table qie (id int(10),name varchar(20),address varchar(30));
#从服务器也同步到qie表
mysql> show tables;
+-----------------+
| Tables_in_shuai |
+-----------------+
| qie             |
+-----------------+
1 row in set (0.00 sec)

#断掉slave功能（两个从服务器都关闭）
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

#检查状态已经关闭同步
mysql> show slave status\G
......省略
    Slave_IO_Running: No
    Slave_SQL_Running: No
 .....省略

• 在client客户机配置数据，验证读写分离

#插入数据记录
MySQL [shuai]> insert into qie values(1,'lisi','heihei');

#在master进行查看
mysql> use shuai;
Database changed
mysql> select * from qie;
+------+------+---------+
| id   | name | address |
+------+------+---------+
|    1 | lisi | heihei  |
+------+------+---------+
1 row in set (0.00 sec)

#从服务器查看   两个从服务器都是显示为空
mysql> select * from qie;
Empty set (0.00 sec)

#shuai01务器插入数据2
mysql> insert into qie values(2,'wangwu','TT');
Query OK, 1 row affected (0.00 sec)

mysql> select * from qie;
+------+--------+---------+
| id   | name   | address |
+------+--------+---------+
|    2 | wangwu | TT      |
+------+--------+---------+
1 row in set (0.00 sec)

#从服务器shuai01插入数据3
mysql> insert into qie values(3'wangwu','TT');

mysql> select * from qie;
+------+--------+---------+
| id   | name   | address |
+------+--------+---------+
|    3 | wangwu | TT      |
+------+--------+---------+
1 row in set (0.00 sec)

• 客户端去查看

MySQL [shuai]> select * from qie;
+------+--------+---------+
| id   | name   | address |
+------+--------+---------+
|    3 | wangwu | TT      |
+------+--------+---------+

MySQL [shuai]> select * from qie;
+------+--------+---------+
| id   | name   | address |
+------+--------+---------+
|    2 | wangwu | TT      |
+------+--------+---------+
发现每一次查询都会轮询在slave1和slave2上查询数据，如果开启主从复制，则数据都会相同
读写分离实验成功