Setting up MySQL replication for maximum stability!

One more MySQL and PHP post for the weekend… We are running a MySQL replication cluster for several years now and I really LOVE it, when it comes to speed and stability. We are not very advanced with MySQL, I know that – yet: who really is? If you are not one of the few selected super brains of database administration, you can still setup a MySQL replication process and make your site grow with it. Here are some basic infos and tips that we learned over the years.

mysql setup

Master/Master setup

1.) A Master/Master setup gives you a better update possibility:  If you ever need to upgrade your MySQL version, taking it offline is probably NOT an option. With a Master/Master setup like we show it in the picture you can work with one “strain” at a time. Probably when there is not too much traffic on the system. We came ultimately from a 4.x and have done updates to 5.5 over the years in that setup.

2.) You can have multiple connections open for maximum speed: When you do your

$db1 = mysql_connect($Host, $Usr, $Pwd, TRUE, MYSQL_CLIENT_INTERACTIVE);

you may also do a second one. That fact gives you a big playground to optimize your code. For example: hold one connection for reads – or hold 3 connections to 3 different servers for reads on specific tables.

$db2 = mysql_connect($otherHost, $Usr, $Pwd, TRUE, MYSQL_CLIENT_INTERACTIVE);

… and so forth!

We have one connection open for WRITES and several for READS! Think about it and how cool that can be, if you need to draw a lot of different data from different tables. You might want to think about point 1 also and avoid mixing the “strains” of your database servers, so you can take one offline if needed!

3.) Overwrite the mysql_connect() with your own to select the databases: We have created our own “dbConnect()”, which opens the MySQL server links in PHP. The thing is, it can check for config values first and define what strain is taken. That is a bit of a hassle, but you can put your slaves and masters in a Config::Array() class and switch one strain off. Work on the offline strain and bring it back when finished.

We also exchanged mysql_query() with our own dbQuery() to make sure we will NOT use a slave for a write or update command.

Also the level of knowledge is reachable within a short time: to setup a Master/Master setup, you need to know way through the my.cnf in /etc/ and switch on binary logging for the masters. You would need to:

  • change auto_increment values, eg:

auto_increment_increment = 4
auto_increment_offset =2

  • understand that the MySQL server receives the commands from its Master and RELAYS those in a log, eg.:

relay-log = /var/lib/mysql/relay.log

  • must be able to connect to the master remotely with a specific user and password, eg.:

GRANT REPLICATION SLAVE ON

  • and that you need to do that, before you CREATE the database and fill it!

I held a presentation about that topic last year in New York and showed, what we do with replication and how far it goes. Here is the slideshare file:

For more advanced cluster setups, I recommend to employ a full time database expert or get the smart guys from Percona involved. If you just need your shop to run continuously and fast, think about 4-5 severs and a replication approach!

Comments are closed.