Post

MySQL8.0-Replication简介和配置

Posted
By Bao Tingyu
14 min read

本文讲解MySQL 8.0复制，来源主要参考官方文档MySQL8.0 Replication

概述

MySQL 8.0复制的场景：

  • 可伸缩性：读写分离，分散压力，提高性能
  • 数据安全：从库可在不污染主库数据的前提下做备份操作
  • 分析处理：主库接收数据，从库分析数据不影响主库性能
  • 远距离数据分布

关于复制的解决方案，详见 Replication Solutions

MySQL 8.0支持两种从库记录复制进度的方式：

  1. binlog files and positions
  2. global transaction identifiers(GTID)

MySQL 8.0支持三种复制同步方式：

  1. One-way(Asynchronous) Replication
  2. Semisynchronous Replication
  3. Delayed Replication

MySQL 8.0支持三种复制格式 Replication Formats：

  1. Statement Based Replication (SBR)
  2. Row Based Replication (RBR)
  3. Mixed Based Replication (MBR)

关于复制的配置文件参数，可参考：Replication and Binary Logging Options and Variables

Binlog + Pos

主库把binlog通过网络写入从库的relaylog，然后从库执行relaylog写入从库的binlog，这个过程中主库会把全部binlog变化传给从库（不可以指定事件），而从库可以指定收到的事件执行。

每个从库会记录主库中已经执行的binlog filename+position.

创建主节点User

首先为主节点创建一个用户共从节点链接使用：

1
2
3

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

另外主节点验证从节点的方式在8.0版本之前用的mysql_native_password，之后用的caching_sha2_password 。以上代码直接用会报错：

1
2

 Last_IO_Errno: 2061
  Last_IO_Error: error connecting to master 'repl@192.168.64.133:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

需使用安全连接或简单地创建用户或更改用户密码的验证方式：

1
2
3
4

// 创建时
mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED WITH [mysql_native_password | caching_sha2_password] BY 'password';
// 更改时
mysql> ALTER USER 'repl'@'%.example.com' IDENTIFIED WITH [mysql_native_password | caching_sha2_password] BY 'password';

详情见：

server_id和server_uuid

主从节点地 server_id和server_uuid必须不同

  • server_id默认都为1，可以在my.cnf文件中添加server_id=1，主从节点间必须不同
  • server_uuid默认会从datadir的auto.cnf文件中读取，读取不到则自动创建：
    1
2

    [auto]
server-uuid=db834596-844e-11ed-b00b-000c290264b9

    如果uuid相同会报如下错误：

    1
2
3

    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后通过以下指令查看：

    1
2

    select @@server_id;
select @@server_uuid;

    主库my.cnf文件示例如下： ```sql [mysqld] server_id = 1

    logbin文件名称 如：binlog.000018

    log_bin=binlog

    redolog刷盘策略

    innodb_flush_log_at_trx_commit=1

    binlog刷盘策略

    sync_binlog=1

datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

1
2
3
4
5

从库`my.cnf`文件示例如下：
```sql
# 其他配置和主库类似
# 添加从库只读
read_only=on

启动复制

从节点记录复制进度有两种方式：

  • binlog filename + position
  • GTID (组复制使用)

这里先使用 binlog filename + position方式。

首先登录主节点mysql查看最新数据binlog信息，运行：

1
2
3
4
5
6
7
8
9
10
11
12
13

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status\G
*************************** 1. row ***************************
			// filename
             File: binlog.000016
         // pos
         Position: 157
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

可以在datadir中查看其他binlog的具体名称。

重启主从mysql，确保配置正常生效。

登录从节点mysql启动复制：

  • MySQL 8.0.23 之前：CHANGE MASTER TO
  • 之后： CHANGE REPLICATION SOURCE TO

示例如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

// 先停止复制线程
mysql> stop replica;
Query OK, 0 rows affected (0.00 sec)
// 配置主节点
mysql> change replication source to
    -> SOURCE_HOST='192.168.64.133',
    -> SOURCE_USER='repl',
    -> SOURCE_PASSWORD='password',
    -> SOURCE_LOG_FILE='binlog.000015',
    -> SOURCE_LOG_POS=157;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
