Flipper Manual

Flipper is a tool for managing pairs of MySQL servers replicating in a master-master configuration.

1. Overview

Flipper is a tool for managing read and write access pairs of MySQL servers (known as nodes) arranged in a master-master replication formation (known as a masterpair). Client machines do not connect "directly" to either node; instead, read and write access to the masterpair is managed through a pair of IP addresses: one for read, one for write. Flipper allows you to move these IP addresses between the nodes in a safe and controlled manner.

A basic Flipper system looks like the following:

Flipper is written in Perl, and has been written to be as portable as possible. It does not require any Perl modules outside of the core. It has been tested with MySQL 5.0, but should work with MySQL 4.0 and later.

2. Quick Start

Follow the instructions here to get started with Flipper in just a few minutes.

These instructions relate to the pre-release versions of Flipper.

2.1 Before you begin

Before you begin, make sure you have the following together:

• Two machines to act as masters, on the same subnet, vlan, etc. and capable of floating an IP address between them.
• Two IP addresses reserved to share between the masters for reads and writes.

2.2 Installation

First of all, check out Flipper from the Proven Scaling Subversion repository to the node you'll use for managing your masterpairs.

The flipper script assumes ‘perl’ will be in ‘/usr/bin’. If your Perl executable is located somewhere else, either change the #! line in the ‘flipper’ script (the first line), or better still put a symlink in place in ‘/usr/bin/perl’ to your ‘perl’ binary, as there are plenty of other scripts out there which rely on ‘perl’ being in ‘/usr/bin’.

Copy the ‘flipper’ script to ‘/usr/bin’.

Copy the ‘lib/Flipper’ directory to some place in Perl's @INC on your system. To list the directories in @INC on your system, run perl -e 'print join "\n",@INC'.

‘/usr/lib/perl5/site_perl’, ‘/usr/local/lib/perl5/site_perl’ on Linux, Solaris, and BSD, and ‘/Library/Perl’ on Mac OS X are good places to put the ‘Flipper’ directory.

For more information on installing Flipper, See section Installation.

2.3 Metadata

Flipper uses a database for storing it's configuration information. This database can be stored in any DBMS supported by Perl's DBI, but currently only MySQL has been tested.

Create the metadata database on a node, then populate it using the schema definition contained in the ‘sql/metadata.sql’ file in subversion. The metadata database can reside on one of the masterpairs that you intend to manage using Flipper, or it can live on a separate database server.

Currently, Flipper requires only read access to the metadata database.

2.4 Pointing Flipper at the Metadata

Flipper uses a DSN-like string to locate it's metadata, along with a username and password for authentication.

These three items of information can be passed to flipper on the command line, using the --metadata or -m switch for the DSN string, and --username or -u and --password or -p switches for the username and password respectively.

You will probably find it more convenient to add a [flipper] section to your ‘my.cnf’ file (or create a ‘~/.my.cnf’ file) containing lines for the metadata DSN, username and password, like this:

[flipper]
metadata=DBI:mysql:database=flippermetadata;mysql_socket=/tmp/mysql.sock
username=flippermetadata
password=letmein

2.5 Configuration

Configuration has to be done manually at the moment. Future versions of Flipper will allow you to use the script itself to manage the configuration.

First, decide on a name for your first masterpair, and decide on names for the two nodes.

2.5.1 IP address information

Insert rows into the masterpair and node tables for the IP addresses of the two nodes and the read and write IP addresses that you will be moving between the nodes. See ip, read_ip, write_ip.

INSERT INTO masterpair
  (masterpair, name, value)
VALUES
  ('<masterpair name>', 'read_ip', '<read ip>'),
  ('<masterpair name>', 'write_ip', '<write ip>');

INSERT INTO node 
	(masterpair, node, name, value)
VALUES
  ('<masterpair name>', '<node 1 name>', 'ip', '<node 1 ip>'),
  ('<masterpair name>', '<node 2 name>', 'ip', '<node 2 ip>');

You'll also need to insert a row into the masterpair table for the netmask of the network containing the nodes. See netmask.

INSERT INTO masterpair 
  (masterpair, name, value)
VALUES
  ('<masterpair name>', 'netmask', '<network netmask>');

2.5.2 ARP sending command

When an IP address is moved from one node to another, an ARP message needs to be sent out to inform other nodes (servers, routers etc) on the network that the IP address is now available on a different physical (MAC) address.

Most modern UNIX-based operating systems send an ARP message out automatically when an IP address is brought up on an interface, and will handle the ARP messages correctly when they receive them.

Linux does not send ARP messages out automatically, although it does handle receipt of them correctly. Therefore Flipper needs to be told how to send the ARP message out.

On RedHat Enterprise Linux, CentOS and Fedora, the arping command, supplied with the operating system, can be used to send ARP messages. In order to use arping, insert a row into the masterpair table.

INSERT INTO masterpair
  (masterpair, name, value)
VALUES
  ('<masterpair name>', 'send_arp_command',
   '/sbin/arping -I $sendarp_interface -c 5 -U -A $sendarp_ip');

You may need to insert a row into the masterpair table for the broadcast address of the network containing the nodes if this is required by the command you're using to send ARP messages.

For more information, see send_arp_command.

2.5.3 MySQL authentication

Flipper needs to be given authentication information so it can connect to the MySQL servers running on the nodes. By default, it will attempt to connect as root with no password, on port 3306. If these defaults are not suitable, insert rows into the masterpair table to tell Flipper how to log into MySQL. This assumes the same authentication credentials are to be used on each node. See mysql_user, mysql_password, mysql_port.

INSERT INTO masterpair
  (masterpair, name, value)
VALUES
  ('<masterpair name>', 'mysql_user', '<mysql username>'),
  ('<masterpair name>', 'mysql_password', '<mysql password>');

2.5.4 SSH authentication

