MySQL主从复制

如何提升并发能力

一般应用对数据库而言都是 “读多写少” ，对数据库读取数据的压力比较大，采用数据库集群的方案，做 主从架构 、进行 读写分离 ，这样同样可以提升数据库的并发处理能力。但并不是所有的应用都需要对数据库进行主从架构的设置，毕竟设置架构本身是有成本的。

如果目的在于提升数据库高并发访问的效率，首先考虑的是如何 优化SQL和索引 ，这种方式简单有效；其次是采用 缓存的策略 ，比如使用 Redis 将热点数据保存在内存数据库中，提升读取的效率；最后才是对数据库采用 主从架构 ，进行 读写分离。

主从复制的作用

实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于 3 个线程来操作，一个主库线程，两个从库线程。

1. 二进制日志转储线程 （Binlog dump thread） ：是一个主库线程。当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件（ Event ）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。
2. 从库 I/O 线程 ：会连接到主库，向主库发送请求更新 Binlog 。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的 中继日志（Relay log） 。
3. 从库 SQL 线程 ：会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

复制三步骤

1. Master 将写操作记录到二进制日志（ binlog ）。
2. Slave 将 Master 的 binary log events 拷贝到它的中继日志（ relay log ）；
3. Slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL 复制是异步的且串行化的，而且重启后从 接入点 开始复制。

复制的问题

复制的最大问题： 延时

复制的基本原则

• 每个 Slave 只有一个 Master
• 每个 Slave 只能有一个唯一的服务器 ID
• 每个 Master 可以有多个 Slave

基础环境搭建

准备多台服务器，并按如下教程安装 MySQL ，且所有节点的版本必须相同

注意：如果是虚拟机环境，通过安装一台机器后克隆虚拟机完成多台服务器的安装，需要修改如下配置

• MAC 地址：通过虚拟机修改
  
• IP 地址：动态 IP 不用管，静态 IP 需要修改
• 主机名（hostname）：
  • Linux：hostnamectl set-hostname xxx
  • Windows：略，自行搜索教程
• server-uuid：在数据目录下，有一个 auto.cnf 文件，文件里面有个 server-uuid ， 每个服务的 server-uuid 需要不同。如果不修改，最后查看状态时会显示：Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

修改 MySQL 配置文件，在 mysqld 下面添加或修改如下配置，修改完成后重启服务器

1. # [必须] 服务器唯一ID（主从复制时需要不一样）
2. server-id=1
3. # [可选] 是否只读，0（默认）表示读写（主机），1表示只读（从机）
4. read-only=OFF
5. # [可选] binlog 文件位置（默认 datadir 目录下，不建议和数据文件放在同一目录或磁盘）（不配置时默认启用并使用默认路径和文件名）
6. log_bin=/usr/local/mysql/log/log-bin
7. # [必须] 设置 binlog 格式
8. binlog_format=MIXED
9. # [可选] 设置不要复制的数据库
10. #binlog-ignore-db=不需要复制的主数据库名字
11. # [可选] 设置需要复制的数据库，默认全部记录。
12. #binlog-do-db=需要复制的主数据库名字

修改完成后重启服务器

建立账户并授权

1. 连接 MySQL
2. 创建账户并设置密码，如果从机只有一台，% 可以设置为从机 IP，或者为多个从机分别创建账户
   CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
3. 授权
   GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';

查询主机状态

通过 SHOW MASTER STATUS; 查看二进制文件状态状态，记录 File 和 Position

1. mysql> show master status;
2. +----------------+----------+--------------+------------------+-------------------+
3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4. +----------------+----------+--------------+------------------+-------------------+
5. | log-bin.000004 | 2090 | | | |
6. +----------------+----------+--------------+------------------+-------------------+
7. 1 row in set (0.00 sec)

注意：执行完此步骤后不要再操作主服务器，防止主服务器状态值变化。如有操作，需要重新查看最新状态

修改 MySQL 配置文件，在 mysqld 下面添加或修改如下配置，修改完成后重启服务器

