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 EncryptionCreate 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:00Add 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.