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

by on July 13, 2010 · 16 comments· LAST UPDATED June 22, 2012

in

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.

Our Sample Setup

  +-----+
  | db1 | -------------------------> -+
  +-----+                             |
   old mysql server                   |
   (192.168.1.8)                      |
  +-----+                             |        ///////////////////////////////
  | db2 | -------------------------> -+------> // Internet (ISP router      //
  +-----+                             |        // with port 80 forwarding)  //
   new mysql server                   |        ///////////////////////////////
   (192.168.1.10)                     |
  +-----+                             |
  | www1| -------------------------> -+
  +-----+
    Apache web server
    (192.168.1.5)

You need to move db1 server database called blogdb and its users to db2 server.

Install MySQL On DB2

Use the apt-get or yum command to install mysql on DB2 server:
$ sudo apt-get install mysql-server mysql-client
$ sudo service mysql start
# set root password for new installation
$ mysqladmin -u root password NEWPASSWORD

OR
$ sudo yum install mysql-server mysql
$ sudo chkconfig mysql on
$ sudo service mysql start
# set root password for new installation
$ mysqladmin -u root password NEWPASSWORD

Make sure OpenSSH server is also installed on DB2.

Get Current MySQL, Usernames, Hostname, And Database Names

Type the following command at shell prompt to list username and hostname list, enter:

mysql -u root -B -N -p -e "SELECT user, host FROM user" mysql

Sample outputs:

vivek	192.168.1.5
tom	192.168.1.5
blog	192.168.1.7
root	localhost
	db1.vm.nixcraft.net.in
root	db1.vm.nixcraft.net.in

The first column is mysql username and second one is network host names. Now, type the following command to get exact details about grants and password for each user from above list:

mysql -u root -p -B -N -e"SHOW GRANTS FOR 'userName'@hostName"
mysql -u root -p -B -N -e"SHOW GRANTS FOR 'vivek'@192.168.1.5"

Sample outputs:

GRANT USAGE ON *.* TO 'vivek'@'192.168.1.5' IDENTIFIED BY PASSWORD 'somePasswordMd5'
GRANT ALL PRIVILEGES ON `blogdb`.* TO 'vivek'@'192.168.1.5'

Where,

  • vivek - MySQL login username
  • 192.168.1.5 - Another server or workstation to access this mysql server
  • somePasswordMd5 - Password stored in mysql database which is not in a clear text format
  • blogdb - Your database name

Now, you've all info and you can move database and users to a new server called db2 as follows using the combination of OpenSSH ssh client and mysql clients as follows:

ssh user@db2 mysql -u root -p'password' -e "create database IF NOT EXISTS blogdb;"
ssh user@db2 mysql -u root -p'password' -e "GRANT USAGE ON *.* TO 'vivek'@'192.168.1.5' IDENTIFIED BY PASSWORD 'somePasswordMd5';"
ssh user@db2 mysql -u root -p'password' -e "GRANT ALL PRIVILEGES ON `blogdb`.* TO 'vivek'@'192.168.1.5';"
mysqldump -u root -p'password' -h 'localhost' blogdb | ssh user@db2 mysql -u root -p'password' blogdb

You can test it as follows from Apache web server:
$ mysql -u vivek -h 192.168.1.10 -p blogdb -e 'show tables;'

A Note About Web Applications

Finally, you need to make changes to your application to point out to new a database server called DB2. For example, change the following from:

 
        $DB_SERVER = "db1.vm.nixcraft.net.in";
        $DB_USER = "vivek";
        $DB_PASS = "your-password";
        $DB_NAME = "blogdb";
 

To:

 
        $DB_SERVER = "db2.vm.nixcraft.net.in";
        $DB_USER = "vivek";
        $DB_PASS = "your-password";
        $DB_NAME = "blogdb";
 

A Sample Shell Script To Migrate Database

#!/bin/bash
# Copyright (c) 2005 nixCraft project <http://cyberciti.biz/fb/>
# This script is licensed under GNU GPL version 2.0 or above
# Author Vivek Gite <vivek@nixcraft.com>
# ------------------------------------------------------------
# SETME First - local mysql user/pass
_lusr="root"
_lpass="MySQLPassword"
_lhost="localhost"
 
# SETME First - remote mysql user/pass
_rusr="root"
_rpass="mySQLPassword"
_rhost="localhost"
 
# SETME First - remote mysql ssh info 
# Make sure ssh keys are set
_rsshusr="vivek"
_rsshhost="db2.vm.nixcraft.net.in"
 
# sql file to hold grants and db info locally
_tmp="/tmp/output.mysql.$$.sql"
 
#### No editing below #####
 
# Input data
_db="$1"
_user="$2"
 
