How To Set Up MySQL Database Replication With SSL Encryption On Debian Squeeze - 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:
The value of server-id must be unique and thus different from the one on the master!
Restart MySQL afterwards:
Before we start setting up the replication, we create an empty database exampledb on server2:
mysql -u root -p
CREATE DATABASE exampledb;
On server2, we can now import the SQL dump snapshot.sql like this:
/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
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='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106, 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';
Finally start the slave:
Then check the slave status:
SHOW SLAVE STATUS \G
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:
mysql> SHOW SLAVE STATUS \G
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!