Asynchronous Replication Automatic Failover in MySQL
MySQL 8.0.22 supports an asynchronous connection failover mechanism.
This feature allows a replica/slave to automatically failover to a new source/master, in case its existing source/master fails.
When the existing connection source/master fails, the replica first retries the same connection for the number of times specified by the MASTER_RETRY_COUNT. The interval between attempts is set by the MASTER_CONNECT_RETRY option. When these attempts are exhausted, the asynchronous connection failover mechanism triggers.
To activate asynchronous connection failover for a replication channel, set SOURCE_CONNECTION_AUTO_FAILOVER=1 on the CHANGE MASTER TO statement for the channel.
Use the below functions to add and delete the server entries from the replica/slave source list.
1. asynchronous_connection_failover_add_source (add the server entries from the source list)
2. asynchronous_connection_failover_delete_source (delete the server entries from the source list)
While using these functions, you need to specify the arguments like (‘channel’,’host’,port,’network_namespace’,weight).
In the previous blog, we saw How to Setup MySQL Master-Master Replication (Circular Replication) Click here to read more. We will use the same configuration to Setup Asynchronous Replication Automatic Failover
| Hostname | IP | Role | Server_ID | Installation Link |
| test-machine01 | 192.168.114.177 | Master-1 | 1 | Click Here |
| test-machine01 | 192.168.114.177 | Master-2 | 3 | Click Here |
| test-machine02 | 192.168.114.176 | Replica | 2 | Click Here |
Replica: Below are the Replica configuration parameters
mysql> prompt mysql Replica>
PROMPT set to ' mysql Replica>'
mysql Replica>
mysql Replica>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
mysql Replica>
mysql Replica>select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 13c84508-5014-11eb-af41-000c2997dedd |
+--------------------------------------+
1 row in set (0.00 sec)
mysql Replica>
[mysqld@inst1]
#General
user = mysql
port = 3306
server_id = 2
bind-address = 0.0.0.0
socket=/u01/mysql-8/mysql.sock
pid_file=/u01/mysql-8/mysqld.pid
# Data Storage
basedir=/u01/mysql-8
datadir=/u01/mysql-8/data
innodb_directories="/u01/mysql-8;/u01/mysql"
#Logging
log_bin = /u01/mysql-8/data/binlog
log_error = /u01/mysql-8/data/mysqld.log
expire_logs_days = 7
relay_log = /u01/mysql-8/data/relay_bin01
relay_log_index = /u01/mysql-8/data/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE
# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates=1
read-only =1
super_read_only =0
Step 1. Master Executed_Gtid_Set: Use below command to show master status to get Executed_Gtid_Set.
Master-1
mysql Master-1>show master status;
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| binlog.000001 | 5652 | | | 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-5,
98411332-6aad-11eb-809a-000c29f9d9e6:1-4 |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Step 2. Replica Configuration Setup: Set above parameters in replica my.cnf file and restart replica mysql instance. Then update master Executed_Gtid_Set in replica gtid_purged variable. And update master information using change master command and start replica.
Replica
[root@test-machine02 ~]# vi /etc/my.cnf
[root@test-machine02 u01]# systemctl restart mysqld@inst1
[root@test-machine02 u01]#
[root@test-machine02 ~]# mysql -hlocalhost -uroot -p -S/u01/mysql-8/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> prompt mysql Replica>
PROMPT set to ' mysql Replica>'
mysql Replica>
mysql Replica> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql Replica> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000022 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql Replica>
mysql Replica>SET @@GLOBAL.gtid_purged = "13ecba9c-444a-11eb-a397-000c29f9d9e6:1-5,98411332-6aad-11eb-809a-000c29f9d9e6:1-4";
Query OK, 0 rows affected (0.00 sec)
mysql Replica>show master status;
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| binlog.000022 | 156 | | | 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-5,
98411332-6aad-11eb-809a-000c29f9d9e6:1-4 |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql Replica>
mysql Replica> change master to master_user='repluser',master_password='Password123#@!',master_host='192.168.114.177',master_port=3306,master_auto_position=1,source_connection_auto_failover=1,master_retry_count=6,master_connect_retry=20 for channel "rep_asyncfailover";
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql Replica>start replica for channel "rep_asyncfailover";
Query OK, 0 rows affected (0.00 sec)
mysql Replica>
Step 3. Check the replication status: Use the below command to check replication status.
Replica
mysql Replica>
mysql Replica>show replica status \G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for master to send event
Source_Host: 192.168.114.177
Source_User: repluser
Source_Port: 3306
Connect_Retry: 20
Source_Log_File: binlog.000001
Read_Source_Log_Pos: 5652
Relay_Log_File: relay_bin01-rep_asyncfailover.000002
Relay_Log_Pos: 409
Relay_Source_Log_File: binlog.000001
Replica_IO_Running: Yes
Replica_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_Source_Log_Pos: 5652
Relay_Log_Space: 632
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: 13ecba9c-444a-11eb-a397-000c29f9d9e6
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates
Source_Retry_Count: 6
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-5,
98411332-6aad-11eb-809a-000c29f9d9e6:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: rep_asyncfailover
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql Replica>SELECT * FROM performance_schema.replication_connection_status\G;
*************************** 1. row ***************************
CHANNEL_NAME: rep_asyncfailover
GROUP_NAME:
SOURCE_UUID: 13ecba9c-444a-11eb-a397-000c29f9d9e6
THREAD_ID: 46
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 37
LAST_HEARTBEAT_TIMESTAMP: 2021-03-15 16:10:19.498679
RECEIVED_TRANSACTION_SET: 13ecba9c-444a-11eb-a397-000c29f9d9e6:6
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION: 13ecba9c-444a-11eb-a397-000c29f9d9e6:6
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2021-03-15 15:56:49.435288
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2021-03-15 15:56:49.435288
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2021-03-15 15:56:49.435954
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2021-03-15 15:56:49.435986
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
1 row in set (0.00 sec)
ERROR:
No query specified
Step 4. Test Replication: Execute transaction in Master-1 and check the same transaction execute in Replica.
Master-1
mysql Master-1>use repl_test1;
mysql Master-1>create table repl_test2 as select * from repl_test3;
Query OK, 20 rows affected (28.61 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql Master-1>
Replica
mysql Replica>use repl_test1;
mysql Replica>show tables;
+----------------------+
| Tables_in_repl_test1 |
+----------------------+
| repl_test1 |
| repl_test2 |
| repl_test3 |
+----------------------+
3 rows in set (0.00 sec)
mysql Replica>
Step 5. Add Sources in Replica: Use the below commands to add both Master-1 & Master-2 in the replica.
Replica
mysql Replica>select asynchronous_connection_failover_add_source('rep_asyncfailover','192.168.114.177',3306,'',100);
+------------------------------------------------------------------------------------------------+
| asynchronous_connection_failover_add_source('rep_asyncfailover','192.168.114.177',3306,'',100) |
+------------------------------------------------------------------------------------------------+
| The UDF asynchronous_connection_failover_add_source() executed successfully. |
+------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)
mysql Replica>select asynchronous_connection_failover_add_source('rep_asyncfailover','192.168.114.177',3307,'',90);
+-----------------------------------------------------------------------------------------------+
| asynchronous_connection_failover_add_source('rep_asyncfailover','192.168.114.177',3307,'',90) |
+-----------------------------------------------------------------------------------------------+
| The UDF asynchronous_connection_failover_add_source() executed successfully. |
+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql Replica>
mysql Replica>select * from mysql.replication_asynchronous_connection_failover;
+-------------------+-----------------+------+-------------------+--------+
| Channel_name | Host | Port | Network_namespace | Weight |
+-------------------+-----------------+------+-------------------+--------+
| rep_asyncfailover | 192.168.114.177 | 3306 | | 100 |
| rep_asyncfailover | 192.168.114.177 | 3307 | | 90 |
+-------------------+-----------------+------+-------------------+--------+
2 rows in set (0.00 sec)
mysql Replica>
Step 6. Test Failover: We will stop Master-1 mysql instance.
Master-1
[root@test-machine01 mysql]# ps -ef|grep mysqld
mysql 74622 1 0 09:46 ? 00:00:59 /usr/sbin/mysqld --defaults-group-suffix=@inst1
mysql 74700 1 0 09:46 ? 00:01:00 /usr/sbin/mysqld --defaults-group-suffix=@inst2
root 93809 74071 0 16:11 pts/2 00:00:00 grep --color=auto mysqld
[root@test-machine01 mysql]#
[root@test-machine01 mysql]# systemctl stop mysqld@inst1
[root@test-machine01 mysql]#
[root@test-machine01 mysql]#
Step 7. Failover InAction: Monitor replica status with show replica status command and mysql.log file. After 2 mins of trying to connect to Master-1 ( master_retry_count=6,master_connect_retry=20 : 6 *20 = 120 second ), Replica with failover to Master-2. Please refer replica mysql.log snippet in the below output. You will also notice Port will change to 3307.
Replica
mysql Replica>show replica status \G;
*************************** 1. row ***************************
Replica_IO_State: Connecting to master
Source_Host: 192.168.114.177
Source_User: repluser
Source_Port: 3306
Connect_Retry: 20
Source_Log_File:
Read_Source_Log_Pos: 6510
Relay_Log_File: relay_bin01-rep_asyncfailover.000002
Relay_Log_Pos: 1267
Relay_Source_Log_File: binlog.000001
Replica_IO_Running: Connecting
Replica_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_Source_Log_Pos: 6510
Relay_Log_Space: 1490
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'repluser@192.168.114.177:3306' - retry-time: 20 retries: 3 message: Can't connect to MySQL server on '192.168.114.177' (111)
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: 13ecba9c-444a-11eb-a397-000c29f9d9e6
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates
Source_Retry_Count: 6
Source_Bind:
Last_IO_Error_Timestamp: 210315 16:13:54
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 13ecba9c-444a-11eb-a397-000c29f9d9e6:6
Executed_Gtid_Set: 13c84508-5014-11eb-af41-000c2997dedd:1-2,
13ecba9c-444a-11eb-a397-000c29f9d9e6:1-6,
98411332-6aad-11eb-809a-000c29f9d9e6:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: rep_asyncfailover
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql Replica>show replica status \G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for master to send event
Source_Host: 192.168.114.177
Source_User: repluser
Source_Port: 3307
Connect_Retry: 20
Source_Log_File: binlog.000001
Read_Source_Log_Pos: 5683
Relay_Log_File: relay_bin01-rep_asyncfailover.000003
Relay_Log_Pos: 449
Relay_Source_Log_File: binlog.000001
Replica_IO_Running: Yes
Replica_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_Source_Log_Pos: 5683
Relay_Log_Space: 1783
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 3
Source_UUID: 98411332-6aad-11eb-809a-000c29f9d9e6
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates
Source_Retry_Count: 6
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 13ecba9c-444a-11eb-a397-000c29f9d9e6:6
Executed_Gtid_Set: 13c84508-5014-11eb-af41-000c2997dedd:1-2,
13ecba9c-444a-11eb-a397-000c29f9d9e6:1-6,
98411332-6aad-11eb-809a-000c29f9d9e6:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: rep_asyncfailover
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql Replica>
ssage: Can't connect to MySQL server on '192.168.114.177' (111), Error_code: MY-002003
2021-03-15T13:13:34.064320Z 14 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'rep_asyncfailover': error connecting to master 'repluser@192.168.114.177:3306' - retry-time: 20 retries: 2 message: Can't connect to MySQL server on '192.168.114.177' (111), Error_code: MY-002003
2021-03-15T13:13:54.068893Z 14 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'rep_asyncfailover': error connecting to master 'repluser@192.168.114.177:3306' - retry-time: 20 retries: 3 message: Can't connect to MySQL server on '192.168.114.177' (111), Error_code: MY-002003
2021-03-15T13:14:14.071283Z 14 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'rep_asyncfailover': error connecting to master 'repluser@192.168.114.177:3306' - retry-time: 20 retries: 4 message: Can't connect to MySQL server on '192.168.114.177' (111), Error_code: MY-002003
2021-03-15T13:14:34.075728Z 14 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'rep_asyncfailover': error connecting to master 'repluser@192.168.114.177:3306' - retry-time: 20 retries: 5 message: Can't connect to MySQL server on '192.168.114.177' (111), Error_code: MY-002003
2021-03-15T13:14:54.079435Z 14 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'rep_asyncfailover': error connecting to master 'repluser@192.168.114.177:3306' - retry-time: 20 retries: 6 message: Can't connect to MySQL server on '192.168.114.177' (111), Error_code: MY-002003
2021-03-15T13:14:54.080924Z 16 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2021-03-15T13:14:54.091725Z 16 [System] [MY-010562] [Repl] Slave I/O thread for channel 'rep_asyncfailover': connected to master 'repluser@192.168.114.177:3307',replication started in log 'FIRST' at position 6510
2021-03-15T13:14:54.093558Z 16 [Warning] [MY-010549] [Repl] The master's UUID has changed, although this should not happen unless you have changed it manually. The old UUID was 13ecba9c-444a-11eb-a397-000c29f9d9e6.
Step 8. Test Replication after Failover: Execute some transaction in Master-2 and check same transaction replicated in replica.
Master-2
mysql Master-2>use repl_test2;
mysql Master-2>create table repl_test1 as select * from repl_test2;
Query OK, 20 rows affected (0.01 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql Master-2>
Replica
mysql Replica>use repl_test2;
mysql Replica>show tables;
+----------------------+
| Tables_in_repl_test2 |
+----------------------+
| repl_test |
| repl_test1 |
| repl_test2 |
| repl_test4 |
+----------------------+
4 rows in set (0.00 sec)
mysql Replica>
This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.