1. # [必须] 服务器唯一ID（主从复制时需要不一样）
2. server-id=2
3. # [可选] 是否只读，0（默认）表示读写（主机），1表示只读（从机）
4. read-only=ON
5. # [可选] 二进制日志文件位置（默认 datadir 目录下，不建议和数据文件放在同一目录或磁盘）（不配置时使用默认路径和文件名）
6. log_bin=/usr/local/mysql/log/log-bin
7. # [可选] 设置二进制日志文件格式
8. binlog_format=MIXED
9. # [可选] 设置中继日志文件位置
10. relay_log=/usr/local/mysql/log/log-relay

修改完成后重启服务器

配置主机信息

使用如下命令配置需要复制的主机信息

1. CHANGE MASTER TO
2. MASTER_HOST='主机的地址',
3. MASTER_PORT='主键的端口',
4. MASTER_USER='主机用户名',
5. MASTER_PASSWORD='主机用户名的密码',
6. MASTER_LOG_FILE='主机的File值',
7. MASTER_LOG_POS=主机的Position值;

举例：CHANGE MASTER TO MASTER_HOST='192.168.220.101',MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='log-bin.000004',MASTER_LOG_POS=2090;

使用 START SLAVE; 开启同步

使用 SHOW SLAVE STATUS\G; 查看状态，如果正常运行，则 Slave_IO_Running 和 Slave_SQL_Running 均显示为 Yes

1. mysql> SHOW SLAVE STATUS\G
2. *************************** 1. row ***************************
3. Slave_IO_State: Waiting for source to send event
4. Master_Host: 192.168.220.101
5. Master_User: slave
6. Master_Port: 3306
7. Connect_Retry: 60
8. Master_Log_File: log-bin.000004
9. Read_Master_Log_Pos: 2090
10. Relay_Log_File: log-relay.000002
11. Relay_Log_Pos: 324
12. Relay_Master_Log_File: log-bin.000004
13. Slave_IO_Running: Yes
14. Slave_SQL_Running: Yes
15. Replicate_Do_DB:
16. Replicate_Ignore_DB:
17. Replicate_Do_Table:
18. Replicate_Ignore_Table:
19. Replicate_Wild_Do_Table:
20. Replicate_Wild_Ignore_Table:
21. Last_Errno: 0
22. Last_Error:
23. Skip_Counter: 0
24. Exec_Master_Log_Pos: 2090
25. Relay_Log_Space: 528
26. Until_Condition: None
27. Until_Log_File:
28. Until_Log_Pos: 0
29. Master_SSL_Allowed: No
30. Master_SSL_CA_File:
31. Master_SSL_CA_Path:
32. Master_SSL_Cert:
33. Master_SSL_Cipher:
34. Master_SSL_Key:
35. Seconds_Behind_Master: 0
36. Master_SSL_Verify_Server_Cert: No
37. Last_IO_Errno: 0
38. Last_IO_Error:
39. Last_SQL_Errno: 0
40. Last_SQL_Error:
41. Replicate_Ignore_Server_Ids:
42. Master_Server_Id: 1
43. Master_UUID: 539c4f2f-d453-11ec-9853-000c29120db6
44. Master_Info_File: mysql.slave_master_info
45. SQL_Delay: 0
46. SQL_Remaining_Delay: NULL
47. Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
48. Master_Retry_Count: 86400
49. Master_Bind:
50. Last_IO_Error_Timestamp:
51. Last_SQL_Error_Timestamp:
52. Master_SSL_Crl:
53. Master_SSL_Crlpath:
54. Retrieved_Gtid_Set:
55. Executed_Gtid_Set:
56. Auto_Position: 0
57. Replicate_Rewrite_DB:
58. Channel_Name:
59. Master_TLS_Version:
60. Master_public_key_path:
61. Get_master_public_key: 0
62. Network_Namespace:
63. 1 row in set, 1 warning (0.00 sec)

若未能正确配置，可能有如下原因

1. 账户密码错误
2. 配置文件问题
3. 连接服务器时语法
4. 主服务器权限

当发现具体问题时，建议先执行 STOP SLAVE; 停止主从，再执行 RESET SLAVE; 重置状态，然后确认主库的二进制文件状态，最后再从 CHANGE MASTER ... 开始执行

测试主从复制

在主库执行一下创建库、创建表、插入数据等操作，查看从库是否刷新数据

执行 STOP SLAVE; 停止主从