Multi-Master Replication Manager for MySQL hands-on

Preamble

For one of our project we setup MySQL Replication with an active/passive configuration (using MySQL Community edition 5.5.20) as suggested in excellent book called High Performance MySQL. This is simply achieve by setting global dynamic parameter read_only to ON on passive node:

mysql> set global read_only=on;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

Even if not a high level MySQL configuration it could be complex to manage and use it at its maximum at application level: all nodes can be used for read queries and write queries must be directed to active node only. Using virtual IPs to not be obliged to change IPs in application code in case of switchover/failover is more or less mandatory and even so application will have to manage read and write queries destination.

Free tool called Multi-Master Replication Manager for MySQL (MMM) is exactly made to manage such configuration, installation and testing will be described in this blog post. On top of your standard MySQL Replication nodes MMM installation requires an additional node called monitoring node. The monitoring host is whatever server but as you will most probably not dedicate a server only for this it could even be one of your applicative servers.

My MySQL Replication architecture is made of two Red Hat Enterprise Linux Server release 5.5 (Tikanga) x64 and the monitoring host is an old Red Hat Enterprise Linux Server release 5.2 (Tikanga) server. For a minimal MySQL Replication configuration made of two servers you need three additional IPs (on top of the one of your physical servers) i.e. n + 1 to manage the following MMM roles (n being number of MySQL Replication nodes):

  • writer handle by one server at a time (the active node).
  • reader handle by the two servers of my configuration.

In below my MySQL Replication servers are called server1 and server2 and monitoring host is called monhost (fake server names and IPs obliviously):

  • server1 has 10.75.1.234 as physical IP.
  • server2 has 10.75.1.235 as physical IP.
  • monhost has 10.75.1.80 as physical IP.
  • 10.75.1.238 and 10.75.1.239 will be used as virtual IPs for reader role. 10.75.1.240 will be used as virtual IP for writer role.

With a picture:

mmm1
mmm1

Installation

The MMM version I have tested is 2.2.1. As usual when installing there are two distinct philosophy, the manual one starting from a tar file:

