MySQL主从复制与读写分离

本章主要介绍：

• 熟悉MySQL主从复制原理
• 熟悉MySQL读写分离原理
• 学会配置MySQL主从复制
• 学会配置MySQL读写分离

简介

? ? ? ? 在实际生产环境中，如果MySQL 数据库的读写都在一台数据库服务器中操作，无论是在安全性，高可用性还是高并发等各个方面都是不能满足实际需求的，本章首先讲解MySQL主从复制和MySQL读写分离的原理，然后介绍如何配置Mysql 主从复制和读写分离

1.1 案例概述

? ? ? ? 在实际的生产环境中，如果对数据库的读和写都在一台数据库服务器中操作，无论时在安全性，高可用性还是高并发等各个方面都是完全不能满足实际需求的，因此，一般来说都是通过主从复制（Master-Slave）的方式来同步数据库，再通过读写分离来提升数据库的并发负载能力这样的方案来进行部署于实施的。

? ? ? ? 如图所示，一台MySQL主服务器带两台 MySQL从服务器做了数据复制，前端应用镜像数据库写操作时，对主服务器进行操作，在进行数据库读操作时，对两台服务器进行操作，这样大量减轻了主服务器的压力

1.1.2 案例前置知识点

1.MySQL 主从复制原理

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

1）MySQL支持的复制类型

（1）基于语句的复制，在主服务器上执行的SQL语句，在从服务器上执行同样的语句，MySQL默认采用基于语句的复制，效率比较高

（2）基于行的复制，把改变的内容复制过去，而不是把命令在服务器上执行一遍

（3）混合类型的复制，默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制

2）复制的工作过程

MySQL 复制的过程如图

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

（2）Slave 将 Master 的 Binary log 复制到其中继日志。首先，Slave 开始一个工作线程--I/O 线程，I/O 线程在Master 上打开一个普通的连接，然后开始Bin log dump，Bin log dump process 从Master 的二进制日志中读取事件，如果已经跟上Master，它会睡眠并等待Master 产生新的事件，I/O 线程将这些事件写入中继日志

（3）SQL slave thread（SQL 从线程）处理过程的最后一步。SQL 线程从中继日志读取事件，并重放其中的事件而更新Slave 的数据，使其于 Master 中的数据一致，只要该线程于I/O 线程保持一致，中继日志通常会位于OS 的缓存中，所以中继日志的开销很小。

? ? ? ? 复制过程有一个很重要的限制，即复制在Slave 上市串行化的，也就是说 Master 上的并行更新操作不能再Slave上并行操作

2.MySQL 读写分离原理

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

目前较为常见的MySQL读写分离分为两种

1）基于程序代码内部实现

? ? ? ? 在代码中根据 select ，insert 进行路由分类，这类方法也是目前生产环境应用最广泛的，优点是性能较好，因为在程序代码中实现，不需要额外的设备作为硬件开支，缺点是需要开发人员来实现，运维人员无从下手

2）基于中间代理层实现

? ? ? ? 代理一般位于客户端和服务器之间，代理服务器接到客户端的请求后通过判断后转发到后端数据库，有两个代表性程序

（1）MySQL-Proxy，MySQL-Proxy 开源项目，通过其自带的 lua 脚本进行SQL判断，虽然是MySQL官方产品，但是MySQL 官方并不建议将MySQL-proxy 用到生产环境

（2）Amoeba（变形虫）该程序用Java语言进行开发，阿里巴巴将其用于生产环境，他不支持事务和存储过程

? ? ? ? 经上述简单的比较，通过程序代码实现MySQL 读写分离自然是一个不错的选择，但是并不是所有的应用都都适合在程序代码中实现读写分离，例如一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大，所以，像这种大型复杂的应用一般会考虑使用代理层来实现，本章后续案例通过 Amoeba 实现

1.2?案例环境

本案例环境使用五台服务器模拟搭建

1.2.1 搭建MySQL 主从复制

1）建立事件同步环境，在节点上搭建时间同步服务器

1.安装NTP

[root@Master ~]# yum -y install ntp

2.配置 NTP

[root@Master ~]# vim /etc/ntp.conf    //添加如下两行
server 127.127.1.0
fudge 127.127.1.0 startum 8

3. 重启服务并设置为开机启动

[root@Master ~]# service ntpd restart
[root@Master ~]# systemctl restart ntpd
[root@Master ~]# systemctl enable ntpd
Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service.
[root@Master ~]#

2）在节点服务器上进行时间同步

[root@Slave01 ~]# yum -y install ntpdate
[root@Slave01 ~]# ntpdate 192.168.182.101

