Adding an Additional MySQL Replication Slave Server

With my ULA network now extended to my new VPS the next step is making it a MySQL slave.

Same Databases on VPS2 and VPS3.

At the moment the only MySQL databases on vps2 are the databases for,, 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		=
ip-address		= ::1
mysql -u root -p
show variables like 'have_ssl';

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

sudo nano /etc/mysql/my.cnf
ssl-ca		= /etc/ssl/StartCom/ca-bundle.pem
bind-address		=
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;

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

rsync -avrpPlog --progress mail.000083-527.sql

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
cd /etc/ssl/StartCom
wget ""

Now to import the mail database:

cd ~
mysql -u root -p
create database mail;
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;

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;

And then back on vps3:

sudo nano /etc/mysql/my.cnf
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.