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>
ARE YOU LOOKING FOR A LINUX JOB?
Submit your RESUME or create a JOB ALERT on LinuxCareers.com job portal.
DO YOU NEED ADDITIONAL HELP?
Get extra help by visiting our LINUX FORUM or simply use comments below.