MySQL数据库的主从复制与读写分离

一、MySQL数据库的主从复制

1.主从复制的概述及原理

（1）主从复制的意义

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

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

（2）主从复制的原理

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

主从复制支持的类型包括：

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

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

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

一般设置类型都会设置成混合类型的，相辅相成更好的执行主从复制

（3）主从复制的过程

MySQL复制的工作过程如图所示，结合图表更好理解。

 

 主(master)数据库启动 bin 二进制日志，这样会有一个Dump 线程，这个线程是把主(master)数据库的写入操作都会记录到这个 bin 的二进制文件中。

然后从(slave)数据库会启动一个 I/0线程(监控主服务器的二进制日志的变化)，这个线程主要是把主(master)数据库的 bin 二进制文件读取到本地，并写入到中继日志(Relay log)文件中。

最后从(slave)数据库其他 SOL 线程,把中继日志(Relay log)文件中的事件再执行一遍,更新从(slave)数据库的数据，保持主从数据一致，重新写入数据库。

2.主从复制实例操作

1.实验环境

主服务器：“master：192.168.10.101”

从服务器1：“slave1：192.168.10.102”

从服务器2：“slave2：192.168.10.103”

2.实验步骤

1：关闭所有服务器的firewalld

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

2：建立时间同步环境

[root@localhost ~]# yum -y install ntp
[root@localhost ~]# vi /etc/ntp.conf
添加：
server 127.127.1.0
fudge 127.127.1.0 stratum 8

[root@localhost ~]# systemctl restart ntpd
[root@localhost ~]# systemctl enable ntpd

3：在从节点上进行时间同步

[root@localhost ~]# yum -y install ntp
[root@localhost ~]# ntpdate 192.168.10.102

注：主从复制时间保持一致有以下原因

数据一致性：如果主从服务器的时间不同步，可能会导致数据的写入和读取在时间顺序上出现混乱。例如，某些依赖时间戳进行数据排序或判断的操作可能会产生错误结果。

事务处理：事务通常有时间相关的约束和逻辑。时间不同步可能导致事务的开始、提交或回滚时间记录不准确，从而影响事务的完整性和可靠性。

备份和恢复：在进行数据库备份和恢复操作时，准确的时间信息有助于确定备份的时间点和恢复的顺序。

监控和审计：为了有效地监控数据库的活动和进行审计，统一且准确的时间对于追踪操作的发生时间、判断异常活动的时间范围等至关重要。

比如，假设主服务器在 10:00 写入了一条数据，而从服务器的时间是 9:55，那么从服务器可能会错误地认为这条数据是在过去的时间写入的，从而导致数据处理的混乱。

又比如，在进行数据库的定期备份时，如果主从服务器时间不同步，可能会导致恢复时无法准确找到特定时间点的有效备份。

4.配置mysql master主服务器

[root@localhost ~]# vi /etc/my.cnf
在[mysqld]模块中修改或添加：

server-id=11           ##修改
log-bin=master-bin      ##修改
log-slave-updates=true    ##添加    （可不用添加）
binlog-format = MIXED

注释：
系统默认采用基于语句的复制类型

1：基于语句的复制。 在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。配置：
binlog_format = STATEMENT

2：基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从 MySQL 5.0开始支持，配置：
binlog_format = ROW

3：混合类型的复制。默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制,配置：binlog_format = MIXED

log-slave-updates=true     #Slave可以是其他 Slave 的 Master，从而扩散 Master 的更新
binlog-ignore-db=test      #不记录指定的数据库的二进制日志  
replicate-ignore-db=test    #设置不需要同步的库
binlog_cache_size = 1M    #日志缓存的大小
expire_logs_days=3       #自动过期清理日志的天数
以上参数在[mysqld]模块中设置

