MySQL – Root password after new installation

Problem
Immediately after installing MySQL you attempt to log in with the system root or admin account but it fails, or indicates that the password is expired.
You also try to use mysqld_safe but that errors out because it’s not installed

Solution
You can look up the temporary password assigned to MySQL root as follows

grep ‘temporary password’ /var/log/mysqld.log

Alternatively you can also perform the following steps to login without a password then reset the root password

1. Stop the mysql service:

systemctl stop mysqld

2. Set the mySQL environment option

systemctl set-environment MYSQLD_OPTS=”–skip-grant-tables”

3. Start mysql using the options we just set

systemctl start mysqld

4. Login as root user

mysql -u root

5. Update the root user password with the following mysql commands

UPDATE mysql.user SET authentication_string = PASSWORD(‘NewPassword’)
-> WHERE User = ‘root’ AND Host = ‘localhost’;

FLUSH PRIVILEGES;
quit

6. Stop the mysql service

systemctl stop mysqld

7. Unset the mySQL environment option so it starts normally

systemctl unset-environment MYSQLD_OPTS

8. Start the mysql service normally:

systemctl start mysqld

Login using the new password:
7. mysql -u root -p

Tested Platforms
MySQL 5.7.19
CentOS 7

Hits: 107

Leave a Reply