# Die if no input given
[ $# -eq 0 ] && { echo "Usage: $0 MySQLDatabaseName MySQLUserName"; exit 1; }
 
# Make sure you can connect to local db server
mysqladmin -u "$_lusr" -p"$_lpass" -h "$_lhost"  ping &>/dev/null || { echo "Error: Mysql server is not online or set correct values for _lusr, _lpass, and _lhost"; exit 2; }
 
# Make sure database exists
mysql -u "$_lusr" -p"$_lpass" -h "$_lhost" -N -B  -e'show databases;' | grep -q "^${_db}$" ||  { echo "Error: Database $_db not found."; exit 3; }
 
##### Step 1: Okay build .sql file with db and users, password info ####
echo "*** Getting info about $_db..."
echo "create database IF NOT EXISTS $_db; " > "$_tmp"
 
# Build mysql query to grab all privs and user@host combo for given db_username
mysql -u "$_lusr" -p"$_lpass" -h "$_lhost" -B -N \
-e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM user" \
mysql \
| mysql  -u "$_lusr" -p"$_lpass" -h "$_lhost" \
| grep  "$_user" \
|  sed 's/Grants for .*/#### &/' >> "$_tmp"
 
##### Step 2: send .sql file to remote server ####
echo "*** Creating $_db on ${rsshhost}..."
scp "$_tmp" ${_rsshusr}@${_rsshhost}:/tmp/
 
#### Step 3: Create db and load users into remote db server ####
ssh ${_rsshusr}@${_rsshhost} mysql -u "$_rusr" -p"$_rpass" -h "$_rhost" < "$_tmp"
 
#### Step 4: Send mysql database and all data ####
echo "*** Exporting $_db from $HOSTNAME to ${_rsshhost}..."
mysqldump -u "$_lusr" -p"$_lpass" -h "$_lhost" "$_db" | ssh ${_rsshusr}@${_rsshhost} mysql -u  -u "$_rusr" -p"$_rpass" -h "$_rhost" "$_db"
 
rm -f "$_tmp"
 

How Do I Use This Script?

Download the above script and edit it to set the following as per your setup:

# SETME First - local mysql DB1 admin user/password
_lusr="root"
_lpass="MySQLPassword"
_lhost="localhost"
# SETME First - remote mysql DB2 admin user/password
_rusr="root"
_rpass="mySQLPassword"
_rhost="localhost"
# Remote SSH Server (DB2 SSH Server)
# Make sure ssh keys are set
_rsshusr="vivek"
_rsshhost="db2.vm.nixcraft.net.in"

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.

Thanks for all your support!

--Vivek Gite

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

1 el*Loco July 13, 2010 at 10:25 pm

You know mk-show-grants from maatkit?
http://www.maatkit.org/doc/mk-show-grants.html

2 nixCraft July 13, 2010 at 10:43 pm

Naa, never used it. Thanks for sharing it.

3 yayabaobao July 14, 2010 at 5:27 am

thanks~

4 sem007 July 14, 2010 at 7:04 am

Right medicine at right time.
Thanks

5 Shantanu Oak July 14, 2010 at 3:36 pm

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.

6 nixCraft July 14, 2010 at 4:09 pm

You can just repeat the commands to add rest of the users or use the script which copies all users.

7 kelsey August 5, 2010 at 10:02 am

Excellent article, just referred it to my blog. thx

8 OmahaJohn March 31, 2011 at 9:52 pm

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.

Regardless, fantastic stuff!

9 Sylwit September 16, 2011 at 4:10 pm

You have a mistake on the line

mysqldump -u “$_lusr” -p”$_lpass” -h “$_lhost” “$_db” | ssh ${_rsshusr}@${_rsshhost} mysql -u -u “$_rusr” -p”$_rpass” -h “$_rhost” “$_db”

twice -u

Great job. I had to modify it a bit and I will give you my version soon, Hope it will help someone.

10 Sylwit September 19, 2011 at 2:16 pm

Here is it

The README explains what did I change

https://github.com/sylwit/Mysql-Migration-Tools

Thank you for this work Vivek

11 Tamnais January 8, 2012 at 2:05 pm

Works like a charm! Thanks!

Noticed a typo in line 71:
echo “*** Creating $_db on ${rsshhost}…”
missing “_”

Also I have added ${_key} since I use several custom-named ssh keys:
_key=”/path/to/id_rsa”
add -i ${_key} to ssh/scp commands

12 Jay Versluis January 1, 2012 at 3:13 am

Vivek, Sylwit,

you’re both my heros ;-) This tool works like a charm – thank you so much for both your hard work!

Happy New Year 2012!!

13 Peter December 13, 2012 at 4:04 pm

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.

You can find the updated script here: https://gist.github.com/4277380

14 Eddy October 21, 2013 at 8:23 pm

First of all, love the script.

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?

15 Eddy October 22, 2013 at 12:30 pm

I’ve drilled into this and it seems that the issue occurs when running the db create sql statement.

16 Greg December 4, 2013 at 1:45 pm

Hello,
for migrate/move databases, i use :
on the source server
1) Generate script create database

mysql -u youradmin -B -N  -e "select concat('CREATE DATABASE ',SCHEMA_NAME,' DEFAULT CHARACTER SET ',DEFAULT_CHARACTER_SET_NAME,' COLLATE ',DEFAULT_COLLATION_NAME,';') from SCHEMATA where SCHEMA_NAME not in('information_schema','mysql','performance_schema','test'); " information_schema > cr_database.sql

2) Generate script create users :

mysql -u youradmin -B -N  -e "select distinct(concat('CREATE USER ',user,';')) from user where user is not null and user != '%' order by user " mysql > cr_user.sql

3) Generate script for grant 1

mysql -u youradmin -B -N  -e "select concat('SHOW GRANTS FOR \'',user,'\'@\'',host,'\';') from user where user is not null order by user " mysql > cr_grant.sql

4) Generate script forr grant 2

mysql -u youradmin -B -N  --force mysql  lance_grant.sql

On the target :
1) cr_database.sql
2) cr_user.sql
3) Import with your backups
4) lance_grant.sql

Hope that help
Grégory

Comments on this FAQ are closed. If you'd like to continue the discussion on this topic, you can do so at our forum.

Tagged as: , , , ,

Previous post:

Next post: