The goal of this article is to setup an HA Linux cluster on RackSpace Cloud Servers for MySQL with multi master replication and HAProxy for load balancing.
Begin by creating the HA Linux client as described here:
HA Linux Cluster On RackSpace Cloud Servers
Once this is complete then return to these instructions.
1. Begin by logging into each of the servers and creating a replication slave user with the following SQL commands:
|
1 2 |
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '[PASSWORD]'; FLUSH PRIVILEGES; |
Where [PASSWORD] is a strong random string. Use the same password on each of the servers.
2. Stop mysqld on both servers:
|
1 |
/sbin/service mysqld stop |
3. On each server, edit the /etc/my.cnf file. Look through the my.cnf file for an existing section of replication options. If none is found then just add to the end of the file.
a. Find the “server-id” line and make sure that each server has a unique server-id. It does not matter what the id number is … it just has to be different on each server.
|
1 |
server-id = 1 |
b. Set replicate-same-server-id to 0. This tells each server to ignore replication logs that refer to it’s on id number.
|
1 |
replicate-same-server-id=0 |
c. Set auto-increment-increment to 2. If you have more then two servers in your cluster then set auto-increment-increment to the number of servers. If you think that you might increase the number of servers in the near future then set auto-increment-increment to the likely maximum number of servers. This parameter controls the span between auto-increment values. So for example a setting of 2 causes a sequence like 2,4,6,6, etc. A value of 5 would cause a sequence of 5,10,15,20, etc.
|
1 |
auto-increment-increment=2 |
d. Set auto-increment-offset to 1 on the first server and 2 on the second server. auto-increment-offset works in conjunction with auto-increment-increment to control the generation of auto-increment values. Each server needs a different auto-increment-offset to avoid conflicts. Assuming two servers, with auto-increment-increment set to 2 and auto-increment-offset set to 1 and 2 on the first and second server respectively will result in the following sequences:
|
1 2 |
Server 1: 1,3,5,7,9,11,... Server 2: 2,4,6,8,10,12,... |
|
1 |
auto-increment-offset=1 |
e. Now on each server, add master-host, master-user, master-password, and master-connect-retry settings. Remember that the master-host should be set to the other server. I like to use hostnames as long as they’ve been defined in /etc/hosts so that there is no dependency on external name-resolution.
Here’s the first server:
|
1 2 3 4 |
master-host = db2 master-user = repl master-password = [PASSWORD] master-connect-retry = 60 |
And here’s the second server:
|
1 2 3 4 |
master-host = db1 master-user = repl master-password = [PASSWORD] master-connect-retry = 60 |
f. Uncomment or add a log-bin line:
|
1 |
log-bin=/var/lib/mysqllogs/bin-log |
g. Setup expire_logs_days and max_binlog_size so that the binary logs don’t grow in an uncontrolled fashion.
|
1 2 |
expire_logs_days = 10 max_binlog_size = 100M |
h. Uncomment or add relay-log and relay-log-index lines:
|
1 2 |
relay-log=/var/lib/mysqllogs/relay-log relay-log-index=/var/lib/mysqllogs/relay-log.index |
4. Next start the mysqld service on both servers:
|
1 |
/sbin/service mysqld start |
5. Now login to the mysql command line on each server and run “SHOW SLAVE STATUS”. Examine the output and verify that “Slave_IO_Running” and “Slave_SQL_Running” are both “YES”.
Congratulations, you now have a high-availability MySQL cluster!
Special thanks to Richard Benson at Dixcart whose similar articles for Debian inspired this effort.