Using Flipper to manage MySQL Pairs

As discussed previously in Options using MySQL pairs I have started evaluating the strengths and weaknesses of various open source options. This is an evaluation of Flipper, a product from Proven Scaling a MySQL consulting organization.

Overall

  • Pros When correctly configured and with a working installation it just works, simple and functional, which is good design.
  • Cons The functionality is incomplete especially when it comes to edge cases, additional manual scripting especially for MySQL specifics is necessary and could have be easily added.

The Flipper documentation is detailed, but I found the implementation could have been easier without reading most of the documentation first. The software comes in RPM packages, but as I’m using Ubuntu, installation is via source.

The documentation however assumes your Master/Fail Over master MySQL environment is already correctly configured, and running with Virtual IP’s and the correct read_only status. There is no information for configuration here, so you need to be comfortable with MySQL Replication before starting.

The default notification of IP addresses is managed by arping. Under Ubuntu 8.04 this actually throws an error for a virtual IP on the same host and then Flipper fails to operate as designed. I spent some time to diagnose the problem first before submitting to the flipper-devel list. The response was prompt, the recommendation was to actually use Linux Heatbeat for the purposes of the address notification. The installation of this was easy, via apt-get and the configuration change to Flipper a single row meta data change in one table, which showed good design in this flexibility.

Overall however, Flipper is only a partial solution. It lacked some functionality I just expected would be included in the initial version. The ability to set read_only on a server, Flipper handles this for a controlled failover, but not for just setting against the read only host. There is no means of starting a MySQL slave using the Flipper CLI, you need to do this again manually with additional scripts.

Overall, while a level of information feedback is available, and controlled failover of a correctly working and configured environment works great, manual steps are necessary in the “not ideal” case, when the tool could offer more.

Some points in addition to the supplied documentation.

  • The ‘flipper’ user may only need SELECT privileges to the necessary meta data tables, but it requires ‘SUPER’ privilege for SLAVE management.
  • Installation of arping necessary with ‘sudo apt-get install arping’
  • The arping command syntax needs to be updated for Ubunutu to ”/usr/sbin/arping -I $sendarp_interface -c 5 $sendarp_ip’ ‘. The path and options change. See 2.5.2 ARP sending command. You also need to adjust the Sudo Privileges for the command

Using Heatbeat

The solution to the ‘arping’ problem was to actually use a different command, send_arp which is part of Heatbeat. It’s ironic that Heatbeat is an entire product that could be used for managing pairs. However the following did work.

sudo apt-get install heartbeat
# Install fails consistently on 8.04, following needed
sudo apt-get update
sudo apt-get install heatbeat
# Weird but necessary
INSERT INTO masterpair (masterpair, name, value) VALUES
  ('pairname', 'broadcast', '192.168.2.255');
UPDATE masterpair SET value="/usr/lib/heartbeat/send_arp -p /tmp/send_arp.pid -i 100 -r 5 $sendarp_interface $sendarp_ip auto $sendarp_broadcast $sendarp_netmask" WHERE masterpair="pairname" AND name="send_arp_command";

Bugs

As a result of this, I found at least one bug. With the send_arp_command you can specifiy $sendarp_broadcast as an argument in the value, however when you do, if the variable is not set, there should be a configuration error in Flipper, rather then it attempting to execute a remote SSH command with the variable undefined which causes an error, but could if not support the write variable protection cause other issues depending on syntax used.

Annoyances

1. One annoying thing was unnecessary stderr for SSH connections under Ubuntu, you can fix by doing a 2>/dev/null to address it. It was however useful in debugging to see the number of SSH connections, and then it help find the ‘arping’ issue, but in general it’s annoying, for example.

./flipper developer swap
Connection to 192.168.2.181 closed.
Connection to 192.168.2.181 closed.
Connection to 192.168.2.187 closed.
Connection to 192.168.2.187 closed.
Connection to 192.168.2.187 closed.
Connection to 192.168.2.187 closed.
Connection to 192.168.2.187 closed.
Connection to 192.168.2.187 closed.
Connection to 192.168.2.181 closed.
Connection to 192.168.2.181 closed.
Connection to 192.168.2.181 closed.
Connection to 192.168.2.181 closed.

2. The slave is listed first, you just automatically think master/slave, the output should be formatted in this though.

./flipper developer status 2>/dev/null
MASTERPAIR: developer
NODE: beta181 has read IP, is read-only, replication running, 0s delay
NODE: alpha187 has write IP, is writable, replication running, 0s delay

3. No information after swap. When you do a swap, it would be good for the status to be shown. You are only going to run this command anyway to confirm.

Enhancements

I started working on modifying the ‘flipper’ script to support a read_only command, but I only had 1 day and ran out of time to finish.

Comments

  1. Coway says

    Very interesting article. Have you used MMM Manager from google mysql master-master replication? When you swap write IP to another node using Flipper, is there any delay for write? I used MMM Manager and found out after moving write role to another node, it has at least 4-second non-function period for inserts, or that is to say, you will lose 4-second transactions.