mysql笔记：24. 主从同步环境搭建

MySQL的主从同步（Master-Slave Replication）是一个常用的数据备份和恢复策略，它允许在两个或多个数据库服务器之间实现数据同步。这种方案不仅可以提高数据备份的效率，还可以实现读写分离，从而提高整个系统的性能和可靠性。其中，主动发送数据的MySQL Server称为主（Master），被动接收数据的MySQL Server称为从（Slave）。

一、主从同步的基本原理

MySQL主从同步是基于二进制日志（binlog）实现的。主数据库将数据变更记录在binlog中，而从数据库则通过读取binlog来获取这些变更，并将它们应用到自己的数据库中。这样，从数据库就可以保持与主数据库的数据一致性。

二、基于日志点的主从同步

基于日志点的复制是指，Slave明确指定从Master中某个日志文件中的某个位置开始复制，从该日志点之后的数据变更都可以复制到Slave。
配置步骤如下：

1. Master与Slave中都配置server_id，并指定使用二进制日志。
2. Master中创建供Slave复制时使用的用户，并为其指定权限。
3. Master中查看当前二进制文件的信息，包括文件名、日志位置。
4. Slave指定Master具体信息，包括IP、端口、用户、复制的起始位置。
5. Slave开启复制。

1. 环境准备

• 两台服务器
  一台作为主服务器（Master），一台作为从服务器（Slave）
• 均安装MySQL8.0
• 均安装Navicat[可选项]
  使用数据库连接工具Navicat可以便捷的连接数据库，从而方便操作。

2. 配置主服务器（Master）

编辑主服务器的MySQL配置文件，通常位于/etc/mysql/mysql.conf.d/mysqld.cnf或/etc/my.cnf，添加下列配置项：

[mysqld]
server-id=1
log-bin=mysql-binlog
binlog-do-db=your-database-name

• server-id：MySQL服务器的唯一ID，主从服务器ID不能相同
• log-bin：启用二进制日志，记录所有数据库的修改信息
• binlog-do-db：指定需要同步的数据库名（可选配置）

重启MySQL服务使配置生效。
然后，登录MySQL并执行以下命令：

# 查看配置server_id的值，确认配置已生效
SHOW VARIABLES LIKE 'server_id';

# 在Master上创建复制用户并授权
CREATE USER 'replication_user'@'%' identified by 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
# 刷新用户权限信息
FLUSH PRIVILEGES;

# 查看Master状态
SHOW MASTER STATUS;

3. 配置从服务器（Slave）

编辑从服务器的MySQL配置文件，添加下列配置项：

[mysqld]  
server-id=2
relay-log=mysql-relay-bin
replicate-do-db=your_database_name

• relay-log：中继日志，从服务器用于存储接收到的二进制日志事件。
• replicate-do-db：指定需要同步的数据库名（可选配置）。

重启MySQL服务以使配置生效。然后，登录MySQL并执行以下命令：

CHANGE MASTER TO   
  MASTER_HOST='master_server_ip',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='recorded_log_file',
  MASTER_LOG_POS=recorded_log_pos;

# 开始复制
START SLAVE; 

# 查看从服务器状态
SHOW SLAVE STATUS\G;

• CHANGE MASTER TO：配置从服务器连接到主服务器的信息。
• START SLAVE：启动从服务器的复制进程。
• SHOW SLAVE STATUS：查看从服务器状态。确保Slave_IO_Running和Slave_SQL_Running的值都是Yes。

4. 测试配置

在主服务器上创建一个新的表并插入一些数据，然后检查从服务器是否已同步这些更改。

三、基于GTID的主从复制

基于日志点的复制是一种非常传统的方式，技术成熟。但也有不足，主要是因为日志点的确定并不方便。
例如：在一个Master、多个Slave的结构下，如果有多个Slave复制失败了，那么每个Slave都需要确定自己所需要的日志位置，因为Slave之间复制的进度是有差异的。如果Master出现故障，需要从多个Slave中选取复制进度最快的那个，将其作为新的Master。这时，其他Slave都需要调整自己的复制点，以新的Master为标准。
MySQL5.6引入全局事务ID，即Global Transaction ID。形式如下：

server_uuid:transaction_id

• server_uuid：MySQL Server的ID，在服务启动时自动生成，此ID使用UUID形式。
• transaction_id：事务ID，它是严格递增的。

通过GTID就可以知道此事务的ID标识，以及来自哪个MySQL Server。
使用GTID方式复制，Slave就不用关心复制的日志点了，只要是自己没复制过的事务，直接复制即可。即便复制中断再次恢复后，Slave也可以自动找到Master中的正确复制位置。
具体配置步骤：

1. Master与Slave中都需要配置，如指定server_id、打开gtid_mode
2. Master中创建Slave复制时使用的用户，并指定权限。
3. Slave指定 Master具体信息，包括IP、端口、用户、复制的起始点
4. Slave开启复制。

1. 环境准备

• 两台服务器
  一台作为主服务器（Master），一台作为从服务器（Slave）
• 均安装MySQL8.0
• 均安装Navicat[可选项]
  使用数据库连接工具Navicat可以便捷的连接数据库，从而方便操作。

