How To configure replication in MySQL using SSL encryption on Debian Lenny

This guide describes how to configure database replication in MySQL using an SSL encryption connection.
MySQL replication synchronizes the database, which allows you to have an exact copy of the database on another server. All database updates on the main server are automatically replicated to another server, which allows you to protect the database from hardware failures. This article will show how to implement exampledb database replication from server1.example.com server (ip address 192.168.0.100) to server2.example.com server (ip address 192.168.0.101) using SSL connection.

Both servers work on Debian Lenny but the configuration can be applied on almost all distributions without changes. The exampledb database with tables and data already existing only on the main one. All commands are run with root privileges.
If MySQL server is not installed on both servers, then proceed with the installation by executing the command on the primary and secondary servers:

aptitude install mysql-server mysql-client

You will be prompted to enter the root password for MySQL, both on the primary and secondary server.
Now let's check the SSL connection support with MySQL. Let's go into MySQL and enter the command at the MySQL command prompt:

mysql -u root -p
mysql> show variables like '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+
7 rows in set (0.00 sec)
mysql>quit;

If the output is as follows, then this means that MySQL was compiled with SSL support, but as you can see from the have_openssl DISABLED table and have_ssl DISABLED is not active.

To enable SSL support, you need to edit the my.cnf file which is located in / etc / mysql /

vi /etc/mysql/my.cnf

Find the * Security Features lines and add the ssl line

[...]
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
ssl
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[...]

Restart MySQL:

/etc/init.d/mysql restart

and check if ssl support is active

mysql -u root -p
show variables like '%ssl%';
mysql> show variables like '%ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
| have_ssl      | YES   |
| ssl_ca        |       |
| ssl_capath    |       |
| ssl_cert      |       |
| ssl_cipher    |       |
| ssl_key       |       |
+---------------+-------+
7 rows in set (0.00 sec)
mysql>quit;

The output shows that ssl is enabled.
Next, configure MySQL for listening on all interfaces, commenting out the line bind-address = 127.0.0.1 in /etc/mysql/my.cnf:
server1:

vi /etc/mysql/my.cnf
[...]
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
[...]

MySQL restore:

/etc/init.d/mysql restart

Now let's see what hangs on our ports:

netstat -tap | grep mysql
server1:~# netstat -tap | grep mysql
tcp        0      0 *:mysql                 *:*                     LISTEN      3771/mysqld
server1:~#

Apparently, MySQL is broadcast on all interfaces.
Now we need to create CA, servers and client certificates for SSL connections. I usually create them in the / etc / mysql / newcerts
directory. Create a newcerts directory:

mkdir /etc/mysql/newcerts && cd /etc/mysql/newcerts

Make sure OpenSSL is installed:

aptitude install openssl

Create a CA certificate:

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

Next, create a certificate for the server ...:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

and for the client:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

The output should look like this:

ls -l
server1:/etc/mysql/newcerts# ls -l
total 32
-rw-r--r-- 1 root root 1346 2010-08-18 20:13 ca-cert.pem
-rw-r--r-- 1 root root 1675 2010-08-18 20:13 ca-key.pem
-rw-r--r-- 1 root root 1099 2010-08-18 20:14 client-cert.pem
-rw-r--r-- 1 root root 1675 2010-08-18 20:14 client-key.pem
-rw-r--r-- 1 root root 956 2010-08-18 20:14 client-req.pem
-rw-r--r-- 1 root root 1099 2010-08-18 20:14 server-cert.pem
-rw-r--r-- 1 root root 1679 2010-08-18 20:14 server-key.pem
-rw-r--r-- 1 root root 956 2010-08-18 20:14 server-req.pem
server1:/etc/mysql/newcerts#

Now we need to transfer the certificates ca-cert.pem, client-cert.pem and client-key.pem to the second server. Create a directory on the second server:
server2:

mkdir /etc/mysql/newcerts

Let's go back to server1 and pass the certificates as follows:
server1:

scp /etc/mysql/newcerts/ca-cert.pem root@192.168.0.101:/etc/mysql/newcerts
scp /etc/mysql/newcerts/client-cert.pem root@192.168.0.101:/etc/mysql/newcerts
scp /etc/mysql/newcerts/client-key.pem root@192.168.0.101:/etc/mysql/newcerts