3）在每台服务器上关闭 firewalld 或指定端口，服务进行开放

[root@Slave01 ~]# systemctl stop firewalld
[root@Slave01 ~]# systemctl disable firewalld

4）安装MySQL数据库，在Master，Slave01，Slave002上安装

1.编译安装MySQL

[root@Master ~]# yum -y install ncurses-devel
[root@Master ~]# tar -zxvf cmake-2.8.6.tar.gz
[root@Master ~]# cd cmake-2.8.6/
[root@Master cmake-2.8.6]# ./configure
[root@Master cmake-2.8.6]# gmake && gmake install
[root@Master cmake-2.8.6]# cd
[root@Master ~]# tar zxvf mysql-5.6.36.tar.gz
[root@Master mysql-5.6.36]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc
[root@Master mysql-5.6.36]# make && make install

2.优化调整

[root@Master mysql-5.6.36]# cp support-files/my-default.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y
[root@Master mysql-5.6.36]# cp support-files//mysql.server /etc/rc.d/init.d/mysql
[root@Master mysql-5.6.36]# chmod +x /etc/rc.d/init.d/mysql  
[root@Master mysql-5.6.36]# chkconfig --add /etc/rc.d/init.d/mysql 
[root@Master mysql-5.6.36]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@Master mysql-5.6.36]# . /etc/profile

3.初始化数据库

