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 &

SUBSCRIBE TO NEWSLETTER
Subscribe to Linux Career NEWSLETTER and receive latest Linux news, jobs, career advice and tutorials.


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, create a JOB ALERT or subscribe to RSS feed on LinuxCareers.com.
LINUX CAREER NEWSLETTER
Subscribe to NEWSLETTER and receive latest news, jobs, career advice and tutorials.
DO YOU NEED ADDITIONAL HELP?
Get extra help by visiting our LINUX FORUM or simply use comments below.