MySQL忘记密码了怎么解决

笔者曾经有一次误删了mysqlroot用户，怎么办？

之前的解决方式是通过忽略授权表的方式重启mysql然后插入相关数据解决该问题的，但是这种方式需要重启mysql，会影响现有业务，那么有没有其他方式可以不重启MySQL就解决呢?

因为mysql的user表示MyISAM引擎的，因此我们可以通过修改对应的文件来解决这个问题。下面是本人在测试环境的一次演练，仅供参考。

一、查看现有用户

  1. 04:18:34 root@localhost [mysql]>select user,host from user;
  2. +---------------+-----------+
  3. | user          | host      |
  4. +---------------+-----------+
  5. | mysql.session | localhost |
  6. | mysql.sys     | localhost |
  7. | root          | localhost |
  8. +---------------+-----------+
  9. 3 rows in set (0,00 sec)

二、删除本地root用户

  1. 04:18:59 root@localhost [mysql]>drop user root@'localhost';

三、再次查看用户

  1. 04:20:02 root@localhost [mysql]>select user,host from user;
  2. +---------------+-----------+
  3. | user          | host      |
  4. +---------------+-----------+
  5. | mysql.session | localhost |
  6. | mysql.sys     | localhost |
  7. +---------------+-----------+

现在我们开始恢复用户数据

四、移动user表相关文件

  1. [root@localhost mysql]# cp user.* /vagrant/mysql/3307/data/test/

五、登录另一个实例并查看user表

  1. 04:23:53 root@localhost [(none)]>use test;
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A
  5. Database changed
  6. 04:23:56 root@localhost [test]>show tables;
  7. +----------------+
  8. | Tables_in_test |
  9. +----------------+
  10. | user           |
  11. +----------------+
  12. 1 row in set (0,00 sec)
  14. 04:23:58 root@localhost [test]>select * from user\G
  15. *************************** 1. row ***************************
  16.                   Host: localhost
  17.                   User: mysql.session
  18.            Select_priv: N
  19.            Insert_priv: N
  20.            Update_priv: N
  21.            Delete_priv: N
  22.            Create_priv: N
  23.              Drop_priv: N
  24.            Reload_priv: N
  25.          Shutdown_priv: N
  26.           Process_priv: N
  27.              File_priv: N
  28.             Grant_priv: N
  29.        References_priv: N
  30.             Index_priv: N
  31.             Alter_priv: N
  32.           Show_db_priv: N
  33.             Super_priv: Y
  34.  Create_tmp_table_priv: N
  35.       Lock_tables_priv: N
  36.           Execute_priv: N
  37.        Repl_slave_priv: N
  38.       Repl_client_priv: N
  39.       Create_view_priv: N
  40.         Show_view_priv: N
  41.    Create_routine_priv: N
  42.     Alter_routine_priv: N
  43.       Create_user_priv: N
  44.             Event_priv: N
  45.           Trigger_priv: N
  46. Create_tablespace_priv: N
  47.               ssl_type:
  48.             ssl_cipher:
  49.            x509_issuer:
  50.           x509_subject:
  51.          max_questions: 0
  52.            max_updates: 0
  53.        max_connections: 0
  54.   max_user_connections: 0
  55.                 plugin: mysql_native_password
  56.  authentication_string: *C7A1AAE2D250AFD864050FAF4935EF6F5D185A92
  57.       password_expired: N
  58.  password_last_changed: 2018-02-23 13:19:12
  59.      password_lifetime: NULL
  60.         account_locked: Y
  61. *************************** 2. row ***************************
  62.                   Host: localhost
  63.                   User: mysql.sys
  64.            Select_priv: N
  65.            Insert_priv: N
  66.            Update_priv: N
  67.            Delete_priv: N
  68.            Create_priv: N
  69.              Drop_priv: N
  70.            Reload_priv: N
  71.          Shutdown_priv: N
  72.           Process_priv: N
  73.              File_priv: N
  74.             Grant_priv: N
  75.        References_priv: N
  76.             Index_priv: N
  77.             Alter_priv: N
  78.           Show_db_priv: N
  79.             Super_priv: N
  80.  Create_tmp_table_priv: N
  81.       Lock_tables_priv: N
  82.           Execute_priv: N
  83.        Repl_slave_priv: N
  84.       Repl_client_priv: N
  85.       Create_view_priv: N
  86.         Show_view_priv: N
  87.    Create_routine_priv: N
  88.     Alter_routine_priv: N
  89.       Create_user_priv: N
  90.             Event_priv: N
  91.           Trigger_priv: N
  92. Create_tablespace_priv: N
  93.               ssl_type:
  94.             ssl_cipher:
  95.            x509_issuer:
  96.           x509_subject:
  97.          max_questions: 0
  98.            max_updates: 0
  99.        max_connections: 0
  100.   max_user_connections: 0
  101.                 plugin: mysql_native_password
  102.  authentication_string: *C7A1AAE2D250AFD864050FAF4935EF6F5D185A92
  103.       password_expired: N
  104.  password_last_changed: 2018-02-23 13:19:26
  105.      password_lifetime: NULL
  106.         account_locked: Y
  107. 2 rows in set (0,00 sec)

六、把本实例的root用户插入刚刚移动过来的user表

  1. 04:25:03 root@localhost [test]>insert into user select * from mysql.user where user = 'root' and host ='localhost';
  2. Query OK, 1 row affected (0,02 sec)
  3. Records: 1  Duplicates: 0  Warnings: 0

七、再次移动user文件到原先的位置

  1. [root@localhost test]# cp user.* /vagrant/mysql/3306/data/mysql

八、重新加载配置到内存

  1. [root@localhost test]# kill -HUP `pidof mysqld`

通过该步mysql并不会重启，对应用不会有影响

九、重新登录，OK

  1. [root@localhost test]# mysql -uroot -p -S /tmp/mysql3306.sock
  2. Enter password:
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 13
  5. Server version: 5.7.21-log MySQL Community Server (GPL)
  7. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. 04:30:57 root@localhost [(none)]>