HowTo: Migrate / Move MySQL Database And Users To New Server

byVivek GiteonJuly 13, 2010

I already wrote about how to move or migrate user accounts from old Linux / UNIX server to a new server including mails and home directories. However, in reality you also need to move MySQL database which may host your blog, forum or just your data stored in MySQL database. The mysqldump command will only export the data and the table structure but it will not include a users grants and privileges. The main function of the MySQL privilege system (which is stored in mysql.user table) is to authenticate a user who connects from a given host and to associate that user with privileges on a database such as SELECT, INSERT, UPDATE, and DELETE.

In this example, migrate a database called wiki with wikiuser username:$ ./script.sh wiki wikiuser

Server moved - 14/July/2010

Dear User,

In the last two days nixcraft moved to the new server (details about our older setup are here). No data is lost and most of the stuff is back as usual. The new server is much more stable. However, required libraries for RSS feed and PDF file generation code are not installed. I will fixed it ASAP. If any one see any other errors or 404 errors, please send me an email at vivek@nixcraft.com. Please ignore rss feed which is currently showing all old entires in your feed. My apologies for the temporary inconvenience and flooding your rss feed and inbox. The IPv6 AAAA entries will be published later on this weekend.

You have copied a single user from the DB1 database, but what about other users for e.g. ‘tom’ and ‘blog’ ? There must be some way to push the entire grants table to the new server. If ‘blogdb’ is the only database, you can safely omit it from mysqldump statement and it will include the required permissions table in the dump, that will be copied to the db2 server.

Really good stuff. I’d love to see version two where this is put into a loop, where you “SELECT user, host FROM mysql.users;” and then iterate through the recordset returned, running the rest of your code on each record you get back.

Vivek, thanks for your work. However, I found the restriction to a single user name not very useful, so I rewrote parts of the script in order to make it automatically determine and save all mysql user accounts that have privileges on the given database. Now, its usage looks like this: `./script.sh myDB`. Another missbehaviour I noticed is that the grant statements aren’t terminated by a semicolon what of course results in MySQL errors on the remote machine. Dunno if I am the only one who noticed that, however – I also fixed this.

I am in the middle of migrating ~200 databases to my new server and have run into a little problem, the script fails if there is a – in the name of the database. It escape everything before the – and throws a database not found error. any fixes?