Table of contents
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:
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
