How to Reset Root Password in MySQL 8.0

Облачные серверы с Ubuntu

Заказывайте Облачный сервер с чистой ОС или стеком LAMP/LEMP. Почасовая оплата, первый платёж — 100 рублей.

Если вы забыли или потеряли пароль от сервера баз данных, его можно сбросить. Для этого вам понадобится root-доступ к серверу по SSH. Инструкция подойдёт для следующих версий ПО:

• MySQL 8.0 и более ранние версии;
• Ubuntu 16.04, 18.04, 20.04.

В статье мы расскажем, как в MySQL сбросить пароль root.

В более поздних дистрибутивах к MySQL можно подключиться без ввода пароля. Такую возможность даёт плагин auth_socket. Доступ без пароля зависит от того, какой пользователь ОС подключается к MySQL. По умолчанию доступ имеет только root-пользователь.

Чтобы проверить, какой у вас доступ, подключитесь к серверу и выполните команду:

Если настроен доступ без пароля, на экране вы увидите примерно следующее:

Если на MySQL настроен доступ по паролю, вы столкнётесь с ошибкой:

Доступ для пользователя root без использования пароля запрещён

Если доступ без пароля не настроен, а пароль утерян, воспользуйтесь нашей инструкцией.

Как сбросить root-пароль MySQL

Чтобы выполнить сброс пароля MySQL, подключитесь к вашему серверу по SSH и следуйте инструкции:

1. Откройте файл параметров MySQL командой:

2. Добавьте в конец файла три параметра:

   [mysqld]
   skip-grant-tables
   
   skip-networking

   
   

   MySQL reset root password

   • skip-grant-tables — параметр, который пропускает проверку прав. Это позволит подключиться к MySQL без пароля любому пользователю.
   • skip-networking — параметр, который запретит удалённые подключения к серверу БД на время сброса пароля.

3. Чтобы сохранить изменения, нажмите CTRL+S. Затем закройте редактор сочетанием клавиш CTRL+X.

4. Чтобы запустить MySQL с нужными параметрами, перезапустите сервер БД командой:

   systemctl restart mysql.service

5. Подключитесь к серверу баз данных при помощи команды:

7. Поменяйте пароль при помощи команды:

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

   Вместо password введите свой новый пароль.

   Если при выполнении команды возникнет ошибка неправильного синтаксиса, попробуйте другую команду:

   SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');

   Если возникнет ошибка «ERROR 1290 (HY000): The MySQL server is running with the —skip-grant-tables option so it cannot execute this statement», выполните команду из 6 шага.

8. Откройте конфигурационный файл:

9. Удалите из файла текст, который добавили во втором шаге:

   [mysqld]
   skip-grant-tables
   skip-networking

   Если на 2 шаге вы добавили только параметры skip-grant-tables и skip-networking, удалите только их.

   Обязательно удалите опцию skip-grant-tables из конфигурационного файла после смены пароля. Если не удалить параметр, после перезапуска сервера баз данных подключаться к MySQL можно будет без пароля от имени любого пользователя.

10. Чтобы сохранить изменения, нажмите CTRL+S. Затем закройте редактор сочетанием клавиш CTRL+X.

11. Подключитесь к серверу баз данных:

    mysql -uroot -p'password'

    Вместо password напишите ваш новый пароль.

Готово, вы сбросили пароль от root-пользователя MySQL.

Introduction

This tutorial will cover how to reset the root password for older and newer versions of MySQL and MariaDB.

Prerequisites

To recover your root MySQL/MariaDB password, you will need:

• Access to the Linux server running MySQL or MariaDB with a sudo user.

Step 1 — Identifying the Database Version

Most modern Linux distributions ship with either MySQL or MariaDB, a popular drop-in replacement which is fully compatible with MySQL. Depending on the database used and its version, you’ll need to use different commands to recover the root password.

mysql 

You’ll see some output like this with MySQL:

mysql  Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using  EditLine wrapper

Or output like this for MariaDB:

mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Make note of which database and which version you’re running, as you’ll use them later. Next, you need to stop the database so you can access it manually.

Step 2 — Stopping the Database Server

To change the root password, you have to shut down the database server beforehand.

