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.
