Learn how to – How to reset MySQL root password on Ubuntu 18.04 / Ubuntu 16.04

Learn how to – How to reset MySQL root password on Ubuntu 18.04 / Ubuntu 16.04.

For some reasons, you may have forgotten MySQL root password and need to reset. Follow this guide to reset the MySQL root password on Ubuntu 18.04 or 16.04 server. This guide will work for any version of MySQL running on any Linux server.

Step 1: Stop mysql service

Before you can reset the password, you need to stop mysql service and start the daemon in safe mode:

$ sudo systemctl stop mysql

For CentOS 7 server, replace mysql with mysqld as service name:

$ sudo systemctl stop mysqld

Step 2: Start MySQL service using mysqld_safe

Once the service has been stopped, run the command mysqld_safe which adds some safety features such as restarting the server when an error occurs and logging runtime information to an error log.

$ sudo mysqld_safe --skip-grant-tables &
[1] 32595
2018-06-26T07:14:43.206936Z mysqld_safe Logging to syslog.
2018-06-26T07:14:43.213306Z mysqld_safe Logging to '/var/log/mysql/error.log'.
180626 07:20:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Step 3: Reset MySQL root password

Login to MySQL using root user with no password:

$ mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 8
Server version: 10.3.7-MariaDB-1:10.3.7+maria~bionic mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

Switch to mysql database and reset root password:

MySQL [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [mysql]> update user set password=PASSWORD("newpassword") where User='root';
Query OK, 3 rows affected (0.000 sec)
Rows matched: 3 Changed: 3 Warnings: 0

MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

MySQL [mysql]> quit
Bye

Step 4: Stop and start MySQL service

Stop and start mysql service to resume normal database operations.

sudo systemctl stop mysql
sudo systemctl start mysql

For any other user, reset the password using the syntax:

[mysql]> update user set password=PASSWORD("password") where User='username';

To get a list of all users in the system, use:

SELECT User, Host, Password FROM mysql.user;

Connect to MySQL database with username and password provided.