[CentOS] 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.

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.

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.

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.