前言:刚才用WIN10/SERVER2012的默认输入法打了个‘%’(全角),保存,重启然后悲剧了
处理方法:
1、停止mysql服务;
在mysql安装目录(windows是在ProgramData文件夹下)下找到my.ini;
在my.ini中找到以下片段[mysqld];
另起一行加入代码:skip-grant-tables 并保存
2、启动mysql服务,并登录mysql(无用户名和密码);
找到user表
2.1、误删除处理:加入root用户,mysql.sys自己看情况
INSERT INTO `user` (`Host`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `Event_priv`, `Trigger_priv`, `Create_tablespace_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`, `password_expired`, `password_last_changed`, `password_lifetime`, `account_locked`) VALUES
('localhost', 'root', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', _binary '', _binary '', _binary '', 0, 0, 0, 0, 'mysql_native_password', password('123456'), 'N', '2018-10-19 13:23:43', NULL, 'N'),
('localhost', 'mysql.sys', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', _binary '', _binary '', _binary '', 0, 0, 0, 0, 'mysql_native_password', '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE', 'N', '2018-10-19 13:23:40', NULL, 'Y');
2.2、错误配置处理:更新root用户
UPDATE user set `Host`='localhost',`authentication_string`=password('123456') where `User`='root';
此步骤可以用可视化编辑工具,如Heidisql,直接编辑处理
3、把my.ini刚才加入的那行删除(# 注释)并重启服务
4、用root用户登录,OK!