By default, Flipper will ssh to the nodes as the user running the flipper script. To change this, either set the ssh_user configuration variable or alternatively edit ‘~/.ssh/config’.

INSERT INTO masterpair
  (masterpair, name, value)
VALUES
  ('<masterpair name>', 'ssh_user', '<ssh username>');

By default, Flipper will use sudo for privilege escalation on the nodes, where required. This is required for non-superusers to execute operations such as bringing down/up IP addresses. You will need to configure sudo on the nodes to enable this. If you set ssh_user to 'root', you can turn the use of sudo off if required by setting the use_sudo to 0.

The security implications of the various possible settings for ssh_user, use_sudo, and ssh and sudo configuration on the nodes are significant. You are strongly advised to make yourself aware of them. See SSH and sudo Security for more information.

2.5.5 Check configuration

Once you've pointed Flipper at the Metadata, and configured Flipper correctly, you should be able to use Flipper's config command to display the configuration information for a particular masterpair. For example, to display all the configuration values for a masterpair called ‘mike’:

$ flipper mike config
MASTERPAIR: mike
        write_ip: 10.6.0.67
        send_arp_command: arping -I $sendarp_interface -c 5 -U -A $sendarp_ip
        read_ip: 10.6.0.66
        netmask: 255.255.255.0
NODE: first_node
        ip: 10.6.0.64
NODE: second_node
        ip: 10.6.0.65
$ 

If you've not pointed Flipper to the Metadata database correctly, you'll be presented with an error which should give useful information as to what the problem was:

$ flipper mike config
Couldn't connect using username 'root' to DSN:
DBI:mysql:database=wrongdb

The error from DBI was:
Unknown database 'wrongdb'

Check that the DSN, username and password specified in my.cnf or as options
to this script are all correct.
$ 

To display a list of the masterpairs, just run flipper with no command:

$ flipper
The following masterpairs exist:
        mike
$

2.6 Set up IP addresses

Once Flipper's configured, it's ready to use. The status command gives an at-a-glance report on the status of a particular masterpair. The first time you run Flipper, the read and write IPs won't be up, and the status command will give output similar to this:

$ flipper mike status
MASTERPAIR: mike
NODE: first_node is writable, replication running, 0s delay
NODE: second_node is writable, replication running, 0s delay
WARNING: No node has the read IP
WARNING: No node has the write IP
$

This tells us that both nodes in the masterpair ‘mike’ are up, MySQL is writable on both nodes, replication is running on both nodes with no delay, but the read and write IPs are not up on either node. We use Flipper's set command to bring the IP up on the appropriate node. In this case, we bring the read IP up on the ‘first_node’ node, and the write IP up on the ‘second_node’ node. The warnings are self-explanatory.

$ flipper mike set read first_node
WARNING: read IP is not up on second_node node.
WARNING: Won't attempt to take down read IP on second_node node.
$ flipper mike set write second_node
WARNING: write IP is not up on first_node node.
WARNING: Won't attempt to take down write IP on first_node node.
$ flipper mike status
MASTERPAIR: mike
NODE: first_node has read IP, is read-only, replication running, 0s delay
NODE: second_node has write IP, is writable, replication running, 0s delay
$

2.7 Using Flipper

This is just a quick overview of what Flipper can do. More detail can be found elsewhere in this documentation. Help is available on the command line by running flipper --help for usage information and perldoc flipper for a brief overview.

Flipper's set command ensures an IP is running on a particular node. If it's already running on the node you specify, it won't be moved. If it's running on the other node, it will be moved.

The swap command moves IPs from one node to another. If you specify an IP, it will move only the IP you specify, otherwise all IPs will be moved to the other node.

The disable command moves any IPs that are up on a particular node to the other node. This is useful for taking a machine out of use for maintenance. The fail command is similar to the disable command, but is used for handling nodes which have suffered an external permanent or semi-permanent failure.

The following example demonstrates the use of the commands. We use the status command to display the current status:

$ flipper mike status
MASTERPAIR: mike
NODE: first_node has read IP, is read-only, replication running, 0s delay
NODE: second_node has write IP, is writable, replication running, 0s delay
$ flipper mike swap
$ flipper mike status
MASTERPAIR: mike
NODE: first_node has write IP, is writable, replication running, 0s delay
NODE: second_node has read IP, is read-only, replication running, 0s delay
$ flipper mike set read first_node
$ flipper mike status
MASTERPAIR: mike
NODE: first_node has read IP, has write IP, is writable, replication running, 0s delay
NODE: second_node is read-only, replication running, 0s delay
$ flipper mike disable first_node
$ flipper mike status
MASTERPAIR: mike
NODE: first_node is read-only, replication running, 0s delay
NODE: second_node has read IP, has write IP, is writable, replication running, 0s delay
$

3. Installation

3.1 Obtaining Flipper

3.1.1 Packages

Pre-built packages for flipper can be downloaded from the Proven Scaling Mirror. If a package is not available for your operating environment, then don't fear - it's very easy to download the source code and install it.

3.1.2 Source Code

To fetch the most recent stable version of Flipper from Subversion, simply run the following command in a shell:

svn checkout svn://svn.provenscaling.com/public/flipper/trunk/ flipper

If you want to check Flipper out to a different directory, specify something other than ‘flipper’ as the second parameter to svn checkout.

3.2 Installing Flipper from svn

3.2.1 Where's Perl?

The flipper script assumes ‘perl’ will be in ‘/usr/bin’. If your Perl executable is located somewhere else, either change the #! line in the ‘flipper’ script, or better still put a symlink in place in ‘/usr/bin/perl’ to your ‘perl’ binary, as there are plenty of other scripts out there which rely on ‘perl’ being in ‘/usr/bin’.

3.2.2 Copying files

