What happens if you forget your MySQL root password? No worries, the below steps will help you to reset mysql root password using options ‘–init-file’ and ‘–skip-grant-tables’.
Option 1: Reset MySQL root password using –init-file option
Step 1: Kill mysqld process
Step 2: Create a new mysql-init (I’ll call it as pass-reset.txt) file.
# vim /var/lib/mysql/pass-reset.txt
Include the below line: (Remember to change the default password – your_new_pass)
If you are using MySQL 5.7.6 and later:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_pass';
If you are using MySQL 5.7.5 and earlier:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your_new_pass');
Save the file and quit.
Step 3: Start mysqld_safe process with the file created in Step 2 as an input.
# mysqld_safe --init-file=/var/lib/mysql/pass-reset.txt Starting mysqld daemon with databases from /var/lib/mysql
Note: If the above command fails with the below error message: # Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 160524 16:42:16 mysqld ended + Done mysqld_safe --init-file=pass-reset.txt
Sol : Look out for the detailed error message in /var/log/mysqld: [ERROR] /usr/libexec/mysqld: File '/root/pass-reset.txt' not found (Errcode: 13) Lookout for the file permission, as MySQL should have enough privileges to read the file.
Couldn’t find mysqld_safe executable?
Well, you do the same with
mysqld executable as shown below:
# mysqld --init-file=/var/lib/mysql/pass-reset.txt
Step 4: Jump to a different terminal and try accessing MySQL root user with the new password
# mysql -u root -p
Step 5: If you are able to login successfully with the new password, then you can kill the mysqld_safe process started in step 3.
# killall mysqld
Step 6: Start mysqld normally
# /etc/init.d/mysql start
Step 7: Login to MySQL root account and check if everything works as expected.
Step 8: This step is very important. Remember to remove the password file created in step 2:
# rm /var/lib/mysql/pass-reset.txt
Option 2: Recover/Reset MySQL root password using –skip-grant-tables option
Step 1: Stop all mysql process
Step 2: Start mysqld_safe daemon with –skip-grant-tables option. Doing so will not prompt for password.
Step 3: Connect to MySQL as root user.
$mysql -u root
Step 4: Reset the root account password.
mysql> use mysql
mysql> update user set password=PASSWORD("new_password_here") where User='root';
Step 5: Quit mysql and connect again using the new password.
$mysql -u root -p