Moving MySQL Users and Databases

Occasionally, as part of my day-to-day duties, we migrate users from one shared hosting server to another. In general we use the cPanel/WHM “Transfer account” feature. On occasion, this doesn’t work quite right and manual interventions must be made.

When the Transfer Account feature breaks a user account, it’s annoying. Usually most of the transfer is usable, with only a few aspects needing to be moved the hard way.

I noticed one transfer go awry, however. A number of files were missing from the directory, so I logged into both servers (old and new) and tarballed/scp’d manually. No problem.

Then I found the SQL databases weren’t working right. After some investigation I found the DBs hadn’t been transferred properly, so I set about transferring those.

Transferring the databases is easy. Getting MySQL to dump the databases with schema and data is pretty easy, even easier if you have access to phpMyAdmin which has a handy “Export” feature. Transferring users and privileges – not so much.

I did find a nifty tool for this though. It’s called “mk-show-grants” and I found it here. It dumps all the lines you need to grant access to a user on another system, or can give you the SQL lines to revoke access on the current system. It’s a Perl script, so you can review the source to know it’s not Being Evil, it just requires the Perl module to access MySQL.

Leave a Reply