2. 修改Master配置文件

打开配置文件my.cnf，在[mysqld]部分添加配置：

[mysqld]
server-id=1
log-bin=mysql-bin
gtid_mode=on
enforce_gtid_consistency=true

重启MySQL服务使配置生效。
然后，登录MySQL并执行以下命令：

# 查看配置gtid_mode的值，确认配置已生效
SHOW VARIABLES LIKE 'gtid_mode';

# 在Master上创建复制用户并授权
CREATE USER 'replication_user'@'%' identified by 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
# 刷新用户权限信息
FLUSH PRIVILEGES;

# 查看Master状态
SHOW MASTER STATUS;

3. 修改Slave配置文件

打开配置文件my.cnf，在[mysqld]部分添加配置：

server_id=2
log-bin=mysql-bin
gtid_mode=on
enforce_gtid_consistency=true
master_info_repository=TABLE
relay_log_info_repository=TABLE

4. Slave指定Master

在Slave中执行命令以指定Master信息

CHANGE MASTER TO   
  MASTER_HOST='master_server_ip',
  MASTER_PORT=port,
  MASTER_USER='replication_user',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION=1 FOR CHANNEL 'master-1';

# 开始复制
START SLAVE; 

# 查看从服务器状态
SHOW SLAVE STATUS\G;

四、复制延时优化和监控

1. 复制延时优化

主从复制最重要的问题是延时，Master中新数据变更同步到Slave中一定会有延时，无论是多快的网络、性能多好的服务器，延时只能缩短，无法消除。尽量缩短延时是复制优化的核心目标。要缩小延时，需要先梳理一下复制过程中的关键点，从中分析哪些地方可以优化。
复制过程有3个关键时间点：

• Master写二进制日志的时间
• 二进制日志传输的时间
• Slave回放日志，写入本地数据库的时间

下面分析一下各个时间点有哪些优化方式：

1. 优化Master写二进制日志
   基本思路是减少事务的执行时间，尽量把大事务转为小事务。例如，一次要更新10000条数据，需要执行1分钟，那么MySQL就需要在全部更新完成后才提交事务，写入二进制日志。
2. 优化二进制日志传输
   二进制日志需要传输到Slave，然后写入Slave的中继日志，这个过程涉及网络I/O和磁盘I/O，优化的思路是让需要传输和写入的内容变少，更少的内容才能传输的更快、写入的更快。
   具体就是结合业务需求，分析一下数据库，看能否减少复制的数据库。是否可以把不需要复制的库忽略掉？
3. 优化Slave中的SQL回放
   默认只有一个线程负责SQL回放。在MySQL5.7中支持了多线程复制方式，所以可以设置多线程复制提升SQL回放速度。

2. 复制延时监控

Slave与Master之间的延时具体是多长时间呢？可以通过工具进行监控。pt-heartbeat就是一款非常成熟的复制状态监控工具，支持MySQL和PostgreSQL。
pt-heartbeat的监控思路很简单，在Master中创建一个heartbeat心跳表，然后定期更新其中的时间戳。此表会被复制到Slave，Slave使用复制完成的时间减去表中的时间戳，就可以得出复制的延时了。

CREATE TABLE heartbeat (
    ts varchar(26) NOT NULL,
    server_id int unsigned NOT NULL PRIMARY KEY,
    file varchar(255) DEFAULT NULL, -- SHOW MASTER STATUS
    position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
    relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
    exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
);

pt-heartbeat计算的是复制结果时间差，与具体的复制方式无关。它支持任意级别的复制结构，表中记录了server_id，可以计算出任意两个MySQL Server之间的延时。

pt-heartbeat严格依赖于时间，所以各MySQL Server的时间设置一定要一致，可以设置为时间同步。

1. Master中创建heartbeat心跳表

pt-heartbeat --user=root --ask-pass --host=[mysql ip] --create-table 
    -D masterDatabaseName --interval=1 --update --replace --daemonize

• --create-table -D masterDatabaseName：表示在数据库masterDatabaseName中创建heartbeat表
• --interval=1：表示更新的时间间隔是1秒，最小的单位是0.01s

2. 对Slave开启监控

执行pt-heartbeat命令，指定Slave的连接信息、目标数据库与数据表即可开启监控。

pt-heartbeat -h Slave_IP --user=root --ask-pass -D masterDatabaseName --table=heartbeat --monitor

监控结果：

0.01s [ 0.00s, 0.01s, 0.00s ]
...

每一行表示一次监控结果，括号前面是当前的延时情况，括号内部的三个数值分别表示1分钟、5分钟、15分钟的平时延时情况。

五、常见故障

1. 主从服务器上的MySQL版本不一致导致失败？

解决：卸载并重新安装相同版本的MySQL服务器。

2. Slave_IO_Running状态异常？

解决：检查主服务器防火墙配置，放行从服务器相关连接请求或直接关闭主服务器的防火墙（仅限开发环境）。

3. 配置正确，但同步失败？

解决：检查bin-log及relay-log文件及其目录的权限，确保配置权限适当。

4. 断电重启后同步中断？

解决：检查上述配置及服务状态，修复异常项。