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:
server2:
vi /etc/mysql/my.cnf
[...] 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:
/etc/init.d/mysql restart
Before we start setting up the replication, we create an empty database exampledb on server2:
mysql -u root -p
CREATE DATABASE exampledb;
quit;
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='192.168.0.100', 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 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:
START 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
Master_Host: 192.168.0.100
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
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3096424
Relay_Log_Space: 407
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/mysql/newcerts/ca-cert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/mysql/newcerts/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /etc/mysql/newcerts/client-key.pem
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
mysql>
Afterwards, you can leave the MySQL shell on server2:
quit;
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
- MySQL: http://www.mysql.com/
- Ubuntu: http://www.ubuntu.com/