Mysql 主从同步

   前言：MySQL的主从复制并不是从硬盘给直接同步，而是逻辑的binlog日志同步到本地的应用执行的过程

什么是binlog日志？        

binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是对库表等内容的查询不会记录。(啥是binlog?   1. 记录数据库增删改,不记录查询的二进制日志.2.作用:用于数据库的主从复制及数据的增量恢复)

主从复制的过程?

在主服务器数据库的增删改操作会写入到主服务器的binlog日志中，从服务器上的io线程把主服务器binlog中的sql语句拿到中继日志，从服务器上的SQL线程执行中继日志中同步过来的SQL语句，从而同步数据到从服务器上。

复制过程涉及到的3个线程：

1、从库开启一个IO线程，负责链接主库请求和接收binlog日志并写入到relay-log

2、从库开启一个sql线程，负责解析relay-log中的事件并执行

3、主库开启一个dump线程，负责响应从库来的IO线程的请求。

---

1 案例1：MySQL一主一从

1.1 问题

• 构建 主-->从 复制结构
• 其中主机192.168.4.10作为主库
• 主机192.168.4.20作为从库

1.2 方案

使用2台RHEL 7虚拟机，如图-1所示。其中192.168.4.10是MySQL主服务器，负责提供同步源；另一台192.168.4.20作为MySQL从服务器，通过调取主服务器上的binlog日志，在本地重做对应的库、表，实现与主服务器的AB复制（同步）。

提前为两台MySQL服务器安装好MySQL-server、MySQL-Client软件包，并为数据库用户root修改密码；Linux客户机上则只需安装MySQL-Client软件包即可。

1.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：初始化现有库

为了在启用binlog日志及同步之前保持主、从库的一致性，建议进行初始化——备份主服务器上现有的库，然后导入到从服务器上。

当现有库、表都采用MyISAM引擎时，可执行离线备份、恢复，这样更有效率；否则，可通过mysqldump等工具来实现库的导出、导入。

1）备份MySQL Master（192.168.4.10）上现有的库

如果服务器已经启用binlog，建议对日志做一次重置，否则可忽略：

1. [root@dbsvr1 ~]# mysql -u root -p
2. Enter password:                                 //以数据库用户root登入
3. .. ..
4. mysql> RESET MASTER;                             //重置binlog日志
5. Query OK, 0 rows affected (0.06 sec)
6. mysql> quit                                     //退出mysql> 环境
7. Bye

     以备份mysql库、sys库为例，导出操作如下：

1. [root@dbsvr1 ~]# mysqldump -u root -p –all-databases > /root/mytest.sql
2. Enter password:                                     //验证口令
3. [root@dbsvr1 ~]# ls -lh /root/mytest.sql             //确认备份结果
4. -rw-r--r--. 1 root root 777172 4月 23 12:21 /root/mytest.sql

2）在MySQL Slave（192.168.4.20）上导入备份的库

先清理目标库，避免导入时冲突。主要是采用InnoDB引擎的库，授权库mysql多采用MyISAM引擎，可不做清理。

1. [root@dbsvr2 ~]# mysql -u root -p
2. Enter password:                                 //以数据库用户root登入
3. .. ..
4. mysql> DROP DATABASE test;                         //删除test库
5. Query OK, 0 rows affected (0.03 sec)
6. mysql> quit                                     //退出mysql> 环境
7. Bye

使用scp工具下载备份文件：

1. [root@dbsvr2 ~]# scp /root/mytest.sql root@192.168.4.20:/
2. root@dbsvr1's password:                         //验证对方系统用户root的口令
3. mytest.sql 100% 759KB 759.0KB/s 00:00
4. [root@dbsvr2 ~]# ls -lh mytest.sql             //确认下载结果
5. -rw-r--r--. 1 root root 759K 4月 23 12:22 /mytest.sql

执行导入操作

1. [root@dbsvr2 ~]# mysql -u root -p < /mytest.sql
2. Enter password:                                 //验证口令

导入成功后，可重新登入 mysql> 环境，确认清理的目标库已恢复：

1. mysql> show databases;
2. +--------------------+
3. | Database |
4. +--------------------+
5. | information_schema |
6. | mysql |
7. | performance_schema |
8. | sys |                         
9. +--------------------+
10. 4 rows in set (0.00 sec)

    步骤二：配置MySQL Master（主服务器，192.168.4.10）

    1）修改/etc/my.cnf配置，重新启动MySQL服务程序

    指定服务器ID号、允许日志同步：

