This page will provide notes on creating a redundant mysql setup in which data is replicated across multiple mysql servers in real time.
The setup i’m going to describe is one that uses two servers but the same could work with more. From my experience a setup like this with more than 5 servers will likely be problematic (the chances that some of the servers get out of sync are greater). My take is that 2 or 3 is ideal.
I should point out that this setup creates and replicates all writes to the database across multiple server installations, if there was a situation where only a limited amount of writes to the database were expected but that setup required a very large amount of reads, it would also be possible to configure a number of mysql slave servers that were specifically setup only for read requests.
This kind of setup (one or two masters and many slave servers) is not the subject of this article even if the basic informations you need are all here, that kind of setup but would work with many server and consequently would be perfect to handle a very large amount of request.
Ok, back to this article, the way this system works is by having 2 mysql servers acting as both master and slaves of each other. To avoid record id duplication ad conflicts each server is given a unique id a identical increment offset but a different increment starting point.
For this setup to work consistently every table in every database should have a unique and auto increment field.
Server 1 will create new records with id’s of: 1,11,21,31 etc… Server 2 will create 2,12,22,32… This way the ids will never collide and it is safe to move them from one to the other machine. Even if two record were created at the exact same time, they would have separate IDs.
First thing we will make some modifications to the my.cnf file These modification will establish a distinct server-id for each of the mysql servers and set them to do a binary log.
In a default freebsd installation the /etc/my.cnf is not present and needs to be created.
Let’s assume we have 2 servers: Server 1 has ip 10.0.0.1, server 2 has ip 10.0.0.2 here is how we would go for the configuration:
my.cnf on server 1
[mysqld] server-id = 1001 log-bin=mysql10001-bin auto_increment_increment = 1 auto_increment_offset = 10 replicate-same-server-id = 0
my.cnf on server 2
[mysqld] server-id = 1002 log-bin=mysql10002-bin auto_increment_increment = 2 auto_increment_offset = 10 replicate-same-server-id = 0
On Server2 run this sql: This will allow for server 1 to replicate the content of server 2.
CREATE USER 'replica'@'10.0.0.1' IDENTIFIED BY '***'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replica'@'10.0.0.1' IDENTIFIED BY '***';
Of course you should replace *** with an actual password of your choice.
This is creating a user called ‘replica’ allowed to connect from server 1 and grating ‘replication’ rights to that user.
On server 1 we do the opposite, we allow a user ‘replica’ coming from server 2 to connect.
CREATE USER 'replica'@'10.0.0.2' IDENTIFIED BY '***'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replica'@'10.0.0.2' IDENTIFIED BY '***';
At this point we are ready to start the replication:
On server 1:
CHANGE MASTER TO MASTER_HOST='10.0.0.2', MASTER_USER='replica', MASTER_PASSWORD='***', MASTER_LOG_FILE='mysql10002-bin', MASTER_LOG_POS=4; START SLAVE;
And on server 2:
CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='replica', MASTER_PASSWORD='***', MASTER_LOG_FILE='mysql10001-bin', MASTER_LOG_POS=4; START SLAVE;
So now we have both servers being a master and a slave of each other.
To be noted that the my.cnf statement “replicate-same-server-id = 0” is helping doing the trick, this tells the server1 that when it receives updates from server2 with it’s own id (server1 id) it should skip them (preventing the whole thing from becoming a infinite loop).