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!


Share this page:

1 Comment(s)

Add comment


From: Ronald Bradford

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.