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:
[...] server-id=2 master-connect-retry=60 replicate-do-db=exampledb [...]
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
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='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';
- MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
- 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:
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
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
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!