SSL Replication

Percona Server generates SSL certificates by default. With SSL replication, we can encrypt the communication between master and slave server.

Encrypted Replication

On Master server add “REQUIRE SSL” to the replication user, as show below.

GRANT REPLICATION SLAVE ON *.* to 'repl_user'@'%' IDENTIFIED BY 'repl_pass' REQUIRE SSL;

CHANGE MASTER MASTER_SSL=1,
MASTER_SSL_CA=’/etc/mysql-ssl/ca-cert.pem’,
MASTER_SSL_CERT=’/etc/mysql-ssl/client-cert.pem’,
MASTER_SSL_KEY=’/etc/mysql-ssl/client-key.pem’;

Replication Command

CHANGE MASTER TO MASTER_HOST=’192.168.19.21′,MASTER_USER=’slave_user’, MASTER_PASSWORD=’slave_pass’, MASTER_LOG_FILE=’mysql-bin.000003′, MASTER_LOG_POS=154, MASTER_SSL=1, MASTER_SSL_CA=’/etc/mysql-ssl/ca.pem’, MASTER_SSL_CERT=’/etc/mysql-ssl/client-cert.pem’, MASTER_SSL_KEY=’/etc/mysql-ssl/client-key.pem’;

START SLAVE;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.19.21
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql02-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             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: 154
              Relay_Log_Space: 529
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql-ssl/ca.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/mysql-ssl/client-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/mysql-ssl/client-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: 1
                  Master_UUID: c9d107d7-cc7b-11e8-bf61-08002716213e
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave 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: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 

Conclusion