Simple MySQL replication cluster with load balancer on the slaves

Scenario

Background

This post is a follow on from my previous post on how to setup a load balanced high availability Apache cluster and uses the same network setup and heartbeat setup. For the purposes of testing and to save on resource I am using the web servers from the previous guide as the MySQL servers in this guide. Obviously in a real world setup this would be doing it wrong

End Goal

You will be able to connect to 1 IP address and port with MySQL client and be routed to 1 of many MySQL servers. The machine doing the routing will be in a high availability setup with another machine.

Server Roles

bhlabs1/6 will load balance mysql queries through mysql-proxy
bhlabs2 will be  the master replication server
bhlabs3-5 will be mysql slaves

I’m separating this guide into 3 sections:

• MySQL Replication Cluster
• mysql-proxy Setup
• Usage with heartbeat

MySQL Replication Cluster

On each of the servers :

# apt-get update
# apt-get install mysql-server

The slave servers will need special access to stream the binary logs from the master server, so that needs to be setup using the following GRANT command as root on the master server:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'ImASlave';

Next un-comment server id and binary log entries in my.cnf (add them if they weren’t there in the first place):

server-id   = 1
log_bin     = /var/log/mysql/mysql-bin.log

You must also set the server-id on all slaves, starting from 2 and incrementing as you go. You do not need the log_bin entry on the slave servers.

Your slave servers must begin replication at exactly the same state as the master server. From the master server, export all databases with lock on all tables:

# mysqldump -p --all-databases --lock-all-tables > initialDbDump.db

And then import on to all slave servers:

# mysql -p -hbhlabs3 < ./initialDbDump.db
# mysql -p -hbhlabs4 < ./initialDbDump.db
# mysql -p -hbhlabs5 < ./initialDbDump.db

Get log position off bhlabs2:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      516 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Set configuration and start slave on bhlabs3-5:

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=516, MASTER_HOST='bhlabs2', MASTER_USER='repl', MASTER_PASSWORD='ImASlave';
mysql> START SLAVE;

You can check connectivity from bhlabs2:

mysql> show full processlist;
+----+------+---------------------+------+-------------+------+----------------------------------------------------------------+-----------------------+
| Id | User | Host                | db   | Command     | Time | State                                                          | Info                  |
+----+------+---------------------+------+-------------+------+----------------------------------------------------------------+-----------------------+
| 16 | repl | 192.168.11.36:40812 | NULL | Binlog Dump |  248 | Has sent all binlog to slave; waiting for binlog to be updated | NULL                  |
| 17 | repl | 192.168.11.37:48080 | NULL | Binlog Dump |  206 | Has sent all binlog to slave; waiting for binlog to be updated | NULL                  |
| 18 | repl | 192.168.11.38:54400 | NULL | Binlog Dump |  202 | Has sent all binlog to slave; waiting for binlog to be updated | NULL                  |
| 22 | root | localhost           | NULL | Query       |    0 | NULL                                                           | show full processlist |
+----+------+---------------------+------+-------------+------+----------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

And then create a test database on bhlabs2 and it should appear on the other 3 servers.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

Drop the test database and it should disappear. If this works you’re set to go.

mysql> drop database test;
Query OK, 0 rows affected (0.07 sec)

Remember from this point all commands that create or change anything in MySQL must now be run on the master server. With this in mind, create a read only user, you don’t want your websites accidentally writing to the slaves, that will break replication!

mysql> grant select on *.* to 'selectuser'@'%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on *.* to 'selectuser'@'localhost' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

And then obivously, a write user:

mysql> grant select,insert,update,delete on *.* to 'writeuser'@'%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert,update,delete on *.* to 'writeuser'@'localhost' identified by 'password';
Query OK, 0 rows affected (0.01 sec)

Proxy Servers

Repeat this whole section on both proxy servers (bhlabs1/6)
Install the proxy:

# apt-get updatep
# apt-get install mysql-proxy

Initial startup:

# mysql-proxy --admin-address=:1337 --proxy-backend-addresses=192.168.11.36:3306 --proxy-backend-addresses=192.168.11.37:3306 --proxy-backend-addresses=192.168.11.38:3306 &

Check the proxy is running via netstat to see if the ports have been bound:

# netstat -l
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 *:4040                  *:*                     LISTEN
tcp        0      0 *:1337                  *:*                     LISTEN

Port 4040 is the proxy inbound port
Port 1337 is the admin database

Connect to admin database:

# mysql -h 127.0.0.1 -P 1337 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.20-agent MySQL Enterprise Agent

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

And then check your proxy_config tables at it should list your backend servers:

mysql> select * from proxy_config;
+----------------------------+--------------------+
| option                     | value              |
+----------------------------+--------------------+
| admin.address              | :1337              |
| proxy.address              | :4040              |
| proxy.lua_script           | NULL               |
| proxy.backend_addresses[0] | 192.168.11.36:3306 |
| proxy.backend_addresses[1] | 192.168.11.37:3306 |
| proxy.backend_addresses[2] | 192.168.11.38:3306 |
| proxy.fix_bug_25371        | 0                  |
| proxy.profiling            | 1                  |
+----------------------------+--------------------+
8 rows in set (0.00 sec)

Quit out of mysql and then reconnect on the proxy ingress port:

# mysql -h 127.0.0.1 -P 4040 -u selectuser -p

And you should then be able to spot a Sleep connection on one of your slave servers through:

mysql> show full processlist;
+----+------------+---------------------+------+---------+------+-------+-----------------------+
| Id | User       | Host                | db   | Command | Time | State | Info                  |
+----+------------+---------------------+------+---------+------+-------+-----------------------+
| 20 | selectuser | 192.168.11.30:58522 | NULL | Sleep   |  161 |       | NULL                  |
| 21 | selectuser | localhost           | NULL | Query   |    0 | NULL  | show full processlist |
+----+------------+---------------------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)

Success!

You can now test failover by shutting down one of your slaves, running queries, even inserts (on the master!), and then starting the slave again. The slave will know the last log position it was at on the master logs and be able to catch back up once you restart it. The proxy will not route queries to a server thats down.

High Availability (Optional)

This will allow your proxy servers to failover between each other on the same address using heartbeat. For how to setup heartbeat initially see the guide here, we’re going to pick up at the end of that guide and just join our mysql-proxy to that shared IP address.

All you need to do to get it to listen on the shared address is add 1 more switch to the startup command, proxy-address:

# mysql-proxy --proxy-address=192.168.11.40:4040 --admin-address=:1337 --proxy-backend-addresses=192.168.11.36:3306 --proxy-backend-addresses=192.168.11.37:3306 --proxy-backend-addresses=192.168.11.38:3306 &

You can then check it’s listening on the specified IP via netstat again. Remember if heartbeat hasn’t failed over to this machine then you won’t be able to reach this address!

# netstat -l
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 192.168.11.40:4040      *:*                     LISTEN

Furthur Reading

Automatically routing writes and reads

It is possible to use 1 user for both your reads and your writes through 1 IP/Port by using mysql-proxy’s very extensive LUA support to route queries to different servers based on queries. See here and here for examples.

Other funky LUA hacks

There’s plenty you can do with LUA and mysql-proxy. O’Reilly published a good guide with examples such as rewriting queries on the fly to correct typos, executing remote shell commands through MySQL, etc.  Worth a read if you want to do anything automated with it.