You can do that for MySQL with:

 systemctl stop mysql

And for MariaDB wtih:

 systemctl stop mariadb

After the database server is stopped, you’ll access it manually to reset the root password.

Step 3 — Restarting the Database Server Without Permission Checking

Start the database without loading the grant tables or enabling networking:

 mysqld_safe --skip-grant-tables --skip-networking 

The ampersand at the end of this command will make this process run in the background so you can continue to use your terminal.

mysql  root

You’ll immediately see a database shell prompt instead.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Now that you have root access, you can change the root password.

Step 4 — Changing the Root Password

Let’s tell the database server to reload the grant tables by issuing the FLUSH PRIVILEGES command.

FLUSH PRIVILEGES

Now we can actually change the root password.

ALTER  @ IDENTIFIED BY 

For MySQL 5.7.5 and older as well as MariaDB 10.1.20 and older, use:

SET PASSWORD FOR @  PASSWORD

Make sure to replace new_password with your new password of choice.

UPDATE mysql.user SET authentication_string  PASSWORD WHERE User   AND Host  

Remember to reload the grant tables after this.

In either case, you should see confirmation that the command has been successfully executed.

Query OK, 0 rows affected (0.00 sec)

The password has been changed, so you can now stop the manual instance of the database server and restart it as it was before.

Step 5 — Restart the Database Server Normally

For MySQL, use:

For MariaDB, use:

Then, restart the service using systemctl.

For MySQL, use:

 systemctl start mysql

For MariaDB, use:

 systemctl start mariadb

Now you can confirm that the new password has been applied correctly by running:

mysql  root 

The command should now prompt for the newly assigned password. Enter it, and you should gain access to the database prompt as expected.

The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.

Introduction

This tutorial demonstrates how to reset the root password for MySQL and MariaDB databases installed with the apt package manager on Ubuntu 18.04. The procedure for changing the root password differs depending on whether you have MySQL or MariaDB installed and the default systemd configuration that ships with the distribution or packages from other vendors. While this tutorial’s instructions may work with other system or database server versions, they have been explicitly tested with Ubuntu 18.04 and distribution-supplied packages.

Prerequisites

To recover your MySQL or MariaDB root password, you will need:

• Access to the Ubuntu 18.04 server running MySQL or MariaDB with a sudo user or other way of accessing the server with root privileges. In order to try out the recovery methods in this tutorial without affecting your production server, use the initial server setup tutorial to create a test server with a regular, non-root user with sudo privileges. Then install MySQL following How to install MySQL on Ubuntu 18.04.

Step 1 — Identifying the Database Version and Stopping the Server

mysql 

If you’re running MariaDB, you’ll see “MariaDB” preceded by the version number in the output:

mysql  Ver 15.1 Distrib 10.1.47-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

You’ll see output like this if you’re running MySQL:

mysql  Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using  EditLine wrapper

 systemctl stop mariadb

For MySQL, shut down the database server by running:

 systemctl stop mysql

With the database stopped, you can restart it in safe mode to reset the root password.

Step 2 — Restarting the Database Server Without Permission Checks

Depending on which database server you’ve installed, the way of starting the server without loading the grant tables differs.

Configuring MariaDB to Start Without Grant Tables

In order to start the MariaDB server without the grant tables, we’ll use the systemd unit file to set additional parameters for the MariaDB server daemon.

 systemctl set-environment 

Then start the MariaDB server:

 systemctl start mariadb

This command won’t produce any output, but it will restart the database server, taking into account the new environment variable settings.

You can ensure it started with sudo systemctl status mariadb.

 mysql  root

You’ll immediately see a database shell prompt:

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Now that you have access to the database server, you can change the root password as shown in Step 3.

Configuring MySQL to Start Without Grant Tables

In order to start the MySQL server without its grant tables, you’ll alter the systemd configuration for MySQL to pass additional command-line parameters to the server upon startup.

 systemctl edit mysql

MySQL service overrides

/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 one 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:

 systemctl daemon-reload

Now start the MySQL server:

 systemctl start mysql

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

 mysql  root

You’ll immediately see a database shell prompt:

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Now that you have access to the server, you can change the root password.

Step 3 — Changing the Root Password

