Reset MySQL 5.7 root password on Ubuntu 18.04 Linux
- This topic has 3 replies, 2 voices, and was last updated 3 years, 11 months ago by
wesleysantos.
- AuthorPosts
- June 24, 2019 at 1:46 PM #1656
Santhosh Kumar D
KeymasterIf you have lost your MySQL database root password, try these commands to reset the MySQL root password without your current root password. These commands were tested on MySQL Ver 14.14 Distrib 5.7.26, Ubuntu 18.04.
Step 1 – Identify the Database Version:
sudo mysql --version
You will get an output like this:
mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper
Step 2 – Stop the MySQL Database Server:
sudo /etc/init.d/mysql stop
Step 3 – Start the database without loading the grant tables or enabling networking:
By running MySQL without loading information about user privileges, you can access the database command line with root privileges without requiring a password. This way, you can gain access to the database.In order to do this, you have to stop the database from loading the grant tables, which stores all user privilege information. This is a security risk, so you should also skip-networking to prevent other clients from connecting.
So, to start the database without loading the grant tables or enabling networking:
sudo mysqld_safe --skip-grant-tables --skip-networking &
In some cases, your MySQL may not be already configured to start without grant tables. So, to configure MySQL to start without grant tables:
sudo systemctl edit mysql
The above command will open a new file which you can use to edit MySQL’s service overrides. These overrides change the default service parameters for MySQL. This is an empty file, so add these following lines in it:
[Service] ExecStart= ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking
The first
ExecStart
statement clears the default value, while the secondExecStart
statement providessystemd
with the new startup command including parameters to disable loading the grant tables and networking capabilities.Press
CTRL-X
to exit the file, thenY
to save the changes that you made, thenENTER
to confirm the file name.Reload the
systemd
configuration to apply these changes:sudo systemctl daemon-reload
Now, start the MySQL server:
sudo systemctl start mysql
The above command will not show any output, but the database server will start and the grant tables and networking will not be enabled.
Step 4 – Now, login to MySQL as root:
sudo mysql -u root mysql
In some cases, the socket file may not exist and you will get an output like this:
mysqld_safe Logging to syslog. mysqld_safe Logging to '/var/log/mysql/error.log'. mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
If you try to connect to MySQL
mysql -u root
without the socket file, you will get an error like this:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run /mysqld/mysqld.sock' (2)
In that case, you’ve to create the
/var/run/mysqld
first:sudo mkdir -v /var/run/mysqld && sudo chown mysql /var/run/mysqld
Now, you can again try to login to MySQL as root:
sudo mysql -u root mysql
You will be logged in.
Step 5 – Changing the root password.
Command the database server to reload the grant tables:mysql> FLUSH PRIVILEGES;
Now, it will let you change or set a new password. For MySQL 5.7.5 and older, use:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
Alternatively, you can also use the following command:
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';
Do replace the
new_password
with your new password.Exit the MySQL:
mysql> exit
Step 6 – Restart the MySQL Database Server.
First, kill any instances of database server which you have started manually in step 3.sudo kill `cat /var/run/mysqld/mysqld.pid`
The above command searches for the PID, or process ID, of MySQL process and exits it.
Restart the MySQL service using
systemctl
:sudo systemctl start mysql
Step 7 – Test the new root password.
Confirm that your password is changed by logging in with your new password:sudo mysql -u root -p
The above command will prompt you to enter your new password.
You now have the administrative access to the MySQL. Make a note of your new root password and store it safely.
Step 8 – Enable grant tables and networking back again.
Enable grant tables and networking back again by erasing those overrides you added in Step 3.
Stop the MySQL Database Server:
sudo /etc/init.d/mysql stop
Open the MySQL file again:
sudo systemctl edit mysql
Remove these lines:
[Service] ExecStart= ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking
Press
CTRL-X
to exit the file, thenY
to save the changes that you made, thenENTER
to confirm the file name.Reload the
systemd
configuration to apply these changes:sudo systemctl daemon-reload
Now, start the MySQL server back again:
sudo systemctl start mysql
October 7, 2019 at 2:58 PM #1959wesleysantos
ParticipantHi dude, how are you?
Your tutorial is excellent, in my case it worked successfully, but there were questions about mysql security, should I perform any procedures to enforce mysql security?
I set mysql to start without the grant tables using the systemctl edit mysql command. Do I need to remove this content after it’s done?October 8, 2019 at 12:39 PM #1963Santhosh Kumar D
KeymasterHi Wesley Santos, please erase the
skip-grant-tables
andskip-networking
lines you added in Step 3.
Many thanks for the clarification. I have added it as step 8 in the above tutorial.October 8, 2019 at 5:52 PM #1965wesleysantos
ParticipantHi man, this has helped me and can also help other people.
Thanks for your return.Best regards,
Wesley - AuthorPosts
- You must be logged in to reply to this topic.
Pingback: Reset root MySQL password (hard way) – OpenStat