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

This guide describes how to set up database replication in MySQL using an SSL connection for encryption.
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 DB replication from server1.example.com (ip address 192.168.0.100) to server2.example.com (ip address 192.168.0.101) using an SSL connection

Both servers run on Debian Lenny, but the configuration can be used on almost all distributions without changes. DB exampledb with tables and data already existing only on the main thing. All commands are executed with root privileges.
If the MySQL server is not installed on both servers, we proceed to the installation by running the following command on the main server and the secondary server:

aptitude install mysql-server mysql-client 

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

 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 looks like this, it means that MySQL was compiled with SSL support, but as shown in 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 lines * Security Features and add the line ssl

 [...] # * 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 by commenting the bind-address = 127.0.0.1 line 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 restart:

 /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:~# 

It seems that 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 directory newcerts:

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

Make sure OpenSSL Unas is installed:

 aptitude install openssl 

Create 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 have to transfer the ca-cert.pem, client-cert.pem and client-key.pem certificates 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 /etc/mysql/my.cnf on server1 and make changes to the * Security Features area by uncommenting the ssl-ca, ssl-cert and ssl-key lines:

 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; 

Stream REQUIRE SSL is not mandatory, if you leave it, slave_user will be allowed to connect via an encrypted and unencrypted connection. When using SSL, only an encrypted connection will be allowed.

(If a replication user has already been created and you only need to specify 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 need MySQL to specify where to store our logs for replication, as well as to indicate which server is the main and which replication server:

 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 [...] 

Restart mysql

 /etc/init.d/mysql restart 

Next, we need to perform several operations:
1. Block the exampledb database on server1
2. Learn master status server1
3. Create SQL dump 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 (because the DB lock will be deleted) we will make a 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 server1 setup. Proceed to setting up server2 by opening the configuration of muscle:
server2:

 vi /etc/mysql/my.cnf 

make sure that we have the following line settings (if not, add them):

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

The value server-id = 2 is unique and it should be different from the value that on server1
')
Restart MySQL:

 /etc/init.d/mysql restart 

Before you start setting up replication, create an empty database:

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

Now you can import the snapshot.sql database dump to server2

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

Go to 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 - ip address or host name in our case is ip
* MASTER_USER - user replication for the primary server
* MASTER_PASSWORD - User Password
* MASTER_LOG_FILE - Value of the Log file on server1 which we learned will execute the SHOW MASTER STATUS command;
* MASTER_LOG_POS - The value obtained when executing the SHOW MASTER STATUS command;
* MASTER_SSL - Creates a connection to the main and secondary server using SSL
* MASTER_SSL_CA - Path to 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 it, server setup is over. If you have done everything correctly then replication is configured correctly and workable.

Thank you very much for your attention!
PS If you found ochepyatku or not properly formulated sentence please inform the PM.