MySQL从入门到精通【进阶篇】之 主从复制详解

0.前言

MySQL的主从复制和读写分离是数据库领域的基本概念，也是保证系统高可用、高性能的重要技术。

1. 主从复制（Master-Slave Replication）是MySQL的一项重要功能。这种技术允许 将一个MySQL数据库服务器（主服务器）上的数据，复制到一个或多个MySQL数据库服务器（从服务器）上。主从复制使用场景主要包括读写分离、故障切换、备份以及数据分析等。主从复制的核心原理是：主服务器将其数据更改记录在二进制日志(binary log)中，从服务器将这些更改请求复制到自己的中继日志(relay log)，然后从服务器重播(replay)中继日志中的事件，使从服务器的数据与主服务器保持一致。

2. 读写分离是指将数据读取和数据更新操作分开，分别在不同的数据库服务器上进行。此技术通常结合主从复制来使用，读操作在从服务器上进行，写操作在主服务器上进行。读写分离的主要优点是可以显著地提高应用性能和扩展性，因为读操作通常要比写操作更频繁，将读操作的负载分摊到从服务器，可以显著地减轻主服务器的压力。

实现MySQL的主从复制和读写分离需要对MySQL的架构和工作原理有深入的了解，而且在实施过程中可能会遇到很多问题，例如数据的一致性问题、延迟问题、故障恢复问题等。因此，在实际操作中需要慎重考虑和细心实施。

1. 主从复制简介

主从复制是一种数据备份技术，用于把一台MySQL数据库服务器上的数据实时复制到其他MySQL服务器上。它的主要作用是进行实时数据备份，并且可以对所有从服务器进行负载均衡。
主从复制的过程大致如下：主服务器上的所有数据变动（如插入、删除、更新等操作）都会被记录到二进制日志（Binary Log）中，这些日志被称为二进制事件。从服务器会启动一个I/O线程，它会连接到主服务器，读取主服务器上的二进制日志中的事件，并将这些事件写入到从服务器的中继日志（Relay Log）中。从服务器还会启动一个SQL线程，它会读取中继日志中的事件，并执行这些事件，以此实现和主服务器的数据同步。

2. 主从复制的工作流程

1. 主服务器的数据更改：在主服务器上对数据进行更改后，这些更改会被记录在二进制日志中，这个过程由Binlog线程完成。

2. 从服务器的I/O线程：从服务器的I/O线程连接主服务器，从二进制日志中读取所有未同步的数据，并把这些数据保存到自己的中继日志中。

3. 从服务器的SQL线程：接着，从服务器上的SQL线程读取中继日志，重放其中的SQL语句，从而把中继日志中的数据更新到从服务器的数据库。
   

主从复制过程中的日志文件作用（Binary Log）和中继日志（Relay Log）

1. 二进制日志（Binary Log）是主服务器上的一种日志文件，记录了所有改变了数据库数据的SQL语句（如INSERT、UPDATE、DELETE等）。每当主服务器上执行一次事务，都会在二进制日志中添加一条记录。二进制日志既可以用于主从复制，也可以用于数据恢复。

2. 中继日志（Relay Log）是从服务器上的一种日志文件，其内容来自主服务器的二进制日志。从服务器上的I/O线程负责从主服务器读取二进制日志中的事件，并将这些事件写入到中继日志中。然后，从服务器上的SQL线程负责读取并执行中继日志中的事件，以此实现和主服务器的数据同步。

还有一些其他类型的日志，错误日志（Error Log）、慢查询日志（Slow Query Log）等，它们在主从复制中起到的作用和重要性相对较小。

3. MySQL主从复制的配置

1. 在主服务器上配置MySQL，打开主服务器的MySQL配置文件my.cnf（根据 的系统，可能在/etc/mysql/my.cnf, /etc/my.cnf,或者 /usr/local/mysql/etc/my.cnf） 添加或者更改以下配置：

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

server-id需要设置为一个唯一的数字，log_bin则是定义了二进制日志文件的位置。

2. 重启MySQL服务以应用新的配置，

service mysql restart

3. 在主服务器上创建一个用于复制的用户，并给它赋予需要的权限：

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

4. 查看主服务器的状态：

mysql> SHOW MASTER STATUS;

执行 SHOW MASTER STATUS; 命令，可以看到类似以下的输出：

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      107 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

这里的File（mysql-bin.000001）和Position（107）是在设置从服务器时需要用到的重要信息，需要记录下这两个值。
记下File和Position的值，这将在配置从服务器时用到。

5. 接着，在从服务器上也需要进行配置，编辑my.cnf文件，添加或者更改以下配置：

