MySQL 5.7主从配置实现读写分离

本篇笔记记录了MySQL 5.7在CentOS中配置主从同步实现读写分离的过程，在mysqldump过程中记录binlog的File和Position参数，所以也适用于不停机增加从库

MySQL安装请参考如下笔记
CentOS 6.9 yum安装MySQL 5.7
CentOS7源码编译安装nginx+php7.2+mysql5.7并使用systemctl管理
将要配置主从的服务器ip
主:192.168.75.134
从:192.168.75.135
在主库上创建测试库
登录mysql

mysql -uroot -p

执行创建数据库语句

mysql> CREATE DATABASE IF NOT EXISTS app DEFAULT CHARSET utf8 COLLATE utf8_unicode_ci;

创建测试数据表

mysql> use app;

mysql> CREATE TABLE `product` (
  `id` INT (10) NOT NULL AUTO_INCREMENT,
  `product_name` VARCHAR (255) NOT NULL DEFAULT '',
  `stock` INT (10) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `product_name` (`product_name`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 ;

插入测试数据

INSERT INTO product (id, product_name, stock) VALUES (1, 'product_name_1', 99);
INSERT INTO product (id, product_name, stock) VALUES (2, 'product_name_2', 88);
INSERT INTO product (id, product_name, stock) VALUES (3, 'product_name_3', 77);
INSERT INTO product (id, product_name, stock) VALUES (4, 'product_name_4', 66);
INSERT INTO product (id, product_name, stock) VALUES (5, 'product_name_5', 55);
INSERT INTO product (id, product_name, stock) VALUES (6, 'product_name_6', 44);
INSERT INTO product (id, product_name, stock) VALUES (7, 'product_name_7', 33);
INSERT INTO product (id, product_name, stock) VALUES (8, 'product_name_8', 22);
INSERT INTO product (id, product_name, stock) VALUES (9, 'product_name_9', 11);
INSERT INTO product (id, product_name, stock) VALUES (10, 'product_name_10', 0);

查看数据

mysql> select * from product;
+----+-----------------+-------+
| id | product_name    | stock |
+----+-----------------+-------+
|  1 | product_name_1  |    99 |
|  2 | product_name_2  |    88 |
|  3 | product_name_3  |    77 |
|  4 | product_name_4  |    66 |
|  5 | product_name_5  |    55 |
|  6 | product_name_6  |    44 |
|  7 | product_name_7  |    33 |
|  8 | product_name_8  |    22 |
|  9 | product_name_9  |    11 |
| 10 | product_name_10 |     0 |
+----+-----------------+-------+
10 rows in set (0.00 sec)

通过以上操作，我们已经在主库上创建了测试数据
主库配置
配置my.conf

vim /etc/my.cnf
#写入如下配置
[mysqld]
log-bin=mysql-bin

binlog-format=mixed

server-id=134

binlog-do-db=app

配置含义
log-bin - 启用二进制日志
binlog-format - 日志格式
server-id - 服务器唯一ID，一般取IP最后一段
binlog-do-db - 要同步的数据库名
配置给从库Slave服务器使用的账号

mysql> CREATE USER 'slave'@'192.168.75.135' IDENTIFIED BY 'vagrant';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.75.135';
mysql> FLUSH PRIVILEGES;

CREATE USER - 创建用户
GRANT REPLICATION - 配置权限
FLUSH PRIVILEGES - 刷新，立即生效
从库配置
配置my.cnf

vim /etc/my.cnf
#写入如下配置
log-bin=mysql-bin

binlog-format=mixed

server-id=135

replicate-do-db=app

log-slave-updates=1

replicate-do-db - 要同步的数据库名
log-slave-updates - 写入从库binlog
备份主库中要同步的数据库

mysqldump -uroot -p --master-data --single-transaction -R --databases app > app.sql

参数说明：
routines - 导出数据库中的存储过程和函数
single_transaction - 导出开始时设置事务隔离状态，并使用一致性快照开始事务，然后unlock tables;而lock-tables是锁住一张表不能写操作，直到dump完毕。
master-data - 默认等于1，将dump起始（change master to）binlog点和pos值写到结果中，等于2是将change master to写到结果中并注释。
将备份传入从库服务器并恢复

scp app.sql root@192.168.75.135:/home

从主库备份到从库设置完成同步是存在间隔的，这个间隔主库可能又产生了新的写操作，所以我们获取binlog的File和Position参数是通过mysqldump生成的备份文件，而不是在主库上执行show master status;的结果，如果这个间隔中可以保证主库不会出现写操作，也可以通过show master status;获取binlog的File和Position参数，以下做个小实验，主库备份以后，再向主库插入两条记录

执行sql

mysql> INSERT INTO product (id, product_name, stock) VALUES (11, 'product_name_11', 111);
mysql> INSERT INTO product (id, product_name, stock) VALUES (12, 'product_name_12', 112);

进入从服务器，从库导入备份

mysql -uroot -p -e'CREATE DATABASE IF NOT EXISTS app DEFAULT CHARSET utf8 COLLATE utf8_unicode_ci;'
mysql -uroot -p app<app.sql

查看备份文件中的File和Position参数

head -50 app.sql
...
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154;
...

我们看到File是mysql-bin.000004,Position是154，进入从库mysql，继续操作
设置同步参数

在进行本步骤之前，请确认主库服务器防火墙开启了3306端口，并且关闭了selinux，否则从库无法从主库获得binlog，同步无法进行

mysql> change master to master_host='192.168.75.134',master_user='slave',master_password='vagrant',master_log_file='mysql-bin.000004',master_log_pos=154;

启动从库同步

mysql> start slave;

查看同步是否正确

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.75.134
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 806
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 972
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: app
          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: 806
              Relay_Log_Space: 1183
              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: 134
                  Master_UUID: 800e7bbf-35ae-11e9-a155-000c29268296
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

以上结果中第13，14行，Slave_IO_Running: Yes和Slave_SQL_Running: Yes都是yes，表示同步开启成功
主库再插入一条数据

mysql> INSERT INTO product (id, product_name, stock) VALUES (13, 'product_name_13', 113);

查看从库数据是否正确

mysql> select * from product;
+----+-----------------+-------+
| id | product_name    | stock |
+----+-----------------+-------+
|  1 | product_name_1  |    99 |
|  2 | product_name_2  |    88 |
|  3 | product_name_3  |    77 |
|  4 | product_name_4  |    66 |
|  5 | product_name_5  |    55 |
|  6 | product_name_6  |    44 |
|  7 | product_name_7  |    33 |
|  8 | product_name_8  |    22 |
|  9 | product_name_9  |    11 |
| 10 | product_name_10 |     0 |
| 11 | product_name_11 |   111 |
| 12 | product_name_12 |   112 |
| 13 | product_name_13 |   113 |
+----+-----------------+-------+
13 rows in set (0.00 sec)

从库查到13条记录，主库备份时product表是10条数据，备份成功后导入从库前主库又插入了2条数据，同步设置成功后主库又插入1条数据，主从数据相同，OK，数据同步成功！