How To Set Up MySQL Database Replication With SSL Encryption On Ubuntu 9.10 - 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=3096424, 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: 3096424
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        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: 3096424
              Relay_Log_Space: 407
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
               Last_SQL_Errno: 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: Kamil Kubis

Everything works great but you forgot to sey that line:

 /etc/mysql/newcerts/*.pem r,

needs to be added into: /etc/apparmor.d/usr.sbin.mysqld file.

 You can also add info that CA Name needs to be different for ca-cert and server-cert