Migrate / Move MySQL Database And Users To New Server

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:

satish 192.168.1.5
tom 192.168.1.5
blog 192.168.1.7
root localhost
db1.vm.linuxbug.net.in
root db1.vm.linuxbug.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 'satish'@192.168.1.5"

Sample outputs:

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

Where,

satiah - 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