You can either copy the ‘flipper’ script to somewhere in your PATH and the Perl modules into @INC, or simply run the ‘flipper’ script from any directory (usually your home directory), invoke it as ./flipper and copy the Perl modules into the same directory.

3.2.2.1 Copying the script to your PATH

You will almost definitely need super-user privileges to do this.

The ‘flipper’ script can be copied to ‘/usr/bin’, ‘/usr/local/bin’ or some other directory in your PATH.

Copy the ‘lib/Flipper’ directory from subversion to some place in Perl's @INC on your system. To list the directories in @INC on your system, run perl -e 'print join "\n",@INC'.

‘/usr/lib/perl5/site_perl’, ‘/usr/local/lib/perl5/site_perl’ on Linux, Solaris, and BSD, and ‘/Library/Perl’ on Mac OS X are good places to put the ‘Flipper’ directory.

3.2.2.2 Installing into your home directory or other arbitary location

The ‘flipper’ script can run anywhere, so you can copy it to your home directory or other arbitary location. This can be useful if you want to install Flipper on a node on which you don't have super-user privileges.

If you do this, and you're only going to ever invoke Flipper as ./flipper, you can copy the ‘lib/Flipper’ directory from subversion to the same directory as the ‘flipper’ script. This works by virtue of the current working directory always appearing in @INC.

If you're on Mac OS X with a case insensitive filesystem, you won't be able to put the ‘Flipper’ subdirectory and the ‘flipper’ script in the same directory. The workaround is to put the ‘Flipper’ directory into ‘/Library/Perl’, as superuser privileges shouldn't be required for that.

4. Configuration

4.1 Metadata Storage

Flipper has a very flexible and extensible metadata storage system.

In theory it is possible for metadata to be stored anywhere in any arbitary format.

Currently only access to metadata stored in a database using Perl's DBI is implemented, but others can be put in place later, such as XML or YAML.

4.1.1 DBI metadata storage

Any DBMS that's supported by DBI can be used to store Flipper's metadata. Currently only MySQL has been tested, but if you have success or failure using another DBMS, please let us know.

There are no restrictions on where the metadata database resides - it can live anywhere, as long as Flipper can get access to read from it.

Currently, Flipper requires only read access to the metadata database. Future versions of Flipper will require write access to the metadata database.

The metadata database contains two tables - one for information relating to masterpairs, and one for information specific to nodes. The schema is available in the sql/metadata.sql file in subversion.

4.2 Metadata Access

4.2.1 Data Source Naming Scheme

Flipper uses a naming scheme for it's data source names (DSNs) which is compatible with Perl's DBI.

When storing metadata in a DBMS and accessing it using DBI, the DSN should be the same as would be passed in a DBI->connect call, for example DBI:mysql:database=flippermetadata;mysql_socket=/tmp/mysql.sock.

Other metadata storage methods can be chosen by specifying something other than DBI before the first colon in the DSN - for example, a future YAML storage method might be specified using YAML:file=/etc/flipper.yaml".

4.2.2 Authentication

As well as the DSN, Flipper can use a username and password if the metadata storage method requires authentication to be used.

4.2.3 Pointing Flipper at Metadata

The three items of information (metadata DSN, username and password) can be passed to flipper on the command line.

Use the --metadata or -m switch to tell Flipper where to find the metadata. The --username or -u and --password or -p switches specify the username and password respectively.

You will probably find it more convenient to add a [flipper] section to your my.cnf file (or create a ~/.my.cnf file) containing lines for the metadata DSN, username and password, like this:

[flipper]
metadata=DBI:mysql:database=flippermetadata;mysql_socket=/tmp/mysql.sock
username=flippermetadata
password=letmein

Any options provided on the command line will override those specified in a my.cnf file.

4.3 Configuration Variables

There are three types of configuration variable in Flipper:

• Masterpair configuration variables
• Node configuration variables
• Cascading variables

Configuration variables have a name and a value. Some configuration variables are compulsory, some are optional. Some optional configuration variables have a default value which is used if the variable is not specified.

Masterpair configuration variables and node configuration variables are specific to masterpairs and nodes respectively.

Cascading variables can be specified at masterpair or node level. If a cascading variable is specified at node level, then it will be used for that node. If a cascading variable is specified at masterpair level, then it will be used for both nodes in the masterpairs - unless it's also specified at node level, in which case the node level value will override the masterpair level value.

4.4 Masterpair and Node names

Masterpair and node names can be up to 20 characters long, and must not contain spaces. The use of other characters which might confuse the shell isn't recommended - stick to alphanumerics, underscore, hyphen and dot to be safe.

Node names are used internally by Flipper only. They need not bear any resemblance to actual hostnames. You could use ‘first_node’ and ‘second_node’ for the two nodes in all your masterpairs, or you may feel more comfortable using node names which resemble the hostnames.

4.5 Network configuration

4.5.1 IP address configuration

4.5.1.1 Node IP addresses

Each node has a base IP address. This is stored as the ip at node level.

Flipper will use this base IP address to connect to the node's ssh daemon and MySQL server. If the ssh daemon is bound to a different IP address to the base address on the node, then you can store the IP address for ssh connections as ssh_ip at node level.

The MySQL server must be listening on the base IP address of the node.

4.5.1.2 Read and Write IP addresses

The read and write IP addresses are used by external clients to query the data stored on the masterpair. Clients performing read operations only should connect to the read IP address. Clients performing read/write operations should connect to the write IP address. These are stored as read_ip and write_ip at masterpair level.

The read and write IP addresses need not be on the same network (either in a physical or logical sense) as the base address of the nodes. They do however need to be on the same network as each other.

4.5.1.3 Netmask and Broadcast addresses

Flipper also needs to know the netmask, and in some cases the broadcast address, for the network on which the read and write IP addresses reside. Although the broadcast address is not always required, it's advisable to always configure it.