[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log

这里的server-id需要和主服务器的不同。

6. 重启从服务器的MySQL服务。

7. 在从服务器上设置复制，使用之前在主服务器上创建的用户，以及记下的File和Position,分别填写到MASTER_LOG_FILE和MASTER_LOG_POS

mysql> CHANGE MASTER TO MASTER_HOST='master_host_name',-> MASTER_USER='repl',-> MASTER_PASSWORD='password',-> MASTER_LOG_FILE='recorded_log_file_name',-> MASTER_LOG_POS=recorded_log_position;

8. 开始复制：

mysql> START SLAVE;

如果失败情况下有时会输出
如果未正确配置从服务器，可能会看到如下错误消息：

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

如果想查看从服务器的状态以确认是否已经成功启动，可以执行SHOW SLAVE STATUS;命令。这将显示出从服务器的各种状态信息。
9. 使用下面的命令检查从服务器的状态，确保复制在正常运行：

mysql> SHOW SLAVE STATUS\G;

如果配置正确，且主从复制已经正常运行，那么 应该能看到"Slave_IO_Running: Yes"和"Slave_SQL_Running: Yes"。

成功的结果示例：

mysql> SHOW SLAVE STATUS;
+------------------+-------------+-------------+-------------+---------------+------------------+-------------------+-------------------+...
| Slave_IO_State   | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File    |...
+------------------+-------------+-------------+-------------+---------------+------------------+-------------------+-------------------+...
| Waiting for master to send event | localhost | root        | 3306          | 60              | mysql-bin.000002  | 323              | mysq...
+------------------+-------------+-------------+-------------+---------------+------------------+-------------------+-------------------+...
1 row in set (0.00 sec)

执行的结果中。IO线程正在等待主服务器发送事件，主服务器是运行在本地的MySQL服务器，连接主服务器的用户名是root，端口是3306，如果连接失败，会每60秒重试一次，主服务器的二进制日志文件名是mysql-bin.000002，从主服务器的二进制日志文件读取的位置是323，从服务器的中继日志文件名是mysql-relay-bin.000001。

SHOW SLAVE STATUS;命令会返回一行由复制从服务器状态变量组成的信息

• Slave_IO_State: slave IO线程当前的状态
  Slave_IO_State列表示复制I/O线程的状态，这里是几种常见的状态：

1. Connecting to master：表示正在连接到主服务器。
2. Waiting for master to send event：表示连接已经建立，正在等待主服务器发送二进制日志事件。
3. Reading event from the master：表示正在从主服务器读取一个二进制日志事件。
4. Waiting to reconnect after a failed master event read：表示读取主服务器的二进制日志事件失败，正在等待重试连接。
5. Waiting for the slave SQL thread to free enough relay log space：表示等待复制SQL线程释放足够的中继日志空间。
6. Queueing master event to the relay log：表示将从主服务器读取的事件写入中继日志。
7. Waiting for master to send event：表示正在等待新的事件。
8. Checking master version：检查主服务器的版本。
9. Registering slave on master：在主服务器上注册从服务器。
10. Requesting binlog dump：请求主服务器发送二进制日志。
11. Waiting to reconnect after a failed binlog dump request：在请求二进制日志失败后，等待重新连接。
12. Finished reading one binlog; switching to next binlog：完成读取一个二进制日志，切换到下一个二进制日志。

• Master_Host: 主服务器的主机名或IP
• Master_User: 连接主服务器的用户名
• Master_Port: 主服务器的端口
• Connect_Retry: 如果连接失败，重试的间隔（秒）
• Master_Log_File: 主服务器的二进制日志文件名
• Read_Master_Log_Pos: 从主服务器的二进制日志文件中读取的位置
• Relay_Log_File: 从服务器的中继日志文件名
• Relay_Log_Pos: 从服务器的中继日志文件读取的位置
• Relay_Master_Log_File: 中继日志中的主服务器二进制日志文件名
• Slave_IO_Running: IO线程是否正在运行
• Slave_SQL_Running: SQL线程是否正在运行
• Replicate_Do_DB: 要复制的数据库，如果没有指定任何数据库，该项为空
• Replicate_Ignore_DB: 要忽略的数据库，如果没有指定任何数据库，该项为空
• Last_Errno: 最后一个错误的错误号
• Last_Error: 最后一个错误的错误信息

4. 参考资料

1. 官网 MySQL 8.0 Reference Manual -17 Replication：https://dev.mysql.com/doc/refman/8.0/en/replication.html

2. "SHOW SLAVE STATUS Syntax"部分，对Slave_IO_State进行了详细解释：https://dev.mysql.com/doc/refman/8.0/en/show-slave-status.html