The database server is now running in a limited mode; the grant tables are not loaded, and there’s no networking support enabled. This lets you access the server without providing a password, but it prohibits you from executing commands that alter data. To reset the root password, you must load the grant tables now that you’ve gained access to the server.

Tell the database server to reload the grant tables by issuing the FLUSH PRIVILEGES command:

">FLUSH PRIVILEGES

You can now change the root password. The method you use depends on whether you are using MariaDB or MySQL.

Changing the MariaDB Password

">UPDATE mysql.user SET password  PASSWORD WHERE user  

You’ll see this output indicating that the password changed:

Query OK, 1 row affected (0.00 sec)

">UPDATE mysql.user SET authentication_string   WHERE user  
">UPDATE mysql.user SET plugin   WHERE user  

Query OK, 0 rows affected (0.01 sec)

The password is now changed. Type exit to exit the MariaDB console and proceed to Step 4 to restart the database server in normal mode.

Changing the MySQL Password

">UPDATE mysql.user SET authentication_string  PASSWORD WHERE user  

You’ll see this output indicating the password was changed successfully:

Query OK, 1 row affected (0.00 sec)

">UPDATE mysql.user SET plugin   WHERE user  

You’ll see output similar to the previous command:

Query OK, 1 row affected (0.00 sec)

The password is now changed. Exit the MySQL console by typing exit.

Let’s restart the database in normal operational mode.

Step 4 — Reverting Your Database Server to Normal Settings

In order to restart the database server in its normal mode, you have to revert the changes you made so that networking is enabled and the grant tables are loaded. Again, the method you use depends on whether you used MariaDB or MySQL.

For MariaDB, unset the MYSQLD_OPTS environment variable you set previously:

 systemctl unset-environment MYSQLD_OPTS

Then, restart the service using systemctl:

 systemctl restart mariadb

For MySQL, remove the modified systemd configuration:

 systemctl revert mysql

Removed /etc/systemd/system/mysql.service.d/override.conf.
Removed /etc/systemd/system/mysql.service.d.

Then, reload the systemd configuration to apply the changes:

 systemctl daemon-reload

Finally, restart the service:

 systemctl restart mysql

mysql  root 

You’ll be prompted for a password. Enter your new password, and you’ll gain access to the database prompt as expected.

The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.

Introduction

This tutorial demonstrates how to reset the root password for MySQL and MariaDB databases installed with the apt package manager on Ubuntu 20.04. The procedure for changing the root password differs depending on whether you have MySQL or MariaDB installed and the default systemd configuration that ships with the distribution or packages from other vendors. While the instructions in this tutorial may work with other system or database server versions, they have been tested with Ubuntu 20.04 and distribution-supplied packages.

Prerequisites

To recover your MySQL or MariaDB root password, you will need:

Step 1 — Identifying the Database Version and Stopping the Server

mysql 

If you’re running MariaDB, you’ll see “MariaDB” preceded by the version number in the output:

mysql  Ver 15.1 Distrib 10.3.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

You’ll see output like this if you’re running MySQL:

mysql  Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))

 systemctl stop mariadb

For MySQL, shut down the database server by running:

 systemctl stop mysql

With the database stopped, you can restart it in safe mode to reset the root password.

Step 2 — Restarting the Database Server Without Permission Checks

Depending on which database server you’ve installed, the way of starting the server without loading the grant tables differs.

Configuring MariaDB to Start Without Grant Tables

In order to start the MariaDB server without the grant tables, we’ll use the systemd unit file to set additional parameters for the MariaDB server daemon.

 systemctl set-environment 

Then start the MariaDB server:

 systemctl start mariadb

This command won’t produce any output, but it will restart the database server, taking into account the new environment variable settings.

You can ensure it started with sudo systemctl status mariadb.

 mysql  root

You’ll immediately see a database shell prompt:

Now that you have access to the database server, you can change the root password as shown in Step 3.

Configuring MySQL to Start Without Grant Tables

In order to start the MySQL server without its grant tables, you’ll alter the systemd configuration for MySQL to pass additional command-line parameters to the server upon startup.

 systemctl edit mysql

This command will open a new file in the nano editor, which you’ll use to edit MySQL’s service overrides. These change the default service parameters for MySQL.