These are stored as netmask and broadcast at masterpair level.

4.5.2 Interface configuration

Flipper needs to know which physical network interface it should use for the read and write IP addresses on each node. If there is only one physical interface configured, then Flipper will use that. If there are multiple physical interfaces on the machine, then Flipper will need to be told which one to use.

The interface is stored as interface. This can be stored at either masterpair level, or node level. If both nodes are to use the same interface for the read and write IPs, then store it at masterpair level, and it will be applied to both nodes.

4.6 SSH and sudo configuration

In order to execute commands on the nodes, Flipper needs to be able to connect to the nodes using ssh. The following configuration variables are used to control how Flipper connects using ssh to the nodes:

• ssh_ip
• ssh_user
• use_sudo

The default configuration is that ssh_ip is unset, ssh_user is unset and use_sudo is set to 1, representing "on".

As described above, if the ssh daemon is bound to a different IP address to the base address on any node, then you can store the IP address for ssh connections as ssh_ip at node level. If Flipper is to use the base IP address of a node for ssh connections, ssh_ip should not be defined for that node.

ssh_user is the user which Flipper will connect as via ssh. If this is unset, Flipper will use the current user to ssh to the nodes, unless overridden by a setting in ‘~/.ssh/config’.

If the ssh_user is a non-root user, then you should leave use_sudo set to 1. This will enable Flipper to use sudo to execute commands which require superuser access - typically the commands used to bring up and take down IP aliases on the nodes.

If you have set ssh_user to ‘root’, then you can disable the use of sudo by changing the use_sudo to 0.

Although ssh_user and use_sudo can both be specified at node level, we recommend that they are only specified at masterpair level and nodes within a masterpair are identically configured.

4.6.1 SSH and sudo Security

You should ensure that you are fully aware of the security implications of how you configure Flipper to work with ssh. The default settings of ssh_user and use_sudo may not be appropriate for the security environment in your organisation.

We would recommend that Flipper be configured to connect to nodes as a specified user with a restricted shell, allowing connections only from the appropriate IP address(es), and privilege escalation for that user via sudo should be restricted to the required commands only.

4.6.1.1 Restricting sudo usage

We recommend that you restrict sudo usage to the bare minimum that Flipper requires to operate. This is particularly important if you are using a role account (eg. an account called ‘flipper’) for Flipper to use on the nodes.

In order to do this, add the following line to your sudoers file. Always edit the sudoers file by using the visudo command.

flipper ALL = NOPASSWD: /sbin/ifconfig, /sbin/arping

This command instructs sudo to allow the ‘flipper’ user access to run the /sbin/ifconfig and /sbin/arping commands as root without a password.

If the path to ifconfig (see path_to_ifconfig) is different on your system, you should change the sudoers line accordingly.

If you are not using a send_arp_command in your environment, then there is no need to specify one here. If you're using something other than /sbin/arping, you should change the sudoers line accordingly.

4.6.1.2 Restricting ssh access

If you use a special account solely for Flipper on the nodes, sometimes called a headless, role or anonymous account, then it is usually desirable to use a restricted shell for that account to stop the role account being used for anonymous actions on the server.

An example restricted shell for use with Flipper can be found in ‘flippersh’. This assumes /sbin/arping is being used in your send_arp_command - if something else (or indeed nothing) is being used, change the restricted shell accordingly.

4.7 MySQL configuration

Flipper needs to connect to MySQL on the nodes.

In order to do this, the mysql_user, mysql_password and mysql_port configuration variables are used to specify the username and password that should be used to connect to MySQL, and the port on the node which should be connected to.

MySQL should be bound to the base IP address of each node.

The user that Flipper connects to on the nodes requires the SUPER privilege.

We recommend that you create a user exclusively for the use of Flipper with just the SUPER privilege. We recommend not using the MySQL root user for use with Flipper.

5. Usage

Typically, the read IP will be up on one node, and the write IP will be up on the other node. There's nothing to stop you having both read and write IPs up on the same node though. This can be very useful in certain situations, for example if you've got unbalanced hardware (one node significantly more powerful or loaded than the other) or as a short term measure if there are connectivity issues or maintenance is being carried out.

5.1 Command Line

Flipper takes options, a masterpair, a command and sometimes parameters on it's command line:

flipper [<options>] [<masterpair> <command> [<parameters>]]

Options control the general operation of Flipper, for example ‘-v’ or ‘--verbose’ which turns on verbose output and ‘-m’ or ‘--metadata’ which tells Flipper where to find it's metadata.

The masterpair is specified so Flipper knows what it's going to operate on. If no masterpair is specified, Flipper will display a list of the available masterpairs.

Commands tell Flipper what to do. Parameters are used by the commands. Some commands, such as status require no parameters. Others, such as the disable command, require paramters - in this case, the name of a node to disable.

If you give flipper an unknown command or option, you'll be presented with some brief usage help. You can also display the usage information by running flipper --help.

5.2 Commands

5.2.1 Displaying configuration - the config command

To display the configuration of a masterpair and it's nodes, use the config command. The masterpair name will be displayed, followed by the configuration variables for that masterpair. After that, for each node, the node name and configuration variables specific to that node will be displayed:

$ flipper customerdata config
MASTERPAIR: customerdata
        write_ip: 192.168.4.54
        send_arp_command: /sbin/arping -I $sendarp_interface -c 5 -U -A $sendarp_ip
        read_ip: 192.168.4.55
        netmask: 255.255.240.0
NODE: dell_2850
        ip: 192.168.3.234
	interface: eth0
NODE: hp_dl380
        ip: 192.168.2.52
	interface: eth1
$ 

5.2.2 Displaying the status of a masterpair - the status command

The status command can be used to display the current status of a masterpair. It also acts as a healthcheck for the current operation of the masterpair - if there are any problems, warnings will be given.

