When you have a MySQL database and want to spread the data reads and writes across multiple servers, you can setup master-master replication. What this does is in almost-realtime copy all commands from each server to the other, each acting as both a Master and Slave server.
This is NOT a good system for backups, as any accident on either server will be replicated to the other server, such as dropping all your tables.
To setup Master-Master Replication the first thing you need to do is setup Master-Slave Replication.
Once you've set up Master-Slave Replication, start on the current Slave server. Edit your /etc/my.cnf file to turn on binary logging on this server. Under the [mysqld] heading add:
log-bin=mysql-bin binlog-ignore-db="mysql"
Create a replication slave account on the slave for the original master server:
# mysql -u root -p mysql> GRANT REPLICATION SLAVE ON *.* TO repl@"%" IDENTIFIED BY '[repl-password]'; mysql> quit
Again, be sure to replace [repl password] with the actual password you want to use. Also, you must ensure that your firewall has port 3306:tcp open, the default port for the mysql server service.
Restart MySQL then get the binary position of the data.
# mysql -u root -p mysql> SHOW MASTER STATUS;
The output should look something like this:
+------------------+--------------------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+--------------------------+------------------+ | mysql-bin.000001 | 3400 | | mysql | +------------------+--------------------------+------------------+ 1 row in set (0.00 sec)
Write down the filename and log position for use on the original Master server.
Now back on the original Master server, set it up as a slave also. Edit /etc/my.cnf and under the [mysqld] heading add:<
master-host = [IP of Slave Server] master-user = repl master-password = [repl password] master-port = 3306
Restart your MySQL service, then login to MySQL and setup the Master File settings.
mysql -u root -p mysql> CHANGE MASTER TO MASTER_LOG_FILE='[File written down]', MASTER_LOG_POS=[position]; mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G
Slave_IO_State status information should identify "Waiting for master to send event". If it stops at "Connecting to Master" check your log file. By default it is located in /var/log/mysqld.log but may be different on your system. Check your my.cnf file for the exact location of your log file.
Note: I've noticed an issue with connecting when using "%" wildcard and have had to specify the connecting server in many instances. MySQL doesn't seem to like the wildcard in quite a few cases.
At this point all reads and writes can be evenly distributed across servers. This is great for load sharing, but I must reiterate, don't use it for backup, a single accidental table drop will affect both databases.
