How to MySQL Replication setup, Master GTID & SSL Encryption

Setting up replication in MySQL is a common DBA task. The replication could be traditional binary log replication or based on GTID replication. This blog is a work log and screen cast of the task of replication setup.

I start with setting up standard binary log replication, then convert it to GTID and lastly, SSL encryption is implemented to secure the replication traffic, ensuring data protection. The setup is assuming MySQL is already installed, the demo includes Percona Server 8.0.28-19.

MySQL source server – create replication user and note binlog co-ordinates

Read more: How to MySQL Replication setup, Master GTID & SSL Encryption

Create replication user and identify binary log co-ordinate to setup replication.

root@ip-172-31-94-56:~# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 8.0.28-20 Percona Server (GPL), Release '20', Revision 'fd4b5a776a6'
...
root@localhost [(none)]> CREATE USER IF NOT EXISTS `replication_user`@`%` IDENTIFIED WITH 'mysql_native_password' BY 'password';
Query OK, 0 rows affected (0.04 sec)

root@localhost [(none)]> GRANT REPLICATION SLAVE ON *.* TO `replication_user`@`%`;
Query OK, 0 rows affected (0.01 sec)

root@localhost [(none)]> show master status;
+----------------+----------+--------------+------------------+------------------------------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+----------------+----------+--------------+------------------+------------------------------------------+
| log-bin.000001 |      698 |              |                  | 5adac524-f616-11ed-9849-12afef3cda15:1-2 |
+----------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

Setup replication on Replica database

[root@ip-172-31-63-168 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 454
Server version: 8.0.28-19 Percona Server (GPL), Release 19, Revision 31e88966cd3
...

root@localhost [(none)]> CHANGE MASTER TO SOURCE_HOST='172.31.94.56', SOURCE_PORT=3306, SOURCE_LOG_FILE='log-bin.000001', SOURCE_LOG_POS=4, SOURCE_USER='replication_user', SOURCE_PASSWORD='password';
Query OK, 0 rows affected, 3 warnings (0.03 sec)

root@localhost [(none)]> START REPLICA;
Query OK, 0 rows affected (0.02 sec)

root@localhost [(none)]> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Connecting to source
                  Source_Host: 172.31.94.56
                  Source_User: replication_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: log-bin.000001
          Read_Source_Log_Pos: 4
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Source_Log_File: log-bin.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: 4
              Relay_Log_Space: 157
              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: NULL
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: 0
                  Source_UUID:
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

root@localhost [(none)]> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 172.31.94.56
                  Source_User: replication_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: log-bin.000001
          Read_Source_Log_Pos: 698
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 910
        Relay_Source_Log_File: log-bin.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: 698
              Relay_Log_Space: 1114
              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: 102
                  Source_UUID: 5adac524-f616-11ed-9849-12afef3cda15
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set: 5adac524-f616-11ed-9849-12afef3cda15:1-2
            Executed_Gtid_Set: 5adac524-f616-11ed-9849-12afef3cda15:1-2
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

Change MySQL replication to use GTID

Consider that we already have the the GTID configuration is already set and hence we won’t need to enable GTID, also this is a lab server without live traffic. That said, we have the steps to enable GTID in live server right after this section.

Source and replica MySQL servers are already configured with GTID

root@ip-172-31-94-56:~# grep gtid /etc/my.cnf
gtid-mode                       = ON
enforce_gtid_consistency        = 1

root@ip-172-31-94-56:~# mysql
root@localhost [(none)]> SHOW GLOBAL VARIABLES LIKE 'enforce_gtid_consistency'; SHOW GLOBAL VARIABLES LIKE 'gtid_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

Change Replica to use GTID replication auto-position

root@localhost [(none)]> STOP REPLICA;
Query OK, 0 rows affected (0.01 sec)

root@localhost [(none)]> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State:
                  Source_Host: 172.31.94.56
                  Source_User: replication_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: log-bin.000001
          Read_Source_Log_Pos: 871
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 1083
        Relay_Source_Log_File: log-bin.000001
           Replica_IO_Running: No
          Replica_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_Source_Log_Pos: 871
              Relay_Log_Space: 1287
              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: NULL
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: 102
                  Source_UUID: 5adac524-f616-11ed-9849-12afef3cda15
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State:
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set: 5adac524-f616-11ed-9849-12afef3cda15:1-3
            Executed_Gtid_Set: 5adac524-f616-11ed-9849-12afef3cda15:1-3
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

root@localhost [(none)]> CHANGE MASTER TO MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

root@localhost [(none)]> START REPLICA;
Query OK, 0 rows affected (0.02 sec)

root@localhost [(none)]> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 172.31.94.56
                  Source_User: replication_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: log-bin.000001
          Read_Source_Log_Pos: 871
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 414
        Relay_Source_Log_File: log-bin.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: 871
              Relay_Log_Space: 618
              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: 102
                  Source_UUID: 5adac524-f616-11ed-9849-12afef3cda15
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 5adac524-f616-11ed-9849-12afef3cda15:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.01 sec)

In case you don’t have GTID enabled already, it is not a straight forward step to enable GTID. Consider following the steps noted below to enable GTID dynamically (no need to restart MySQL).

How to enable GTID in MySQL for setting-up GTID replication

On each server, execute:

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;

Check error log for any specific errors and correct your application accordingly so that it only uses GTID compatible features and does not generate any warnings.

On each server, execute:

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;

