MySQL Server & High Availability

MySQL LogoHere's a question I'm asked often: how can you set up MySQL to be High Availability in a cheap and effective way? In my experience, one of the best ways to accomplish this is with the use of a Master-Master Replication setup across two servers.  This way if one machine drops off the network or goes down, the other will take it's place without missing a beat.  Additionally, if the server returns, it will automatically catch up and work alongside the other live one.

There are several ways to accomplish this, but the following tutorial will show one of the simplest ways to accomplish this goal.

Disclaimer: As will any tutorial, I am not responsible for any actions you do on your own servers. If something breaks, feel free to leave a comment and I'll do what I can to help out. It's assumed you have MySQL Version 5.x on your Linux-based system and you're comfortable enough with vi and other basic elements of the system.

With that out of the way, let's set up some replication!  We're going to have two servers with the following details:

  • Server_A - 10.0.0.10
  • Server_B - 10.0.0.11

On Server_A, add the following lines to your /etc/my.cnf file:

server-id=10

log-bin

binlog-do-db={Database to Replicate}

binlog-ignore-db=mysql

Next, still on Server_A, we'll need to create a replication account in MySQL:

mysql> CREATE USER 'repl'@'10.0.0.11' IDENTIFIED BY 'slave';

mysql> GRANT ALL ON *.* TO 'repl'@'10.0.0.11' IDENTIFIED BY 'slave';

Restart MySQL on Server_A.

Next we'll need to edit the /etc/my.cnf file on Server_B. Add the following lines, changing it as you need:

server-id=11

master-host = 10.0.0.10

master-user = repl

master-password = slave

master-port = 3306

So far it's pretty easy, right? Notice that the server-id value is different in the two files. Make sure that this number is unique throughout your network.

Restart MySQL on Server_B and then enter the following commands into MySQL:

mysql> start slave;

mysql> show slave status \G;

You should see output similar to the following:

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.10

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqld-bin.000008

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000012

Relay_Log_Pos: 236

Relay_Master_Log_File: mysqld-bin.000008

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: 236

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)

Two really important lines are Slave_IO_Running and Slave_SQL_Running. Notice how they both say "yes". If yours doesn't, then the replication isn't set up properly.  Double-check your settings and try again.

If you've ever configured replication on MySQL server you're probably saying to yourself "How is this different from normal replication?"  It's a valid question, because we now have a working Master/Slave configuration.  This next part is where the magic comes in.

Going from Master/Slave to Master/Master

On Server_B, add the following lines to the /etc/my.cnf file:

log-bin

binlog-do-db={Database to Replicate}

Create a replication slave on Server_B for Server_A:

mysql> CREATE USER 'repl'@'10.0.0.10' IDENTIFIED BY 'slave';

mysql> GRANT ALL ON *.* TO 'repl'@'10.0.0.10' IDENTIFIED BY 'slave';

I've used the same username and password for the sake of simplicity, but you can set them as whatever you'd like.

On Server_A, add the following lines to the /etc/my.cnf file:

master-host = 10.0.0.11

master-user = repl

master-password = slave

master-port = 3306

Looks easy, right? Oddly enough, it really is just this simple. Restart the MySQL servers on Server_A and Server_B. Now it's time to finish the configuration.

Server_A:

mysql> start slave;

Server_B:

mysql> show master status;

Server_A:

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.11

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqld-bin.000008

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000012

Relay_Log_Pos: 236

Relay_Master_Log_File: mysqld-bin.000008

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: 236

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)

There we go! Check to make sure that Slave_IQ_Running and Slave_SQL_Running are both Yes (on both servers), and you're set to go. Now you can create your tables, populate them, and get some work done.

Master-Master ReplicationThe AUTO_INCREMENT Collision Conundrum

There is one little thing that might bite you with busy servers, and that's index collisions. If a both servers have data recorded to the same table(s) at exactly the same time, there is a chance that different data will exist for the same index value, causing a collision when the servers try to update each other.  There are two ways to get around this.

The first is to not use AUTO_INCREMENT for primary keys. Either use unique identifiers or have a single system parameter table ready to provide index numbers whenever they're required.  The other is to change the AUTO_INCREMENT behavior.  Personally, I prefer this second option because it's quite easy to do.

Once again, let's open up the my.cnf file on Server_A and Server_B and add the following lines:

Server_A

auto_increment_increment=5

auto_increment_offset=1

Server_B

auto_increment_increment=5

auto_increment_offset=2

The first line tells MySQL to auto-increment tables by 5 rather than 1, and the second is the start number.  In this example, Server_A would receive values 1, 6, 11, 16, and so on, while Server_B would receive 2, 7, 12, 17, and so on.  This is not a perfect solution, but it certainly beats some of the alternatives … especially if you're running servers that are being hammered with read/write requests.

Easy, right? Well … maybe after some practice. But that's all there is to it.  The nice thing about this setup is that it works really well with load balancing systems and can help resolve some serious bottlenecks if you have people doing heavy reporting on live data.  Is it the best solution? Maybe not. But if you're on a tight budget, this can save you quite a bit compared to some of the other options.