// 配置过滤器，只监听test数据库，更多filter操作参见
// https://dev.mysql.com/doc/refman/8.0/en/change-replication-filter.html
mysql > change replication filter replicate_do_db=(test);
Query OK, 0 rows affected (0.00 sec)
// 开启复制
mysql> start replica;
Query OK, 0 rows affected (0.00 sec)
// 
mysql> show replica status\G
*************************** 1. row ***************************

 Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.64.133
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000016
          Read_Source_Log_Pos: 449
               Relay_Log_File: 192-relay-bin.000008
                Relay_Log_Pos: 659
        Relay_Source_Log_File: binlog.000016
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
			...
				// 没有报错，正常运行
               Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
			...

关于启动参数和配置详情，可参考Replication and Binary Logging Options and Variables

GTID

每个事务被提交到主节点和从节点的信息都可以被定位跟踪，这意味着当发生故障转移时，大大简化了新主节点的任务。GTID-based replication基于事务，只要主节点的提交的事务在从节点也提交了，那么就能够保证主从一致性

GTID格式和存储

GTID是由 主节点创建 关于被提交事务 的全局唯一标识，而且是单调递增、无间隔的。如果之前由binlog写入失败或者事务被过滤，则当前不会为主库分配GTID。 格式：GTID = source_id:transaction_id

从节点保持这和主节点提交事务相同的GTID，从节点会在复制的事务执行前记录GTID，不管这个事务是否执行成功，这个记录是指写入系统变量和mysql.gtid_executed这个表中（或暂存与binlog中，之后等待写入这个表中），表示例如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CREATE TABLE gtid_executed (
    source_uuid CHAR(36) NOT NULL,
    interval_start BIGINT(20) NOT NULL,
    interval_end BIGINT(20) NOT NULL,
    PRIMARY KEY (source_uuid, interval_start)
)


// 压缩前
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
|--------------------------------------+----------------+--------------|
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 37             | 37           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 38             | 38           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 39             | 39           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 40             | 40           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 41             | 41           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 42             | 42           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 43             | 43           |
...

// 压缩后
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
|--------------------------------------+----------------+--------------|
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 37             | 43           |
...

Warning As with other MySQL system tables, do not attempt to create or modify this table yourself.

GTID生命周期

  1. 一个事务在主节点被提交，这个事务被分配一个GTID，由主节点的server_uuid和可用的最小非零事务序列号组成。GTID写入主节点binlog，然后马上写入事务binlog（这两次写入可认为原子操作）。如果事务没有写入binlog（比如发生了过滤，或者事务只读），则事务不会被分配GTID。
  2. 事务提交时，GTID的binlog提交在事务binlog的开头处（Gtid_log_event），而gtid的binlog写入后，会在binlog文件大小达到最大或主机关机时将binlog文件里的gtid信息写入mysql.gtid_executed表中。
  3. 当一个事务被分配GTID时，当前的GTID会被写入到the set of GTIDs： gtid_executed这个系统变量中（@@GLOBAL.gtid_executed）。由2和3可知，mysql.gtid_executed表中不一定会记录最新的gtid（最新的在binlog中，可能还未提取），而gtid_executed系统变量会记录最新的；当且仅当binlog关闭时，gtid会直接写入mysql.gtid_executed表中，然而主库的binlog必须开启，从库可以关闭binlog。
  4. 当主库的binlog传输到从节点的relaylog后，从节点读取主库传来的GTID，并设置系统变量gtid_next标识下一个gtid值。
  5. 在处理传来的事务之前会先读取并校验gtid（和老的gtid_next对比），确保这个gtid没有被用过，且当前只有一个session在读。所以这个从库处理gtid属于并发控制的过程。gtid_owned系统变量记录了当前正在使用GTID的线程id。如果当前gtid已经被提交过了，那么这个事务会被忽略。
  6. 如果GTID没用过，那个从库不会创建新的GTID而是直接使用gtid_next系统变量的值作为GTID（在这之前已经赋予gtid_next主库传来的GTID，即从库当前事务的GTID等于主库的GTID）。
  7. 如果从库开启了binlog，那么同主库，在写入事务的binlog开头写入GTID的binlog(Gtid_log_event)；如果从库没有开启binlog，那么会直接持久化到mysql.gtid_executed表中，这种情况下这个表记录了最新的gtid.

