CentOS 6.8 configure MySQL to replicate in SSL encryption mode
The master-slave replication of MySQL uses plaintext transmission by default, which is not secure. You can configure the master-slave replication to use SSL encryption connection.
Configuration on Master
1. Generate key
mysql_ssl_rsa_setup --uid=mysql
2. Copy all keys to / var/lib/mysql (mysql installation directory) of slave
scp /var/lib/mysql/*.pem root@ip:/var/lib/mysql/
3. Modify / etc/my.conf
character-set-server=utf8 lower_case_table_names=1 slow_query_log=1 long_query_time=10.000000 require_secure_transport=ON server-id=1 log-bin=mysql-bin log-bin-index=master-bin.index innodb_flush_log_at_trx_commit=1 sync_binlog=1 expire_logs_days=10 max_binlog_size=1073741824 binlog-do-db=mytest binlog_format=ROW
4. Restart mysql
service mysqld restart
5. Create user - authorize replication
#Create a user with a user (repl) password (MyPWD123! @ × '), and only the (192.168.80) network segment is allowed to log in, and the login must be SSL
CREATE USER 'repl'@'192.168.80.%' IDENTIFIED BY 'MyPWD123!@#' REQUIRE SSL;
#Authorize (repl) users to copy
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.80.%';
#Refresh permissions
FLUSH PRIVILEGES;
6. Export current data and log file location
mysqldump -u root -p --databases mytest --master-data > dbdump.db
Configuration on Slave
1. Configure / etc/my.conf
character-set-server=utf8 lower_case_table_names=1 slow_query_log=1 long_query_time=10.000000 require_secure_transport=ON server-id=2 relay-log=slave-relay-bin relay-log-index=slave-relay-bin.index expire_logs_days=10 max_relay_log_size=1073741824 relay_log_recovery=ON relay_log_info_repository=TABLE
2. Restart MySQL
service mysqld restart
3. Manually create the database, and restore the backup data through the command after completion
mysql -u root -p mytest < /home/mysqldata/dbdump.db
4. Set master-slave replication
CHANGE MASTER TO MASTER_HOST='192.168.80.110', MASTER_USER='repl', MASTER_PASSWORD='MyPWD123!@#', MASTER_LOG_FILE='mysql-bin.000001', MASTER_SSL=1, MASTER_SSL_CA='/var/lib/mysql/ca.pem', MASTER_SSL_CERT='/var/lib/mysql/client-cert.pem', MASTER_SSL_KEY='/var/lib/mysql/client-key.pem', MASTER_LOG_POS=154;
Note that there must be no wrong writing here,
This content can be found at the beginning of dbdump.db, which records the location of the current binary file at the time of master backup. slave will copy data from this point after data recovery.
5. Enable master-slave replication
mysql> START SLAVE;
6. View synchronization status
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.110
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 5218
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 5384
Relay_Master_Log_File: mysql-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: 5218
Relay_Log_Space: 5591
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /var/lib/mysql/ca.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /var/lib/mysql/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /var/lib/mysql/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: 0819a2ef-b543-11e8-b05c-000c291d9005
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:
1 row in set (0.00 sec)
Master_SSL_CA_File: /var/lib/mysql/ca.pem
Master_SSL_Cert: /var/lib/mysql/client-cert.pem
Master_SSL_Key: /var/lib/mysql/client-key.pem
You can see that the configuration has taken effect.
Reference resources: