Transparent MySQL migration using MySQL proxy

How can we transparently migrate MySQL from one server to another when we don’t want to disrupt end users? That was the question posed as we come to the final phase of decommissioning a server. We have transitioned almost all services away from the older server (CHIMAY)- but there is one external cron that is not under our control that we can see in the logs which generates several MySQL queries. Therefore- we need to transparently move MySQL through another server (TECATE). Here’s the scenario:

CHIMAY was running a bunch of services, including several MySQL databases. Those databases were copied to a server called STONE. Almost all of the clients, scripts, etc were modified to use STONE. We could not just assign the CHIMAY IP address to STONE because they are on completely different networks using different providers. Therefore, we determined that the best way forward was to proxy MySQL requests from another server (TECATE) in the same address space as CHIMAY. Additionally, the proxy server, TECATE was already running a MySQL database for other reasons, so the proxy had to stay out of the way.

Here is the plan:

1) Assign a new ip address to CHIMAY

2) Assign CHIMAY’s ip address to TECATE as an alias (eth0:0)

3) Set up a proxy on TECATE for MySQL which would transparently send MySQL requests on to the production database residing on STONE.

We decided to use mysql-proxy to handle the heavy lifting, even though it is listed as being alpha quality- there seems to be a reasonably large community actively using it. Below is how we accomplished this:

1) Compile and install mysql-proxy on TECATE.

2) Assign a new IP address to CHIMAY, and move its original IP address over to TECATE as an alias (eth0:0).

3) Configure mysql proxy as follows:

proxy-address =www.xxx.yyy.zzz:3306  <– This is the aliased IP address on TECATE (CHIMAY’s old address)

proxy-backend-addresses =aaa.bbb.ccc.ddd:3306 <— This is the new productuion server, STONE

Thus- all requests to CHIMAY now simply get proxied untouched to STONE.

We did run into a couple of issues after the above was set up and tested:

a)  The proxy crashed! We got this error-

Error:

file network-mysqld-proto.c: line 191 (network_mysqld_proto_skip): assertion failed: (*_off + size <= packet->len)

We narrowed the issue to the client version used to access- a machine with an old 3.23 client would crash the proxy, while the newer 5.0.27 would not. There are bug reports, but nothing new on this since 2008.

b) the proxy is not entirely transparent- the MySQL connects on the new production server STONE showed the source as user@TECATE, not as from the remote caller. Therefore, we needed to add grants to STONE. Once those were done- voila!

I hope I didn’t miss any critical points- but the above highlights the most important parts in this transition. There are further enhancements we could use using lua scripts, but this is only a short term solution to an immediate problem and the time spent would not be justified for this project.

Leave a Reply

Your email address will not be published. Required fields are marked *