Information about which nodes have which IPs, the read-only or read-write state of the MySQL server and the replication state are given. A typical 'healthy' status output should look like this:

$ flipper customerdata status
MASTERPAIR: customerdata
NODE: dell_2850 has read IP, is read-only, replication running, 0s delay
NODE: hp_dl380 has write IP, is writable, replication running, 0s delay
$

Warnings will be displayed if there are any problems:

• Any IP is up on more than one node, or not up on any node
• Replication isn't running
• Inconsistencies in read-only / read-write state (eg. if the server has the write IP, but is read-only)
• Problems connecting to MySQL or ssh on any node

5.2.3 Moving an IP to a node - the set command

The set command ensures that the specified IP is up on the given node. It takes two parameters - firstly the name of an IP, and secondly, the name of a node.

If the IP is currently up on the other node to the given one, then it will be taken down on that other node and brought up on the given node. If the write IP is being moved, replication will be synced.

Nothing will happen if the specified IP is already up on the given node and an information message will be displayed. If the specified IP is not up on the other node to the one given (ie. it is not up on either node), no attempt will be made to take down the IP from the other node, but if the write IP is being dealt with, replication will still be synced.

The following example demonstrates the use of the set command:

First, display status of customerdata masterpair:

$ flipper customerdata status
MASTERPAIR: customerdata
NODE: dell_2850 has read IP, is writable, replication running, 0s delay
NODE: hp_dl380 is writable, replication running, 0s delay
WARNING: MySQL server on read IP is writable
WARNING: No node has the write IP

Put the write IP on dell_2850:

$ flipper customerdata set write dell_2850
WARNING: write IP is not up on hp_dl380 node.
WARNING: Won't attempt to take down write IP on hp_dl380 node.

Display status again, noticing that dell_2850 now has the write IP:

$ flipper customerdata status 
MASTERPAIR: customerdata
NODE: dell_2850 has read IP, has write IP, is writable, replication running, 0s delay
NODE: hp_dl380 is read-only, replication running, 0s delay

Put the read IP onto hp_dl380:

$ flipper customerdata set read hp_dl380

Display status again, noticing that the read IP has moved to hp_dl380:

$ flipper customerdata status
MASTERPAIR: customerdata
NODE: dell_2850 has write IP, is writable, replication running, 0s delay
NODE: hp_dl380 has read IP, is read-only, replication running, 0s delay

Finally, put write IP on dell_2850. This has no effect, as it's already there:

$ flipper customerdata set write dell_2850
INFO:  The write IP is already up on the dell_2850 node.

5.2.4 Swapping IPs from one node to the other - the swap command

The swap command moves a single IP, or all IPs to the other node to the one on which they're running. It takes an optional parameter, which is the name of an IP. If this parameter is specified, then only the given IP will be swapped to the other node. If this parameter is not specified, all the IPs will be moved to the other node.

The following example demonstrates the use of the swap command:

First, display status of masterpair:

$ flipper customerdata status
MASTERPAIR: customerdata
NODE: dell_2850 has read IP, has write IP, is writable, replication running, 0s delay
NODE: hp_dl380 is read-only, replication running, 0s delay

Move the read IP to the other node:

$ flipper customerdata swap read

Display status again, noticing that the read IP has moved to the other node:

$ flipper customerdata status
MASTERPAIR: customerdata
NODE: dell_2850 has write IP, is writable, replication running, 0s delay
NODE: hp_dl380 has read IP, is read-only, replication running, 0s delay

Move all IPs to the other node:

$ flipper customerdata swap

Display status again, noticing that the IPs have moved to the other node:

$ flipper customerdata status
MASTERPAIR: customerdata
NODE: dell_2850 has read IP, is read-only, replication running, 0s delay
NODE: hp_dl380 has write IP, is writable, replication running, 0s delay

5.2.5 Moving IPs away from a node - the disable command

If you wish to remove all IPs from a node, perhaps to undertake some maintenance work, then use the disable command. It requires a single parameter, which is the name of a node. All the IPs which are up on the specified node will be moved to the other node. If there are no IPs up on the specified node, nothing will happen and an informational message will be displayed.

The following example demonstrates the use of the disable command:

First, display status:

$ flipper mike status
MASTERPAIR: mike
NODE: first_node has read IP, is read-only, replication running, 0s delay
NODE: second_node has write IP, is writable, replication running, 0s delay

Disable dell_2850, that is, move read IP to hp_dl380:

$ flipper mike disable first_node

Show status, noticing that the read IP has moved to hp_dl380:

$ flipper mike status
MASTERPAIR: mike
NODE: first_node is read-only, replication running, 0s delay
NODE: second_node has read IP, has write IP, is writable, replication running, 0s delay

Disable hp_dl380, that is, move all IPs to dell_2850:

$ flipper mike disable second_node

Show status again, noticing that all IPs have now moved to dell_2850:

$ flipper mike status
MASTERPAIR: mike
NODE: first_node has read IP, has write IP, is writable, replication running, 0s delay
NODE: second_node is read-only, replication running, 0s delay

There are no IPs on the second node, so disable will do nothing:

$ flipper mike disable second_node
INFO:  No IPs on the second_node node.

5.2.6 Handling a failed node - the fail command

The set, swap and disable commands all rely on being able to connect to ssh and MySQL on both nodes in order to carry out operations. This is to ensure that the masterpair is always in a consistent, operable state.

If a node fails due to hardware or network failure, and there is no prospect of recovery of that node within an acceptable timeframe, then it is possible to use the fail command.

Under normal circumstances, the fail command acts exactly the same as the disable command - it will remove IPs from the given node, and bring them up on the other node, whilst ensuring the masterpair is in a consistent, operable state.

The fail command differs in that it will attempt to contact the failed node, and if it can't, it will carry on regardless. As such, the ‘--yes’ option must be specified on the command line to confirm that the user wishes to carry out this operation.

