Reset MySQL 5.7 root password on Ubuntu 18.04 Linux

LearnTips Internet Reset MySQL 5.7 root password on Ubuntu 18.04 Linux

Tagged: , ,

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #1656

    If you have lost your MySQL database root password, then try these commands to reset the MySQL root password without your current root password. These commands were tested on MySQL Ver 14.14 Distrib 5.7.26, Ubuntu 18.04.

    Step 1 – Identify the Database Version:
    sudo mysql --version

    You will get an output like this:
    mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper

    Step 2 – Stop the MySQL Database Server:
    sudo /etc/init.d/mysql stop

    Step 3 – Start the database without loading the grant tables or enabling networking:
    By running MySQL without loading information about user privileges, you can access the database command line with root privileges without requiring a password. This way, you can gain access to the database.

    In order to do this, you have to stop the database from loading the grant tables, which stores all user privilege information. This is a security risk, so you should also skip-networking to prevent other clients from connecting.

    So, to start the database without loading the grant tables or enabling networking:
    sudo mysqld_safe --skip-grant-tables --skip-networking &

    In some cases, your MySQL may not be already configured to start without grant tables. So, to configure MySQL to start without grant tables:
    sudo systemctl edit mysql

    The above command will open a new file which you can use to edit MySQL’s service overrides. These overrides change the default service parameters for MySQL. This is an empty file, so add these following lines in it:
    [Service]
    ExecStart=
    ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking

    The first ExecStart statement clears the default value, while the second ExecStart statement provides systemd with the new startup command including parameters to disable loading the grant tables and networking capabilities.

    Press CTRL-X to exit the file, then Y to save the changes that you made, then ENTER to confirm the file name.

    Reload the systemd configuration to apply these changes:
    sudo systemctl daemon-reload

    Now, start the MySQL server:
    sudo systemctl start mysql

    The above command will not show any output, but the database server will start and the grant tables and networking will not be enabled.

    Step 4 – Now, login to MySQL as root:
    sudo mysql -u root mysql

    In some cases, the socket file may not exist and you will get an output like this:
    mysqld_safe Logging to syslog.
    mysqld_safe Logging to '/var/log/mysql/error.log'.
    mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

    If you try to connect to MySQL mysql -u root without the socket file, you will get an error like this:
    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run /mysqld/mysqld.sock' (2)

    In that case, you’ve to create the /var/run/mysqld first:
    sudo mkdir -v /var/run/mysqld && sudo chown mysql /var/run/mysqld

    Now, you can again try to login to MySQL as root:
    sudo mysql -u root mysql

    You will be logged in.

    Step 5 – Changing the root password.
    Command the database server to reload the grant tables:
    mysql> FLUSH PRIVILEGES;

    Now, it will let you change or set a new password. For MySQL 5.7.5 and older, use:
    mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

    Alternatively, you can also use the following command:
    mysql> UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';

    Do replace the new_password with your new password.

    Exit the MySQL:
    mysql> exit

    Step 6 – Restart the MySQL Database Server.
    First, kill any instances of database server which you have started manually in step 3.
    sudo killcat /var/run/mysqld/mysqld.pid

    The above command searches for the PID, or process ID, of MySQL process and exits it.

    Restart the MySQL service using systemctl:
    sudo systemctl start mysql

    Step 7 – Test the new root password.
    Confirm that your password is changed by logging in with your new password:
    sudo mysql -u root -p

    The above command will prompt you to enter your new password.

    You now have the administrative access to the MySQL. Make a note of your new root password and store it safely.

    Step 8 – Enable grant tables and networking back again.

    Enable grant tables and networking back again by erasing those overrides that you had added in step 3.

    Stop the MySQL Database Server:
    sudo /etc/init.d/mysql stop

    Open the MySQL file again:
    sudo systemctl edit mysql

    Remove these lines:
    [Service]
    ExecStart=
    ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking

    Press CTRL-X to exit the file, then Y to save the changes that you made, then ENTER to confirm the file name.

    Reload the systemd configuration to apply these changes:
    sudo systemctl daemon-reload

    Now, start the MySQL server back again:
    sudo systemctl start mysql

    #1959
    Avatarwesleysantos
    Participant

    Hi dude, how are you?
    Your tutorial is excellent, in my case it worked successfully, but there were questions about mysql security, should I perform any procedures to enforce mysql security?
    I set mysql to start without the grant tables using the systemctl edit mysql command. Do I need to remove this content after it’s done?

    #1963

    Hi Wesley Santos, please erase the skip-grant-tables and skip-networking lines that you had added in Step 3.

    Many thanks for the clarification. I have added it as step 8 in the above tutorial.

    #1965
    Avatarwesleysantos
    Participant

    Hi man, this has helped me and can also help other people.
    Thanks for your return.

    Best regards,
    Wesley

Viewing 4 posts - 1 through 4 (of 4 total)
  • You must be logged in to reply to this topic.