{"id":2922,"date":"2023-06-04T12:01:57","date_gmt":"2023-06-04T12:01:57","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=2922"},"modified":"2024-01-27T17:26:53","modified_gmt":"2024-01-27T17:26:53","slug":"how-to-mysql-replication-setup-master-gtid-ssl-encryption","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/how-to-mysql-replication-setup-master-gtid-ssl-encryption","title":{"rendered":"How to MySQL Replication setup, Master GTID &#038; SSL Encryption"},"content":{"rendered":"\n<p>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.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-4-3 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe title=\"MySQL Replication, GTID, SSL Encryption\" width=\"500\" height=\"375\" src=\"https:\/\/www.youtube.com\/embed\/x4XUBl1JZCk?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">MySQL source server &#8211; create replication user and note binlog co-ordinates<\/h3>\n\n\n<a class=\"wp-block-read-more\" href=\"https:\/\/kedar.nitty-witty.com\/blog\/how-to-mysql-replication-setup-master-gtid-ssl-encryption\" target=\"_self\">Read more<span class=\"screen-reader-text\">: How to MySQL Replication setup, Master GTID &#038; SSL Encryption<\/span><\/a>\n\n\n<p>Create replication user and identify binary log co-ordinate to setup replication.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>root@ip-172-31-94-56:~# mysql\nWelcome to the MySQL monitor. Commands end with ; or \\g.\nYour MySQL connection id is 35\nServer version: 8.0.28-20 Percona Server (GPL), Release '20', Revision 'fd4b5a776a6'\n...\nroot@localhost &#91;(none)]&gt; CREATE USER IF NOT EXISTS `replication_user`@`%` IDENTIFIED WITH 'mysql_native_password' BY 'password';\nQuery OK, 0 rows affected (0.04 sec)\n\nroot@localhost &#91;(none)]&gt; GRANT REPLICATION SLAVE ON *.* TO `replication_user`@`%`;\nQuery OK, 0 rows affected (0.01 sec)\n\nroot@localhost &#91;(none)]&gt; show master status;\n+----------------+----------+--------------+------------------+------------------------------------------+\n| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |\n+----------------+----------+--------------+------------------+------------------------------------------+\n| log-bin.000001 |      698 |              |                  | 5adac524-f616-11ed-9849-12afef3cda15:1-2 |\n+----------------+----------+--------------+------------------+------------------------------------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Setup replication on Replica database<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@ip-172-31-63-168 ~]# mysql\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\nYour MySQL connection id is 454\nServer version: 8.0.28-19 Percona Server (GPL), Release 19, Revision 31e88966cd3\n...\n\nroot@localhost &#91;(none)]&gt; 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';\nQuery OK, 0 rows affected, 3 warnings (0.03 sec)\n\nroot@localhost &#91;(none)]&gt; START REPLICA;\nQuery OK, 0 rows affected (0.02 sec)\n\nroot@localhost &#91;(none)]&gt; SHOW REPLICA STATUS\\G\n*************************** 1. row ***************************\n             Replica_IO_State: Connecting to source\n                  Source_Host: 172.31.94.56\n                  Source_User: replication_user\n                  Source_Port: 3306\n                Connect_Retry: 60\n              Source_Log_File: log-bin.000001\n          Read_Source_Log_Pos: 4\n               Relay_Log_File: relay-bin.000001\n                Relay_Log_Pos: 4\n        Relay_Source_Log_File: log-bin.000001\n           Replica_IO_Running: Connecting\n          Replica_SQL_Running: Yes\n              Replicate_Do_DB:\n          Replicate_Ignore_DB:\n           Replicate_Do_Table:\n       Replicate_Ignore_Table:\n      Replicate_Wild_Do_Table:\n  Replicate_Wild_Ignore_Table:\n                   Last_Errno: 0\n                   Last_Error:\n                 Skip_Counter: 0\n          Exec_Source_Log_Pos: 4\n              Relay_Log_Space: 157\n              Until_Condition: None\n               Until_Log_File:\n                Until_Log_Pos: 0\n           Source_SSL_Allowed: No\n           Source_SSL_CA_File:\n           Source_SSL_CA_Path:\n              Source_SSL_Cert:\n            Source_SSL_Cipher:\n               Source_SSL_Key:\n        Seconds_Behind_Source: NULL\nSource_SSL_Verify_Server_Cert: No\n                Last_IO_Errno: 0\n                Last_IO_Error:\n               Last_SQL_Errno: 0\n               Last_SQL_Error:\n  Replicate_Ignore_Server_Ids:\n             Source_Server_Id: 0\n                  Source_UUID:\n             Source_Info_File: mysql.slave_master_info\n                    SQL_Delay: 0\n          SQL_Remaining_Delay: NULL\n    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates\n           Source_Retry_Count: 86400\n                  Source_Bind:\n      Last_IO_Error_Timestamp:\n     Last_SQL_Error_Timestamp:\n               Source_SSL_Crl:\n           Source_SSL_Crlpath:\n           Retrieved_Gtid_Set:\n            Executed_Gtid_Set:\n                Auto_Position: 0\n         Replicate_Rewrite_DB:\n                 Channel_Name:\n           Source_TLS_Version:\n       Source_public_key_path:\n        Get_Source_public_key: 0\n            Network_Namespace:\n1 row in set (0.00 sec)\n\nroot@localhost &#91;(none)]&gt; SHOW REPLICA STATUS\\G\n*************************** 1. row ***************************\n             Replica_IO_State: Waiting for source to send event\n                  Source_Host: 172.31.94.56\n                  Source_User: replication_user\n                  Source_Port: 3306\n                Connect_Retry: 60\n              Source_Log_File: log-bin.000001\n          Read_Source_Log_Pos: 698\n               Relay_Log_File: relay-bin.000002\n                Relay_Log_Pos: 910\n        Relay_Source_Log_File: log-bin.000001\n           Replica_IO_Running: Yes\n          Replica_SQL_Running: Yes\n              Replicate_Do_DB:\n          Replicate_Ignore_DB:\n           Replicate_Do_Table:\n       Replicate_Ignore_Table:\n      Replicate_Wild_Do_Table:\n  Replicate_Wild_Ignore_Table:\n                   Last_Errno: 0\n                   Last_Error:\n                 Skip_Counter: 0\n          Exec_Source_Log_Pos: 698\n              Relay_Log_Space: 1114\n              Until_Condition: None\n               Until_Log_File:\n                Until_Log_Pos: 0\n           Source_SSL_Allowed: No\n           Source_SSL_CA_File:\n           Source_SSL_CA_Path:\n              Source_SSL_Cert:\n            Source_SSL_Cipher:\n               Source_SSL_Key:\n        Seconds_Behind_Source: 0\nSource_SSL_Verify_Server_Cert: No\n                Last_IO_Errno: 0\n                Last_IO_Error:\n               Last_SQL_Errno: 0\n               Last_SQL_Error:\n  Replicate_Ignore_Server_Ids:\n             Source_Server_Id: 102\n                  Source_UUID: 5adac524-f616-11ed-9849-12afef3cda15\n             Source_Info_File: mysql.slave_master_info\n                    SQL_Delay: 0\n          SQL_Remaining_Delay: NULL\n    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates\n           Source_Retry_Count: 86400\n                  Source_Bind:\n      Last_IO_Error_Timestamp:\n     Last_SQL_Error_Timestamp:\n               Source_SSL_Crl:\n           Source_SSL_Crlpath:\n           Retrieved_Gtid_Set: 5adac524-f616-11ed-9849-12afef3cda15:1-2\n            Executed_Gtid_Set: 5adac524-f616-11ed-9849-12afef3cda15:1-2\n                Auto_Position: 0\n         Replicate_Rewrite_DB:\n                 Channel_Name:\n           Source_TLS_Version:\n       Source_public_key_path:\n        Get_Source_public_key: 0\n            Network_Namespace:\n1 row in set (0.00 sec)<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Change MySQL replication to use GTID<\/h2>\n\n\n\n<p>Consider that we already have the the GTID configuration is already set and hence we won&#8217;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.<\/p>\n\n\n\n<p>Source and replica MySQL servers are already configured with GTID<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>root@ip-172-31-94-56:~# grep gtid \/etc\/my.cnf\ngtid-mode                       = ON\nenforce_gtid_consistency        = 1\n\nroot@ip-172-31-94-56:~# mysql\nroot@localhost &#91;(none)]&gt; SHOW GLOBAL VARIABLES LIKE 'enforce_gtid_consistency'; SHOW GLOBAL VARIABLES LIKE 'gtid_mode';\n+--------------------------+-------+\n| Variable_name            | Value |\n+--------------------------+-------+\n| enforce_gtid_consistency | ON    |\n+--------------------------+-------+\n1 row in set (0.00 sec)\n\n+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| gtid_mode     | ON    |\n+---------------+-------+\n1 row in set (0.00 sec)<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Change Replica to use GTID replication auto-position<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>root@localhost &#91;(none)]&gt; STOP REPLICA;\nQuery OK, 0 rows affected (0.01 sec)\n\nroot@localhost &#91;(none)]&gt; SHOW REPLICA STATUS\\G\n*************************** 1. row ***************************\n             Replica_IO_State:\n                  Source_Host: 172.31.94.56\n                  Source_User: replication_user\n                  Source_Port: 3306\n                Connect_Retry: 60\n              Source_Log_File: log-bin.000001\n          Read_Source_Log_Pos: 871\n               Relay_Log_File: relay-bin.000002\n                Relay_Log_Pos: 1083\n        Relay_Source_Log_File: log-bin.000001\n           Replica_IO_Running: No\n          Replica_SQL_Running: No\n              Replicate_Do_DB:\n          Replicate_Ignore_DB:\n           Replicate_Do_Table:\n       Replicate_Ignore_Table:\n      Replicate_Wild_Do_Table:\n  Replicate_Wild_Ignore_Table:\n                   Last_Errno: 0\n                   Last_Error:\n                 Skip_Counter: 0\n          Exec_Source_Log_Pos: 871\n              Relay_Log_Space: 1287\n              Until_Condition: None\n               Until_Log_File:\n                Until_Log_Pos: 0\n           Source_SSL_Allowed: No\n           Source_SSL_CA_File:\n           Source_SSL_CA_Path:\n              Source_SSL_Cert:\n            Source_SSL_Cipher:\n               Source_SSL_Key:\n        Seconds_Behind_Source: NULL\nSource_SSL_Verify_Server_Cert: No\n                Last_IO_Errno: 0\n                Last_IO_Error:\n               Last_SQL_Errno: 0\n               Last_SQL_Error:\n  Replicate_Ignore_Server_Ids:\n             Source_Server_Id: 102\n                  Source_UUID: 5adac524-f616-11ed-9849-12afef3cda15\n             Source_Info_File: mysql.slave_master_info\n                    SQL_Delay: 0\n          SQL_Remaining_Delay: NULL\n    Replica_SQL_Running_State:\n           Source_Retry_Count: 86400\n                  Source_Bind:\n      Last_IO_Error_Timestamp:\n     Last_SQL_Error_Timestamp:\n               Source_SSL_Crl:\n           Source_SSL_Crlpath:\n           Retrieved_Gtid_Set: 5adac524-f616-11ed-9849-12afef3cda15:1-3\n            Executed_Gtid_Set: 5adac524-f616-11ed-9849-12afef3cda15:1-3\n                Auto_Position: 0\n         Replicate_Rewrite_DB:\n                 Channel_Name:\n           Source_TLS_Version:\n       Source_public_key_path:\n        Get_Source_public_key: 0\n            Network_Namespace:\n1 row in set (0.00 sec)\n\nroot@localhost &#91;(none)]&gt; CHANGE MASTER TO MASTER_AUTO_POSITION=1;\nQuery OK, 0 rows affected, 2 warnings (0.02 sec)\n\nroot@localhost &#91;(none)]&gt; START REPLICA;\nQuery OK, 0 rows affected (0.02 sec)\n\nroot@localhost &#91;(none)]&gt; SHOW REPLICA STATUS\\G\n*************************** 1. row ***************************\n             Replica_IO_State: Waiting for source to send event\n                  Source_Host: 172.31.94.56\n                  Source_User: replication_user\n                  Source_Port: 3306\n                Connect_Retry: 60\n              Source_Log_File: log-bin.000001\n          Read_Source_Log_Pos: 871\n               Relay_Log_File: relay-bin.000002\n                Relay_Log_Pos: 414\n        Relay_Source_Log_File: log-bin.000001\n           Replica_IO_Running: Yes\n          Replica_SQL_Running: Yes\n              Replicate_Do_DB:\n          Replicate_Ignore_DB:\n           Replicate_Do_Table:\n       Replicate_Ignore_Table:\n      Replicate_Wild_Do_Table:\n  Replicate_Wild_Ignore_Table:\n                   Last_Errno: 0\n                   Last_Error:\n                 Skip_Counter: 0\n          Exec_Source_Log_Pos: 871\n              Relay_Log_Space: 618\n              Until_Condition: None\n               Until_Log_File:\n                Until_Log_Pos: 0\n           Source_SSL_Allowed: No\n           Source_SSL_CA_File:\n           Source_SSL_CA_Path:\n              Source_SSL_Cert:\n            Source_SSL_Cipher:\n               Source_SSL_Key:\n        Seconds_Behind_Source: 0\nSource_SSL_Verify_Server_Cert: No\n                Last_IO_Errno: 0\n                Last_IO_Error:\n               Last_SQL_Errno: 0\n               Last_SQL_Error:\n  Replicate_Ignore_Server_Ids:\n             Source_Server_Id: 102\n                  Source_UUID: 5adac524-f616-11ed-9849-12afef3cda15\n             Source_Info_File: mysql.slave_master_info\n                    SQL_Delay: 0\n          SQL_Remaining_Delay: NULL\n    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates\n           Source_Retry_Count: 86400\n                  Source_Bind:\n      Last_IO_Error_Timestamp:\n     Last_SQL_Error_Timestamp:\n               Source_SSL_Crl:\n           Source_SSL_Crlpath:\n           Retrieved_Gtid_Set:\n            Executed_Gtid_Set: 5adac524-f616-11ed-9849-12afef3cda15:1-3\n                Auto_Position: 1\n         Replicate_Rewrite_DB:\n                 Channel_Name:\n           Source_TLS_Version:\n       Source_public_key_path:\n        Get_Source_public_key: 0\n            Network_Namespace:\n1 row in set (0.01 sec)<\/code><\/pre>\n\n\n\n<p>In case you don&#8217;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).<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to enable GTID in MySQL for setting-up GTID replication<\/h2>\n\n\n\n<p>On each server, execute:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;<\/code><\/pre>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>On each server, execute:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;<\/code><\/pre>\n\n\n\n<p>On each server, execute following statements on statement at a time:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;<\/code><\/pre>\n\n\n\n<p>Wait until you see the status variable ONGOING_ANONYMOUS_TRANSACTION_COUNT shows zero atleast once.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';<\/code><\/pre>\n\n\n\n<p>Once all the replicas caught-up, flush binary logs on all servers (This is important specifically on backup server in the case if you&#8217;re backing up binary logs for point-in-time recovery)<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FLUSH BINARY LOGS;<\/code><\/pre>\n\n\n\n<p>Execute this on all the servers:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @@GLOBAL.GTID_MODE = ON;<\/code><\/pre>\n\n\n\n<p>Finally change replication to use AUTO POSITION<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>STOP REPLICA;<br>CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1;<br>START REPLICA;<\/code><\/pre>\n\n\n\n<p><strong>Reference<\/strong> &#8211; https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/replication-mode-change-online-enable-gtids.html<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Enable SSL to encrypt replication traffic<\/h2>\n\n\n\n<p>Copy *.pem from Source to Replica<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>root@ip-172-31-94-56:~# cd \/var\/lib\/mysql\nroot@ip-172-31-94-56:\/var\/lib\/mysql# ls -lhtr *.pem\n-rw------- 1 mysql mysql 1.7K May 19 07:25 ca-key.pem\n-rw-r--r-- 1 mysql mysql 1.1K May 19 07:25 ca.pem\n-rw------- 1 mysql mysql 1.7K May 19 07:25 server-key.pem\n-rw-r--r-- 1 mysql mysql 1.1K May 19 07:25 server-cert.pem\n-rw------- 1 mysql mysql 1.7K May 19 07:25 client-key.pem\n-rw-r--r-- 1 mysql mysql 1.1K May 19 07:25 client-cert.pem\n-rw-r--r-- 1 mysql mysql  452 May 19 07:25 public_key.pem\n-rw------- 1 mysql mysql 1.7K May 19 07:25 private_key.pem\n\nroot@ip-172-31-94-56:\/var\/lib\/mysql# scp *.pem 172.31.63.168:\/tmp\/\nca-key.pem         100% 1676     1.3MB\/s   00:00\nca.pem             100% 1120   955.3KB\/s   00:00\nclient-cert.pem    100% 1120   973.3KB\/s   00:00\nclient-key.pem     100% 1676     1.3MB\/s   00:00\nprivate_key.pem    100% 1680     1.4MB\/s   00:00\npublic_key.pem     100%  452   243.8KB\/s   00:00\nserver-cert.pem    100% 1120   953.6KB\/s   00:00\nserver-key.pem     100% 1680     1.3MB\/s   00:00<\/code><\/pre>\n\n\n\n<p>Add REQUIRE SSL to replication user grants<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sroot@ip-172-31-94-56:\/var\/lib\/mysql# mysql\n...\nroot@localhost &#91;(none)]&gt; show grants for replication_user;\n+----------------------------------------------------------+\n| Grants for replication_user@%                            |\n+----------------------------------------------------------+\n| GRANT REPLICATION SLAVE ON *.* TO `replication_user`@`%` |\n+----------------------------------------------------------+\n1 row in set (0.00 sec)\n\nroot@localhost &#91;(none)]&gt; ALTER USER replication_user@'%' REQUIRE SSL;\nQuery OK, 0 rows affected (0.01 sec)<\/code><\/pre>\n\n\n\n<p>On Replica MySQL server, move pem file under datadir and restart MySQL<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;root@ip-172-31-63-168 ~]# cd \/var\/lib\/mysql\n&#91;root@ip-172-31-63-168 mysql]# cp -f \/tmp\/*.pem .\n\n\n&#91;root@ip-172-31-63-168 mysql]# chown mysql:mysql \/var\/lib\/mysql -R\n&#91;root@ip-172-31-63-168 mysql]# systemctl restart mysql\n<\/code><\/pre>\n\n\n\n<p>Note that you can also use ALTER INSTANCE command to reload certificates without restarting MySQL. <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code> ALTER INSTANCE RELOAD TLS;<\/code><\/pre>\n\n\n\n<p>Reference: https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/alter-instance.html<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Start encrypted replication traffic on replica<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code># On replica MySQL was restarted hence the current replication is stopped. We will try to start it.\n\n&#91;root@ip-172-31-63-168 mysql]# mysql\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\nYour MySQL connection id is 7\nServer version: 8.0.28-19 Percona Server (GPL), Release 19, Revision 31e88966cd3\n...\nroot@localhost &#91;(none)]&gt; START SLAVE;\nQuery OK, 0 rows affected, 1 warning (0.02 sec)\n\nroot@localhost &#91;(none)]&gt; SHOW SLAVE STATUS\\G\n*************************** 1. row ***************************\n               Slave_IO_State: Connecting to source\n                  Master_Host: 172.31.94.56\n                  Master_User: replication_user\n                  Master_Port: 3306\n                Connect_Retry: 60\n              Master_Log_File: log-bin.000001\n          Read_Master_Log_Pos: 1349\n               Relay_Log_File: relay-bin.000002\n                Relay_Log_Pos: 892\n        Relay_Master_Log_File: log-bin.000001\n             Slave_IO_Running: Connecting\n            Slave_SQL_Running: Yes\n              Replicate_Do_DB:\n          Replicate_Ignore_DB:\n           Replicate_Do_Table:\n       Replicate_Ignore_Table:\n      Replicate_Wild_Do_Table:\n  Replicate_Wild_Ignore_Table:\n                   Last_Errno: 0\n                   Last_Error:\n                 Skip_Counter: 0\n          Exec_Master_Log_Pos: 1349\n              Relay_Log_Space: 1112\n              Until_Condition: None\n               Until_Log_File:\n                Until_Log_Pos: 0\n           Master_SSL_Allowed: No\n           Master_SSL_CA_File:\n           Master_SSL_CA_Path:\n              Master_SSL_Cert:\n            Master_SSL_Cipher:\n               Master_SSL_Key:\n        Seconds_Behind_Master: NULL\nMaster_SSL_Verify_Server_Cert: No\n                Last_IO_Errno: 1045\n                <strong>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)<\/strong>\n               Last_SQL_Errno: 0\n               Last_SQL_Error:\n  Replicate_Ignore_Server_Ids:\n             Master_Server_Id: 0\n                  Master_UUID: 5adac524-f616-11ed-9849-12afef3cda15\n             Master_Info_File: mysql.slave_master_info\n                    SQL_Delay: 0\n          SQL_Remaining_Delay: NULL\n      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates\n           Master_Retry_Count: 86400\n                  Master_Bind:\n      Last_IO_Error_Timestamp: 230519 08:11:15\n     Last_SQL_Error_Timestamp:\n<\/code><\/pre>\n\n\n\n<p>But we&#8217;re getting replication error as replica is not able to connect using the provided user. The reason behind this is &#8220;REQUIRE SSL&#8221; grant option. We will have to issue CHANGE MASTER TO command to specify the SSL configuration options.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>root@localhost &#91;(none)]&gt; CHANGE MASTER TO SOURCE_SSL_CA = \"ca.pem\", SOURCE_SSL_CERT = \"server-cert.pem\", SOURCE_SSL_KEY = \"server-key.pem\", SOURCE_SSL=1;\n\nroot@localhost &#91;(none)]&gt; START SLAVE;\nQuery OK, 0 rows affected, 1 warning (0.02 sec)\n\nroot@localhost &#91;(none)]&gt; SHOW SLAVE STATUS\\G\n*************************** 1. row ***************************\n               Slave_IO_State: Waiting for source to send event\n                  Master_Host: 172.31.94.56\n                  Master_User: replication_user\n                  Master_Port: 3306\n                Connect_Retry: 60\n              Master_Log_File: log-bin.000001\n          Read_Master_Log_Pos: 1349\n               Relay_Log_File: relay-bin.000002\n                Relay_Log_Pos: 414\n        Relay_Master_Log_File: log-bin.000001\n             Slave_IO_Running: Yes\n            Slave_SQL_Running: Yes\n              Replicate_Do_DB:\n          Replicate_Ignore_DB:\n           Replicate_Do_Table:\n       Replicate_Ignore_Table:\n      Replicate_Wild_Do_Table:\n  Replicate_Wild_Ignore_Table:\n                   Last_Errno: 0\n                   Last_Error:\n                 Skip_Counter: 0\n          Exec_Master_Log_Pos: 1349\n              Relay_Log_Space: 618\n              Until_Condition: None\n               Until_Log_File:\n                Until_Log_Pos: 0\n           Master_SSL_Allowed: Yes\n           Master_SSL_CA_File: ca.pem\n           Master_SSL_CA_Path:\n              Master_SSL_Cert: server-cert.pem\n            Master_SSL_Cipher:\n               Master_SSL_Key: server-key.pem\n        Seconds_Behind_Master: 0\nMaster_SSL_Verify_Server_Cert: No\n                Last_IO_Errno: 0\n                Last_IO_Error:\n               Last_SQL_Errno: 0\n               Last_SQL_Error:\n  Replicate_Ignore_Server_Ids:\n             Master_Server_Id: 102\n                  Master_UUID: 5adac524-f616-11ed-9849-12afef3cda15\n             Master_Info_File: mysql.slave_master_info\n                    SQL_Delay: 0\n          SQL_Remaining_Delay: NULL\n      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates\n           Master_Retry_Count: 86400\n                  Master_Bind:\n      Last_IO_Error_Timestamp:\n     Last_SQL_Error_Timestamp:\n               Master_SSL_Crl:\n           Master_SSL_Crlpath:\n           Retrieved_Gtid_Set:\n            Executed_Gtid_Set: 5adac524-f616-11ed-9849-12afef3cda15:1-5\n                Auto_Position: 1\n         Replicate_Rewrite_DB:\n                 Channel_Name:\n           Master_TLS_Version:\n       Master_public_key_path:\n        Get_master_public_key: 0\n            Network_Namespace:\n1 row in set, 1 warning (0.00 sec)<\/code><\/pre>\n\n\n\n<p>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.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"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&hellip;\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[869,8,377],"tags":[612,614,611,380,427,576,100,610],"class_list":{"0":"post-2922","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mariadb","7":"category-mysql","8":"category-mysql-articles","9":"tag-convert-to-gtid-replication","10":"tag-database-encryption","11":"tag-encrypt-replication-traffic","12":"tag-gtid-replication","13":"tag-mysql","14":"tag-mysql-replication","15":"tag-replication","16":"tag-ssl-encryption"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2922","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=2922"}],"version-history":[{"count":2,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2922\/revisions"}],"predecessor-version":[{"id":2924,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2922\/revisions\/2924"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2922"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2922"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2922"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}