MySQL 5.7 误删管理员 root%'localhost'账户的解决方法.
1) 停止数据库
kill MySQL 进程
2) 在加上 --skip-grant-tables 启动 MySQL 进程加粗样式
其中 --skip-grant-tables 意思是启动 MySQL 服务的时候跳过权限表认证.
启动后, 连接到 MySQL 的 root 将不需要口令
- cd /mysqldata/MySQL/bin
- ./mysqld_safe --defaults-file=/etc/my.cnf --user=MySQL --skip-grant-tables&
3) 用空密码的 root 用户连接到 MySQL, 并且更改 root 口令
- MySQL -uroot -p
- use MySQL;
- insert into user set user='root',ssl_cipher='',x509_issuer='',x509_subject='';
- flush privileges;
- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'mN6WT8c#' WITH GRANT OPTION;
- flush privileges;
4) 此时查看新添加用户的权限
- MySQL> select * from MySQL.user where user='root'\G;
- *************************** 1. row ***************************
- Host: localhost
- User: root
- Select_priv: Y
- Insert_priv: Y
- Update_priv: Y
- Delete_priv: Y
- Create_priv: Y
- Drop_priv: Y
- Reload_priv: Y
- Shutdown_priv: Y
- Process_priv: Y
- File_priv: Y
- Grant_priv: Y
- References_priv: Y
- Index_priv: Y
- Alter_priv: Y
- Show_db_priv: Y
- Super_priv: Y
- Create_tmp_table_priv: Y
- Lock_tables_priv: Y
- Execute_priv: Y
- Repl_slave_priv: Y
- Repl_client_priv: Y
- Create_view_priv: Y
- Show_view_priv: Y
- Create_routine_priv: Y
- Alter_routine_priv: Y
- Create_user_priv: Y
- Event_priv: Y
- Trigger_priv: Y
- Create_tablespace_priv: Y
- ssl_type:
- ssl_cipher:
- x509_issuer:
- x509_subject:
- max_questions: 0
- max_updates: 0
- max_connections: 0
- max_user_connections: 0
- plugin: mysql_native_password
- authentication_string: *A1027271824AD76D2BBEED42D431EF9273BB492A
- password_expired: N
- password_last_changed: 2019-07-11 16:29:23
- password_lifetime: NULL
- account_locked: N
- *************************** 2. row ***************************
- Host: %
- User: root
- Select_priv: Y
- Insert_priv: Y
- Update_priv: Y
- Delete_priv: Y
- Create_priv: Y
- Drop_priv: Y
- Reload_priv: Y
- Shutdown_priv: Y
- Process_priv: Y
- File_priv: Y
- Grant_priv: N
- References_priv: Y
- Index_priv: Y
- Alter_priv: Y
- Show_db_priv: Y
- Super_priv: Y
- Create_tmp_table_priv: Y
- Lock_tables_priv: Y
- Execute_priv: Y
- Repl_slave_priv: Y
- Repl_client_priv: Y
- Create_view_priv: Y
- Show_view_priv: Y
- Create_routine_priv: Y
- Alter_routine_priv: Y
- Create_user_priv: Y
- Event_priv: Y
- Trigger_priv: Y
- Create_tablespace_priv: Y
- ssl_type:
- ssl_cipher:
- x509_issuer:
- x509_subject:
- max_questions: 0
- max_updates: 0
- max_connections: 0
- max_user_connections: 0
- plugin: mysql_native_password
- authentication_string: *A1F1CB851D62F002C09A0C9C4A76262473432F55
- password_expired: N
- password_last_changed: 2019-07-04 16:55:40
- password_lifetime: NULL
- account_locked: N
- 2 rows in set (0.00 sec)
- ERROR:
- No query specified
5) 重启 MySQL 服务
- cd /mysqldata/MySQL/bin
- [root@linuxidc bin]# ./mysqladmin -uroot -p shutdown
输入密码:
或者 kill MySQL 进程
- [root@linuxidc /]# cd /mysqldata/MySQL/bin
- [root@linuxidc bin]# ./mysqld_safe --defaults-file=/etc/my.cnf --user=MySQL &
来源: http://www.linuxidc.com/Linux/2019-08/160125.htm