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.
Conventions
- # – requires given linux commands to be executed with root privileges either directly as a root user or by use of
sudocommand - $ – requires given linux commands to be executed as a regular non-privileged user
Other Versions of this Tutorial
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>