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!!!

    You May Also Like

    About the Author: admin

    Leave a Reply

    Your email address will not be published. Required fields are marked *