Moving MySQL to new server: Howto move the very easy way all MySQL databases, configs and passwords from server to server

The fastest and easiest way to move MySQL server to server is as root on both machines. Else if NOT root on both Linux machines, be creative and modify below as needed or do it the traditional user-way.

My tested environment for this MySQL server move howto

• Old server SuSE 10.1 to new server openSUSE 11.0
• root on both dedicated servers
• similar versions MySQL on both sides, both systems updated
• SSH access to both Linux servers
• Serverkey authentication to avoid passwords mistype errors or waste of time for copy paste - it's more secure and you may have serverkey authentication anyway.
• Same filesystem on both sides - I have ext3. Same charset in use on all systems - I strictly use all UTF8. That avoids any charset conflict. Whatever you have, be consistent - including on your local Linux box !! Copy paste between different charset configured system sometimes results in horrible errors.

Here the conditions for an easy MySQL to work perfectly and easily. Follow the preparations, follow the step by step procedure and you'll see that the actual MySQL move is a matter of minutes and all instantly UP on the new server without any additional reconfiguration, etc. That how I had it after my recent multiple server move. Absolutely everything worked from very beginning without any problems.

Definitions for this MySQL server move howto

1. Old server = server1
2. New server = server2
3. Replace server1 and server2 where needed by correct IP. Avoid domain names where ever possible. Remember you are moving and even large established ISP may have OLD NS data, thus connecting you eventually to wrong server!! By using strictly IPs for such communication via SSH, you always force correct server-connection.

Step by step easy howto

• Find the system path to your entire mysql folder = locate mysql. For openSUSE this is: /var/lib/mysql
• Make sure you have on both server rsync installed. On openSUSE this is default for LAMP.
• STOP on both ends MySQL servers - on openSUSE this is done with: rcmysql stop
• For above paths - logged in on new server = server2, adapt paths if different from my openSUSE as needed on one or both server-sides. Then use the one command line below, wait until transfer finished, restart on both sides MySQL again.

MySQL transfer of all databases using rsync

Logged in to server2!

rsync -axv --progress --stats root@server1:/var/lib/mysql/ /var/lib/mysql/

The transfer may take seconds or a few minutes, depending on size of your entire MySQL system and databases. That above will transfer all your MySQL stuff, including root access, mysqladmin stuff, postfix and system mysql stuff - ALL. And it all worked perfectly and instantly. Else - either I have been more lucky than you or you made a mistake. If latter - repeat correct procedure.

All transfer finished - restart mysql using: rcmysql start

If your new server allows surfing / apache access on IP, then test your blog, forum or whatever is running on MySQL. Some of your dynamic software may need a change in domain name or IP integrated into software configuration. You'll hopefully know your system. Gallery2 users find additional tips to test run gallery2 on IP.

You should be able to use all same passwords as before. For mysqladmin as well as for all software using mysql.

Why to shut down BOTH MySQL servers during actual data-move? Actually it is obvious. The data transfer takes time - up to several minutes for larger db. Hence on running system transferred data may change during the course of transfer and thus corrupt your db. Even on a larger site like mine, the complete MySQL move was but a very few minutes - may be 2 or 3 minutes all together. Better to shut down your dynamic content for that time than to risk a corrupt db with damaged data.

There are of course other ways, most of them much harder, longer, using reconfiguration of passwords, etc. If you are root on both server, enjoy the power of doing things the really easy and correct way.

What if you are NO root on BOTH servers?

Then you have to do it the hard way or the expensive way. That's the price for having NO root server!

What if you are root on new server but regular user on old server?

Then you need a lucky day, coffee, cake or Swiss chocolate or some cash and try following procedure: Depending on exact situation, you have a few options. You have to create them!

If you are on managed root server: You are paying for root server management. All machine has only your data. Ask your server manager to do above procedure for you. You have to give him SHH access to your new server to do the transfer.

If he does NOT want to or you don't want to give him root access to your new server, then you can ask your server manager to rsync as above ALL mysql folder into your own server-user space on old server. From there you to the transfer to your new server.

If you are on shared hosting. NO way to do as above, because on shared hosting up to several hundred customers / sites are in the same mysql - path, including all passwords and access data for all other sites. Hence no decent server admin will give you a full rsync! In latter case you follow the regular mysqldump procedure. Google will help you to find the details.

Love and Bliss

hans