mysql user表沒有了的解決方法：1、在「[mysqld]」中加入「skip-grant-tables」；2、編輯my.cnf設定文件，新增「sql_mode=NO_ENGINE_SUBSTITUTION」；3 、重啟mysql服務。
本文操作環境：Windows7系統，mysql5.5版，Dell G3電腦。
mysql user表沒有了怎麼辦？
mysql 忘記密碼，重設密碼，mysql.user表為空的解決方法：
一、用戶表有用戶，直接修改密碼
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
登入後複製
修改mysql設定檔my.cnf：
在[mysqld]中加入
重啟mysql服務，用空密碼直接登錄，查詢用戶表，有結果則如下步驟修改，結果為空直接最後的解決辦法
mysql> select Host,User,authentication_string from mysql.user;
+-----------+---------------+-------------------------------------------+
| Host | User | authentication_string |
+-----------+---------------+-------------------------------------------+
| localhost | root | *6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | root | *6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
登入後複製
修改對應用戶的密碼
# 重置密码为 123456
mysql> update mysql.user set authentication_string=password('123456') where user='root'
Query OK, 2 rows affected, 1 warning (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 1
# 刷新权限，使配置生效
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
# 退出
mysql> quit
Bye登入後複製
取消或註解掉my.cnf設定檔添加的選項skip-grant-tables ，重啟mysql服務，搞定收工。
二、用戶表沒有數據，則需要插入用戶數據
mysql> select Host,User,authentication_string from mysql.user;
Empty set (0.00 sec)
# 退出登陆
mysql> exit
Bye
登入後複製
編輯my.cnf配置文件，添加或修改下面兩項（我這沒有sql_mode項目）：
sql_mode=NO_ENGINE_SUBSTITUTION
skip-grant-tables
登入後複製
- 停止mysql服務，啟動資料庫的安全模式：mysqld_safe &
- 使用root 空密碼連接資料庫： mysql -uroot -p
##插入root用戶資料
mysql> insert into mysql.user(Host,User,authentication_string) values("%","root",password("123456"));
Query OK, 1 row affected, 4 warnings (0.00 sec)登入後複製
再次查詢mysql.user表，會報錯
mysql> select Host,User,authentication_string from mysql.user;
ERROR 1194 (HY000): Table 'user' is marked as crashed and should be repaired
登入後複製
需要修正mysql.user表
mysql> REPAIR TABLE mysql.user;
+------------+--------+----------+-------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------+--------+----------+-------------------------------------------------+
| mysql.user | repair | info | Wrong bytesec: 113-108- 95 at 396; Skipped |
| mysql.user | repair | info | Found block that points outside data file at 32 |
| mysql.user | repair | info | Found block that points outside data file at 36 |
| mysql.user | repair | info | Found block that points outside data file at 40 |
| mysql.user | repair | info | Found block that points outside data file at 44 |
| mysql.user | repair | info | Found block that points outside data file at 48 |
| mysql.user | repair | info | Found block that points outside data file at 52 |
| mysql.user | repair | info | Found block that points outside data file at 56 |
| mysql.user | repair | info | Found block that points outside data file at 60 |
| mysql.user | repair | warning | Number of rows changed from 4 to 3 |
| mysql.user | repair | status | OK |
+------------+--------+----------+-------------------------------------------------+
11 rows in set (0.64 sec)
# 再次查询user表
mysql> select Host,User,authentication_string from mysql.user;
+-----------+---------------+-------------------------------------------+
| Host | User | authentication_string |
+-----------+---------------+-------------------------------------------+
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.00 sec)
登入後複製
修改root用戶的權限：
update mysql.user set
`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'
where user='root';
# 更新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 退出登陆
mysql> exit
Bye
登入後複製
退出mysql，將my.cnf設定檔的修改還原- 結束mysqld_safe 程序：pkill mysql
-
- 啟動mysql服務: systemctl start mysql
問題解決！
推薦學習：《
mysql影片教學》
以上是mysql user表沒有了怎麼辦的詳細內容。更多資訊請關注PHP中文網其他相關文章！