MySQL service overrides

/usr/sbin/mysqld --skip-grant-tables --skip-networking

The first ExecStart statement clears the default value, while the second one 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:

 systemctl daemon-reload

Now start the MySQL server:

 systemctl start mysql

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

 mysql  root

You’ll immediately see a database shell prompt:

Now that you have access to the server, you can change the root password.

Step 3 — Changing the Root Password

The database server is now running in a limited mode; the grant tables are not loaded, and there’s no networking support enabled. This lets you access the server without providing a password, but it prohibits you from executing commands that alter data. To reset the root password, you must load the grant tables now that you’ve gained access to the server.

Tell the database server to reload the grant tables by issuing the FLUSH PRIVILEGES command:

">FLUSH PRIVILEGES

You can now change the root password. The method you use depends on whether you are using MariaDB or MySQL.

Changing the MariaDB Password

">ALTER  @ IDENTIFIED BY 

You’ll see this output indicating that the password changed:

Query OK, 0 rows affected (0.001 sec)

">UPDATE mysql.user SET authentication_string   WHERE user  
">UPDATE mysql.user SET plugin   WHERE user  

Query OK, 0 rows affected (0.01 sec)

The password is now changed. Type exit to exit the MariaDB console and proceed to Step 4 to restart the database server in normal mode.

Changing the MySQL Password

">ALTER  @ IDENTIFIED WITH caching_sha2_password BY 

You’ll see this output indicating the password was changed successfully:

Query OK, 0 rows affected (0.01 sec)

The password is now changed. Exit the MySQL console by typing exit.

Let’s restart the database in normal operational mode.

Step 4 — Reverting Your Database Server to Normal Settings

In order to restart the database server in its normal mode, you have to revert the changes you made so that networking is enabled and the grant tables are loaded. Again, the method you use depends on whether you used MariaDB or MySQL.

For MariaDB, unset the MYSQLD_OPTS environment variable you set previously:

 systemctl unset-environment MYSQLD_OPTS

Then, restart the service using systemctl:

 systemctl restart mariadb

For MySQL, remove the modified systemd configuration:

 systemctl revert mysql

Removed /etc/systemd/system/mysql.service.d/override.conf.
Removed /etc/systemd/system/mysql.service.d.

Then, reload the systemd configuration to apply the changes:

 systemctl daemon-reload

Finally, restart the service:

 systemctl restart mysql

mysql  root 

You’ll be prompted for a password. Enter your new password, and you’ll gain access to the database prompt as expected.

mysql Ver -0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

mysql  Ver 14.14 Distrib , for Linux (x86_64) using  EditLine wrapper

sudo /etc/init.d/mysql stop

sudo mkdir /var/run/mysqld

sudo chown mysql /var/run/mysqld

sudo mysqld_safe --skip-grant-tables&

[1] 1283
[email protected]:~$ 2019-02-12T11:15:59.872516Z mysqld_safe Logging to syslog.
2019-02-12T11:15:59.879527Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2019-02-12T11:15:59.922502Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

sudo mysql --user=root mysql

UPDATE mysql.user SET authentication_string=null WHERE User='root';

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

update user set authentication_string=PASSWORD('') where user='root';

update user set plugin="mysql_native_password" where User='root';

update user set Password=PASSWORD('') where user='root';

update user set plugin="mysql_native_password" where User='root';

2020-05-30T07:23:38.547616Z mysqld_safe mysqld from pid file /var/lib/mysql/ubuntu.pid ended

sudo /etc/init.d/mysql start

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Luckily there is an easy to achieve and this tutorial will guide you through the process of recovering or resetting root password in MySQL 8.0 version.

As per MySQL documentation there are two ways to reset the root MySQL password. We will review both.

Reset MySQL Root Password Using –init-file

One of the ways to reset the root password is to create a local file and then start the MySQL service using --init-file option as shown.

# vim /home/user/init-file.txt

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

In the above change “new_password” with the password that you wish to use.

# systemctl stop mysqld.service     # for distros using systemd 
# /etc/init.d/mysqld stop           # for distros using init

# mysqld --user=mysql --init-file=/home/user/init-file.txt --console

