Clustering MySQL
To begin with we'll install and configure MySQL for normal use on each of the boxes.
sudo apt-get install mysql-server --yes Set a strong MySQL root password and wait for the packages to download and install, then edit /etc/mysql/my.cnf to make MySQL listen on all IP addresses.
bind-address = 0.0.0.0
Now restart MySQL and fire up the MySQL command-line client to check all is good.
sudo /etc/init.d/mysql restart
mysql -u root -p
Enter password: [enter the MySQL root password you chose earlier]
mysql> \q
If you got the mysql> prompt then MySQL is running. Try connecting to the other node across the network to see if the firewall is opened and MySQL is listening on the network interface.
mysql -h slave.domainname.com(or ip address) -u root -p
Enter password: [enter the MySQL root password you chose earlier]
ERROR 1130 (00000): Host 'db-01' is not allowed to connect to this MySQL server
If you got the above error then everything is working fine - MySQL connected and refused to authorize the client. We'll create some valid accounts for this later. If you got a different error (such as the one below), check MySQL is running on both boxes and that the firewall rules are allowing connections from the correct hosts.
Can't connect to MySQL server on 'slave.domainname.com(or IP Address)' (10061)
One-way replication
The first thing we want to do is setup a simple master-slave replication to see that it's possible to replicate data from one database host to the other. This requires a binary log so tell MySQL on db-01 to keep one. Edit /etc/mysql/my.cnf and set the following values under the replication section.
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = my_application
binlog_ignore_db = mysql
binlog_ignore_db = test
On master.domainname.com grant replication slave rights to slave.domainname.com. Change some_password to a real, strong password. Afterwards, make sure you restart MySQL.
mysql -u root -p
Enter password: [enter the MySQL root password you chose earlier]
mysql> grant replication slave on *.* to 'replication'@'slave.domainname.com' identified by 'some_password';
mysql> \q
sudo /etc/init.d/mysql restart
Jump on to slave.domainname.com and set it up to replicate data from master.domainname.com by editing /etc/mysql/my.cnf, again replacing the hostname, username and password with the values for master.domainname.com.
server-id = 2
master-host = master.domainname.com(or IP Address of Master)
master-user = replication
master-password = some_password
master-port = 3306
One way replication should now be setup. Restart MySQL and check the status of the slave on slave.domainname.com. If the Slave_IO_State is "Waiting for master to send event" then you've been successful.
# Run this on slave.domainname.com only
sudo /etc/init.d/mysql restart
mysql -u root -p
Enter password: [enter the MySQL root password you chose earlier]
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 193.219.108.241
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 98
Relay_Log_File: mysqld-relay-bin.000004
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 98
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
All being well it's time to test replication is working. We'll create the database we've configured replication for (my_application) on master.domainname.com and watch as it appears on slave.domainname.com as well.
# On both nodes
mysql -u root -p
Enter password: [enter the MySQL root password you chose earlier]
mysql> show databases;
There should be two - mysql and test.
# On master.domainname.com only
mysql -u root -p
Enter password: [enter the MySQL root password you chose earlier]
mysql> create database my_application;;
# On both nodes
mysql -u root -p
Enter password: [enter the MySQL root password you chose earlier]
mysql> show databases;
The new database, my_application should appear in the output of both nodes. Success! If it doesn't show on both nodes (it didn't for me the first time I set it up), here are some tips for finding out what's wrong.
Trouble-shooting one-way replication
If the slave status above doesn't show Slave_IO_State: Waiting for master to send event, Slave_IO_Running: Yes and Slave_SQL_Running: Yes then something is wrong. This happened a few times while I was setting up replication - here's how I debugged it.
Telnet is one of the best tools in the world for debugging connectivity issues. If you haven't already, install it now.
sudo apt-get install telnet
SSH to the node that you want to check connectivity from (slave.domainname.com) and telnet to the other node (master.domainname.com) on the MySQL port (3306).
# on slave.domainname.com
telnet master.domainname.com mysql
The problem I encountered was ERROR 1130 (00000): Host 'slave.domainname.com' is not allowed to connect to this MySQL server. This happens when an incorrect hostname was used in the grant replication slave query above. In my case I had granted access to clients using the full hostname (master.domainname.com) but MySQL looked in /etc/hosts and found a short name (slave.domainname.com). Run the grant replication slave query again using the hostname given in the error message.
# on master.domainname.com
mysql -u root -p
Enter password: [enter the MySQL root password you chose earlier]
mysql> grant replication slave on *.* to 'replication'@'slave.domainname.com' identified by 'some_password';
mysql> \q
sudo /etc/init.d/mysql restart
Another problem I encountered was that the slave status remained "connecting to master" for a long time. If you can connect using telnet this is probably caused by the server-id being the same on both servers. Check in /etc/mysql/my.cnf and if necessary change the values and restart MySQL.
Testing it all works
Until now both boxes have been firewalled to allow MySQL connections only from each other. To prove that the database failover works we'll have to connect from another box, possibly your desktop or laptop.
Create a user which you can use to query the database, again on both boxes.
# on both boxes
mysql -u root -p
Enter password: [enter the MySQL root password you chose earlier]
mysql> grant all, replication_client on my_application.* to 'some_user'@'10.1.1.1' identified by 'some_other_password';
mysql> \q
mysql -u some_user -p -h hostname or ip address my_application
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.1.1
[unimportant lines snipped]