在本章中,我们将介绍以下配方:
如第 6 章、二进制日志中所述,复制可以将来自一个 MySQL 数据库服务器(主服务器)的数据复制到一个或多个 MySQL 数据库服务器(从服务器)。默认情况下,复制是异步的;从机无需永久连接即可从主机接收更新。您可以配置为复制数据库中的所有数据库、选定数据库,甚至选定表。
在本章中,您将学习如何设置传统复制;复制选定的数据库和表;并设置多源复制、链复制、延迟复制和半同步复制。
在高层,复制是这样工作的:在服务器(master)上执行的所有 DDL 和 DML 语句都记录到二进制日志中,由连接到它的服务器(称为slaves)拉取。二进制日志被简单地复制到从机,并保存为中继日志。这个过程由一个名为IO 线程的线程负责。还有一个称为SQL 线程的线程,它按顺序执行中继日志中的语句。
本博客非常清楚地解释了复制的工作原理:
https://www.percona.com/blog/2013/01/09/how-does-mysql-replication-really-work/
复制的优点是(摘自手册,见https://dev.mysql.com/doc/refman/8.0/en/replication.html :
横向扩展解决方案:在多个从机之间分散负载以提高性能。在此环境中,所有写入和更新都必须在主服务器上进行。但是,读取可以在一个或多个从机上进行。此模型可以提高写入性能(因为主设备专用于更新),同时在越来越多的从设备上显著提高读取速度。
数据安全:由于数据被复制到从机,并且从机可以暂停复制过程,因此可以在从机上运行备份服务,而不会损坏相应的主数据。
分析:可以在主机上创建实时数据,而信息分析可以在从机上进行,不会影响主机的性能。
远程数据分发:您可以使用复制创建数据的本地副本,供远程站点使用,而无需永久访问主机。
有许多复制拓扑。其中一些是传统的主从复制、链复制、主-主复制、多源复制等。
传统复制涉及一个主设备和多个从设备。
链复制指一台服务器从另一台服务器复制,而另一台服务器又从另一台服务器复制。中间服务器称为中继主机(主机-->中继主机-->从机)。
这主要用于在两个数据中心之间设置复制。主主设备及其从设备将位于一个数据中心。辅助主机(中继)从另一个数据中心的主主机复制。其他数据中心的所有从机都是从辅助主机复制的。
主机复制:在这种拓扑结构中,两个主机都接受彼此之间的写入和复制。
多源复制:在这种拓扑结构中,从机将从多个主机而不是一个主机进行复制。
如果要设置链复制,可以按照此处提到的相同步骤,将主机替换为中继主机。
在本节中,将说明单从机的设置。同样的原则也可用于建立链复制。通常,在设置另一个从机时,从该从机获取备份。
大纲:
server_id
CHANGE MASTER TO
命令步骤:
SERVER_ID
。请参阅第 6 章、二进制日志记录,了解如何启用二进制日志记录。
* 主机上的:创建复制用户。从属服务器使用此帐户连接到主服务器:*****```sql mysql> GRANT REPLICATION SLAVE ON .* TO 'binlog_user'@'%' IDENTIFIED BY 'binlog_P@ss12';Query OK, 0 rows affected, 1 warning (0.00 sec)
3. 从机上的**:设置唯一的`SERVER_ID`选项(应该与您在主机上设置的不同):**
**```sql
mysql> SET @@GLOBAL.SERVER_ID = 32;
mysqldump
或mydumper
。无法使用mysqlpump
,因为二进制日志位置不一致。mysqldump
:
shell> mysqldump -h <master_host> -u backup_user --password=<pass> --all-databases --routines --events --single-transaction --master-data > dump.sql
从另一个从机获取备份时,必须通过--slave-dump
选项。mydumper
shell> mydumper -h <master_host> -u backup_user --password=<pass> --use-savepoints --trx-consistency-only --kill-long-queries --outputdir /backups
mysqldump
:
**```sql
shell> mysql -u
`mydumper`:
```sql
shell> myloader --directory=/backups --user=<user> --password=<password> --queries-per-transaction=5000 --threads=8 --overwrite-tables
mysql> CHANGE MASTER TO MASTER_HOST='<master_host>', MASTER_USER='binlog_user', MASTER_PASSWORD='binlog_P@ss12', MASTER_LOG_FILE='<log_file_name>', MASTER_LOG_POS=<position>
mysqldump
:<log_file_name>
和<position>
包含在备份转储文件中。例如:
shell> less dump.sql
--
-- Position to start replication or point-in-time recovery from (the master of this slave)
--
CHANGE MASTER TO MASTER_LOG_FILE='centos7-bin.000001', MASTER_LOG_POS=463;
mydumper
:<log_file_name>
和<position>
存储在元数据文件中:
shell> cat metadata
Started dump at: 2017-08-26 06:26:19
SHOW MASTER STATUS:
Log: server1.000012
Pos: 154122
GTID:
SHOW SLAVE STATUS:
Host: xx.xxx.xxx.xxx
Log: centos7-bin.000001
Pos: 463223
GTID:
Finished dump at: 2017-08-26 06:26:24
如果您从一个从机或主机获取备份以设置另一个从机,则必须使用SHOW SLAVE STATUS
中的位置。如果要设置链复制,可以使用SHOW MASTER STATUS
中的位置。
START SLAVE
命令:mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
*************************** 1\. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xx.xxx.xxx.xxx
Master_User: binlog_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: server1-bin.000001
Read_Master_Log_Pos: 463
Relay_Log_File: server2-relay-bin.000004
Relay_Log_Pos: 322
Relay_Master_Log_File: server1-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 463
Relay_Log_Space: 1957
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 32
Master_UUID: b52ef45a-7ff4-11e7-9091-42010a940003
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
您应该查找显示复制延迟的Seconds_Behind_Master
。如果为0
,则表示从机与主机同步;任何非零值表示延迟的秒数,如果为NULL
,则表示不进行复制。
这个食谱会引起很多人的兴趣,因为我们中的许多人都尝试过这样做。让我们深入了解一下细节。
假设母版为master1
和master2
。
步骤:
master1
和master2
之间设置复制。master2
设为只读:mysql> SET @@GLOBAL.READ_ONLY=ON;
master2
上,检查当前二进制日志坐标。mysql> SHOW MASTER STATUS;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| server1.000017 | 473 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从前面的输出中,您可以从server1.000017
和473
位置在master1
上启动复制。
master1
上执行CHANGE MASTER TO
命令:mysql> CHANGE MASTER TO MASTER_HOST='<master2_host>', MASTER_USER='binlog_user', MASTER_PASSWORD='binlog_P@ss12', MASTER_LOG_FILE='<log_file_name>', MASTER_LOG_POS=<position>
master1
上启动从机:mysql> START SLAVE;
master2
读写,应用程序可以开始向其写入。 mysql> SET @@GLOBAL.READ_ONLY=OFF;
MySQL 多源复制使复制从机能够同时接收来自多个源的事务。多源复制可用于将多个服务器备份到单个服务器、合并表碎片,以及将多个服务器中的数据整合到单个服务器。多源复制在应用事务时不会实现任何冲突检测或解决,如果需要,这些任务将留给应用程序执行。在多源复制拓扑中,从机为其应从中接收事务的每个主机创建一个复制通道。
在本节中,您将学习如何设置具有多个主设备的从设备。此方法与通过通道设置传统复制相同。
假设您正在将server3
设置为server1
和server2
的从属。您需要通过一个通道创建从server1
到server3
的传统复制,并通过另一个通道创建从server2
到server3
的传统复制。要确保从机上的数据一致,请确保复制了不同的数据库集或应用程序处理了冲突。
开始之前,从 server1 进行备份并在server3
上恢复;同样地,从server2
进行备份并在server3
上恢复,如第 9 章、复制所述。
server3
上,将复制存储库从FILE
修改为TABLE
。您可以通过运行以下命令动态更改它:mysql> STOP SLAVE; //If slave is already running
mysql> SET GLOBAL master_info_repository = 'TABLE';
mysql> SET GLOBAL relay_log_info_repository = 'TABLE';
还要在配置文件中进行更改:
shell> sudo vi /etc/my.cnf
[mysqld]
master-info-repository=TABLE
relay-log-info-repository=TABLE
server3
上,通过名为master-1
的通道执行CHANGE MASTER TO
命令,使其成为server1
的从机。你可以给它取任何名字:mysql> CHANGE MASTER TO MASTER_HOST='server1', MASTER_USER='binlog_user', MASTER_PORT=3306, MASTER_PASSWORD='binlog_P@ss12', MASTER_LOG_FILE='server1.000017', MASTER_LOG_POS=788 FOR CHANNEL 'master-1';
server3
上执行CHANGE MASTER TO
命令,使其成为master-2
通道上server2
的从机:mysql> CHANGE MASTER TO MASTER_HOST='server2', MASTER_USER='binlog_user', MASTER_PORT=3306, MASTER_PASSWORD='binlog_P@ss12', MASTER_LOG_FILE='server2.000014', MASTER_LOG_POS=75438 FOR CHANNEL 'master-2';
START SLAVE FOR CHANNEL
语句,如下所示:mysql> START SLAVE FOR CHANNEL 'master-1';
Query OK, 0 rows affected (0.01 sec)
mysql> START SLAVE FOR CHANNEL 'master-2';
Query OK, 0 rows affected (0.00 sec)
SHOW SLAVE STATUS
语句验证从机状态:mysql> SHOW SLAVE STATUS\G
*************************** 1\. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: server1
Master_User: binlog_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: server1.000017
Read_Master_Log_Pos: 788
Relay_Log_File: server3-relay-bin-master@002d1.000002
Relay_Log_Pos: 318
Relay_Master_Log_File: server1.000017
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 788
Relay_Log_Space: 540
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 32
Master_UUID: 7cc7fca7-4deb-11e7-a53e-42010a940002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master-1
Master_TLS_Version:
*************************** 2\. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: server2
Master_User: binlog_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: server2.000014
Read_Master_Log_Pos: 75438
Relay_Log_File: server3-relay-bin-master@002d2.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: server2.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 75438
Relay_Log_Space: 544
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 32
Master_UUID: b52ef45a-7ff4-11e7-9091-42010a940003
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master-2
Master_TLS_Version:
2 rows in set (0.00 sec)
mysql> SHOW SLAVE STATUS FOR CHANNEL 'master-1' \G
mysql> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1\. row ***************************
CHANNEL_NAME: master-1
GROUP_NAME:
SOURCE_UUID: 7cc7fca7-4deb-11e7-a53e-42010a940002
THREAD_ID: 36
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 73
LAST_HEARTBEAT_TIMESTAMP: 2017-09-15 12:42:10.910051
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION:
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION:
QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 2\. row ***************************
CHANNEL_NAME: master-2
GROUP_NAME:
SOURCE_UUID: b52ef45a-7ff4-11e7-9091-42010a940003
THREAD_ID: 38
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 73
LAST_HEARTBEAT_TIMESTAMP: 2017-09-15 12:42:13.986271
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION:
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION:
QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
2 rows in set (0.00 sec)
You can specify all the slave-related commands for a channel by appending FOR CHANNEL 'channel_name'
:
mysql> STOP SLAVE FOR CHANNEL 'master-1';
mysql> RESET SLAVE FOR CHANNEL 'master-2';
您可以控制要复制哪些表或数据库。在主机上,您可以通过使用--binlog-do-db
和--binlog-ignore-db
选项来控制二进制日志记录来控制要记录更改的数据库,如第 6 章、二进制日志记录所述。更好的方法是在从机端进行控制。您可以通过使用--replicate-*
选项或通过创建复制筛选器动态地执行或忽略从主机接收的语句。
要创建筛选器,需要执行CHANGE REPLICATION FILTER
语句。
假设您只想复制db1
和db2
。使用以下语句创建复制筛选器。
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);
请注意,应该在括号内指定所有数据库。
您可以使用REPLICATE_DO_TABLE
指定要复制的表:
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = ('db1.table1');
假设您想对表使用正则表达式;您可以使用REPLICATE_WILD_DO_TABLE
选项:
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.imp%');
您可以使用各种IGNORE
选项提及一些带有正则表达式的数据库或表。
就像您可以选择复制数据库一样,您可以使用REPLICATE_IGNORE_DB
忽略复制中的数据库:
mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (db1, db2);
您可以使用REPLICATE_IGNORE_TABLE
和REPLICATE_WILD_IGNORE_TABLE
选项忽略某些表。REPLICATE_WILD_IGNORE_TABLE
选项允许使用通配符,其中 asREPLICATE_IGNORE_TABLE
只接受完整的表名:
mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = ('db1.table1');
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('db1.new%', 'db2.new%');
You can also set filters for a channel by specifying the channel name:
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (d1) FOR CHANNEL 'master-1';
参见https://dev.mysql.com/doc/refman/8.0/en/change-replication-filter.html 了解复制筛选器的更多详细信息。如果您使用多个过滤器,请参阅https://dev.mysql.com/doc/refman/8.0/en/replication-rules.html 了解 MySQL 如何评估过滤器。
如果设置了主从复制,服务器 B 和 C 从服务器 a 复制:服务器 a-->(服务器 B,服务器 C),并且您希望使服务器 C 成为服务器 B 的从属服务器,则必须停止服务器 B 和服务器 C 上的复制。然后使用START SLAVE UNTIL
命令将它们带到相同的主日志位置。之后,您可以从服务器 B 获取主日志坐标,并在服务器 C 上执行CHANGE MASTER TO
命令。
SHOW SLAVE STATUS\G
命令中的Relay_Master_Log_File
和Exec_Master_Log_Pos
位置:
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1\. row ***************************
Slave_IO_State:
Master_Host: xx.xxx.xxx.xxx
Master_User: binlog_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: server_A-bin.000023
Read_Master_Log_Pos: 2604
Relay_Log_File: server_C-relay-bin.000002
Relay_Log_Pos: 1228
Relay_Master_Log_File: server_A-bin.000023
~
Exec_Master_Log_Pos: 2604
Relay_Log_Space: 1437
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
~
1 row in set (0.00 sec)
SHOW SLAVE STATUS\G
命令中的Relay_Master_Log_File
和Exec_Master_Log_Pos
位置:
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1\. row ***************************
Slave_IO_State:
Master_Host: xx.xxx.xxx.xxx
Master_User: binlog_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: server_A-bin.000023
Read_Master_Log_Pos: 8250241
Relay_Log_File: server_B-relay-bin.000002
Relay_Log_Pos: 1228
Relay_Master_Log_File: server_A-bin.000023
~
Exec_Master_Log_Pos: 8250241
Relay_Log_Space: 8248167
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
~
1 row in set (0.00 sec)
server_A-bin.000023
、2604
)
服务器 B:(server_A-bin.000023
、8250241
)
服务器 B 在前面,所以我们必须将服务器 C 带到服务器 B 的位置。START SLAVE UNTIL
语句同步到服务器 B 的位置:
**```sql mysql> START SLAVE UNTIL MASTER_LOG_FILE='centos7-bin.000023', MASTER_LOG_POS=8250241; Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> SHOW WARNINGS\G 1. row Level: Note Code: 1278 Message: It is recommended to use --skip-slave-start when doing step-by-step replication with START SLAVE UNTIL; otherwise, you will get problems if you get an unexpected slave's mysqld restart 1 row in set (0.00 sec)
5. 服务器 C 上的**:通过检查`SHOW SLAVE STATUS`输出中的`Exec_Master_Log_Pos`和`Until_Log_Pos`(两者应相同),等待服务器 C 赶上:**
**```sql
mysql> SHOW SLAVE STATUS\G
*************************** 1\. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xx.xxx.xxx.xxx
Master_User: binlog_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: server_A-bin.000023
Read_Master_Log_Pos: 8250241
Relay_Log_File: server_C-relay-bin.000003
Relay_Log_Pos: 8247959
Relay_Master_Log_File: server_A-bin.000023
Slave_IO_Running: Yes
Slave_SQL_Running: No
~
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 8250241
Relay_Log_Space: 8249242
Until_Condition: Master
Until_Log_File: server_A-bin.000023
Until_Log_Pos: 8250241
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
~
1 row in set (0.00 sec)
mysql> SHOW MASTER STATUS;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| server_B-bin.000003 | 36379324 | | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1\. row ***************************
Slave_IO_State:
Master_Host: xx.xxx.xxx.xxx
Master_User: binlog_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: server_A-bin.000023
Read_Master_Log_Pos: 8250241
Relay_Log_File: server_B-relay-bin.000002
Relay_Log_Pos: 1228
Relay_Master_Log_File: server_A-bin.000023
~
Exec_Master_Log_Pos: 8250241
Relay_Log_Space: 8248167
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
~
1 row in set (0.00 sec)
CHANGE MASTER TO
命令,指向服务器 B,您必须使用上一步得到的位置:
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.04 sec)
mysql> CHANGE MASTER TO MASTER_HOST = 'Server B', MASTER_USER = 'binlog_user', MASTER_PASSWORD = 'binlog_P@ss12', MASTER_LOG_FILE='server_B-bin.000003', MASTER_LOG_POS=36379324;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G
Query OK, 0 rows affected, 1 warning (0.00 sec)
*************************** 1\. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xx.xxx.xxx.xx
Master_User: binlog_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: server_B-bin.000003
Read_Master_Log_Pos: 36380416
Relay_Log_File: server_C-relay-bin.000002
Relay_Log_Pos: 1413
Relay_Master_Log_File: server_B-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
~
Exec_Master_Log_Pos: 36380416
Relay_Log_Space: 1622
~
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
~
1 row in set (0.00 sec)
如果有链复制设置(例如服务器 a-->服务器 B-->服务器 C),并且希望使服务器 C 成为服务器 a 的直接从属服务器,则必须停止服务器 B 上的复制,让服务器 C 赶上服务器 B,然后找到与服务器 B 停止位置相对应的服务器 a 坐标。使用这些坐标,您可以在服务器 C 上执行CHANGE MASTER TO
命令,并使其成为服务器 a 的从属服务器。
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW MASTER STATUS;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| server_B-bin.000003 | 44627878 | | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Relay_Master_Log_File
和Exec_Master_Log_Pos
应等于服务器 B 上主状态的输出。一旦延迟被捕获,停止从机:
mysql> SHOW SLAVE STATUS\G
*************************** 1\. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 35.186.157.16
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: server_B-bin.000003
Read_Master_Log_Pos: 44627878
Relay_Log_File: ubuntu2-relay-bin.000002
Relay_Log_Pos: 8248875
Relay_Master_Log_File: server_B-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 44627878
Relay_Log_Space: 8249084
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
~
1 row in set (0.00 sec)
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)
SHOW SLAVE STATUS
输出中获取服务器 A 的坐标(记下Relay_Master_Log_File
和Exec_Master_Log_Pos
,并启动从机:
mysql> SHOW SLAVE STATUS\G
*************************** 1\. row ***************************
Slave_IO_State:
Master_Host: xx.xxx.xxx.xxx
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: server_A-bin.000023
Read_Master_Log_Pos: 16497695
Relay_Log_File: server_B-relay-bin.000004
Relay_Log_Pos: 8247776
Relay_Master_Log_File: server_A-bin.000023
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 16497695
Relay_Log_Space: 8248152
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)
CHANGE MASTER TO COMMAND
指向服务器 A。使用上一步(server_A-bin.000023
和16497695
记录的位置。最后启动从机并验证从机状态:
**```sql mysql> STOP SLAVE; Query OK, 0 rows affected (0.07 sec)
```sql
mysql> CHANGE MASTER TO MASTER_HOST = 'Server A', MASTER_USER = 'binlog_user', MASTER_PASSWORD = 'binlog_P@ss12', MASTER_LOG_FILE='server_A-bin.000023', MASTER_LOG_POS=16497695;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.07 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1\. row ***************************
Slave_IO_State:
Master_Host: xx.xxx.xxx.xxx
Master_User: binlog_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: server_A-bin.000023
Read_Master_Log_Pos: 16497695
Relay_Log_File: server_C-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: server_A-bin.000023
Slave_IO_Running: No
Slave_SQL_Running: No
~
Skip_Counter: 0
Exec_Master_Log_Pos: 16497695
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
~
1 row in set (0.00 sec)
有时,为了进行灾难恢复,您需要一个延迟的从属服务器。假设在主机上执行了一个灾难性的语句(例如DROP DATABASE
命令)。您必须从备份中使用时间点恢复来恢复数据库。这将导致巨大的停机时间,具体取决于数据库的大小。为了避免这种情况,您可以使用一个延迟的从机,它将始终从主机延迟一段配置的时间。如果发生灾难,而延迟的从机没有应用该语句,则可以停止从机并启动,直到灾难性语句出现,这样灾难性语句就不会被执行。然后把它提升到大师级。
该过程与设置正常复制完全相同,只是您在CHANGE MASTER TO
命令中指定了MASTER_DELAY
。
如何测量延迟?
在 MySQL 8.0 之前的版本中,延迟是基于Seconds_Behind_Master
值测量的。在 MySQL 8.0 中,基于写入二进制日志的original_commit_timestamp
和immediate_commit_timestamp
进行测量。
original_commit_timestamp
是自事务写入(提交)原始主机的二进制日志的纪元起的微秒数。
immediate_commit_timestamp
是自将事务写入(提交)即时主机的二进制日志时起的微秒数。
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.06 sec)
CHANGE MASTER TO MASTER_DELAY =
并启动从机。假设您需要 1 小时的延迟,您可以将MASTER_DELAY
设置为3600
秒:mysql> CHANGE MASTER TO MASTER_DELAY = 3600;
Query OK, 0 rows affected (0.04 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
SQL_Delay
:从机必须滞后于主机的秒数。
SQL_Remaining_Delay
:延时的剩余秒数。当保持延迟时,该值为空。
Slave_SQL_Running_State
:SQL 线程的状态。mysql> SHOW SLAVE STATUS\G
*************************** 1\. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 35.186.158.188
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: server_A-bin.000023
Read_Master_Log_Pos: 24745149
Relay_Log_File: server_B-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: server_A-bin.000023
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
~
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 16497695
Relay_Log_Space: 8247985
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
~
Seconds_Behind_Master: 52
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
~
SQL_Delay: 3600
SQL_Remaining_Delay: 3549
Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
~
1 row in set (0.00 sec)
请注意,一旦维持延迟,Seconds_Behind_Master
将显示为0
。
全局事务标识符(GTID)是创建的唯一标识符,并与源服务器(主服务器)上提交的每个事务关联。此标识符是唯一的,不仅对其源服务器是唯一的,而且在给定复制设置中的所有服务器上也是唯一的。所有事务和所有 GTID 之间存在一对一映射。
GTID 表示为一对坐标,由冒号字符(:
分隔):
GTID = source_id:transaction_id
source_id
选项标识发起服务器。通常,服务器的server_uuid
选项用于此目的。transaction_id
选项是一个序列号,由事务在此服务器上提交的顺序决定。例如,要提交的第一个事务具有1
作为其transaction_id
,并且要在同一发起服务器上提交的第十个事务被分配了10
的transaction_id
。
正如您在前面的方法中所看到的,您必须提到二进制日志文件和位置作为复制的起点。如果要将从属设备从一个主设备切换到另一个主设备,尤其是在故障切换期间,则必须从新的主设备获取位置以同步从属设备,这可能会很痛苦。为了避免这些问题,您可以使用基于 GTID 的复制,其中 MySQL 使用 GTID 自动检测二进制日志位置。
如果已在服务器之间设置复制,请执行以下步骤:
my.cnf
中启用 GTID:shell> sudo vi /etc/my.cnf [mysqld]gtid_mode=ON
enforce-gtid-consistency=true
skip_slave_start
On master mysql> SET @@global.read_only = ON; On Slaves (if replication is already setup) mysql> SHOW SLAVE STATUS\G
skip_slave_start
,因此在指定START SLAVE
命令之前,从机不会启动。如果启动从机,它将失败并出现此错误-The replication receiver thread cannot start because the master has GTID_MODE = OFF and this server has GTID_MODE = ON
:shell> sudo systemctl restart mysql
shell> sudo systemctl restart mysql
CHANGE MASTER TO
命令设置 GTID 复制:mysql> CHANGE MASTER TO MASTER_HOST = <master_host>, MASTER_PORT = <port>, MASTER_USER = 'binlog_user', MASTER_PASSWORD = 'binlog_P@ss12', MASTER_AUTO_POSITION = 1;
您可以观察到二进制日志文件和位置没有给出;相反,给出了MASTER_AUTO_POSITION
,它会自动查找执行的 GTID。
START SLAVE
:mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
*************************** 1\. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xx.xxx.xxx.xxx
Master_User: binlog_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: server1-bin.000002
Read_Master_Log_Pos: 345
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 562
Relay_Master_Log_File: server1-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 345
Relay_Log_Space: 770
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 32
Master_UUID: b52ef45a-7ff4-11e7-9091-42010a940003
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: b52ef45a-7ff4-11e7-9091-42010a940003:1
Executed_Gtid_Set: b52ef45a-7ff4-11e7-9091-42010a940003:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
To know more about GTID, refer to https://dev.mysql.com/doc/refman/5.6/en/replication-gtids-concepts.html.
默认情况下,复制是异步的。主设备不知道写入是否已到达从属设备。如果主设备和从设备之间存在延迟,并且主设备崩溃,您将丢失尚未到达从设备的数据。要克服这种情况,可以使用半同步复制。
在半同步复制中,主设备等待至少一个从设备接收到写操作。默认情况下,rpl_semi_sync_master_wait_point
的值为AFTER_SYNC
;这意味着主服务器将事务同步到二进制日志,由从服务器使用。
之后,从机向主机发送确认,然后主机提交事务并将结果返回给客户端。因此,如果写入已到达中继日志,就足够了;从属服务器不需要提交事务。您可以通过将变量rpl_semi_sync_master_wait_point
更改为AFTER_COMMIT
来更改此行为。在这种情况下,主服务器将事务提交到存储引擎,但不将结果返回给客户端。一旦事务在从属服务器上提交,主服务器将收到事务确认,然后将结果返回给客户端。
如果希望在更多从机上确认事务,可以增加动态变量rpl_semi_sync_master_wait_for_slave_count
的值。您还可以通过动态变量rpl_semi_sync_master_timeout
设置主机需要等待多少毫秒才能从从机获得确认;默认值为10
秒。
在完全同步复制中,主服务器等待所有从属服务器提交事务。要实现这一点,必须使用 Galera 集群。
在较高的级别上,您需要在需要半同步复制的主服务器和所有从属服务器上安装并启用半同步插件。必须重新启动从 IO 线程才能使更改生效。您可以根据您的网络和应用程序调整rpl_semi_sync_master_timeout
的值。1
秒的值是一个良好的开端:
rpl_semi_sync_master
插件:mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.86 sec)
验证插件是否已激活:
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.01 sec)
mysql> SET @@GLOBAL.rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync_master_enabled';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
+------------------------------+-------+
1 row in set (0.00 sec)
mysql> SET @@GLOBAL.rpl_semi_sync_master_timeout=1000;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync_master_timeout';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 1000 |
+------------------------------+-------+
1 row in set (0.00 sec)
rpl_semi_sync_slave
插件:mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.22 sec)
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE |
+---------------------+---------------+
1 row in set (0.08 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.02 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW STATUS LIKE 'Rpl_semi_sync_master_clients';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
+------------------------------+-------+
1 row in set (0.01 sec)
当超时发生且从属服务器赶上时,主服务器在异步和半同步复制之间切换。要检查主机使用的复制类型,请检查Rpl_semi_sync_master_status
的状态(on 表示半同步,off 表示异步):
mysql> SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)
您可以使用以下方法验证半同步复制:
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)
mysql> USE employees;
Database changed
mysql> DROP TABLE IF EXISTS employees_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)
您会注意到,主服务器已切换到异步复制,因为即使在 1 秒之后,它也没有从从属服务器获得任何确认(值为rpl_semi_sync_master_timeout
):
mysql> SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)
mysql> DROP TABLE IF EXISTS employees_test;
Query OK, 0 rows affected (1.02 sec)
mysql> SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF |
+-----------------------------+-------+
1 row in set (0.01 sec
mysql> START SLAVE;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)
```************