11. [root@dbsvr1 mysql]# vim /etc/my.cnf
12. [mysqld]
13. log_bin=dbsvr1-bin                     //启用binlog日志，并指定文件名前缀
14. server_id = 10                         //指定服务器ID号

15. 重启mysql服务：

16. [root@dbsvr1 ~]# systemctl restart mysqld.service

2）新建一个备份用户，授予复制权限

1. mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicater'@'192.168.4.%' IDENTIFIED BY 'pwd123';
2. Query OK, 0 rows affected, 1 warning (0.09 sec)

3）检查Master服务器的同步状态

在已经初始化现有库的情况下，查看MASTER状态，记录下当前的日志文件名、偏移的位置（下面SLAVE发起复制时需要用到）：

1. mysql> SHOW MASTER STATUS\G
2. *************************** 1. row ***************************
3. File: dbsvr1-bin.000001 //记住当前的日志文件名
4. Position: 154 //记住当前的位置
5. Binlog_Do_DB:
6. Binlog_Ignore_DB:
7. Executed_Gtid_Set:
8. 1 row in set (0.00 sec)

步骤三：配置MySQL Slave（从服务器，192.168.4.20）

1）修改/etc/my.cnf配置，重新启动MySQL服务程序

指定服务器ID号、允许日志同步：

1. [root@dbsvr2 ~]# vim /etc/my.cnf
2. [mysqld]
3. log_bin=dbsvr2-bin                     //启动SQL日志，并指定文件名前缀
4. server_id = 20                         //指定服务器ID号，不要与Master的相同
5. .. ..

在生产环境中，还可以根据需要设置更详细的同步选项。比如，指定当主、从网络中断时的重试超时时间（slave-net-timeout=60 ）等，具体可参考MySQL手册。

配置完成后，重启mysql服务：

[root@dbsvr2 ~]# systemctl restart mysqld.service

通过CHANGE MASTER语句指定MASTER服务器的IP地址、同步用户名/密码、起始日志文件、偏移位置（参考MASTER上的状态输出）：

1. mysql> CHANGE MASTER TO MASTER_HOST='192.168.4.10',
2. -> MASTER_USER='replicater',
3. -> MASTER_PASSWORD='pwd123',
4. -> MASTER_LOG_FILE='dbsvr1-bin.000002',     //对应Master的日志文件
5. -> MASTER_LOG_POS=334;                         //对应Master的日志偏移位置
6. Query OK, 0 rows affected, 2 warnings (0.12 sec)

然后执行START SLAVE（较早版本中为SLAVE START）启动复制：

1. mysql> START SLAVE;                             //启动复制

Query OK, 0 rows affected (0.00 sec)

注意：一旦启用SLAVE复制，当需要修改MASTER信息时，应先执行STOP SLAVE停止复制，然后重新修改、启动复制。

通过上述连接操作，MASTER服务器的设置信息自动存为master.info文件，以后每次MySQL服务程序时会自动调用并更新，无需重复设置。查看master.info文件的开头部分内容，可验证相关设置：

1. [root@dbsvr2 ~]# ls -lh /var/lib/mysql/master.info
2. -rw-r-----. 1 mysql mysql 132 4月 23 12:06 /var/lib/mysql/master.info
3. [root@dbsvr2 ~]# head /var/lib/mysql/master.info
4. 25
5. dbsvr1-bin.000001
6. 154
7. 192.168.4.10
8. replicater
9. pwd123
10. 3306
11. 60
12. 0

2）检查Slave服务器的同步状态

通过SHOW SLAVE STATUS语句可查看从服务器状态，确认其中的IO线程、SQL线程正常运行，才能成功同步：

