Load Balancing MySQL: Part I

We, at CSS Labs, are working towards getting a load balanced MySQL server setup on EC2. So the first of the experimentation is on local physical machines/VMs. So this will be roughly divided into four long parts.

The first step towards achieving this to get the replication working.

So heres are what you need, to achieve just that.

1. Need two boxes or VMs running MySQL servers on them. I ran two Debian Lenny Machines with MySQL 5.0.x
2. There need to connected over the network with no firewall restrictions stopping them from communicating.

One of the machine will be called the “Master” and the other a “Slave”. The following will describe how they need to be configured.

Master

Make sure the you allow the external connections to local mysql server. For this you need to add both your Master’s and Slave’s IP addresses to the /etc/hosts.allow file

mysql: 192.168.1.1 192.168.1.2

Now we need to edit the MySQL configuration file, /etc/mysql/my.cnf. I suggest you back up this file before you make any modifications.

Comment the lines that have the following entries under the mysqld section.

bind-address
skip-networking

The following line need to be added to under the mysqld section.

log-bin=/var/log/mysql/mysql-bin
binlog-do-db=classicmodels #You need change this to your db name
server-id = 1

The first line tells MySQL to start writing a log, and tells it where to write the log. Make sure this directory is empty of all replication logs, especially if you’re starting again after replication has already been used.

The second line chooses the database to write the log for. You should change this to your database.

The third line gives the server an ID (to distinguish it from the Slave).

Now we need to setup a “Slave” user which will replicate the data from the master. We will call this user slave_user with the password slpassword, you could/would need to change these accordingly.

mysql> GRANT ALL ON classicmodels.* TO slave_user@'%' IDENTIFIED BY 'slpassword';
mysql> FLUSH PRIVILEGES;
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave_user@'%' IDENTIFIED BY 'slpassword';

Restart MySQL service.

master> /etc/init.d/mysql restart

Now log into mysql and check to find the position the Master is at in the logs.

mysql> show master status;
+---------------------+----------+-------------------------------+------------------+
| File    | Position | Binlog_Do_DB      | Binlog_Ignore_DB
+---------------------+----------+-------------------------------+------------------+
| mysql-bin.000001 | 98 | classicmodels |
+---------------------+----------+-------------------------------+------------------+

Please make a note of this, as this would be needed while configuring the slave.

Slave

The /etc/mysql/my.cnf of the slave needs to edited to tell the mysql on the Slave about the master’s existence and that it is Slave.

#This id will notify the mysql thats its a Slave
#This number o increment with the new slave additions
server-id        = 2

#IP of the master
master-host        = 192.168.1.1

#The number of seconds that the slave thread
#sleeps before trying to reconnect to the master
#in case the master goes down or the connection is lost.
master-connect-retry    = 60

#The username configured for slave on the Master
master-user        = slave_user
master-password        = slpassword

#The DB thats needs to be replicated
replicate-do-db        = classicmodels

#The name to use for the file in which the slave
#records           information about the relay logs.
relay-log        = /var/log/mysql/slave-relay

#The name to use for the relay log index file
relay-log-index        = /var/log/mysql/slave-relay.index

The following lines must be comment as mentioned in the Master section.

bind-address
skip-networking

Now log into the Slave’s MySQL and make the master’s detail entry.

mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='slave_user', MASTER_PASSWORD='slpassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
mysql> slave start;

Now all we need to do is just restart the Slave.

slave> /etc/init.d/mysql restart

But here i faced an issue with the, when logged into the MySQL prompt and did the following.

mysql> LOAD DATA FROM MASTER;
ERROR 1219 (HY000): Error running query on master: Access denied; you need the RELOAD privilege for this operation

If you see this you need to type do the following the the MySQL on the Master.

mysql> GRANT RELOAD on *.* to slave_user@'%' identified by 'slpassword';
OR
mysql> GRANT SUPER on *.* to slave_user@'%' identified by 'slpassword';

This should get you started with MySQL replication. Now getting load balancer running between the client and mysql servers should have scalable read mysql dataserver on EC2. More follows.