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.

Hello world!

It’s a startlingly appropriate title for this blog.

Here’s the deal. I am a Jr. Systems Administrator. I work for a company that provides hosting services, anything from shared web-space to multiple-cabinets of colocation space. If you have a hosting need, chances are we can meet it.

But this blog isn’t about my employer. It isn’t even really about me, so much as it is about the things I learn along the way, which may include experience from my employment or on personal projects.

Disclaimers are required at this time:

I am really easy to find. While I may give an air of anonymity, a quick WHOIS will reveal who I am and where I live. From that it will be a very quick process to determine who I work for and who I work with. For that reason I do not give details regarding clients. No names (of people or company), no IP addresses, no routing tables, nothing. Anything I post containing anything close to identifying information regarding my employer or their clients can and will be obscured to prevent readers from determining anything useful, and I will endeavor to ensure readers are unable to determine anything at all.

Any opinion, stated or implied, is my own and does not reflect the views of my employer or colleagues. Any advice is given on the grounds that it has worked for me, and no warranty or guarantee is given whatsoever that it will work for anyone else. I am happy to offer assistance to others if I can, but this is given on the same basis with no warranty of guarantee.