[root@Master ~]# groupadd mysql
[root@Master ~]# useradd -M -s /sbin/nologin mysql -g mysql
[root@Master ~]# chown -R mysql:mysql /usr/local/mysql
[root@Master ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql

4.启动MySQL服务

[root@Master ~]# systemctl start mysql
[root@Master ~]# mysqladmin -u root password 'pwd123'    //为root用户配置密码

5）配置MySQL Master 主服务器

1.在/etc/my.cnf 中修改或增加如下内容

[root@Master ~]# vim /etc/my.cnf
server-id = 11
log_bin = master-bin
log-slave-updates = true

2.重启MySQL服务

[root@Master ~]# systemctl restart mysql.service

3.登录MySQL程序，给从服务器授权

[root@Master ~]# mysql -u root -p    //使用root身份登录
Enter password:     //输入刚刚设置的root密码

mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.182.%' IDENTIFIED BY '123456' ;
Query OK, 0 rows affected (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 |      412 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

? ? ? ? 其中，file 列显示日志名，Position 列显示偏移量，这两个值在后面配置从服务器的时候需要Slave 应从该点在Master 上进行新的更新

6）配置从服务器

1.在/etc/my.cnf 中修改或增加下面内容

[root@Slave01 ~]# vim /etc/my.cnf
server_id = 22
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

这里需要注意 server_id 不能于主服务器相同

2.重启MySQL服务

[root@Master ~]# systemctl restart mysql

3.登录MySQL，配置同步

按主服务器的结果更改下面命令中的 master_log_file 和 master_log_pos 参数

[root@Slave01 ~]# mysql -u root -p
Enter password: 
mysql> change master to master_host='192.168.182.101',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=412;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> 

4.启动同步

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

mysql> 

5.查看Slave状态，确保一下两个值为yes

7）验证主从复制效果

1.在主，从服务器上登录mysql

[root@Master ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.36-log Source distribution

Copyright (c) 2000, 2017, 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 |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

两台数据库执行结果应该相同

2.在主服务器上新建数据库 db_test

mysql> create database db_test;

3.在主，从服务器上分别查看数据库，显示数据库相同，则主从复制成功

2.2 搭建MySQL 读写分离

? ? ? ? Amoeba 项目开源框架于 2008 年发布一款 Amoeba for MySQL? 软件，这个软件致力于MySQL 的分布式数据库前端代理层，它主要为应用层访问 MySQL 的时候充当SQL 路由功能，并具有负载均衡，高可用性，SQL过滤，读写分离，可路由相关的到目标数据库，可以并发请求多台数据库。通过Amoeba 能够完成数据源的高可用，负载均衡，数据切片的功能，目前 Amoeba 已在很多企业的生产线上使用，其版本可在官网https://sourceforge.net/projects/amoeba/files/?下载

1）在主机Amoeba 上安装Java环境

? ? ? ? 因为Amoeba 是基于 jdk1.5 开发的，所以广泛推荐使用 jdk1.5 或 1.6 版本，高版本不建议使用

[root@amoeba ~]# chmod +x jdk-6u14-linux-x64.bin 
[root@amoeba ~]# ./jdk-6u14-linux-x64.bin     //根据提示按按回车键即可
[root@amoeba ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@amoeba ~]# vim /etc/profil        //末尾添加如下配置

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

[root@amoeba ~]# source /etc/profilee.
[root@amoeba ~]# java -version
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)
[root@amoeba ~]#

增加以下配置

[root@Amoeba ~]# 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

[root@Amoeba ~]# source /etc/profile
[root@Amoeba ~]# java -version
java version "1.6.0_45"
Java(TM) SE Runtime Environment (build 1.6.0_45-b06)
Java HotSpot(TM) 64-Bit Server VM (build 20.45-b01, mixed mode)
[root@Amoeba ~]# 

Java 环境以及配置成功

2）安装并配置Amoeba 软件

[root@amoeba ~]# mkdir /usr/local/amoeba
[root@amoeba ~]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@amoeba ~]# chmod -R 755 /usr/local/amoeba/   
[root@amoeba ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop        //显示此内容说明Amoeba安装成功
[root@amoeba ~]#

3）配置 Amoeba 读写分离，两个 Slave 读负载均衡

1.Master，Slave01，Slave02 中开放权限给 Amoeba 访问

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

mysql> 

2.编辑amoeba.xml 配置文件

[root@amoeba ~]# cd /usr/local/amoeba/
[root@amoeba amoeba]# vim conf/amoeba.xml

以下修改后的内容，

注意取消注释?

3.编辑 dbServers.xml 配置文件

[root@amoeba amoeba]# vim conf/dbServers.xml

修改的内容为箭头指定部分

4.配置无误后，可以启动Amoeba 软件，

[root@amoeba amoeba]# bin/amoeba start&
[root@amoeba ~]# netstat -anpt | grep java
tcp6       0      0 127.0.0.1:13354         :::*                    LISTEN      8810/java           
tcp6       0      0 :::8066                 :::*                    LISTEN      8810/java           
tcp6       0      0 192.168.182.104:48662   192.168.182.101:3306    ESTABLISHED 8810/java           
tcp6       0      0 192.168.182.104:50074   192.168.182.102:3306    ESTABLISHED 8810/java           
tcp6       0      0 192.168.182.104:53410   192.168.182.103:3306    ESTABLISHED 8810/java           
[root@amoeba ~]# 

4）测试

1.在客户端主机上

[root@NFS ~]# yum -y install mysql

可以通过代理访问MySQL，

[root@NFS ~]# mysql -u amoeba -p123456 -h 192.168.182.104 -P8066
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1545595021
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> 

2.在Master 上创建一个表，同步各从服务器上，然后关掉服务器 Slvae 功能，再插入区别语句

mysql> use db_test;
Database changed
mysql> create table zang (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql>

分别在两台从服务器上

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

mysql> 

在主服务器上

mysql> insert into zang values ('1','zhang','this_is_master');
Query OK, 1 row affected (0.00 sec)

mysql> 

3.从服务器上同步了表，手动插入其他内容

Slave01
mysql> use db_test;
Database changed
mysql> insert into zang values ('2','zhang','this_is_slave1');
Query OK, 1 row affected (0.01 sec)


Slave02
mysql> use db_test;
Database changed
mysql> insert into zang values ('3','zhang','this_is_slave2');
Query OK, 1 row affected (0.01 sec)

4.测试读操作

在客户机上第一次查询结果如下

MySQL [db_test]> select * from zang ;
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    2 | zhang | this_is_slave1 |
+------+-------+----------------+
1 row in set (0.01 sec)

MySQL [db_test]>

第二次查询结果如下

MySQL [db_test]> select * from zang ;
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    2 | zhang | this_is_slave2 |
+------+-------+----------------+
1 row in set (0.02 sec)

MySQL [db_test]>

第三次查询结果如下

MySQL [db_test]> select * from zang ;
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    2 | zhang | this_is_slave1 |
+------+-------+----------------+
1 row in set (0.01 sec)

MySQL [db_test]>

5.测试写操作

在客户机上插入一条语句

MySQL [db_test]> insert into zang values ('4','zhang','write_test');
Query OK, 1 row affected (0.01 sec)

MySQL [db_test]> 

? ? ? ? 但是在客户机上查询不到，最终只有在Master 上才能看到这条语句内容，说明写操作在Master服务器上

mysql> select * from zang;
+------+-------+----------------+
| id   | name  | address        |
+------+-------+----------------+
|    1 | zhang | this_is_master |
|    4 | zhang | write_test     |
+------+-------+----------------+
2 rows in set (0.01 sec)

mysql> 

? ? ? ? 由此验证，以及实现了MySQL 读写分离，目前所有的写操作都全部在 Master（主服务器）上，用来避免数据的不同步；所有的读操作都分摊给了Slave （从服务器），用来分担数据库压力