As an old firewall developer, I’m dreaming of a MySQL Switch (MySwitch).

You can modify an application to change the database destination. Often the application has to be restarted creating down time. Most servers have many applications connected to them. To make a switch to a Slave server everything has to happen together. Some applications start up MUCH slower than others creating more down time.

The purpose of this project is to take control of application/s connections to MySQL servers . I propose using programacion lenguaje c Network Address Translation (NAT) as a “MySQL Switch” (MySwitch) to move applications between servers. Simply stated, the application would not connect directly to the database server. It would instead connect to the MySwitch. The switch would then change the destination address inside the IP packet to direct it to the correct server. Because the source address packet does not change the responding packet would return back to the source application. Of Course nothing is this simple.

Some exceptions to this are; You can’t switch servers in the middle of a conversion. The Slave would have to be make Read/Write before you switch. The slave replication should not be delayed. Sleeping connections should be closed gracefully on the Master. How do you hold off our new connections while the switch is made or can it be made fast enough? No DNS changes have to be made and no IPs have to be moved between servers.

I’m thinking of a Linux project written in C, maybe with a NodeJS web interface. It would document and control the Master / Slave relationships. Perhaps you could point a service at a Master or a leaf Slave and it would build the configuration. At the least you could document the cluster configurations in a JSON file. Maybe the Slave could be configured as the Master and the current state at the time of the switch could be document so turning the old Master into a slave could eased.

Here is a simple outline of what needs to happen.

• A client, of a client cluster (10.0.0.1) connects to MySwitch (10.0.1.1)
• MySwitch changes the destination IP (10.0.1.1) to the correct MySQL server (10.0.2.1)
  (MySQL requests are made and returned to the client.)
• The admin makes a request to switch to MySQL server (10.0.2.2)
• MySwitch checks that the slave (10.0.2.2) is less than ? seconds delayed.
• MySwitch starts queuing new connection requests and continues processing all other packet types.
• MySwitch checks for connections from the client cluster on the Master (10.0.2.1).
• MySwitch
• MySwitch closes sleeping connections opened by client cluster members.
• MySwitch double checks the slave delay.
• MySwitch changes the slave to read write and resets the slave connection.
• MySwitch processes all the queued connections to the new MySQL server (10.0.2.2)

MySQL commands needed

SHOW SLAVE STATUS\G

Check that:
Master_Host: (Master = correct master)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

SELECT ID FROM information_schema.processlist WHERE host like ’67.67%’ ;

This is used to check connections from the “Application Cluster” (67.67) on the Master.

SELECT concat(‘KILL ‘,id,’;’) FROM information_schema.processlist WHERE host like ’67.67%’ and COMMAND = ‘Sleep’ into outfile ‘/tmp/a.txt’;
SOURCE /tmp/a.txt;

This is used to kill all the open connection for the “Application Cluster” on the Master.

SET GLOBAL READ_ONLY=0;
RESET SLAVE;

These are used to turn off replication on the Slave;

AM I A DREAMER? CAN THIS BE DONE? WHAT AM I MISSING?

Related references

http://dev.mysql.com/doc/refman/5.6/en/replication-solutions-switch.html
http://www.codepin.org/project/mpp/docs/article1-part1/
http://www.ietf.org/rfc/rfc3027.txt
http://dev.mysql.com/doc/internals/en/client-server-protocol.html
https://www.google.com/patents/US20050086342
https://code.google.com/p/mysql-master-ha/
http://scriptingmysql.wordpress.com/2012/12/06/using-the-mysql-script-mysqlfailover-for-automatic-failover-with-mysql-5-6-gtid-replication/

F5 – BigIP
https://devcentral.f5.com/questions/tcp-connection-queuing
http://support.f5.com/kb/en-us/products/big-ip_ltm/manuals/product/ltm_configuration_guide_10_1/ltm_appendixa_monitor_types.html (Search for MySQL)

IP Queuing
http://www.cs.fsu.edu/~baker/devices/lxr/http/source/linux/net/netfilter/nfnetlink_queue.c?v=2.6.25

Tweet