Adding an Additional MySQL Replication Slave Server

Same Databases on VPS2 and VPS3.

At the moment the only MySQL databases on vps2 are the databases for david-j-lane.co.uk, watfordjc.co.uk, and my mail server. The only slave database is 'mail'.

Since vps2 has a MySQL server_id of 2, vps3 is going to be server_id 3.

Installing and Configuring MySQL

On vps3 I need to run some commands to install MySQL. These were previosly performed on vps2 in article Ubuntu Server VPS Upgrade.

First make sure my sewrver is up to date. Since a new kernel is available I rebooted after installing it, and then cleaned up the old packages to free up disk space:

sudo apt-get update
sudo apt-get dist-upgrade
sudo reboot
sudo apt-get autoremove
sudo apt-get autoclean

With everything up to date, install mysql:

sudo apt-get install mysql-server mysql-client
sudo nano /etc/mysql/my.cnf

ip-address		= 127.0.0.1
ip-address		= ::1

mysql -u root -p

show variables like 'have_ssl';
quit

As the Variable_name have_ssl has a value of DISABLED, I know SSL support is compiled in.

sudo nano /etc/mysql/my.cnf

[client]
…
ssl-ca		= /etc/ssl/StartCom/ca-bundle.pem
…
[mysqld]
…
bind-address		= 127.0.0.1
bind-address		= ::1
server-id			= 3
log_bin				= /var/log/mysql/mysql-bin.log
relay-bin			= /var/log/mysql/mysql-relay-bin.log
binlog_do_db		= mail

sudo service mysql restart

Creating a Copy of Master Database

On my home server, I need to create a database dump of the mail database. I am going to be modifying the steps at MySQL replication - adding additional slave to achieve this.

In an SSH session to home, I need to lock the tables and get the file and position values:

mysql -u root -p

use mail;
flush tables with read lock;
show master status;

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
mysql-bin.000083 | 527 | mail | |

Keeping that first shell open, open a second shell and create a copy of the mail database:

mysqldump -u root -p mail > mail.000083-527.sql

Back in the first shell, unlock the tables and quit the mysql shell:

unlock tables;
quit;

Back in the second shell, copy the dump to vps3 and then exit:

rsync -avrpPlog --progress mail.000083-527.sql thejc@vps3.thejc.me.uk:
exit

Configuring Replication Slave

The first thing that needs doing is downloading the StartCom ca-bundle.pem (since I am using a StartSSL cert for MySQL on my home server):

sudo su
mkdir /etc/ssl/StartCom
chown thejc:sudo /etc/ssl/StarrtCom
exit
cd /etc/ssl/StartCom
wget "https://www.startcom.com/certs/ca-bundle.pem"

Now to import the mail database:

cd ~
mysql -u root -p

create database mail;
quit;

mysql -u root -p mail < mail.000083-527.sql

And now to setup replication:

mysql -u root -p
change master to MASTER_HOST='fdd7:5938:e2e6:1::3306:1',MASTER_USER='replicator',MASTER_PASSWORD='randompassword',MASTER_LOG_FILE='mysql-bin.000083',MASTER_LOG_POS=527,MASTER_SSL=1,MASTER_SSL_CA='/etc/ssl/StartCom/ca-bundle.pem';
grant select on mail.* to 'mail'@'localhost' identified by 'mail';
flush privileges;
quit;

At this point I ran into a problem and I couldn't resolve it after several hours. I was getting a MySQL error 2029 when running show slave status on vps3, yet using exactly the same parameters on the command line I was able to connect fine.

Because of that, I decided that the only way to get things working would be to remove the need of connecting using SSL.

On my home server:

mysql -u root -p

grant usage on mail.* to 'replicator'@'%' require none;
flush privileges;
quit;

And then back on vps3:

sudo nano /etc/mysql/my.cnf

[client]
…
#ssl-ca=/etc/ssl/StartCom/ca-bundle.pem

sudo service mysql restart
mysql -u root -p

stop slave;
change master to MASTER_HOST='fdd7:5938:e2e6:1::3306:1',MASTER_USER='replicator',MASTER_PASSWORD='randompassword',MASTER_LOG_FILE='mysql-bin.000083',MASTER_LOG_POS=527,MASTER_SSL=0;
start slave;

At this point both vps2 (with SSL) and vps3 (without SSL) were at Relay_log_Pos 253, Relay_master_log_File mysql-bin.000084, and Slave_IO_State Waiting for master to send event.

Obviously there is a problem with MySQL and SSL connections, but I don't know where because the only software difference between the installation on vps2 and vps3 is that vps3 has a later kernel as it is on KVM rather than Xen.