MySQL replication is a process where a master server replicates its data to one or more slave servers. This can provide several benefits such as increased data availability, improved performance, and easier backups. One important aspect of replication is security, and in this article, we will explore how to configure MySQL master-slave replication with authentication using SSL certificates.
By using SSL certificates, we can ensure that the data being replicated is secure and can only be accessed by authorized parties. Additionally, SSL certificates can also be used to authenticate the master and slave servers, providing an additional layer of security.
In this article, we will go through the process of setting up a master-slave replication configuration with SSL certificates, including creating and installing the certificates, configuring the master and slave servers, and troubleshooting common issues.
MySQL Replication Benefits
The main benefits of MySQL Master-Slave Replication are:
- Performance: One of the main benefits is that it allows for the offloading of read-only queries from the master database to the slaves, improving the performance and scalability of the database system. This is because the master database is only responsible for handling written transactions, while the slaves can handle read transactions.
- Redundancy and fault tolerance: Another benefit of MySQL Master-Slave Replication is that it provides a level of redundancy and fault tolerance. If the master database goes down, the slaves can continue to serve read requests, minimizing downtime. Additionally, if a slave database goes down, it can be replaced with a new slave, and the data can be re-synced from the master.
- Backup and Recovery: MySQL Master-Slave Replication can also be used for backups and data recovery. By regularly taking backups of the slaves, you can have a copy of the data that can be used in case of a disaster on the master database.
This article will help you to set up master-slave replication between two MySQL servers. This tutorial is tested on Ubuntu 22.04 Linux instance with MySQL 8.0 database server.
Setup Details:
For the testing purpose, I have created two system in my local network. Both system have different IP of same network. Assuming that the master and slave database servers have the below IPs as below:
Master Server: 11.11.11.11 Slave Server: 22.22.22.22
If you are using different – different versions of MySQL on Master and Slave use this link to check compatibility.
Step 1: Create SSL Certificates [Master]
First, you need to generate a CA certificate. A Certificate Authority (CA) is a trusted third party organization that issues digital certificates used to verify the identity and authenticity of an entity. Then we can generate digital certificate for the master and client system signed by the CA certificate.
- Create a directory to store the new SSL certificates.
mkdir /etc/mysql/certscd /etc/mysql/certs - Now, generate the certificate to use as Certificate Authority.
openssl genrsa 4096 > ca-key.pemopenssl req -new -x509 -nodes -days 9999 -key ca-key.pem > ca-cert.pem - Generate the certificate for the MySQL master server:
openssl req -newkey rsa:4096 -days 9999 -nodes -keyout server-key.pem > server-req.pemopenssl x509 -req -in server-req.pem -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem - Then generate the certificate for the client (slave) system.
openssl req -newkey rsa:4096 -days 9999 -nodes -keyout client-key.pem > client-req.pemopenssl x509 -req -in client-req.pem -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
Important Note: Make sure to use a different "Common Name (FQDN)" for the CA files than the master and client certificate files.
Step 2: Update MySQL Configuraion File [Master]
Next the MySQL configuration file (eg: /etc/mysql/mysql.conf.d/mysqld.cnf) and add/update the following entries:
1 2 3 4 5 6 7 8 9 10 11 | [mysqld] server-id = 1 binlog-format = mixed log-bin = mysql-bin innodb_flush_log_at_trx_commit = 1 sync_binlog=1 ssl ssl-ca=/etc/mysql/certs/ca-cert.pem ssl-cert=/etc/mysql/certs/server-cert.pem ssl-key=/etc/mysql/certs/server-key.pem |
Step 3: Create Replication User [Master]
In this step, we will create a new user in the MySQL server that will be used to authenticate clients with the master service. So, log in to the MySQL server on the master database server with the root privileged user. Then create a new user, that will be used for the replication process.
Connect to MySQL server then execute and execute the following queries to create a new user and grant them to slave access.
1 2 3 | mysql> CREATE USER 'replication'@'22.22.22.22' IDENTIFIED BY 'aSecUrEPa$$w0rd' REQUIRE SSL; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'22.22.22.22'; mysql> FLUSH PRIVILEGES; |
Make sure to replace '22.22.22.22' with the IP address of slave instance.
Make sure to use a strong password for the user account.
Step 4: Dump Databases [Master]
Now, export all the databases on the master instance using the `mysqldump` utility.
mysqldump -uroot -p --skip-lock-tables --single-transaction \
--flush-logs --hex-blob --master-data=2 -A | gzip -c > dump.sql.gz
Copy the dump file to the slave server.
scp dump.sql.gz [email protected]:
The command will prompt for the remote user password. Once the user is authentication, the file will be copied to remote server under the home directory of 'root' account.
Step 5: Copy SSL Certificates Files [Master]
Copy the client key and certificate and ca certificate files to the slave instance. Make sure that the "/etc/mysql/certs" directory exists remote system.
scp /etc/mysql/certs/ca-cert.pem [email protected]:/etc/mysql/certs/scp /etc/mysql/certs/client-cert.pem [email protected]:/etc/mysql/certs/scp /etc/mysql/certs/client-key.pem [email protected]:/etc/mysql/certs/
Note: Configure SSH keys for auto login or use passwords to connect to remote.
Step 6: Restore Database Backup [Slave]
Login to the MySQL replication client server. Extract the dump file using gunzip utility. After that, restore the database.
gunzip dump.sql.gzmysql -uroot -p -f < dump.sql
Then check for the Log file position in backup file.
head dump.sql -n80 | grep "MASTER_LOG_POS"
The output will look like this:
Output-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=154;
Note the MASTER_LOG_FILE and MASTER_LOG_POS values, that will be used later in this article.
Step 7: Update Slave MySQL Configuration [Slave]:
Next the MySQL configuration file (eg: /etc/mysql/mysql.conf.d/mysqld.cnf) and add/update the folloiwng entries:
1 2 3 4 5 6 7 | [mysqld] server-id = 2 binlog-format = mixed log-bin = mysql-bin relay-log = mysql-relay-bin log-slave-updates = 1 read-only = 1 |
Step 8: Start Slave Replication [Slave]
Configure the slave instance with the IP address of Master instance along with replication user, their password and the certificate copied from master instance. Make sure to update `mysql-bin.######` and `MASTER_LOG_POS=###` same as we get in step 6. Include all zeros.
1 2 3 4 5 6 7 8 | mysql> CHANGE MASTER TO MASTER_HOST='11.11.11.11',MASTER_USER='replication', MASTER_PASSWORD='aSecUrEPa$$w0rd', MASTER_SSL_CA='/etc/mysql/certs/ca-cert.pem', MASTER_SSL_CERT='/etc/mysql/certs/client-cert.pem', MASTER_SSL_KEY='/etc/mysql/certs/client-key.pem', MASTER_SSL=1, MASTER_LOG_FILE='mysql-bin.######', MASTER_LOG_POS=###; |
Finally start the replication process:
1 | mysql> START SLAVE; |
This will enable and start the replication process.
Step 9: Verify Replication
You can run the `SHOW SLAVE STATUS` query to check for the current status of the replication:
1 | mysql> SHOW SLAVE STATUS \G |
Output*************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 11.11.11.11 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000177 Read_Master_Log_Pos: 100443919 Relay_Log_File: mysql-relay-bin.000498 Relay_Log_Pos: 104189538 Relay_Master_Log_File: mysql-bin.000169 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: 104189322 Relay_Log_Space: 972921462 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/mysql/certs/ca-cert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/mysql/certs/client-cert.pem Master_SSL_Cipher: Master_SSL_Key: /etc/mysql/certs/client-key.pem Seconds_Behind_Master: 13803 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: a548db65-a007-11ec-9f5e-b626277913b3 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Waiting for dependent transaction to commit 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: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec)
Check for the value of `Seconds_Behind_Master`. The value zero (0) means both systems are synced completely.
MySQL Master-slave Replication has been configured successfully on your system and is in working mode. Thanks for using this tutorial, Kindly do not forget to share it with your friends.
Conclusion
In this article, we have covered the process of configuring MySQL master-slave replication with authentication using SSL certificates. We have gone through the steps of creating and installing the certificates, configuring the master and slave servers, and troubleshooting common issues. By using SSL certificates, we have ensured that the data being replicated is secure and can only be accessed by authorized parties. Additionally, SSL certificates have been used to authenticate the master and slave servers, providing an additional layer of security. This approach can help organizations to improve the security of their replication environment and to ensure the integrity and confidentiality of their data.
Overall, MySQL Master-Slave Replication is a useful tool for improving the performance, scalability, and fault tolerance of a MySQL database system. It allows for the distribution of data across multiple servers and can be used for backups and data recovery.
7 Comments
Don’t forget to write firewall-cmd statements which allows 3306 port and mysql service.
gotta try this
This is what i was looking for. Thank you so much for sharing this. Very helpful.
hi all,
i have followed all the steps mentioned above.
But did not work.
it is not useful …
Hi Sunny,
What issue are you facing here…?
have you added port n service in firewall??
Hi Rahual,
Above your all steps are ok if database does not using triggers if database are using triggers then it is not successfully. so please add all steps for with triggers replication.
Short Example:-
if database are using triggers then would be configure row-based replication.
mysqldump -u root –skip-triggers database > database.sql
master server : my.cnf
[mysqld]
binlog_format = row
mysql> show variables like “binlog_format”;
…………………………………………………………………………………
Slave server:- my.cnf
[mysqld]
binlog_format = row
mysql> show variables like “binlog_format”;