MySQL 数据库主从复制（Master-Slave Replication）是一种常见的数据同步解决方案，它能够帮助我们实现数据的高可用性和负载均衡。在这篇文章中，我们将详细介绍如何配置 MySQL 主从复制，并分享一些常见的故障处理方法。

为什么选择主从复制？

优点

  1. 高可用性：即使主数据库发生故障，从数据库也可以提供读服务，确保业务的连续性。
  2. 负载均衡：读写分离，将读操作分散到多个从数据库上，提升系统性能。
  3. 数据备份：从数据库可以作为主数据库的实时备份，防止数据丢失。
  4. 灾难恢复：在灾难发生时，可以快速切换到从数据库，恢复业务。

主从复制的配置步骤

1. 创建复制用户

首先，在主数据库上创建一个专门用于复制的用户，并授予 REPLICATION SLAVE 权限：

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

2. 获取主数据库的二进制日志信息

在主数据库上锁定表并记录当前的二进制日志文件名和位置：

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

SHOW MASTER STATUS 命令会输出类似以下内容：

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+

记录下 File 和 Position 的值，然后解锁表：

UNLOCK TABLES;

3. 配置主数据库

在主数据库的配置文件 my.cnf 中启用二进制日志（binlog）：

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

4. 配置从数据库

在从数据库的配置文件 my.cnf 中设置唯一的 server-id：

[mysqld]
log_bin = mysql-bin
server-id=2

5. 启动复制

在从数据库中执行以下命令，指定主数据库的连接信息：

CHANGE MASTER TO MASTER_HOST='master_host', 
MASTER_USER='replication_user', 
MASTER_PASSWORD='replication_password', 
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=107;
START SLAVE;

6. 验证复制状态

在从数据库中运行以下命令，检查复制状态：

SHOW SLAVE STATUS\G;

确保 Slave_IO_Running 和 Slave_SQL_Running 均为 Yes，并且没有错误。

7.进阶选项-配置多源复制通道

在 MySQL 5.7.6 及以上版本中，可以使用多源复制（multi-source replication），即一个从数据库实例可以同时从多个主数据库实例接收数据。每个主数据库实例都通过一个独立的复制通道（channel）来区分。以下是详细配置步骤：

1. 创建复制用户

在从数据库中，为每个主数据库配置一个独立的复制通道：

在每个主数据库上创建一个专门用于复制的用户，并授予 REPLICATION SLAVE 权限。

2. 获取主数据库的二进制日志信息

在每个主数据库上锁定表并记录当前的二进制日志文件名和位置。

3. 配置从数据库

在从数据库的配置文件 my.cnf 中设置唯一的 server-id 并启用二进制日志：

[mysqld]
server-id=100
log-bin=mysql-bin
relay-log=relay-bin

4. 配置多源复制通道

在从数据库中，为每个主数据库配置一个独立的复制通道：

CHANGE MASTER TO MASTER_HOST='master1_host', 
MASTER_USER='replication_user', 
MASTER_PASSWORD='replication_password', 
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=107 
FOR CHANNEL 'channel1';

CHANGE MASTER TO MASTER_HOST='master2_host', 
MASTER_USER='replication_user', 
MASTER_PASSWORD='replication_password', 
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', 
MASTER_LOG_POS=200 
FOR CHANNEL 'channel2';

START SLAVE FOR CHANNEL 'channel1';
START SLAVE FOR CHANNEL 'channel2';

5. 验证复制状态

在从数据库中运行以下命令，检查每个通道的复制状态：

SHOW SLAVE STATUS FOR CHANNEL 'channel1'\G;
SHOW SLAVE STATUS FOR CHANNEL 'channel2'\G;

常见故障处理

1. 复制延迟

问题描述：从数据库的延迟较大，导致数据不同步。

解决方案：

  1. 检查网络延迟和带宽。
  2. 优化主数据库的写入性能，减少大事务。
  3. 使用 pt-heartbeat 工具监控和分析复制延迟。

2. 复制中断

问题描述：从数据库的复制中断，SHOW SLAVE STATUS 显示错误信息。

解决方案：

  1. 检查网络连接，确保主从数据库之间的网络畅通。
  2. 查看 Last_IO_Error 和 Last_SQL_Error 信息，定位错误原因。
  3. 使用 START SLAVE 和 STOP SLAVE 命令重新启动复制。多源复制则使用 START SLAVE FOR CHANNEL 'channel_name' 和 STOP SLAVE FOR CHANNEL 'channel_name' 命令重新启动复制。
  4. 如果复制日志丢失或损坏，可以使用 CHANGE MASTER TO 命令重新配置复制。

3. 数据不一致

问题描述：主从数据库的数据不一致。

解决方案：

  1. 使用 pt-table-checksum 工具检查数据一致性。
  2. 使用 pt-table-sync 工具同步数据。
  3. 检查和修复导致数据不一致的应用逻辑或配置问题。

实际举例

1.查看一个主库

MySQL 数据库同步之主从复制实践及故障处理_数据库

这个主库的配置：

server_id=1
#作为主服务器master时
log_bin=mysql-bin
max_binlog_size = 500M
expire_logs_days=2
binlog_do_db=ji*****ng

binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema
binlog_ignore_db=sys

主库，指定哪些数据库可同步，哪些数据库忽略不要同步：binlog_do_db=某数据库名，binlog_ignore_db=mysql 等。

2.查看一个从库

MySQL 数据库同步之主从复制实践及故障处理_mysql_02

MySQL 数据库同步之主从复制实践及故障处理_主从复制_03

看到  Slave_IO_Running:Yes,  Slave_SQL_Running:Yes;

Slave_IO_State:Waiting for master to send event,

Slave_SQL_Runngin_State: Slave has read all relay log; waiting for more...

这是正常的。

如果不是Slave_SQL_Running：No，Slave_SQL_Runngin_State：将显示错误信息。

从库的配置：

server-id = 1
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 7
# 同步
log-slave-updates = true
skip-slave-start = true
max_binlog_size = 100M
log-bin = /data/mysql/mysql-bin
relay-log = /data/mysql/relay-log
relay-log-index = /data/mysql/relay-log-index
relay-log-info-file = /data/mysql/relay-log.info
master-info-repository = table
relay-log-info-repository = table

# 只同步指定表
replicate-do-table=tpmall.tp_banner
replicate-do-table=tpmall.tp_article
replicate-do-table=tpmall.tp_category
replicate-do-table=tpmall.tp_video

只同步指定表时，需要：

master-info-repository = table
relay-log-info-repository = table
  1. 当你的从库设置了白名单时，出方向，入方向，都要增加主库的IP地址。

常见的错误实例：

以某个名为tpmall数据库为例, 同步停止，报错为：

Could not execute Write_rows event on table mall.video;
Duplicate entry 'PC-018-0003' for key 'PRIMARY', Error_code: 1062;
handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.003927, end_log_pos 450807777

这句的意思是，从库已经有这条记录了，又是主键，所以加不进去，只需删掉从库这一条。再继续同步。

# 调整错误后
start slave for channel 'tpmall';

# 在mysql工具里，用下面几句，可以跳过这一个错误。
stop slave for channel 'tpmall';
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave for channel 'tpmall';
show slave status for channel 'tpmall';

结语

MySQL 主从复制是一种强大且灵活的数据库同步解决方案，通过合理的配置和监控，可以实现高可用性、负载均衡和数据备份。然而，在实际应用中，我们需要根据具体的业务需求进行优化，并及时处理各种可能出现的故障。

希望这篇文章能帮助大家更好地理解和实施 MySQL 主从复制。如果你有任何问题或建议，欢迎在评论区交流。