忘记root密码怎么，一般的思路是关闭实例后使用--skip-grant-tables参数重启MySQL，再重置密码，这期间有一段时间MySQL是无法对外提供服务的；MySQL8.0之前mysql.user是MyISAM表，可以根据MyISAM表的特点，无需重启实例修改密码；MySQL8.0开始放弃MyISAM引擎，经过测试是需要重启的
下面是免重启重置MySQL root密码(适用于MySQL5.7及以前的版本)
- 有两个server-id分别为3307101和3309101的实例
[root@vdb_172_16_123_101 /root]# ps aux | grep mysql mysql 2792 0.3 10.3 1067856 184220 pts/0 Sl 19:46 0:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3307/my.cnf mysql 2822 0.4 10.5 1067856 186072 pts/0 Sl 19:46 0:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3309/my.cnf root 2869 0.0 0.0 112648 960 pts/0 R+ 19:47 0:00 grep --color=auto mysql
- 3307101实例测试访问
[root@vdb_172_16_123_101 /root]# mysql -S /tmp/mysql_3307.sock -p'%*KCuPs3l6_W' 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.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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. root@localhost:mysql_3307.sock [(none)]> exit Bye
- 在3309101实例创建一个库testdb(库名自己随意定义)
root@localhost:mysql_3309.sock [(none)]> create database testdb; Query OK, 1 row affected (0.00 sec)
- 把3307101实例mysql库下的user.*拷贝到3309101实例的testdb库下
[root@vdb_172_16_123_101 /root]# cp -a /data/mysql/mysql_3307/data/mysql/user.* /data/mysql/mysql_3309/data/testdb/ [root@vdb_172_16_123_101 /root]# mysql -S /tmp/mysql_3309.sock -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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. root@localhost:mysql_3309.sock [(none)]> use testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed root@localhost:mysql_3309.sock [testdb]> show tables; +------------------+ | Tables_in_testdb | +------------------+ | user | +------------------+ 1 row in set (0.00 sec)
- 在3309101实例下修改testdb库的user表的密码
[root@vdb_172_16_123_101 /root]# mysql -S /tmp/mysql_3309.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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.
root@localhost:mysql_3309.sock [(none)]> select user,host,authentication_string from testdb.user;
+-----------+-----------+-------------------------------------------+
| user | host | authentication_string |
+-----------+-----------+-------------------------------------------+
| root | localhost | *527F89C205EA0A51A7537FFA7B6090D91A7D77AE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
root@localhost:mysql_3309.sock [(none)]> update testdb.user set authentication_string=password('newpass') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
root@localhost:mysql_3309.sock [(none)]> exit
Bye
6.把testdb库下的user.*拷贝回3307101实例mysql库目录下
[root@vdb_172_16_123_101 /root]# cp -a /data/mysql/mysql_3309/data/testdb/user.* /data/mysql/mysql_3307/data/mysql cp: overwrite ‘/data/mysql/mysql_3307/data/mysql/user.frm’? y cp: overwrite ‘/data/mysql/mysql_3307/data/mysql/user.MYD’? y cp: overwrite ‘/data/mysql/mysql_3307/data/mysql/user.MYI’? y
- 给3307101一个HUP信号，重新加载配置
[root@vdb_172_16_123_101 /root]# ps aux | grep mysqld mysql 2792 0.0 10.7 1067856 189960 pts/0 Sl 19:46 0:01 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3307/my.cnf mysql 2822 0.0 11.6 1067856 206352 pts/0 Sl 19:46 0:01 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3309/my.cnf root 2912 0.0 0.0 112648 964 pts/0 S+ 20:07 0:00 grep --color=auto mysqld [root@vdb_172_16_123_101 /root]# kill -HUP 2792
- 用新密码连接3307101实例
[root@vdb_172_16_123_101 /root]# mysql -S /tmp/mysql_3307.sock -p'newpass' 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 5 Server version: 5.7.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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. root@localhost:mysql_3307.sock [(none)]>
总结：
(1) --skip-grant-tables是官方推荐的方法，但要重启实例；
(2) MySQL8.0以前mysql.user表是MyISAM引擎表，可以利用这个特点无需重启实例，不会对业务造成影响；
(3) 为了安全起见, 建议做好密码的保管工作。