GTID自动定位

开启GTID复制，需要在从库设置主节点时添加SOURCE_AUTO_POSITION 或 MASTER_AUTO_POSITION. 如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

// binlogfile + pos
mysql> change replication source to
    -> SOURCE_HOST='192.168.64.133',
    -> SOURCE_USER='repl',
    -> SOURCE_PASSWORD='password',
    // 
    -> SOURCE_LOG_FILE='binlog.000015',
    //
    -> SOURCE_LOG_POS=157;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

// GTID
mysql> change replication source to
    -> SOURCE_HOST='192.168.64.133',
    -> SOURCE_USER='repl',
    -> SOURCE_PASSWORD='password',
    //
	-> SOURCE_AUTO_POSITION=1
Query OK, 0 rows affected, 2 warnings (0.00 sec)

GTID配置

Step 1: Synchronize the servers

先让所有节点只读，用于同步数据:

1

mysql> SET @@GLOBAL.read_only = ON;

Important It is important to understand that logs containing transactions without GTIDs cannot be used on servers where GTIDs are enabled. Before proceeding, you must be sure that transactions without GTIDs do not exist anywhere in the topology.

Step 2: Stop both servers

1

$> mysqladmin -uusername -p shutdown

Step 3: Start both servers with GTIDs enabled

在配置文件my.cnf中写入：

1
2

gtid_mode=ON
enforce-gtid-consistency=ON

Step 4: Configure the replica to use GTID-based auto-positioning

1
2
3
4
5
6

mysql> CHANGE REPLICATION SOURCE TO
     >     SOURCE_HOST = host,
     >     SOURCE_PORT = port,
     >     SOURCE_USER = user,
     >     SOURCE_PASSWORD = password,
     >     SOURCE_AUTO_POSITION = 1;

Step 5: Start the replica and disable read-only mode

1
2

mysql> START REPLICA;
mysql> SET @@GLOBAL.read_only = OFF;

Restoring GTID mode replicas

When restoring a replica in a GTID based replication setup that has encountered an error, injecting an empty transaction may not solve the problem because an event does not have a GTID.

Use mysqlbinlog to find the next transaction, which is probably the first transaction in the next log file after the event. Copy everything up to the COMMIT for that transaction, being sure to include the SET @@SESSION.gtid_next. Even if you are not using row-based replication, you can still run binary log row events in the command line client.

Stop the replica and run the transaction you copied. The mysqlbinlog output sets the delimiter to /*!*/;, so set it back:

1
2
3
4
5
6
7
8
9
10

mysql> DELIMITER ;
Restart replication from the correct position automatically:

mysql> SET GTID_NEXT=automatic;
mysql> RESET SLAVE;
mysql> START SLAVE;
Or from MySQL 8.0.22:
mysql> SET GTID_NEXT=automatic;
mysql> RESET REPLICA;
mysql> START REPLICA;

GTID限制

  1. 涉及非事务性存储引擎 非事务的存储引擎（如 MyISAM）的表更新无法通过GTID的方式复制到事务的存储引擎（如InnoDB）的表中。原因是：非事务的表更新会分配多个GTID会造成事务的表存在GTID不匹配。

  2. CREATE TABLE … SELECT statements MySQL 8.0.21之前， CREATE TABLE ... SELECT不能用GTID，之后可以。

  3. Temporary tables 详见Restrictions on Replication with GTIDs
  4. Preventing execution of unsupported statements
  5. Skipping transactions
  6. Ignoring servers
  7. GTID mode and mysql_upgrade

GTID配置参数

Global Transaction ID System Variables

编程, MySQL
mysql
This post is licensed under CC BY 4.0 by the author.