[root@server1 mysql-mmm-2.2.1]# make install
mkdir -p  /usr/lib/perl5/vendor_perl/5.8.8/MMM /usr/lib/mysql-mmm /usr/sbin /var/log/mysql-mmm /etc /etc/mysql-mmm /etc/init.d/
cp -r lib/Common/ /usr/lib/perl5/vendor_perl/5.8.8/MMM
[ -f /etc/mysql-mmm/mmm_common.conf ] || cp etc/mysql-mmm/mmm_common.conf /etc/mysql-mmm/
mkdir -p /usr/lib/mysql-mmm/agent/
cp -r lib/Agent/ /usr/lib/perl5/vendor_perl/5.8.8/MMM
cp -r bin/agent/* /usr/lib/mysql-mmm/agent/
cp -r etc/init.d/mysql-mmm-agent /etc/init.d/
cp sbin/mmm_agentd /usr/sbin
[ -f /etc/mysql-mmm/mmm_agent.conf  ] || cp etc/mysql-mmm/mmm_agent.conf  /etc/mysql-mmm/
mkdir -p /usr/lib/mysql-mmm/monitor/
cp -r lib/Monitor/ /usr/lib/perl5/vendor_perl/5.8.8/MMM
cp -r bin/monitor/* /usr/lib/mysql-mmm/monitor/
cp -r etc/init.d/mysql-mmm-monitor /etc/init.d/
cp sbin/mmm_control sbin/mmm_mond /usr/sbin
[ -f /etc/mysql-mmm/mmm_mon.conf    ] || cp etc/mysql-mmm/mmm_mon.conf    /etc/mysql-mmm/
mkdir -p /usr/lib/mysql-mmm/tools/
cp -r lib/Tools/ /usr/lib/perl5/vendor_perl/5.8.8/MMM
cp -r bin/tools/* /usr/lib/mysql-mmm/tools/
cp sbin/mmm_backup sbin/mmm_clone sbin/mmm_restore /usr/sbin
[ -f /etc/mysql-mmm/mmm_tools.conf  ] || cp etc/mysql-mmm/mmm_tools.conf  /etc/mysql-mmm/

But then in any case you will have to resolve the documented dependencies…

That’s why the repository installation is strongly suggested if your Linux is part of the supported ones (fortunately for me RedHat is). First you have to configure Extra Packages for Enterprise Linux (EPEL) with the download-able rpm:

[root@server1 ~]# yum repolist
Loaded plugins: rhnplugin, security
There was an error parsing the RHN proxy settings.
RHN Satellite or RHN Classic support will be disabled.
repo id                                                                      repo name                                                                                                           status
epel                                                                         Extra Packages for Enterprise Linux 5 - x86_64                                                                      enabled: 6,985
repolist: 6,985

Then the list of available packages can be get with:

[root@server1 ~]# yum list "mysql-mmm*"
Loaded plugins: rhnplugin, security
There was an error parsing the RHN proxy settings.
RHN Satellite or RHN Classic support will be disabled.
Available Packages
mysql-mmm.noarch                                                                                         2.2.1-1.el5                                                                                  installed
mysql-mmm-agent.noarch                                                                                   2.2.1-1.el5                                                                                  epel
mysql-mmm-monitor.noarch                                                                                 2.2.1-1.el5                                                                                  epel
mysql-mmm-tools.noarch                                                                                   2.2.1-1.el5                                                                                  epel

I have installed mysql-mmm.noarch (mysql-mmm-agent.noarch is probably enough) on my MySQL Replication servers and mysql-mmm.noarch and mysql-mmm-monitor.noarch on my monitoring host.

Configuration

The configuration files are under /etc/mysql-mmm/ directory. The first to modify and deploy on all servers is mmm_common.conf (I have used my single network interface called eth0):

active_master_role      writer
 
<host default>
        cluster_interface       eth0
 
        pid_path                /var/run/mmm_agentd.pid
        bin_path                /usr/lib/mysql-mmm/
 
        mysql_port              3316
        mysql_pidfile           /mysql55/software/5.5.20/conf/5.5.20.pid
        mysql_rcscript          /etc/init.d/STMmysql-5.5.20
        mysql_cnf               /mysql55/software/5.5.20/conf/my.cnf
 
        replication_user        repladmin
        replication_password    password
 
        agent_user              mmm_agent
        agent_password          password
</host>
 
<host db1>
        ip                      10.75.1.234
        mode                    master
        peer                    db2
</host>
 
<host db2>
        ip                      10.75.1.235
        mode                    master
        peer                    db1
</host>
 
<role writer>
        hosts                   db1, db2
        ips                     10.75.1.240
        mode                    exclusive
</role>
 
<role reader>
        hosts                   db1, db2
        ips                     10.75.1.238, 10.75.1.239
        mode                    balanced
</role>

On monitoring host you need to configure as well mmm_mon.conf file:

include mmm_common.conf
 
<monitor>
        ip                                      10.75.1.80
        pid_path                                /var/run/mmm_mond.pid
        bin_path                                /usr/lib/mysql-mmm/
        status_path                             /var/lib/misc/mmm_mond.status
        ping_ips                                10.75.1.80, 10.75.1.234, 10.75.1.235
</monitor>
 
<host default>
        monitor_user                    mmm_monitor
        monitor_password                password
</host>
 
debug 0

On MySQL Replication servers you need to customize mmm_agent.conf file to reflect server list (db1, db2, …):

include mmm_common.conf
this db1

On all MySQL Replication nodes you need to create mmm_monitor and mmm_agent MySQL accounts:

GRANT REPLICATION CLIENT                 ON *.* TO 'mmm_monitor'@'%' IDENTIFIED BY 'password';
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'%'   IDENTIFIED BY 'password';

Finally on MySQL replication servers start the agent with:

[root@server1 ~]# /etc/init.d/mysql-mmm-agent start
[root@server1 ~]# ps -ef | grep mmm |grep -v grep
root     12410     1  0 Apr10 ?        00:00:00 mmm_agentd
root     12411 12410  0 Apr10 ?        00:01:11 mmm_agentd

On monitoring host start the monitor with:

[root@monhost ~]# /etc/init.d/mysql-mmm-monitor start
[root@monhost ~]# ps -ef | grep mmm | grep -v grep
root      3358     1  0 Apr10 ?        00:00:00 mmm_mond
root      3359  3358  0 Apr10 ?        00:02:54 mmm_mond

Then the tool to use, on monitoring host, is mmm_control. If you have something wrong in your configuration you will see something like (I initially forgot to specify non-default port number, pid file, conf file and stop/start script):

[root@monhost ~]# mmm_control show
  db1(10.75.1.234) master/HARD_OFFLINE. Roles:
  db2(10.75.1.235) master/HARD_OFFLINE. Roles:

When solved you still have a bit of configuration to putting online the MySQL replication hosts:

[root@monhost ~]# mmm_control show
  db1(10.75.1.234) master/AWAITING_RECOVERY. Roles:
  db2(10.75.1.235) master/AWAITING_RECOVERY. Roles:

Achieved with:

[root@monhost ~]# mmm_control set_online db1
OK: State of 'db1' changed to ONLINE. Now you can wait some time and check its new roles!
[root@monhost ~]# mmm_control set_online db2
OK: State of 'db2' changed to ONLINE. Now you can wait some time and check its new roles!
[root@monhost ~]# mmm_control show
  db1(10.75.1.234) master/ONLINE. Roles: reader(10.75.1.238), writer(10.75.1.240)
  db2(10.75.1.235) master/ONLINE. Roles: reader(10.75.1.239)

If you check on your active MySQl Replication server, at network level you should see something like (active server holds one reader IP and the writer one):

[root@server1 ~]# ip addr list dev eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000
    link/ether 00:50:56:ba:00:9b brd ff:ff:ff:ff:ff:ff
    inet 10.75.1.234/24 brd 10.75.1.255 scope global eth0
    inet 10.75.1.238/32 scope global eth0
    inet 10.75.1.240/32 scope global eth0
    inet6 fe80::250:56ff:feba:9b/64 scope link
       valid_lft forever preferred_lft forever

Testing

How to check replication status and nodes availability

[root@monhost ~]# mmm_control checks all all
db2  ping         [last change: 2012/04/10 17:04:39]  OK
db2  mysql        [last change: 2012/04/10 17:04:39]  OK
db2  rep_threads  [last change: 2012/04/10 17:04:39]  OK
db2  rep_backlog  [last change: 2012/04/10 17:04:39]  OK: Backlog is null
db1  ping         [last change: 2012/04/10 17:04:39]  OK
db1  mysql        [last change: 2012/04/10 17:04:39]  OK
db1  rep_threads  [last change: 2012/04/10 17:04:39]  OK
db1  rep_backlog  [last change: 2012/04/10 17:04:39]  OK: Backlog is null

How to move writer role:

[root@monhost ~]# mmm_control show
  db1(10.75.1.234) master/ONLINE. Roles: reader(10.75.1.238), writer(10.75.1.240)
  db2(10.75.1.235) master/ONLINE. Roles: reader(10.75.1.239)
 
[root@monhost ~]# 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!
[root@monhost ~]# mmm_control show
  db1(10.75.1.234) master/ONLINE. Roles: reader(10.75.1.238)
  db2(10.75.1.235) master/ONLINE. Roles: reader(10.75.1.239), writer(10.75.1.240)

Remark:
In this case the writer IP is moved and read_only parameter is updated as expected.

Mode of failover actions for monitor:

[root@monhost ~]# mmm_control mode
ACTIVE

Putting offline a node to solve replication issues:

[root@monhost ~]# mmm_control show
  db1(10.75.1.234) master/ONLINE. Roles: reader(10.75.1.238), writer(10.75.1.240)
  db2(10.75.1.235) master/ONLINE. Roles: reader(10.75.1.239)
 
[root@monhost ~]# mmm_control set_offline db2
OK: State of 'db2' changed to ADMIN_OFFLINE. Now you can wait some time and check all roles!
[root@monhost ~]# mmm_control show
  db1(10.75.1.234) master/ONLINE. Roles: reader(10.75.1.238), reader(10.75.1.239), writer(10.75.1.240)
  db2(10.75.1.235) master/ADMIN_OFFLINE. Roles:
mysql> show slave status;
+----------------+--------------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Slave_IO_State | Master_Host        | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------+--------------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
|                | server1.st.com     | repladmin   |        3316 |            60 | mysql-bin.000010 |               26652 | 5-relay-bin.000009 |           253 | mysql-bin.000010      | No               | No                | orgsecdb        |                     |                    |                        |                         |                             |          0 |            |            0 |               26652 |             551 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |             0 |               |              0 |                |                             |                1 |
+----------------+--------------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.00 sec)
[root@monhost ~]# mmm_control set_online db2
OK: State of 'db2' changed to ONLINE. Now you can wait some time and check its new roles!
[root@monhost ~]# mmm_control show
  db1(10.75.1.234) master/ONLINE. Roles: reader(10.75.1.239), writer(10.75.1.240)
  db2(10.75.1.235) master/ONLINE. Roles: reader(10.75.1.238)
mysql> show slave status;
+----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Slave_IO_State                   | Master_Host        | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Waiting for master to send event | server1.st.com     | repladmin   |        3316 |            60 | mysql-bin.000010 |               26652 | 5-relay-bin.000010 |           253 | mysql-bin.000010      | Yes              | Yes               | orgsecdb        |                     |                    |                        |                         |                             |          0 |            |            0 |               26652 |             551 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 |
+----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.00 sec)

References

One thought on “Multi-Master Replication Manager for MySQL hands-on

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>