Objective
The objective is to reset lost root MySQL password on Ubuntu 18.04 Bionic Beaver LinuxOperating 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 viasudo
command is required. 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
Other Versions of this Tutorial
Ubuntu 20.04 (Focal Fossa)Instructions
Reset by using mysql_secure_installation
The simplest approach to reset MySQL database root password is to executemysql_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 stopNext, 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/mysqldOnce ready manually start MySQL with the following linux command and options:
$ sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking & $ [1] 2708Confirm that the process is running as expected:
$ jobs [1]+ Running sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
Subscribe to RSS and NEWSLETTER and receive latest Linux news, jobs, career advice and tutorials.
$ 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: 0Quit MySQL session:
mysql> quit ByeGracefully terminate current
mysqld
process: $ sudo pkill mysqld linuxconfig@ubuntu:~$ jobs [1]+ Done sudo /usr/sbin/mysqld --skip-grant-tables --skip-networkingLastly, start MYSQL database:
$ sudo service mysql start
$ 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>