Multi-master replication for MySQL is easy to setup, is similar to master-slave replication and provides a host of features and options not available with the regular master-slave setup. Add to that the MMM database management tool, and you have a flexible, highly available MySQL cluster on which to host your application.
replication for MySQL provides a host of new features and options not available
with the regular master-slave setup. Also known as circular replication,
with multi-master replication you have one active and one passive server, but
both configured in otherwise exactly the same way.
going to introduce this configuration, then show you how to use the
Multi-master Replication Manager for MySQL.
Easy Failover with Multi-Master Replication
replication above and beyond anything else, gives you very easy and simple
failover. Why is that? Because you don’t need to make any
configuration changes. Here’s how.
standard MySQL replication, the master database receives changes, and all the
while the slave database watches its binary log file for activity. When
it sees activity there, it copies those transactions using the IO thread from
the master’s binary log, to its own relay log file. It is then that the
slave’s SQL thread kicks in, and applies those transactions one-by-one in a
serial fashion to the slave database. This happens in the order the
transactions were committed.
that configuration, if you want to promote a slave to a master, you have to
disable the slave, change some other settings, possibly change read-only mode,
and or the server_id if that hasn’t been set.
Multi-master replication. Here we have the master and slave setup as
above. Db2 is a slave of db1 for example. However, in addition
with this setup you also make db1 a slave to db2. As above, the
transactions flow from master to slave. They then flow back upstream to
the master. The master sees the server_id in the transaction as its OWN
server_id and knows not to apply this transaction. In other words, db1
will only apply a transaction that ORIGINATED on db2. In all other
ways, the servers are configured exactly the same way. In fact you don’t
even *NEED* to enable read-only mode, though we recommend it.
Setting up Master-Master Replication
by setting up master-slave replication as you normally would. In this setup,
also make a few additions. Be sure to set:
= 12345 # could use last digits of IP address, must be
ahead and set these; be sure your replication user exists on both master and
slave, restart master and slave, and make sure replication is happening to the
is now only one last step. On the *master* go ahead and issue the same
CHANGE MASTER TO command that you would normally use pointing db2 to your
master db1, but in reverse so the master points to the slave. Be sure to
grab the master_log_file and position on the slave so you can run this command:
mysql> change master to master_user='abc', master_password='abc', master_host='db2', master_log_file='mysql-bin.000001', master_log_pos='12345';
mysql> start slave; mysql> show slave statusG;
for these two lines:
Slave_IO_Running: Yes Slave_SQL_Running: Yes
Building Highly Available MySQL with MMM
sure you can see the advantages of MySQL Multi-master replication
already. But there’s a lot more in store for you. Next up you’ll
want to take a look at MMM. This toolset provides a monitoring daemon,
and agents that run on the various MySQL nodes. It monitors activity and
makes sure your master is available. If it becomes unavailable, it will
automatically switch your application to point to your alternate master.
exposes a virtual IP, which your application can use to connect. That VIP
will always point to the writer *role* whichever database in your multi-master
setup that currently holds the role. There are also reader roles with
associated IP addresses for your application to use. MMM manages all of
these for you.
more, if the monitor daemon dies for some reason, the currently assigned VIPs
are persistent. So obviously, you’ll want to monitor to make sure the that
process is running, but it won’t bring down your application if it dies.
Setting up MMM
setup the EPEL repository.
A quick Google search should get you details on this. Next use yum or
apt-get to install your packages. For yum:
$ yum install mysql-mmm-agent $ yum install mysql-mmm-monitor $ yum install mysql-mmm-tools
are a few files to edit to configure MMM, but they’re not too bad.
create an mmm_common.conf file and copy to /etc/mysql-mmm/ on monitor server as
well as each server running the agent daemon:
active_master_role writer <host default> cluster_interface eth0 pid_path /var/run/mysql-mmm/mmmd_agent.pid bin_path /usr/libexec/mysql-mmm/ replication_user replication replication_password abc agent_user mmm_agent agent_password abc </host> <host db1> ip 192.168.1.86 mode master peer db2 </host> <host db2> ip 192.168.1.87 mode master peer db1 </host> <role writer> hosts db1, db2 ips 192.168.1.100 mode exclusive </role> <role reader> hosts db1, db2 ips 192.168.1.186, 192.168.1.187 mode balanced </role>
edit the mmm_agent.conf file on each machine it will run on (adjust the
"this" line as needed).
include mmm_common.conf this db1
edit the mmm_mon.conf file on the box it runs on.
include mmm_common.conf <monitor> ip 127.0.0.1 pid_path /var/run/mysql-mmm/mmmd_mon.pid bin_path /usr/libexec/mysql-mmm status_path /var/lib/mysql-mmm/mmmd_mon.status ping_ips 192.168.1.86, 192.168.1.87 auto_set_online 60 </monitor> <host default> monitor_user mmm_monitor monitor_password abc </host> debug 0
all those files edited? Great, start the agent on each MySQL server.
$ /etc/init.d/mysql-mmm-agent start
on the monitor box.
$ /etc/init.d/mysql-mmm-monitor start
you’re up and running, use the control tool. Try these commands:
[[email protected] init.d]# mmm_control show db1(192.168.1.86) master/ONLINE. Roles: reader(192.168.1.187), writer(192.168.1.100) db2(192.168.1.87) master/ONLINE. Roles: reader(192.168.1.186) [[email protected] init.d]# mmm_control move_role writer db2 OK: Role 'writer' has been moved from 'db1' to 'db2'. Now you can wait some time and check new roles info! [[email protected] init.d]# mmm_control show db1(192.168.1.86) master/ONLINE. Roles: reader(192.168.1.187) db2(192.168.1.87) master/ONLINE. Roles: reader(192.168.1.186), writer(192.168.1.100) [[email protected] init.d]# mmm_control move_role writer db1 OK: Role 'writer' has been moved from 'db2' to 'db1'. Now you can wait some time and check new roles info! [[email protected] init.d]# mmm_control show db1(192.168.1.86) master/ONLINE. Roles: reader(192.168.1.187), writer(192.168.1.100) db2(192.168.1.87) master/ONLINE. Roles: reader(192.168.1.186)
Consistency Checks With Maatkit
discussed MySQL replication in depth in the past, and particularly how
replication can get out of sync. Just as with any MySQL replication
topology, Multi-master has that same limitation. Given that, be sure you
are using the Maatkit mk-table-checksum and mk-table-sync technologies to alert
you if they get out of sync, and fix them when they do.
works by calculating the checksums of all your tables on the master and
gathering those into a table. That table then propagates through
replication as well. You then run another check command on the slave side
(or current passive master) to verify that all is ok. See Fixing
you can see, Multi-master replication is not overly complicated to setup and a
lot like master-slave replication. Despite that, it provides a whole host
of advantages like icing on an already great cake. Add to that the MMM
management tool, and you have a very flexible highly available MySQL cluster to
host your application on top of.
MySQL 17.6.10. MySQL Cluster Replication: Multi-Master and Circular Replication