If a node has suffered a problem with MySQL, but can still be contacted via ssh, then the fail command will handle moving IPs away from the failed node correctly, but will not attempt to synchronise replication.

If a node cannot be contacted via ssh, then the fail command won't be able to take down the IPs from the failed node. There are dangers associated with this, as it could potentially leave a masterpair in an inconsistent state - for example, if used to fail a node which is only temporarily inaccessible, when that node comes back, one or more IPs will be up on both nodes.

6. Applications and Flipper

6.1 Common MySQL error messages

In a typical Flipper-based multi-master system, there are a few error messages which any client should be able to handle in order to provide a "seamless" experience for users, despite failover occurring. During any failover using Flipper (and indeed other systems based on a similar model) the following errors must be handled:

‘ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement’

After the active master has been set read-only using SET GLOBAL read_only=1, any client which tries to run a new data-changing statement will receive this error.

‘ERROR 2006 (HY000): MySQL server has gone away’

If the failover process occurs while a connection is idle, or if the connection is killed by the Quiesce::KillAll module, this error will be received on the next attempt to run a query.

If you choose to use the Quiesce::KillAll module to kill off connected users before a failover, you must handle two additional errors:

‘ERROR 2013 (HY000): Lost connection to MySQL server during query’

If a connection is killed while it is currently running a query, the query will be aborted, and this error will be generated by the client.

‘ERROR 1040 (00000): Too many connections’

If a connection attempt is made during the quiesce process, while max_connections is set to not allow new connections.

In nearly all cases, the best action to take would be to disconnect (if the connection is not already dropped), pause, reconnect, and retry.

7. Bugs

There are almost definitely plenty of bugs in flipper. We would be delighted to hear from you if you find a bug - see See section Contacting Proven Scaling.

If you find a bug, please let us know which version of Flipper and Perl you're using (give us the output of flipper --version), let us know the platform(s) you're using (operating system, version, distribution, hardware) and also send us the output that Flipper produces when reproducing the bug when invoked with the ‘--verbose’ and ‘--debug’ switches (or ‘-vd’ for short).

7.1 Known Bugs

Display of replication delay causes an error when using MySQL 4.0 servers. The Seconds_Behind_Master variable is used to determine replication delay, and this is only present in MySQL 4.1.1 and newer. It is possible to determine the replication delay on MySQL 4.0 from the Time column in the processlist for the slave SQL thread.

