mariadb mysql_如何重设MySQL或MariaDB根密码

mariadb mysql

介绍 (Introduction)

Forgetting passwords happens to the best of us. If you forget or lose the root password to your MySQL or MariaDB database, you can still gain access and reset the password if you have access to the server and a sudo-enabled user account.

忘记密码是我们最大的责任。 如果您忘记或丢失了MySQL或MariaDB数据库的root密码，那么如果您有权访问服务器和启用了sudo用户帐户，仍然可以访问并重置密码。

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

本教程将介绍如何为MySQL和MariaDB的旧版本和新版本重置根密码。

先决条件 (Prerequisites)

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

要恢复您MySQL / MariaDB根密码，您将需要：

• Access to the Linux server running MySQL or MariaDB with a sudo user.
  使用sudo用户访问运行MySQL或MariaDB的Linux服务器。

步骤1 —识别数据库版本 (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.

大多数现代Linux发行版均附带MySQL或MariaDB，这是一种流行的直接替代产品，与MySQL完全兼容。 根据所使用的数据库及其版本，您将需要使用不同的命令来恢复root密码。

You can check your version with the following command:

您可以使用以下命令检查您的版本：

• mysql --version
  mysql --version

You’ll see some output like this with MySQL:

您将在MySQL中看到类似以下的输出：

   
   

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

Or output like this for MariaDB:

或对于MariaDB这样的输出：

   
   

    
    MariaDB output
   
   
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.

记下您正在运行哪个数据库和哪个版本，以备后用。 接下来，您需要停止数据库，以便可以手动访问它。

步骤2 —停止数据库服务器 (Step 2 — Stopping the Database Server)

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

要更改root密码，您必须事先关闭数据库服务器。

You can do that for MySQL with:

您可以使用以下方法对MySQL执行此操作：

• sudo systemctl stop mysql

  sudo systemctl停止mysql

And for MariaDB wtih:

对于MariaDB：

• sudo systemctl stop mariadb

  sudo systemctl停止mariadb

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

停止数据库服务器后，您将手动访问它以重置root密码。

步骤3 —在没有权限检查的情况下重新启动数据库服务器 (Step 3 — Restarting the Database Server Without Permission Checking)

If you run MySQL and MariaDB without loading information about user privileges, it will allow you to access the database command line with root privileges without providing a password. This will allow you to gain access to the database without knowing it.

如果您在运行MySQL和MariaDB时未加载有关用户特权的信息，则它将允许您以root特权访问数据库命令行而无需提供密码。 这将允许您在不知道数据库的情况下访问数据库。

To do this, you need to stop the database from loading the grant tables, which store user privilege information. Because this is a bit of a security risk, you should also skip networking as well to prevent other clients from connecting.

为此，您需要停止数据库加载存储用户特权信息的授权表 。 因为这有一定的安全风险，所以您还应该跳过联网，以防止其他客户端连接。

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

在不加载授权表或启用网络的情况下启动数据库：

• sudo mysqld_safe --skip-grant-tables --skip-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.

该命令末尾的＆符号将使此过程在后台运行，因此您可以继续使用终端。

Now you can connect to the database as the root user, which should not ask for a password.

现在，您可以以root用户身份连接到数据库，该用户不需要密码。

• mysql -u root
  mysql -u root

You’ll immediately see a database shell prompt instead.

您将立即看到数据库外壳提示。

MySQL prompt

MySQL提示

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

mysql>

MariaDB prompt

MariaDB提示

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.

现在您具有root用户访问权限，可以更改root用户密码。

步骤4 —更改根密码 (Step 4 — Changing the Root Password)

One simple way to change the root password for modern versions of MySQL is using the ALTER USER command. However, this command won’t work right now because the grant tables aren’t loaded.

更改现代MySQL版本根密码的一种简单方法是使用ALTER USER命令。 但是，此命令现在无法使用，因为未加载授权表。

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

让我们通过发出FLUSH PRIVILEGES命令告诉数据库服务器重新加载授权表。

• FLUSH PRIVILEGES;
  冲洗特权;

Now we can actually change the root password.

现在，我们可以实际更改root密码了。

For MySQL 5.7.6 and newer as well as MariaDB 10.1.20 and newer, use the following command.

对于MySQL 5.7.6和更高版本以及MariaDB 10.1.20和更高版本 ，请使用以下命令。

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

  ALTER USER'root'@'localhost'由' new_password '标识；

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

对于MySQL 5.7.5和 更高版本以及MariaDB 10.1.20和更高版本，请使用：

• SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

  为'root'@'localhost'设置密码= PASSWORD(' new_password ');

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

确保用您选择的新密码替换new_password 。

Note: If the ALTER USER command doesn’t work, it’s usually indicative of a bigger problem. However, you can try UPDATE ... SET to reset the root password instead.

注意 ：如果ALTER USER命令不起作用，通常表明存在更大的问题。 但是，您可以尝试使用UPDATE ... SET重置根密码。

• UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';

  更新mysql.user SET authentication_string = PASSWORD(' new_password ')WHERE User =' root'AND Host ='localhost';

Remember to reload the grant tables after this.

请记住，此后要重新加载授权表。

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

无论哪种情况，您都应该看到确认命令已成功执行的信息。

   
   

    
    Output
   
   
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.

密码已更改，因此您现在可以停止数据库服务器的手动实例，然后像以前一样重新启动它。

步骤5 —正常重启数据库服务器 (Step 5 — Restart the Database Server Normally)

First, stop the instance of the database server that you started manually in Step 3. This command searches for the PID, or process ID, of MySQL or MariaDB process and sends SIGTERM to tell it to exit smoothly after performing clean-up operations. You can learn more in this Linux process management tutorial.

首先，停止您在步骤3中手动启动的数据库服务器的实例。此命令搜索MySQL或MariaDB进程的PID或进程ID，并发送SIGTERM告知其在执行清理操作后可以顺利退出。 您可以在本Linux进程管理教程中了解更多信息。

For MySQL, use:

对于MySQL，请使用：

• sudo kill `cat /var/run/mysqld/mysqld.pid`
  须藤杀死`猫/ var / run / mysqld / mysqld.pid`

For MariaDB, use:

对于MariaDB，请使用：

• sudo kill `/var/run/mariadb/mariadb.pid`
  sudo kill`/ var / run / mariadb / mariadb.pid`

Then, restart the service using systemctl.

然后，使用systemctl重新启动服务。

For MySQL, use:

对于MySQL，请使用：

• sudo systemctl start mysql
  sudo systemctl启动mysql

For MariaDB, use:

对于MariaDB，请使用：

• sudo systemctl start mariadb
  sudo systemctl启动mariadb

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

现在，您可以通过运行以下命令确认新密码已正确应用：

• mysql -u root -p
  mysql -u root -p

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

现在，该命令将提示您输入新分配的密码。 输入它，您应该可以按预期访问数据库提示。

结论 (Conclusion)

You now have administrative access to the MySQL or MariaDB server restored. Make sure the new root password you choose is strong and secure and keep it in safe place.

现在，您具有对还原MySQL或MariaDB服务器的管理访问权限。 确保您选择的新的超级用户密码是安全的，并将其放在安全的地方。

翻译自: https://www.digitalocean.com/community/tutorials/how-to-reset-your-mysql-or-mariadb-root-password

mariadb mysql