MySQL主从复制与读写分离

MySQL 主从复制（Master-Slave Replication）

概念

MySQL 主从复制是一种将一台 MySQL 服务器（主服务器，Master）的变更自动复制到另一台或多台 MySQL 服务器（从服务器，Slave）的机制。主服务器负责处理写操作，从服务器主要用于处理读操作。

工作原理

1. 主服务器记录变更：

   • 主服务器在执行写操作（如 INSERT、UPDATE、DELETE）时，将这些操作记录到二进制日志（binlog）中。binlog 是一种记录所有更改数据库内容的日志文件。

2. 从服务器读取日志：

   • 从服务器有一个 I/O 线程，负责连接到主服务器并读取主服务器的 binlog，然后将其保存到从服务器的中继日志（relay log）中。

3. 从服务器重放日志：

   • 从服务器有一个 SQL 线程，负责读取中继日志并执行日志中的变更操作。这使得从服务器的数据与主服务器保持一致。

配置步骤

1. 在主服务器上启用二进制日志：

   [mysqld] log-bin=mysql-bin server-id=1
   

2. 在从服务器上配置复制参数：

   [mysqld] server-id=2 relay-log=relay-log
   

3. 创建复制用户并授予权限：

   CREATE USER 'replica'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%'; FLUSH PRIVILEGES;
   

4. 在从服务器上启动复制：

   CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='replica', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 4; START SLAVE;

优缺点

优点：

• 提高读性能：通过增加从服务器，可以分担主服务器的读负载。
• 数据备份：从服务器可以作为数据的实时备份，提高数据的可用性。

缺点：

• 数据延迟：由于复制过程需要时间，从服务器的数据可能会比主服务器稍有延迟。
• 单点故障：如果主服务器出现故障，需要手动或自动将其中一台从服务器提升为主服务器。

MySQL 读写分离（Read-Write Splitting）

概念

读写分离是将数据库的读操作和写操作分离到不同的数据库实例上进行的策略。通常结合主从复制使用，即主服务器处理写操作，从服务器处理读操作。

工作原理

1. 应用程序在执行数据库操作时，通过负载均衡组件或逻辑判断，将写操作发送到主服务器，读操作发送到从服务器：

   • 应用程序可以使用中间件（如 MySQL Proxy、MaxScale）或代码层的逻辑来实现读写分离。

2. 从服务器通过主从复制机制，从主服务器同步最新的数据：

   • 确保从服务器的数据尽量与主服务器保持同步，尽管会有一定的延迟。

配置步骤

1. 确保已经配置好主从复制。
2. 在应用程序中实现读写分离逻辑：
   • 例如，在 Java 应用中，可以通过配置多个数据源，一个用于写操作（指向主服务器），一个用于读操作（指向从服务器）。

优缺点

优点：

• 提高性能：通过将读操作分散到多个从服务器，可以显著提高读性能。
• 减少主服务器压力：主服务器专注于处理写操作，从而提高写性能。

缺点：

• 一致性问题：由于复制的延迟，读操作有可能读到延迟的数据，导致数据一致性问题。
• 复杂性增加：需要在应用层或中间层实现读写分离的逻辑，增加了系统的复杂性。

具体案例演示如下

master：192.168.10.101

slave1:192.168.10.102

slave2:192.168.10.103

amoeba：192.168.10.104

client：192.168.10.105

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

在从节点上进行时间同步

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

[root@localhost ~]# ntpdate 192.168.10.102

在所有服务器上安装mysql数据库

步骤略

配置mysql master主服务器

[root@localhost ~]# vi /etc/my.cnf

在[mysqld]模块中修改或添加：

server-id=11           ##修改

log-bin=master-bin      ##修改

log-slave-updates=true    ##添加    （可不用添加）

binlog-format = MIXED

注释：

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

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

binlog_format = STATEMENT

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

binlog_format = ROW

混合类型的复制。默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制,配置：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)

从服务器的配置

[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

，当然，这没有任何影响。只是看起来不爽。

验证主从复制

在主从服务器上分别查询数据库

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

4 rows in set (0.00 sec)

在主服务器上创建数据库

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)

在从服务器上再次查询数据库，可以看到从服务器上也有了db_test数据库了

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| db_test            |

| mysql              |

| performance_schema |

| test               |

+--------------------+

5 rows in set (0.00 sec)

扩展：主主复制

如果需要将一个slave1服务器作为另一台slave2的master，

在slave1上修改my.cnf，在[mysqld]模块添加

server-id=11

log-bin=master-bin

log-slave-updates=true

并重启mysql

在slave1上执行以下命令创建一个授权用户，用于在slave2上链接slave1

mysql> grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123456' ;

mysql> flush privileges;

mysql> show master status;

show出来的信息做为slave2上连接slave1时的参数

重启Mysql服务不会影响主从关系

搭建Mysql读写分离

在主机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安装成功

安装并配置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       ##有此提示表示成功

配置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行，去掉注释符

                       

               

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

  master"  parent="abstractServer">         ##45行

               

                       

                        192.168.1.101            ##48行

               

       

        slave1"  parent="abstractServer">              ##52行

               

                       

                        192.168.1.102               ##55行

               

       

        slave2"  parent="abstractServer">

               

                       

                        192.168.1.103

               

       

        slaves" virtual="true">                    ##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是正常开启的。

测试

（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上

导出导入数据库：

[root@localhost ~]# mysqldump -u root -p --database auth > /opt/auth.sql      

[root@localhost data]# mysql -u root -p < /opt/ auth.sql