Case study: Alternative Way To Recover MySQL Root Password

I had a task to migrate full data from MySQL 5.6 AWS RDS instance to Softlayer dedicated MySQL 5.6 server. On that database there was no update operation. The data was of blog website. So, there was only read operation.

What I had done:

Took backup using mysqldump.

# mysqldump -uabcuser -p --all-databases --events --max-allowed-packet=1G > fulldump.sql

After that moved the fulldump.sql file on Softlayer server, Where already MySQL 5.6 was installed and we had root password. In next step I had restored data using below command:

# mysql -uroot -p < fulldump.sql

After restoring mysqldump file successfully I tried to logged in MySQL command prompt using old

# mysql -uroot -p

But unable to login and getting error.

After restarting MySQL service  I tried to login login again using root user but I couldn’t login, because mysql.user table was  replaced.

I also tried to login MySQL in mysqld_safe mode but no successes.

Solution:

I took back-up of mysql.user table from data directory of our test instance db and replace it with current mysql.user table. Already we had the root user password of test db instance.

After restarting MySQL service again try to login at this time was able to login successful with root user of test db.