()
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 theReplica 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]#Generaluser = mysqlport = 3306server_id = 2bind-address = 0.0.0.0socket=/u01/mysql-8/mysql.sockpid_file=/u01/mysql-8/mysqld.pid# Data Storagebasedir=/u01/mysql-8datadir=/u01/mysql-8/datainnodb_directories="/u01/mysql-8;/u01/mysql"#Logginglog_bin = /u01/mysql-8/data/binloglog_error = /u01/mysql-8/data/mysqld.logexpire_logs_days = 7relay_log = /u01/mysql-8/data/relay_bin01relay_log_index = /u01/mysql-8/data/relay_bin.indexrelay_log_recovery = onmaster_info_repository = TABLErelay_log_info_repository = TABLE# GTID Based Replication Parametergtid-mode=onenforce-gtid-consistency=1log_slave_updates=1read-only =1super_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.sockEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 17Server version: 8.0.22 MySQL Community Server - GPLCopyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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: 0Source_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 specifiedmysql 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.435288LAST_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.0000001 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: 0mysql 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 mysqldmysql 74622 1 0 09:46 ? 00:00:59 /usr/sbin/mysqld --defaults-group-suffix=@inst1mysql 74700 1 0 09:46 ? 00:01:00 /usr/sbin/mysqld --defaults-group-suffix=@inst2root 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: 0Source_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 specifiedmysql 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: 0Source_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 specifiedmysql Replica>ssage: Can't connect to MySQL server on '192.168.114.177' (111), Error_code: MY-0020032021-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-0020032021-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-0020032021-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-0020032021-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-0020032021-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-0020032021-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 65102021-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: 0mysql 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/subscribeand 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.
How useful was this post?
Click on a star to rate it!
Average rating / 5. Vote count:
No votes so far! Be the first to rate this post.
As you found this post useful...
Follow us on social media!
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?