On each server, execute following statements on statement at a time:

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

Wait until you see the status variable ONGOING_ANONYMOUS_TRANSACTION_COUNT shows zero atleast once.

SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';

Once all the replicas caught-up, flush binary logs on all servers (This is important specifically on backup server in the case if you’re backing up binary logs for point-in-time recovery)

FLUSH BINARY LOGS;

Execute this on all the servers:

SET @@GLOBAL.GTID_MODE = ON;

Finally change replication to use AUTO POSITION

STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1;
START REPLICA;

Reference – https://dev.mysql.com/doc/refman/8.0/en/replication-mode-change-online-enable-gtids.html

Enable SSL to encrypt replication traffic

Copy *.pem from Source to Replica

root@ip-172-31-94-56:~# cd /var/lib/mysql
root@ip-172-31-94-56:/var/lib/mysql# ls -lhtr *.pem
-rw------- 1 mysql mysql 1.7K May 19 07:25 ca-key.pem
-rw-r--r-- 1 mysql mysql 1.1K May 19 07:25 ca.pem
-rw------- 1 mysql mysql 1.7K May 19 07:25 server-key.pem
-rw-r--r-- 1 mysql mysql 1.1K May 19 07:25 server-cert.pem
-rw------- 1 mysql mysql 1.7K May 19 07:25 client-key.pem
-rw-r--r-- 1 mysql mysql 1.1K May 19 07:25 client-cert.pem
-rw-r--r-- 1 mysql mysql  452 May 19 07:25 public_key.pem
-rw------- 1 mysql mysql 1.7K May 19 07:25 private_key.pem

root@ip-172-31-94-56:/var/lib/mysql# scp *.pem 172.31.63.168:/tmp/
ca-key.pem         100% 1676     1.3MB/s   00:00
ca.pem             100% 1120   955.3KB/s   00:00
client-cert.pem    100% 1120   973.3KB/s   00:00
client-key.pem     100% 1676     1.3MB/s   00:00
private_key.pem    100% 1680     1.4MB/s   00:00
public_key.pem     100%  452   243.8KB/s   00:00
server-cert.pem    100% 1120   953.6KB/s   00:00
server-key.pem     100% 1680     1.3MB/s   00:00

Add REQUIRE SSL to replication user grants

sroot@ip-172-31-94-56:/var/lib/mysql# mysql
...
root@localhost [(none)]> show grants for replication_user;
+----------------------------------------------------------+
| Grants for replication_user@%                            |
+----------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `replication_user`@`%` |
+----------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [(none)]> ALTER USER replication_user@'%' REQUIRE SSL;
Query OK, 0 rows affected (0.01 sec)

On Replica MySQL server, move pem file under datadir and restart MySQL

[root@ip-172-31-63-168 ~]# cd /var/lib/mysql
[root@ip-172-31-63-168 mysql]# cp -f /tmp/*.pem .


[root@ip-172-31-63-168 mysql]# chown mysql:mysql /var/lib/mysql -R
[root@ip-172-31-63-168 mysql]# systemctl restart mysql

Note that you can also use ALTER INSTANCE command to reload certificates without restarting MySQL.

 ALTER INSTANCE RELOAD TLS;

Reference: https://dev.mysql.com/doc/refman/8.0/en/alter-instance.html

Start encrypted replication traffic on replica

# On replica MySQL was restarted hence the current replication is stopped. We will try to start it.

[root@ip-172-31-63-168 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.28-19 Percona Server (GPL), Release 19, Revision 31e88966cd3
...
root@localhost [(none)]> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.02 sec)

root@localhost [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to source
                  Master_Host: 172.31.94.56
                  Master_User: replication_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000001
          Read_Master_Log_Pos: 1349
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 892
        Relay_Master_Log_File: log-bin.000001
             Slave_IO_Running: Connecting
            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: 1349
              Relay_Log_Space: 1112
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'replication_user@172.31.94.56:3306' - retry-time: 60 retries: 1 message: Access denied for user 'replication_user'@'172.31.63.168' (using password: YES)
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID: 5adac524-f616-11ed-9849-12afef3cda15
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 230519 08:11:15
     Last_SQL_Error_Timestamp:

But we’re getting replication error as replica is not able to connect using the provided user. The reason behind this is “REQUIRE SSL” grant option. We will have to issue CHANGE MASTER TO command to specify the SSL configuration options.

root@localhost [(none)]> CHANGE MASTER TO SOURCE_SSL_CA = "ca.pem", SOURCE_SSL_CERT = "server-cert.pem", SOURCE_SSL_KEY = "server-key.pem", SOURCE_SSL=1;

root@localhost [(none)]> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.02 sec)

root@localhost [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.31.94.56
                  Master_User: replication_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000001
          Read_Master_Log_Pos: 1349
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 414
        Relay_Master_Log_File: log-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: 1349
              Relay_Log_Space: 618
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: ca.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: server-cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: server-key.pem
        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: 102
                  Master_UUID: 5adac524-f616-11ed-9849-12afef3cda15
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 5adac524-f616-11ed-9849-12afef3cda15:1-5
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

Setting up replication in MySQL involves creating a replication user, configuring binary log replication, converting to GTID replication, and implementing SSL encryption. By following the step-by-step guide provided in this blog, you can successfully set up replication and ensure data consistency and security in your MySQL environment. In the next blog I will share work-log to enable binary log encryption.

Leave a Reply

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