Next, open server1 /etc/mysql/my.cnf and make changes to the * Security Features area by uncommenting the lines ssl-ca, ssl-cert and ssl-key:

vi /etc/mysql/my.cnf
[...]
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
ssl
ssl-ca=/etc/mysql/newcerts/ca-cert.pem
ssl-cert=/etc/mysql/newcerts/server-cert.pem
ssl-key=/etc/mysql/newcerts/server-key.pem
[...]

Restart MySQL:

/etc/init.d/mysql restart

Now we will create the replication user slave_user, which will be used on server2, to access the database on server1

mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password' REQUIRE SSL;

The REQUIRE SSL line is optional, if you leave it, slave_user will be allowed to connect through an encrypted and non-encrypted connection. When using SSL, only an encrypted connection will be allowed.

(If the replication user has already been created and you need to specify only an SSL connection, then you need to change the user as follows:

GRANT USAGE ON *.* TO 'slave_user'@'%' REQUIRE SSL;

)

FLUSH PRIVILEGES;
quit;

In addition, we must specify MySQL where to store our logs for replication, and also indicate which server is the main and which is replication:

vi /etc/mysql/my.cnf
[...]
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = exampledb
[...]

MySQL restart

/etc/init.d/mysql restart

Next, we need to carry out several operations:
1. Lock the exampledb database on server1
2. Find out the master status server1
3. Create a dump of SQL exampledb (for import to server2)
4. Unlock our database

mysql -u root -p
USE exampledb;
FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |       98 | exampledb    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>

Now, without leaving the mysql console (since the database lock will be removed) we will backup and transfer it to server2
server1:

cd /tmp
mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql
scp snapshot.sql root@192.168.0.101:/tmp

After the operation, you can safely unlock our database
server1:

UNLOCK TABLES;
quit;

This completes the setup of server1. Let's set up server2 by opening the muscle config:
server2:

vi /etc/mysql/my.cnf

make sure that we have the following line settings (if we don’t have them, add it):

[...]
server-id=2
master-connect-retry=60
replicate-do-db=exampledb
[...]

The value of server-id = 2 is unique and it should differ from the value on server1

Restart MySQL:

/etc/init.d/mysql restart

Before you start configuring replication, create an empty database:

mysql -u root -p
CREATE DATABASE exampledb;
quit;

Now you can import the dump of the snapshot.sql database on server2

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql

We go into MySQL and execute the command so that server2 is a secondary server, and server1 is defined as the primary server:

mysql -u root -p
CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98, MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/newcerts/ca-cert.pem', MASTER_SSL_CERT = '/etc/mysql/newcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/mysql/newcerts/client-key.pem';

* MASTER_HOST - the IP address or host name in our case is ip
* MASTER_USER - the replication user for the primary server
* MASTER_PASSWORD - User Password
* MASTER_LOG_FILE - Value The log file on server1 that we learned will execute the SHOW MASTER STATUS command;
* MASTER_LOG_POS - The value obtained by executing the SHOW MASTER STATUS command;
* MASTER_SSL - Creates a connection between the primary and secondary server using SSL
* MASTER_SSL_CA - Path to the CA certificate (server2)
* MASTER_SSL_CERT - Path to the client-cert.pem certificate (server2)
* MASTER_SSL_KEY - Path to the client-key.pem certificate (server2)

And finally

START SLAVE;

Now check the status of server2

SHOW SLAVE STATUS \G
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.100
                Master_User: slave_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: exampledb
        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: 98
            Relay_Log_Space: 235
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: Yes
         Master_SSL_CA_File: /etc/mysql/newcerts/ca-cert.pem
         Master_SSL_CA_Path:
            Master_SSL_Cert: /etc/mysql/newcerts/client-cert.pem
          Master_SSL_Cipher:
             Master_SSL_Key: /etc/mysql/newcerts/client-key.pem
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)
mysql>

After that, you can exit MySQL:

quit;

That's all, the server setup is done. If you did everything correctly, then replication is configured correctly and is operational.

Thank you very much for your attention!
PS If you find a piece of paper or an incorrectly formulated sentence, please inform the PM.