1

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?

Reset Reboot
  • 13
  • 2
  • Am I correct in thinking you want MySQL on the original server to act as a proxy for the instance of a different server? I don't believe that's possible. What operating system(s) are you running? – John Gardeniers Aug 05 '10 at 11:09

5 Answers5

5

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.

quanta
  • 51,413
  • 19
  • 159
  • 217
b0fh
  • 3,313
  • 1
  • 21
  • 32
0

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

Community
  • 1
Jasper
  • 1,084
  • 10
  • 10
0

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

Craig
  • 1,354
  • 6
  • 14
0

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.

Warner
  • 23,756
  • 2
  • 59
  • 69
0

There was a windows shell script (.vbs) on stackoverflow that decomposes and recomposes a mysql file into text files and back. From the get go it exports Forms, Modules, Macros and Reports. Here is a link to that here. It's the first chosen answer. Now for some reason the original author chose not to export Tables and Queries or Table Relationships simply because they are stored kind of differently by Access. In addition that script deletes each 'object' as it exports it, so one difficulty was that you can't delete a table until you delete it's relationship. A bit of a hassle. Also he didn't provide support between .adp and .mdb files. You have to do that manually.

All of that is possible however, so if you have linkedtables you can figure out how to export them into text form. Then export the Relationships. Delete the Relationships, and then delete the tables. (If you've written the ip in any modules or pass-through queries you'll have to do it to them too.) Have the script delete them everything you chose to export from the original file. There after you will essentially have your msaccess file in a bunch of text files. From there on it's just a simple case of doing a find/replace for each file (through code) And then recomposing the file from each changed element.

It's a bummer to program but it sure helps a ton moving from production to development environments without a sweat.

EDIT Seems somebody did something similar to what I did, but probably better than my version: http://accesssvn.codeplex.com/ EDIT AGAIN Hmmm, If it works for you then great but that program isn't working for me :( Plus it's in C# :(

Community
  • 1
Mallow
  • 144
  • 2
  • 9