How To Set Up MySQL Database Replication With SSL Encryption On Debian Lenny - Page 2

4 Configuring The Slave

Now we must configure the slave. Open /etc/mysql/my.cnf and make sure you have the following settings in the [mysqld] section:


vi /etc/mysql/my.cnf


The value of server-id must be unique and thus different from the one on the master!

Restart MySQL afterwards:

/etc/init.d/mysql restart

Before we start setting up the replication, we create an empty database exampledb on server2:

mysql -u root -p


On server2, we can now import the SQL dump snapshot.sql like this:

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

Now connect to MySQL again...

mysql -u root -p

... and run the following command to make server2 a slave of server1 (it is important that you replace the values in the following command with the values you got from the SHOW MASTER STATUS; command that we ran on server1!):

CHANGE MASTER TO MASTER_HOST='', 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 is the IP address or hostname of the master (in this example it is
  • MASTER_USER is the user we granted replication privileges on the master.
  • MASTER_PASSWORD is the password of MASTER_USER on the master.
  • MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
  • MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.
  • MASTER_SSL makes the slave use an SSL connection to the master.
  • MASTER_SSL_CA is the path to the ca-cert.pem file on the slave.
  • MASTER_SSL_CERT is the path to the client-cert.pem file on the slave.
  • MASTER_SSL_KEY is the path to the client-key.pem file on the slave.

Finally start the slave:


Then check the slave status:


It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should check your setup again and take a look at /var/log/syslog to find out about any errors); as you're using an SSL connection now, you should also find values in the fields Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_Cert, and Master_SSL_Key:

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                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
                 Last_Errno: 0
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 235
            Until_Condition: None
              Until_Log_Pos: 0
         Master_SSL_Allowed: Yes
         Master_SSL_CA_File: /etc/mysql/newcerts/ca-cert.pem
            Master_SSL_Cert: /etc/mysql/newcerts/client-cert.pem
             Master_SSL_Key: /etc/mysql/newcerts/client-key.pem
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)


Afterwards, you can leave the MySQL shell on server2:


That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave. Test it!


5 Links

Share this page:

5 Comment(s)

Add comment


From: TripHunter at: 2010-09-17 08:13:42

Thanks for this. Extremely useful post and just what i needed for a travel customer of mine

From: at: 2010-09-10 00:52:30

Falko, as usual, this is an excellent How To!  Thank you.

I discovered that there are some tweaks that are necessary for Drupal.  I am running Drupal 6 multisite on ISPConfig2 on Debian Lenny (I used your ISPConfig/Drupal5 How To to setup the original environment in Drupal5 and have since upgraded to D6 ).

The tweaks mainly involve ignoring some temporary tables that will render your Drupal replication out of sync very quickly.

A list of the key tweaks (some required) are here:

Thanks again for the awesome instructions and hopefully this additional info helps someone; I was pulling my hair out wondering what was wrong!

From: Andy Beverley at: 2012-04-27 20:34:18

Are the permissions really correct in the directory listing above? Surely the private keys should be only readable by mysql?

From: razvan at: 2013-01-15 20:15:06

Thank you for your tutorial.

It was great and useful, but for Ubuntu 12.10 has two problems that did not allow me to activate  the SSL for master.

1. The generated keys for server and client are missing RSA and from header. This generates a  "SSL error: Unable to get private key from '/etc/mysql/server-key.pem' " in "/var/log/mysql/error.log".

So instead of

shoud be

 and instead of

  -----END PRIVATE KEY-----
should be

See:      MySQL :: Re: MySQL 5.5.8 SSL error: Unable to get private key from 'server-key.pem'

 2. The generated files should be placed in /etc/mysql/
At least this was the solution that worked for me.

All the best.


From: Ronald Bradford at: 2010-08-30 17:27:01

I've just read your article and while I've not physically tried your commands,  I found it a good detailed example I can use as a reference when clients ask for information regarding MySQL SSL.