使用mysql_secure_installation重置

重置MySQL数据库root密码的最简单方法是执行“mysql_secure_installation程序”，并在提示我们输入新的根目录MySQL密码时：

$ sudo mysql_secure_installation
....
Please set the password for root here.
New password: 
Re-enter new password:

使用跳过授权表重置

如果从某种原因出现上述方法失败，请按照以下步骤使用--skip-grant-tables以重置MySQL root密码。

让我们开始停止当前运行的MySQL数据库：

$ sudo service mysql stop

接下来，创建MySQL进程使用的/var/run/mysqld目录来存储和访问套接字文件：

$ sudo mkdir -p /var/run/mysqld
$ sudo chown mysql:mysql /var/run/mysqld

一旦准备好使用以下Linux命令和选项手动启动MySQL：

$ sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
$ [1] 2708

确认此过程按预期运行：

$ jobs
[1]+  Running     sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

在此阶段，我们能够在没有密码的情况下访问MySQL数据库：

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-1ubuntu1 (Ubuntu)
Copyright (c) 2000, 2015, 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>

使用MySQL会话首次刷新权限：

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

接下来，重置root密码。
以下命令将MySQL root密码重置为onitroad.com：

mysql> USE mysql; 
Database changed
mysql> UPDATE user SET authentication_string=PASSWORD("onitroad.com") WHERE User='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1
mysql> UPDATE user SET plugin="mysql_native_password" WHERE User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

退出MySQL会话：

mysql> quit                                                                                                                                                                                    
Bye

优雅地终止当前的mysqld过程：

$ sudo pkill mysqld                                                                                                                                                        
onitroad@ubuntu:~$ jobs                                                                                                                                                                     
[1]+  Done       sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking

最后，启动mysql数据库：

$ sudo service mysql start

如果一切顺利，我们现在应该能够使用root密码登录MySQL数据库：

$ mysql -u root --password=onitroad.com                                                                                                                                 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20-1ubuntu1 (Ubuntu)
Copyright (c) 2000, 2015, 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>