1. mysql> SHOW SLAVE STATUS\G
2. Slave_IO_State: Waiting for master to send event
3. Master_Host: 192.168.4.1
4. Master_User: replicater
5. Master_Port: 3306
6. Connect_Retry: 60
7. Master_Log_File: dbsvr1-bin.000001
8. Read_Master_Log_Pos: 154
9. Relay_Log_File: db2-relay-bin.000003
10. Relay_Log_Pos: 321
11. Relay_Master_Log_File: dbsvr1-bin.000001
12. Slave_IO_Running: Yes //IO线程应该已运行
13. Slave_SQL_Running: Yes //SQL线程应该已运行
14. Replicate_Do_DB:
15. Replicate_Ignore_DB:
16. Replicate_Do_Table:
17. Replicate_Ignore_Table:
18. Replicate_Wild_Do_Table:
19. Replicate_Wild_Ignore_Table:
20. Last_Errno: 0
21. Last_Error:
22. Skip_Counter: 0
23. Exec_Master_Log_Pos: 154
24. Relay_Log_Space: 2490
25. Until_Condition: None
26. Until_Log_File:
27. Until_Log_Pos: 0
28. Master_SSL_Allowed: No
29. Master_SSL_CA_File:
30. Master_SSL_CA_Path:
31. Master_SSL_Cert:
32. Master_SSL_Cipher:
33. Master_SSL_Key:
34. Seconds_Behind_Master: 0
35. Master_SSL_Verify_Server_Cert: No
36. Last_IO_Errno: 0
37. Last_IO_Error:
38. Last_SQL_Errno: 0
39. Last_SQL_Error:
40. Replicate_Ignore_Server_Ids:
41. Master_Server_Id: 10
42. Master_UUID: 2d4d8a11-27b7-11e7-ae78-52540055c180
43. Master_Info_File: /var/lib/mysql/master.info
44. SQL_Delay: 0
45. SQL_Remaining_Delay: NULL
46. Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
47. Master_Retry_Count: 86400
48. Master_Bind:
49. Last_IO_Error_Timestamp:
50. Last_SQL_Error_Timestamp:
51. Master_SSL_Crl:
52. Master_SSL_Crlpath:
53. Retrieved_Gtid_Set:
54. Executed_Gtid_Set:
55. Auto_Position: 0
56. Replicate_Rewrite_DB:
57. Channel_Name:
58. Master_TLS_Version:
59. 1 row in set (0.00 sec)

若START SLAVE直接报错失败，请检查CHANGE MASTER相关设置是否有误，纠正后再重试；若IO线程或SQL线程有一个为“No”，则应检查服务器的错误日志，分析并排除故障后重启主从复制。

步骤四：测试主从同步效果

1）在Master上操作数据库、表、表记录

新建newdb库、newtable表，随意插入几条表记录：

1. mysql> CREATE DATABASE newdb;                         //新建库newdb
2. Query OK, 1 row affected (0.17 sec)
4. mysql> USE newdb;                                     //切换到newdb库
5. Database changed
7. mysql> CREATE TABLE newtable(id int(4));             //新建newtable表
8. Query OK, 0 rows affected (0.46 sec)
9. 图像 小部件
10. mysql> INSERT INTO newtable VALUES(1234),(5678);     //插入2条表记录
11. Query OK, 2 rows affected (0.24 sec)
12. Records: 2 Duplicates: 0 Warnings: 0
13. mysql> SELECT * FROM newtable;                         //确认表数据
14. +------+
15. | id |
16. +------+
17. | 1234 |
18. | 5678 |
19. +------+
20. 2 rows in set (0.00 sec)

2）在Slave上确认自动同步的结果

直接切换到newdb库，并查询newtable表的记录，应该与Master上的一样，这才说明主从同步已经成功生效：

1. mysql> USE newdb;                                     //直接切换到newdb库
2. Reading table information for completion of table and column names
3. You can turn off this feature to get a quicker startup with -A
5. Database changed
7. mysql> SELECT * FROM newtable;                     //输出表记录
8. +------+
9. | id |
10. +------+
11. | 1234 |
12. | 5678 |
13. +------+
14. 2 rows in set (0.02 sec)

3）在Master服务器上可查看Slave主机的信息

1. mysql> SHOW SLAVE HOSTS;
2. +-----------+------+------+-----------+--------------------------------------+
3. | Server_id | Host | Port | Master_id | Slave_UUID |
4. +-----------+------+------+-----------+--------------------------------------+

5. ​
   mysql> SHOW SLAVE HOSTS;
   +-----------+------+------+-----------+--------------------------------------+
   | Server_id | Host | Port | Master_id | Slave_UUID                           |
   +-----------+------+------+-----------+--------------------------------------+
   |         2 |      | 3306 |        10 | 512cf7c1-27c4-11e7-8f4b-5254007b030b |
   +-----------+------+------+-----------+--------------------------------------+
   1 row in set (0.00 sec)

   ​

   | 2 | | 3306 | 10 | 512cf7c1-27c4-11e7-8f4b-5254007b030b |
6. +-----------+------+------+-----------+--------------------------------------+
7. 1 row in set (0.00 sec)