Migrate MySQL Databases, Users, and Privileges to Different Server

1.) Databases:

• Get list of databases

1	mysql -uroot -proot_password -e "show databases"

• Dump all databases

1	mysqldump -uroot -proot_password --all-databases > databases.sql

Note: you can dump only the databases that you want to move. Also, you need to exclude the “mysql” database itself if you are moving into an mysql server with existing records.

* Transfer sql dumps to new server with your preferred way (ftp, scp, rsync, etc)

• Create Databases on new server.

1	mysql -uroot -proot_password -e "create database database_name_1;"

1	mysql -uroot -proot_password -e "create database database_name_2;"

1	mysql -uroot -proot_password -e "create database database_name_3;"

• Import database dumps to new server

1	mysql -uroot -proot_password < databases.sql

2.) Database Users and Privileges:

• Get list of database users and their privileges.

1	mysql -B -N -uroot -proot_password -e "SELECT user, host FROM user" mysql

• Then get the privileges and password hash for each users from the above step.

1	mysql -B -N -uroot -proot_password -e "SHOW GRANTS FOR 'db_user'@'db_host'"

Note: Above command will output similar to these two lines or you will get more if the db_user has access to more than one databases.

1	GRANT USAGE ON *.* TO 'db_user'@'db_host' IDENTIFIED BY PASSWORD '*23AW9301879BCC8BC24343CDCDC40F375606C43D'

1	GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'db_host'

• Then you need to execute all these lines or queries on the new MySQL server. You can put them all in a text file (example: users.txt) and add semi-colon on each line, then import to mysql (example below):

1	mysql -uroot -proot_password < users.txt