HOW TO RESET MYSQL OR MARIADB ROOT PASSWORD ON UBUNTU 18.04

Determine which database server you are using, by version check

• mysql -V

Next, shut down database server

• sudo systemctl stop mariadb (for mariadb)
• sudo systemctl stop mysql (for mysql)

Now restart it in safe mode to reset root password

• Configure Mariadb to start without grant tables

• sudo systemctl set-environment MYSQLD_OPTS=”–skip-grant-tables –skip-networking”
• sudo systemctl start mariadb

•  Configure MySQL to start without grant tables

• sudo systemctl edit mysql

(now an empty file will open,paste the following content there)

[Service]

ExecStart=

ExecStart=/usr/sbin/mysqld –daemonize

–pid-file=/run/mysqld/mysqld.pid –skip-grant-tables –skip-networking

• sudo systemctl daemon-reload
• sudo systemctl start mysql

Now connect to database as root user

• sudo mysql -u root

Tell the database server to reload the grant tables

• mysql>FLUSH PRIVILEGES;

Changing Root password 

Changing Mariadb password

MariaDB [(none)]>UPDATE mysql.user SET password = PASSWORD(‘new_password’) WHERE user = ‘root’;

• MariaDB [(none)]>UPDATE mysql.user SET authentication_string = ” WHERE user = ‘root’;
• MariaDB [(none)]>UPDATE mysql.user SET plugin = ” WHERE user = ‘root’;
• MariaDB [(none)]>exit

(Query OK output will get)

• Changing MySQL password

• mysql>UPDATE mysql.user SET authentication_string = PASSWORD(‘new_password’) WHERE user = ‘root’;
• mysql>UPDATE mysql.user SET plugin = ‘mysql_native_password’ WHERE user = ‘root’;

(Query OK output will get)

Revert your database server to normal operational mode

• For MariaDB

• sudo systemctl unset-environment MYSQLD_OPTS
• sudo systemctl restart mariadb

For MySQL

sudo systemctl revert mysql

sudo systemctl daemon-reload

sudo systemctl restart mysql

The database is restarted and is in normal operational state now…..

You have successfully reset your mysql/mariadb root password…

Confirm it by:

• mysql -u root -p

That’s it!!!