Asynchronous Replication Automatic Failover in MySQL

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

HostnameIPRoleServer_IDInstallation Link
test-machine01192.168.114.177Master-11Click Here
test-machine01192.168.114.177Master-23Click Here
test-machine02192.168.114.176Replica2Click 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.

 105 Total Views,  2 Views Today

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

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?

Spread the love

Leave a Reply

Your email address will not be published. Required fields are marked *

sixteen − 2 =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru
Direct Call:+91-9310167776
Email: info@dbsguru.com