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 testing purposes, I’ve set up two systems within my local network, each assigned a unique IP address from the same network range. These systems are configured as follows:
- Master Server IP: 192.168.1.100
- Slave Server IP: 192.168.1.200
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/certs
cd /etc/mysql/certs
- Now, generate the certificate to use as Certificate Authority.
openssl genrsa 4096 > ca-key.pem
openssl 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.pem
openssl 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.pem
openssl 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:
[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.
CREATE USER 'replication'@'192.168.1.200' IDENTIFIED BY 'aSecUrEPa$$w0rd' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.200';
FLUSH PRIVILEGES;
Make sure to replace "192.168.1.200" with the IP address of slave instance.
Make sure to use a strong password for the user account.
Step 4: Export Databases [Master]
Use the mysqldump utility to export all databases from the master server:
mysqldump -uroot -p --skip-lock-tables --single-transaction \
--flush-logs --hex-blob --master-data=2 -A | gzip -c > dump.sql.gz
Next, transfer the export file to the slave server:
scp dump.sql.gz [email protected]:
This command will request the password for the remote user. After successful authentication, the file will be transferred to the remote server, specifically into the root account's home directory.
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]
Log in to the MySQL replication client server. Use the gunzip utility to decompress the dump file. Following that, proceed to restore the database.
gunzip dump.sql.gz
mysql -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:
-- 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:
[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.
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.100',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:
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:
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.1.100
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.
Congratulations, 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”;