MySQL Switch project

October 24, 2013

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 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?

MarkGrennanSigniture

 

 

 

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

 

posted in Commentary by mark

Follow comments via the RSS Feed | Leave a comment | Trackback URL

8 Comments to "MySQL Switch project"

  1. mysqlha.blogspot.com/ wrote:

    I think a good proxy implementation would provide a great foundation for this. However, I am not sure we have that in open source.

  2. JonV wrote:

    If you skip your idea of using (almost) direct server return (not changing source address), this already exists; commercially at least. I believe ScaleArc can do this.

    Now, the problem with your idea that’s quite similar to direct server return.. it won’t work.

    The reply will be coming from an IP that the client making the query has not established a connection with.

    You would either need to be a full proxy, or you’d need to do the translation at layer 2 (MAC address). If you do the latter (which is direct server return), you need to leave the destination IP alone and configure the final destination (server mysql is on) to reply to that IP (but not advertise it).

    The latter would likely break if you have a stateful firewall between the application servers and the database servers as well.

  3. profiles.google.com/10047852389… wrote:

    Check out php.net/mysqlnd_ms which pretty much has all you want but is a driver based approach.

  4. Mike wrote:

    Is it perhaps similar to MySQL Proxy http://dev.mysql.com/downloads/mysql-proxy/ ?

  5. mark wrote:

    The MySQL proxy project is mostly abandoned. I tested it about two years ago and it wouldn’t even hold up to MySQL slap. I wrote about this in my blog. MySQL Proxy also does a little more the I need. That OK but I’m looking for a switch not a Proxy.

  6. Mark wrote:

    MySQLd_ms is good. Unforchantly, I have applications written in lots more then PHP. If all my programs where PHP I’d still have to go to each application/server and make the change in coordination with the database change.

  7. Mark wrote:

    Absolutly! ScaleArc for the win! I was even using ScaleArc until a couple of months ago and I would LOVE to have it back! ScaleArc is a DREAM Product. It already does everything I’m wishing for with MySWITCH and so much more. (If you could see me now, picture a cartoon character of me with starts in my eyes reading ScaleArc.)

    As for the rest, I think your half right. I’ve done this for other services like DNS and HTTP. It has to be done before the packet is routed in IP-Tables. The receiving server doesn’t care about the MAC address the packet came from? Maybe I could call this thing a MyRouter (I think that’s taken.) Anyway like with routers, the MAC addresses don’t matter.

    Source 10.0.0.1 10.0.0.1 10.0.0.1 10.0.0.1 10.0.0.1
    Dest 10.0.1.1 –> 10.0.1.1 NAT 10.0.2.1 Route 10.0.2.1 –> 10.0.2.1 Received

    The receiver gets the packet and the only thing that is changed is the destination address so it returns the packet back to the source. It would seem a bit confusing that the answer came from a different IP then it was talking to. So yes, this doesn’t work through some stateful firewall’s and routers. Maybe to make this work it would have to do a double NAT and both the source and destination address would have to be fixed and translated or maybe it would have to be stateful. Being a DBA now I’m a little fussy on my TCP-IP so I’ll leave this part at – I made it work before?

  8. mark wrote:

    Your right a full proxy would be better. And JonV said ScaleArc does what I’m looking for and he is 110% right. I’ve used ScaleArc and it is a dream. I’ve also tried DRDB with HeartBeat for automated failover and MMM and tungsten and more. Most are good and solve a problem. I just want to change servers without touching any application and without changing the MySQL version I’m using. Dreaming? But it’s so vivid.

Leave Your Comment

You must be logged in to post a comment.

 



Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org
Creative Commons License
MySQL Fan Boy by Mark Grennan is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.