Behaviour of swap command when an IP is not up on either host is accidentally fixed to taking it down on the second of the two nodes in the masterpair (which will of course fail, as it's not up on that node) and bringing it up on the first of the two nodes. Correct behaviour should be to do nothing with that IP, and potentially print a warning.

If both read and write IPs are on the same node, the status command complains that MySQL on the node with the read IP is writable, which is of course very desirable in this case, and shouldn't generate a warning.

A node with no IPs assigned to it should default to being read-only. Currently the behaviour is undefined.

8. Future Developments

The following are ideas for future releases of flipper:

• Packaged builds

  Provide RPMs for RHEL/CentOS, plus packages for other distributions and operating systems as required.

• Second Read IP

  In some scenarios, the read and write IPs will want to normally reside on the same host, with the other host being used for 'slow' reads for reporting purposes etc. Adding a second read IP to Flipper would cater for this scenario.

• Slave Support

  If there are slaves pointing at a masterpair, replication to those slaves will almost definitely break when the IP that the slaves are pointing at moves from one host to another, because of differences in binary log positions. By logging the binary log positions when the IP is moved, we can simplify the process of re-pointing slaves, at least in non-failure-event scenarios.

• Logging

  Logging of events in the metadata (or other) database, along with master replication status, to facilitate the above.

• Ability to use script to manage metadata

  At the moment, the only way to configure Flipper is to manipulate the metadata database directly. This isn't very user friendly and there's no error checking. There should be some facility to use a script to make configuration easier.

• Bring up appropriate IPs on reboot

  A script could be written to bring up and/or ensure the appropriate IPs are available on a machine. Although it would not be difficult to make this script platform-independent, incorporating it into each operating system / distribution's rc chain would require substantially more effort.

• Automatic Failover

  Although we ruled out implementing automatic failover of read and write IP addresses on machine and/or replication failure for the first releases of Flipper, and we don't necessarily believe it to be a good idea, we're not ruling it out.

• HP-UX support

  The network interface and IP alias routines in Flipper don't work on HP-UX, but it would be possible to fix this if required.

• Refactor code to OO style

  Flipper's code would be a lot more elegant if it were refactored in an object oriented style.

• Sanity checking of configuration

  Flipper doesn't currently carry out much sanity checking of it's configuration - it should!

• Consistency of output

  The consistency of the output that Flipper produces when the ‘--verbose’ and/or ‘--debug’ options are used could be much improved.

• Documentation Improvements

  The documentation could be improved by adding notes on how to set up master-master replication, and a 'cookbook' with common use scenarios.

9. Platform Notes

9.1 Linux

9.1.1 ARP announcements

ARP annoucements (also known as "Gratuitous ARPs") are normally sent out when an IP address becomes available on a particular physical interface. Other devices (switches, routers and nodes) on the network will use these ARP announcements to maintain a mapping of physical interface (MAC address) to IP address, so packets for a particular IP address get sent to the correct physical interface. This mapping is commonly known as an ARP table.

Linux, unlike other operating systems, does not automatically send ARP announcements when an IP address is brought up on an interface. Therefore, when using Flipper to manage nodes running Linux, Flipper needs to send out the ARP announcements itself.

The send_arp_command configuration variable is used to tell Flipper how the ARP announcements should be sent out.

The most convenient way to send out ARP announcements differs from distribution to distribution.

9.1.2 RHEL/CentOS

The /sbin/arping command (supplied as standard with the distribution) can be used to send the ARP announcements. The best value for send_arp_command is /sbin/arping -I $sendarp_interface -c 5 -U -A $sendarp_ip - supply the absolute pathname because ‘/sbin’ isn't normally in the default path.

9.1.3 Debian

The arping command as supplied by default with RHEL/CentOS can be obtained from the package repository.

9.2 HP-UX

Flipper's network interface and IP alias handling code does not work on HP-UX. Therefore, it is not possible to use Flipper to manage nodes running HP-UX. It is however possible to run the flipper script on a node running HP-UX to manage a masterpair where the nodes run Linux, FreeBSD or another supported operating system.

9.3 Unsupported Platforms

Windows, VMS and i5/OS are not supported, and are not likely to be in the future.

10. Configuration Reference

This chapter describes the configuration variables available in Flipper.

For more general information on how the configuration system works, See section Configuration.

Variables are either compulsory or optional. They are either relevant to nodes or masterpairs only, or specific to the level at which they are defined.

10.1 ip

Applicable to nodes only. Compulsory - each node must have an ip variable defined.

Specifies the IP address of each node. Flipper will use this IP address to connect to MySQL on the node.

10.2 write_ip

Applicable to masterpairs only. Compulsory - each masterpair must have a write_ip variable defined.

This is the IP address which will be used for write access to MySQL.

10.3 read_ip

Applicable to masterpairs only. Compulsory - each masterpair must have a read_ip variable defined.

This is the IP address which will be used for read access to MySQL.

10.4 netmask

Applicable to masterpairs only. Compulsory - each masterpair must have a netmask variable defined.

This is the netmask for the read_ip and write_ip. It's normally the same as the netmask for the nodes' main IPs, but doesn't necessarily have to be the same.

10.5 broadcast

Applicable to masterpairs only. Optional.

This is the broadcast address for the network for the read_ip and write_ip. Not needed for normal use but may be required for sending ARP messages.

10.6 path_to_ifconfig

Specific to node or masterpair, whereever defined. Optional. Defaults to ‘/sbin’.

If the ifconfig command doesn't exist in ‘/sbin’, for example on FreeBSD where it is in ‘/usr/sbin’, specify this variable to point Flipper to the correct location.

10.7 ssh_ip

Applicable to nodes only. Optional.

If ssh is bound to a different IP address than MySQL on a node, define a ssh_ip variable for that node, and Flipper will use that IP address for ssh rather than the one specified by the ip variable.

10.8 ssh_user

Specific to node or masterpair, whereever defined. Optional. Defaults to root.

This specifies the user which will Flipper will use to ssh to the nodes. If you don't want it to use the root user, then specify this variable.

10.9 use_sudo

Specific to node or masterpair, whereever defined. Optional. Defaults to 0, which means "no".

If set to 1, Flipper will use sudo for privilege escalation when carrying out operations on nodes (such as bringing up and taking down IP aliases) which require superuser privileges.

10.10 mysql_user

Specific to node or masterpair, whereever defined. Optional. Defaults to root.

This is the user which Flipper will use to connect to MySQL on the nodes. This is not the user which is used to connect to the metadata database - that is specified using the --username command line switch or in ‘my.cnf’.

10.11 mysql_password

Specific to node or masterpair, whereever defined. Optional. Defaults to empty string.

This is the password which Flipper will use to connect to MySQL on the nodes. This is not the user which is used to connect to the metadata database - that is specified using the --password command line switch or in ‘my.cnf’.

10.12 mysql_port

Specific to node or masterpair, whereever defined. Optional. Defaults to 3306.

If MySQL on the nodes runs on a non-standard port, use this variable to specify the port number.

10.13 interface

Specific to node or masterpair, whereever defined. Potentially compulsory. No default value.

This variable specifies the interface to be used for the read and write IP addresses which are controlled by Flipper on the nodes.

If no value is configured and there's only a single interface on a node, that single interface will be used.

If no value is configured and multiple interfaces exist on a node, the interface must be specified.

10.14 read_interface

Specific to node or masterpair, whereever defined. Optional. Defaults to an automatically assigned interface.

The exact interface to bring up the read_ip on, for example eth0:98.

10.15 write_interface

Specific to node or masterpair, whereever defined. Optional. Defaults to an automatically assigned interface.

The exact interface to bring up the write_ip on, for example eth0:99.

10.16 send_arp_command

Specific to node or masterpair, whereever defined. Optional. No default value.

Specifies the command which will be used to send ARP messages when an IP address is brought up on a node.

The following variables will be interpolated into the command:

• $sendarp_ip - the IP address which is being brought up
• $sendarp_netmask - the netmask
• $sendarp_broadcast - the broadcast address of the network (only available if the broadcast variable has been specified)
• $sendarp_interface - the physical interface on which the IP address has been brought up

11. Credits

Proven Scaling would like to thank the following people for their contributions towards Flipper:

• Graham Wood, Jon Topper, Wayne Pascoe for hardware for testing Flipper on multiple platforms.
• Barry Leung for a timely donation of a copy of "Mastering Perl" to the cause.

License (LGPLv3)

The GNU Lesser General Public License

Version 3, 29 June 2007

Copyright © 2007 Free Software Foundation, Inc. http://fsf.org/

Everyone is permitted to copy and distribute verbatim copies of this
license document, but changing it is not allowed.

This version of the GNU Lesser General Public License incorporates the terms and conditions of version 3 of the GNU General Public License, supplemented by the additional permissions listed below.

1. Additional Definitions.

   As used herein, “this License” refers to version 3 of the GNU Lesser General Public License, and the “GNU GPL” refers to version 3 of the GNU General Public License.

   “The Library” refers to a covered work governed by this License, other than an Application or a Combined Work as defined below.

   An “Application” is any work that makes use of an interface provided by the Library, but which is not otherwise based on the Library. Defining a subclass of a class defined by the Library is deemed a mode of using an interface provided by the Library.

   A “Combined Work” is a work produced by combining or linking an Application with the Library. The particular version of the Library with which the Combined Work was made is also called the “Linked Version”.

   The “Minimal Corresponding Source” for a Combined Work means the Corresponding Source for the Combined Work, excluding any source code for portions of the Combined Work that, considered in isolation, are based on the Application, and not on the Linked Version.

   The “Corresponding Application Code” for a Combined Work means the object code and/or source code for the Application, including any data and utility programs needed for reproducing the Combined Work from the Application, but excluding the System Libraries of the Combined Work.

2. Exception to Section 3 of the GNU GPL.

   You may convey a covered work under sections 3 and 4 of this License without being bound by section 3 of the GNU GPL.

3. Conveying Modified Versions.

   If you modify a copy of the Library, and, in your modifications, a facility refers to a function or data to be supplied by an Application that uses the facility (other than as an argument passed when the facility is invoked), then you may convey a copy of the modified version:

   1. under this License, provided that you make a good faith effort to ensure that, in the event an Application does not supply the function or data, the facility still operates, and performs whatever part of its purpose remains meaningful, or
   2. under the GNU GPL, with none of the additional permissions of this License applicable to that copy.
4. Object Code Incorporating Material from Library Header Files.

   The object code form of an Application may incorporate material from a header file that is part of the Library. You may convey such object code under terms of your choice, provided that, if the incorporated material is not limited to numerical parameters, data structure layouts and accessors, or small macros, inline functions and templates (ten or fewer lines in length), you do both of the following:

   1. Give prominent notice with each copy of the object code that the Library is used in it and that the Library and its use are covered by this License.
   2. Accompany the object code with a copy of the GNU GPL and this license document.
5. Combined Works.

   You may convey a Combined Work under terms of your choice that, taken together, effectively do not restrict modification of the portions of the Library contained in the Combined Work and reverse engineering for debugging such modifications, if you also do each of the following:

   1. Give prominent notice with each copy of the Combined Work that the Library is used in it and that the Library and its use are covered by this License.
   2. Accompany the Combined Work with a copy of the GNU GPL and this license document.
   3. For a Combined Work that displays copyright notices during execution, include the copyright notice for the Library among these notices, as well as a reference directing the user to the copies of the GNU GPL and this license document.
   4. Do one of the following:
      1. Convey the Minimal Corresponding Source under the terms of this License, and the Corresponding Application Code in a form suitable for, and under terms that permit, the user to recombine or relink the Application with a modified version of the Linked Version to produce a modified Combined Work, in the manner specified by section 6 of the GNU GPL for conveying Corresponding Source.
      2. Use a suitable shared library mechanism for linking with the Library. A suitable mechanism is one that (a) uses at run time a copy of the Library already present on the user's computer system, and (b) will operate properly with a modified version of the Library that is interface-compatible with the Linked Version.
   5. Provide Installation Information, but only if you would otherwise be required to provide such information under section 6 of the GNU GPL, and only to the extent that such information is necessary to install and execute a modified version of the Combined Work produced by recombining or relinking the Application with a modified version of the Linked Version. (If you use option 4d0, the Installation Information must accompany the Minimal Corresponding Source and Corresponding Application Code. If you use option 4d1, you must provide the Installation Information in the manner specified by section 6 of the GNU GPL for conveying Corresponding Source.)
6. Combined Libraries.

   You may place library facilities that are a work based on the Library side by side in a single library together with other library facilities that are not Applications and are not covered by this License, and convey such a combined library under terms of your choice, if you do both of the following:

   1. Accompany the combined library with a copy of the same work based on the Library, uncombined with any other library facilities, conveyed under the terms of this License.
   2. Give prominent notice with the combined library that part of it is a work based on the Library, and explaining where to find the accompanying uncombined form of the same work.
7. Revised Versions of the GNU Lesser General Public License.

   The Free Software Foundation may publish revised and/or new versions of the GNU Lesser General Public License from time to time. Such new versions will be similar in spirit to the present version, but may differ in detail to address new problems or concerns.

   Each version is given a distinguishing version number. If the Library as you received it specifies that a certain numbered version of the GNU Lesser General Public License “or any later version” applies to it, you have the option of following the terms and conditions either of that published version or of any later version published by the Free Software Foundation. If the Library as you received it does not specify a version number of the GNU Lesser General Public License, you may choose any version of the GNU Lesser General Public License ever published by the Free Software Foundation.

   If the Library as you received it specifies that a proxy can decide whether future versions of the GNU Lesser General Public License shall apply, that proxy's public statement of acceptance of any version is permanent authorization for you to choose that version for the Library.

Contacting Proven Scaling

If you have any questions, comments, problems, suggestions, you've found a bug or you simply want to let us know that you're using this software, we'd be delighted to hear from you. Please drop us an email at software@provenscaling.com. Let us know what version of MySQL you're using and what platform you're on.

Proven Scaling's home on the web is at http://www.provenscaling.com/.

Variable Index

Concept Index

Table of Contents

About This Document

This document was generated by Jeremy Cole on November, 25 2008 using texi2html 1.78.

The buttons in the navigation panels have the following meaning:

where the Example assumes that the current position is at Subsubsection One-Two-Three of a document of the following structure:

• 1. Section One
  • 1.1 Subsection One-One
    • ...
  • 1.2 Subsection One-Two
    • 1.2.1 Subsubsection One-Two-One
    • 1.2.2 Subsubsection One-Two-Two
    • 1.2.3 Subsubsection One-Two-Three     <== Current Position
    • 1.2.4 Subsubsection One-Two-Four
  • 1.3 Subsection One-Three
    • ...
  • 1.4 Subsection One-Four