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:
|
1 |
mysqldump -uroot -p --all-databases > /tmp/dumpofalldb.sql |
Copy your sql dump file with eg rsync
|
1 |
rsync -avh /tmp/dumpofalldb.sql username@192.168.10.11:/tmp |
On the new server, import your dumps:
|
1 |
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.
|
1 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
#!/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
|
1 |
mysql -uroot -p |
And paste the output from our previous script.
Et Voila, your databases and users are migrated!