Make sure to stop the server and start it normally after that.

# systemctl stop mysqld.service        # for distros using systemd 
# systemctl restart mysqld.service     # for distros using systemd 

# /etc/init.d/mysqld stop              # for distros using init
# /etc/init.d/mysqld restart           # for distros using init

You should now be able to connect to the MySQL server as root using the new password.

# mysql -u root -p

Reset MySQL Root Password Using –skip-grant-tables

If the server is started --skip-grant-tables, the option for --skip-networking is automatically activated so remote connections will not be available.

First make sure that the MySQL service is stopped.

# systemctl stop mysqld.service     # for distros using systemd 
# /etc/init.d/mysqld stop           # for distros using init

# mysqld --skip-grant-tables --user=mysql &

Then, you can connect to the mysql server by simply running.

# mysql

Since account-management is disabled when the service is started with --skip-grant-tables option, we will have to reload the grants. That way we will be able to change the password later:

# FLUSH PRIVILEGES;

# ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_passowrd';

Now stop the MySQL server and start it normally.

# systemctl stop mysqld.service        # for distros using systemd 
# systemctl restart mysqld.service     # for distros using systemd 

# /etc/init.d/mysqld stop              # for distros using init
# /etc/init.d/mysqld restart           # for distros using init

You should be able to connect with your new password.

# mysql -u root -p

Conclusion

In this article you learned how to reset lost root password for the MySQL 8.0 server. I hope the process was easy.

If you are setting up a MySQL or MariaDB database server for the first time, chances are you will be running mysql_secure_installation soon afterwards to implement basic security settings.

Suggested Read: Change MySQL or MariaDB Root Password

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.

Recover MySQL or MariaDB root Password

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 &

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

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!

If you read this far, tweet to the author to show them you care. Tweet a thanks

---

If you’re currently logged in as the root account, you can even omit the FOR clause in your SET PASSWORD statement as shown below:

If you forgot your current root password and can’t connect to the MySQL server, then you need to reset the root password with another method.

Resetting root password for Windows and Unix

Resetting a root password requires you to restart your MySQL server and add the --init-file option to the sqld command.

The --init-file option is used to provide an initialization file. The file should contain SQL statements that you want to run during the startup process.

Next, stop the currently running MySQL service.

After that, you need to restart the service using mysqld --init-file=init.txt command. Keep in mind that you need to provide the absolute path to the init file.

Once the server has started, you can test connecting to the MySQL server using the root account.

Don’t forget to delete the init.txt file as well to prevent others from finding out your root password.

The method above should work both for Windows and Unix-like operating systems.

Resetting root password using –skip-grant-tables option

Alternatively, you can use the --skip-grant-tables option to reset your root password when you forgot it.

To reset your root password using this option, you need to:

• Restart your MySQL server with --skip-grant-tables enabled
• Load the grant table so that you can change the root password with ALTER USER statement
• Alter the password for [email protected]

First, turn off your MySQL server and run it again using the mysqld command with --skip-grant-tables added:

sudo service mysql stop

That should restart MySQL server with --skip-grant-tables option enabled.

Next, use the mysql command to log into the server from the command line:

You can change NewPassword with your actual desired password.

And now you’re done. Stop the running mysqld command with CTRL + C shortcut and run it normally:

You have learned how to reset MySQL root password. Nice work!

The MySQL --skip-grant-tables option is used to start the MySQL server without loading the grant tables.

SQL statements related to accounts management are disabled when the --skip-grant-tables option is active.

ERROR  HY000: The MySQL server is running with the --skip-grant-tables 
option so it cannot execute this statement

To fix this error, you need to load the grant tables to the MySQL server using the FLUSH PRIVILEGES command:

mysql> FLUSH PRIVILEGES

Query OK,  rows affected 0.01 sec

By reloading the grant tables, you should be able to execute the statements that are previously unavailable.

sudo service mysql stop
sudo service mysql start

Here’s an example .ini file with the skip-grant-tables option enabled:

# SERVER SECTION
# you have installed the server correctly (see above) so it reads this 

You need to either comment or remove the highlighted line above.

And that’s how you fix the ERROR 1290 caused by the --skip-grant-tables option. Nice work!