Reset MySQL root password in CentOS 6 / 7

Sometimes you forget passwords. And MySQL root password is not an exception to those. However, it’s quite easy to reset it via SSH.

Follow the steps below to reset the MySQL root password.

Change MySQL root password in the SSH command line

Login to SSH session of root user on your server first.

For MySQL up to 5.6 inclusive

• Stop MySQL server: service mysqld stop
• Start MySQL server in safe mode, and in background: sudo mysqld_safe --skip-grant-tables &
• Connect to MySQL via command line client: mysql -u root
• Run query to update root user password, using command line MySQL client (which is connected in previous step):

UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

• Run another query to save changes to password: FLUSH PRIVILEGES;
• Type command to quit command line MySQL client: \q
• Restart MySQL server: service mysqld restart

For MySQL version 5.7 and above

Create file /var/lib/mysql/init.sql:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('YourNewPass!');
FLUSH PRIVILEGES;

Then:

chown mysql:mysql /var/lib/mysql/init.sql

Adjust your my.cnf and add:

[mysqld]
...
init-file=/var/lib/mysql/init.sql

Restart MySQL:

systemctl restart mysqld

That’s it. The password is already changed. Don’t forget to remove the .ini setting and the .sql file.

Now save your new password to a safe location and never forget it 🙂
We recommend KeePass application for securely storing passwords.