How to reset root MySQL password on Ubuntu 18.04 Bionic Beaver Linux

Objective

The objective is to reset lost root MySQL password on Ubuntu 18.04 Bionic Beaver Linux

Operating System and Software Versions

• Operating System: - Ubuntu 18.04 Bionic Beaver
• Software: - mysql Ver 14.14 or higher

Requirements

Privileged access to your Ubuntu System as root or via sudo command is required.

Difficulty

EASY

Conventions

• # - requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command
• $ - requires given linux commands to be executed as a regular non-privileged user

Instructions

Reset by using mysql_secure_installation

The simplest approach to reset MySQL database root password is to execute mysql_secure_installation program and when prompted entering your new root MySQL password:

$ sudo mysql_secure_installation
....
Please set the password for root here.

New password: 
Re-enter new password:

Reset by using skip-grant-tables

If from some reason the above method fails follow the step below to use --skip-grant-tables to reset MySQL root password.

Let's start by stopping the currently running MySQL database:

$ sudo service mysql stop

Next, create a /var/run/mysqld directory to be used by MySQL process to store and access socket file:

$ sudo mkdir -p /var/run/mysqld
$ sudo chown mysql:mysql /var/run/mysqld

Once ready manually start MySQL with the following linux command and options:

$ sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
$ [1] 2708

Confirm that the process is running as expected:

$ jobs
[1]+  Running     sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

At this stage we are able to access MySQL database without password:

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-1ubuntu1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.



Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Using the MySQL session first flush privileges:

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Next, reset root password. The following commands will reset MySQL root password to linuxconfig.org:

mysql> USE mysql; 
Database changed
mysql> UPDATE user SET authentication_string=PASSWORD("linuxconfig.org") WHERE User='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

mysql> UPDATE user SET plugin="mysql_native_password" WHERE User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Quit MySQL session:

mysql> quit                                                                                                                                                                                    
Bye

Gracefully terminate current mysqld process:

$ sudo pkill mysqld                                                                                                                                                        
linuxconfig@ubuntu:~$ jobs                                                                                                                                                                     
[1]+  Done       sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking

Lastly, start MYSQL database:

$ sudo service mysql start

If all went well you should now be able to login to your MySQL database with a root password:

$ mysql -u root --password=linuxconfig.org                                                                                                                                 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20-1ubuntu1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>