[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -u root -p
mysql> grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123456' ;
mysql> flush privileges;
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      337 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

5.从服务器的配置

[root@localhost ~]# vi /etc/my.cnf
在[mysqld]模块中修改或添加：

server-id       = 22            ##修改，值不能和其他mysql服务器重复
relay-log=relay-log-bin            ##添加（可不指定）
relay-log-index=slave-relay-bin.index    ##添加（可不指定）

注释：
--relay-log=name    中继日志的文件的名字
 --relay-log-index=name      MySQL slave 在启动时需要检查relay log index 文件中的relay log信息，此处定义该索引文件的名字 

[root@localhost ~]# systemctl restart mysqld

[root@localhost ~]# mysql -u root -p
mysql> change master to master_host='192.168.10.101',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=337;

注释： Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的Relay Log文件(relay-log-bin.xxxxxx)的最末端，并将读取到的Master端的master-bin的文件名和位置记录到master- info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个master-bin的哪个位置开始往后的日志内容，请发给我

mysql> start slave;
mysql> show slave status\G           ##注意后面不要加分号
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.102
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 411
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 284
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 411
              Relay_Log_Space: 461
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 11
                  Master_UUID: 167be460-3d4d-11e8-ad42-000c29ae7f64
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

备注：如果后面加了分号，显示的最后一行会提示ERROR: No query specified
，当然，这没有任何影响。只是看起来不爽。

6.验证主从复制

（1）在主从服务器上分别查询数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

（2）在主服务器上创建数据库
mysql> create database db_test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_test            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

（3）在从服务器上再次查询数据库，可以看到从服务器上也有了db_test数据库了
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_test            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

二、读写分离

1.读写分离原理

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

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

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

(2)基于中间代理层实现
代理一般位于客户端和服务器之间,代理服务器接到客户端请求后通过判断后转发到后端数据库，有两个代表性程序。

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

② Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由 Java 语言进行开发,阿里巴巴将其用于生产环境。它不支持事务和存储过程。

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

2.案例演示

1.案例环境

 2.实现过程

1：在主机amoeba上安装java环境

[root@localhost ~]# chmod +x jdk-6u14-linux-x64.bin
[root@localhost ~]# ./jdk-6u14-linux-x64.bin
[root@localhost ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@localhost ~]# vi /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:$JAVA_HOME/bin
export AMOEBA_HOME=/usr/local/amoeba/
export PATH=$PATH:$AMOEBA_HOME/bin

[root@localhost local]# source /etc/profile
[root@localhost local]# java -version            ##查询版本，确定java安装成功

2：安装并配置amoeba

[root@localhost local]# mkdir /usr/local/amoeba
[root@localhost ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@localhost ~]# chmod -R 755 /usr/local/amoeba/
[root@localhost ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop       ##有此提示表示成功

3：配置amoeba读写分离

（1）在三个mysql服务器中开放权限给amoeba访问（只在master中即可，会复制到slave中）
mysql> grant all on *.* to test@'192.168.10.%' identified by '123.com';

（2）在amoeba上配置amoeba.xml文件
[root@localhost amoeba]# systemctl stop firewalld

[root@localhost ~]# cd /usr/local/amoeba/conf
[root@localhost conf]# vi amoeba.xml
修改红色部分，此处设置的是mysql客户端连接amoeba时用的账号和密码

       

             amoeba                 ##30行

             123456               ##32行

             
                       
             ${amoeba.home}/conf/access_list.conf
                       
             
                       
         

                 。。。。。。。。略。。。。。。。

       
             
                          ${amoeba.home}/conf/rule.xml
                ${amoeba.home}/conf/ruleFunctionMap.xml
             
               
                ${amoeba.home}/conf/functionMap.xml
                1500
                master             ##115行

               
                master             ##118行
                slaves    ##119行此处的注释去掉
               
                true
       

（3）编辑dbServer.xml文件

[root@localhost conf]# vi dbServers.xml
修改（注意去掉注释），slave2的复制一个slave1
     
                  test         ##26行

                        
                  123.com  ##29行，去掉注释符
                        
               

                       。。。。。。。。。略。。。。。。。。。。

          ##45行
               
                       
                        192.168.1.101            ##48行
               
       

            ##52行
               
                       
                        192.168.1.102               ##55行
               
       

       
               
                       
                        192.168.1.103
               
       

                           ##59行
               
                       
                        1
                       
                        slave1,slave2          ##65行
               
       

（4）启动amoeba软件
[root@localhost ~]# cd /usr/local/amoeba/
[root@localhost amoeba]# bin/amoeba start&
注：当在前台运行某个作业时,终端被该作业占据;而在后台运行作业时,它不会占据终端。可以使用&命令把作业放到后台执行
[root@localhost amoeba]# netstat -anpt | grep java
如果能看到8066和3306两个端口号，证明amoeba是正常开启的。

4：测试

（1）：在client上
[root@localhost yum.repos.d]# yum -y install mariadb
[root@localhost yum.repos.d]# mysql -u amoeba -p 123456 -h 192.168.10.104 -P 8066
Enter password:            ##密码：123456

（2）：在master服务器上创建表
mysql> stop slave;
MySQL [test]> use auth
MySQL [auth]> create table users (id int(10),name char(20));
（3）：在两个slave服务器上
mysql> stop slave;

（4）：在master服务器上
mysql> insert into users values ('1','zhangsan');

（5）：在slave1上
mysql> use auth;
mysql>insert into zang values ('2','zhangsan');

（6）：在slave2上
mysql> use auth;
mysql>insert into zang values ('3','zhangsan);

（7）：在client上查询三次
mysql> use auth;
mysql> select * from users;
对比三次的输出，验证读操作，发现没有在master写入的数据，而slave上写的能查到

（8）：在client上
mysql> use auth;
mysql>insert into users values ('4','zhangsan');
mysql> select * from users;      ##发现在client上查询不到自己写的数据

（9）：在master上
mysql> select * from users;      ##能查到在client上写入的数据，说明写操作在master上

（10）：在slave上
mysql> select * from users;       ##发现没有数据，说明写入的操作是在master上

以上就是实现读写分离的基本过程