MySQL: Keep the 'frontend', move the db to another computer

Question

We have this MySQL database working in a production environment, and we need to move the database to another, unused server with more space. Our problem is, we already have really huge amounts of programs working with the current IP address, and changing it all would be a nightmare and too slow, so we have thought of making the current MySQL instance a 'front-end' that takes request and redirects to a 'slave' or 'back-end' one that really holds the true database.

This all comes also with the idea of allowing to have some time to migrate the existing software to the 'slave' MySQL and seamlessly migrating the database to the new server.

Anyone has tried their hand?

Answer 1

Is your existing server used for anything else than MySQL ? if so, you could always assign the existing address to the new server.

Barring that, you could also use NAT to forward MySQL's port to the new host. For instance on linux:

iptables -t NAT -A PREROUTING -d 10.0.0.1 -p tcp --dport 3306 -j DNAT --to-destination 10.0.0.2

Don't forget to enable forwarding and add relevant firewall rules.

Answer 2

You could split the datadir from the rest of the mysql app and move the datadir to a volume on another server. However, you should be aware that you can't just do this with any network protocol, like samba.

Supposedly you can do this by using NFS, depending what kind of server/SAN/NAS you bringing it to. See for example http://serverfault.com/questions/30525/mysql-5-1-34-on-nfs-w-netapp.

Probably the best way to do this, would be to use iSCSI. You should figure out if this option is giving you enough IOPS to deal with your database, though.

Answer 3

Take a look at MySQL Proxy, link text. It may accomplish what you want.

Answer 4

If you had a slave replicant, you would be able to move the IP dynamically as MySQL does not bind to the interface. You are going to have a difficult time finding a solution without impact with a single server.

You might be able to duplicate the schema and work out a migration plan, which includes later merging the data set.