Friday, September 9, 2011

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:

root localhost

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'@"

Sample outputs:

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


satiah - MySQL login username - 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

No comments:

Post a Comment