How to Reset MySQL or MariaDB Root Password in Linux

    Posted in Linux Servers on Nov 08, 2019

    One of these settings is the password for the database root account – which you must keep private and use only when strictly required.

    If you forget the password or need to reset it (for example, when a database administrator changes roles – or is laid off!).

    This article will come in handy. We will explain how to reset or recover forgottent MySQL or MariaDB root password in Linux.

    Although we will use a MariaDB server in this article, the instructions should work for MySQL as well.

    To begin, stop the database service and check the service status, we should see the environment variable we set previously:

    ------------- SystemD ------------- 
    # systemctl stop mariadb
    
    ------------- SysVinit -------------
    # /etc/init.d/mysqld stop

    Next, start the service with

    --skip-grant-tables:
    ------------- SystemD ------------- 
    # systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
    # systemctl start mariadb
    # systemctl status mariadb
    
    ------------- SysVinit -------------
    # mysqld_safe --skip-grant-tables &

    Start MySQL/MariaDB with Skip Tables

    Skip table.png

    This will allow you to connect to the database server as root without a password (you may need to switch to a different terminal to do so):

    # mysql -u root

    From then on, follow the steps outlined below.

    MariaDB [(none)]> USE mysql;
    MariaDB [(none)]> UPDATE user SET password=PASSWORD('YourNewPasswordHere') WHERE User='root' AND Host = 'localhost';
    MariaDB [(none)]> FLUSH PRIVILEGES;

    Finally, stop the service, unset the environment variable and start the service once again:

    ------------- SystemD ------------- 
    # systemctl stop mariadb
    # systemctl unset-environment MYSQLD_OPTS
    # systemctl start mariadb
    
    ------------- SysVinit -------------
    # /etc/init.d/mysql stop
    # /etc/init.d/mysql start

    This will cause the previous changes to take effect, allowing you to connect to the database server using the new password.

    Summary:-

    In this article we have discussed how to reset the MariaDB / MySQL root password. As always, feel free to use the comment form below to drop us a note if you have any questions or feedback. We look forward to hearing from you!

    Services Cloudtechtiq offers:

    linux-dedicated-seerver.jpg