Reset MySQL 5.7 root password on Ubuntu 18.04 Linux

If 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 second ExecStart statement provides systemd with the new startup command including parameters to disable loading the grant tables and networking capabilities.

Press CTRL-X to exit the file, then Y to save the changes that you made, then ENTER 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, then Y to save the changes that you made, then ENTER 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