When you need to migrate a MySQL server instance to a new server for any reason, eg upgrade of hardware, distri upgrade,… you can easily do the following:
Migrating the databases
On the “old” mysql server, you need to export all databases by:
mysqldump -uroot -p --all-databases > /tmp/dumpofalldb.sql
Copy your sql dump file with eg rsync
rsync -avh /tmp/dumpofalldb.sql username@192.168.10.11:/tmp
On the new server, import your dumps:
mysql -uroot -p < /tmp/dumpofalldb.sql
Migrating the users
If you are migrating to the same MySQL version you can export all users and settings by dumping the mysql database itself.
mysqldump -uroot -p mysql > /tmp/dumpmysqldb.sql
and just copy & import of that file on the new server
However, when doing a MySQL version upgrade, we need to export all the Users and their privileges.
Here’s an easy bash script to do this.
#!/bin/bash
# addapted from http://serverfault.com/questions/105612/how-to-copy-user-priviledges-with-mysql
read -p "Enter your MySQL admin user: " User
read -sp "Enter password" Password
echo ""
echo "-----------------------------------------------------------------------------------"
( mysql -u$User -p$Password --batch --skip-column-names -e "SELECT user, host FROM user" mysql ) | while read user host
do
CreateUser=`mysql -u$User -p$Password --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@'$host'"`
echo "$CreateUser;"
echo "flush privileges;"
done
echo "-----------------------------------------------------------------------------------"
Copy the lines between the ———————-
Login on your new server, go to the mysql shell via the admin user
mysql -uroot -p
And paste the output from our previous script.
Et Voila, your databases and users are migrated!