MPP is a MySQL State Manager for MySQL server failover negotiation and management. It is intended to be used as a 2nd party management application used in combination with any load balancing management software. It was initially designed to be used in conjunction with Linux Virtual Server, and later F5 Networks BigIP, but can work with any load balancer that can utilize external check scripts or accomplish an HTTP REST call. MPP now integrates with MySQL Proxy using an injection mechanism.

This software allows the configuration of MySQL servers into a pool. The configured pool is managed based on a logic that is assigned to it. MPP currently supports generic fail-over logic, and has intentions to eventually support generic cluster logic.

MPP began it's origins in the 2nd half of 2004, and was released into internal production as proof-of-concept in 2005. Two and a half years later after testing in a variety of production environments with integration in several 2nd party controllers, and sights set on MySQL Proxy integration, it is released.

MPP is written in PERL, released as Open Source Apache 2 license which is compatible with GPL 3.

MPP was written as a 2nd party plugin to controllers. These controllers are what enact the physical change of state of MySQL nodes.

• MPP has been tested under the following controllers:
  • Linux Virtual Server
  • Red Hat Clustering Suite, Piranha
  • F5 Network SmartDNS
  • F5 Network Big IP
• MPP is in progress to be reworked and tested under MySQL Proxy
  MySQL Proxy (work completed r1.0.1, initial testing completed r1.0.3)

The PERL requirements for using MPP is as follows:

  MODULE	MIN VERSION	AVAILABLE
  -----------	-----------	--------------
  CGIbasic	0.010		MPP Bundled
  Config_info	1.00803		MPP Bundled
  LogBasic	0.00.003	MPP Bundled
  Storable	PERL >= 5.8	PERL Bundled
  Net::HTTP	PERL >= 5.8	PERL Bundled
  Net::Ping	PERL >= 5.8	PERL Bundled
  IO::FILE	PERL >= 5.8	PERL Bundled
  DBD::MySQL	2.9006		CPAN
  DBI		1.46		CPAN

This software was recently imported from our internal engineering lab, and has been merged with modification recomendations from our real world implementation. The resulting code available here has been tested and considered ready for release.

Documentation

• MySQL Failover Strategy using State Management, introducing MPP - Part 1
  Having a strategy for failover has become almost standard in the business world. Whether it is a total site failover from a NOC in one city to another, or just a simple redundant server. Over the years many technology solutions have been created for or adopted to MySQL to provide this type of strategy.
  
  
• MySQL Failover Strategy using State Management, introducing MPP - Part 2
  In Part 1 we have seen how the concept of state management works. Now it's time to apply that concept to a load-balancer. In this Part 2 we will look at a strategy for using Linux Virtual Server with MPP for failover, and also take a closer look at the mechanics of MPP itself.
  
  
• MySQL Failover Strategy using State Management, introducing MPP - Part 3
  In part 2 we discussed the internals of MPP and how MPP can be used with LVS to create a failover strategy. In this part 3 we will discuss how to configure and operate MPP, and additionally use MPP with MySQL Proxy to create a failover strategy.
  
  

Get the code

You can download the source code from Subversion.

• Subversion trunk: https://dev.codepin.org/svn/mpp/trunk/mpp
• Subversion tags (revisions): https://dev.codepin.org/svn/mpp/tags/mpp
• Latest Revisions:

  date	revision	tarball	summarized description
  2007.12.10	1.0.3	mpp-1.0.3.tgz	mysql-polld daemon-like script is created for the replacement of the cron option for polling nodes. Initial testing for integration with MySQL Proxy 0.6.0 has been completed. Various fixes and updates.
  2007.11.16	1.0.2	mpp-1.0.2.tgz	The mpp.lua file has been updated to address an issue I refer to as "all_node_down", where all Proxy backend servers are down and MPP cannot connect to load its evaluations into Proxy. This version also address the issue where you could not supply a blank password to mpp-proxyadmin.pl. This version also contains a work around for MySQL Bug #32464 for DBD::mysql.
  2007.11.13	1.0.1	mpp-1.0.1.tgz	Integration with MySQL Proxy Tested. Added a mysqlproxy host module into the API set, mpp.lua Lua script for MySQL Proxy, and created an admin application mpp-proxyadmin.pl to manage MPP evaluations from MPP cache into Proxy internals.
  2007.11.11	1.0.0	mpp-1.0.0.tgz	First release. Integration with Linux Virtual Server and F5 Networks BigIP tested.

There is a plan in progress that MPP will be combined into MySQL Proxy. The plan is multi-phased:

1. (completed r1.0.1) MySQLProxy+MPP combination by handshake - get them to talk to each other.
2. Implement these load balancing methods in MySQL Proxy using Lua:
   • round robin
   • weighted connections
   • least connections
   • weighted least connections
   • read-only versus read-write, SQL query load balancing
3. (completed r1.0.3) Create a daemon to perform the polling, rahter than relying on cron for implementation
4. Parts of MPP converted in Lua Snipits using PERL Inline::Lua; Lua Snipts integrated into MySQL Proxy
5. Merging of MPP into MySQL Proxy without external dependencies, with the following attributions:
   • MPP-as-Lua still packageable into a MPP PERL-library using Inline:Lua for 2nd party integration into other controllers.
   • MPP Clients can/will be written for managing MPP logic in a running MySQL Proxy implementation of MPP.
          (This allows the administrator to say, "Uh.. change your mind to make this decision instead." Ex: "I don't care if the STANDBY is in [replication] FAILure, promote it to ACTIVE and fail-over to it anyway.")