title: MySQL server heartbeat link: https://onemoretech.wordpress.com/2013/10/09/mysql-server-heartbeat/ author: phil2nc description: post_id: 6505 created: 2013/10/09 09:18:56 created_gmt: 2013/10/09 13:18:56 comment_status: closed post_name: mysql-server-heartbeat status: publish post_type: post

MySQL server heartbeat

Actively monitoring replication connectivity with MySQL's heartbeat provides step-by-step instructions on activating the heartbeat option for master-slave replication that became available in v5.5, it's worth a read. Up until recently I haven't seen much reason to upgrade my current farm of stock MySQL 5.1 servers on RHEL 6. A new project to make Openfire more highly available (a topic for another day) and actually running several MySQL master-slave instances in production for WordPress got me thinking about monitoring solutions. The above url came through in onse search. The business case for at least upgrading to MySQL Server 5.5 (and possibly MariaDB) is made in the first paragraph of the post:

Until MySQL 5.5 the only variable used to identify a network connectivity problem between Master and Slave was slave-net-timeout. This variable specifies the number of seconds to wait for more Binary Logs events from the master before abort the connection and establish it again. With a default value of 3600 this has been a historically bad configured variable and stalled connections or high latency peaks were not detected for a long period of time or not detected at all. We needed an active master/slave connection check. And here is where replication’s heartbeat can help us.

Implementing the heartbeat option turns out to be very easy, once you're database has been upgraded to 5.5 or newer. All you need to do is connect to the slave server and turn it on.

mysql> stop slave;
mysql> change master to MASTER_HEARTBEAT_PERIOD = 1;
mysql> start slave;

The heartbeat then become effective, and will show up in a "slave status\G;" query. Two new status variables become available after that: slave_heartbeat_period slave_received_heartbeats (to get these do a "show slave status like '%heartbeat%';") For me the choice isn't very hard at all. The IUS Community repository already has MySQL 5.5 for RHEL 6 (and 5!), so upgrading can be done easily and cleanly. As with all major component upgrades you should first do a mysqldump of the master and then re-initialize both nodes after completion -- there's just too much that can go wrong during any upgrade not to take that precaution. Official doc on upgrading from MySQL 5.1 to 5.5 is on the MySQL site. I've blogged before on how to upgrade using the IUS repo, and the use of the mysql_upgrade script to let databases created under 5.1 work properly with 5.5. In general you'll want to back up /etc/my.cnf and do a separate mysqldump of all databases, including mysql. While it should be possible to do the package upgrade against the existing data file in /var/lib/mysql (or wherever you maintain them), never procede without full backups. Also note that upgrading mysql may result in the upgrade of related packages, such as those for php. It's always best to try things out in a test environment before doing so in production.

Copyright 2004-2019 Phil Lembo