The solution to the problem that the mysql user table is gone: 1. Add "skip-grant-tables" to "[mysqld]"; 2. Edit the my.cnf configuration file and add "sql_mode=NO_ENGINE_SUBSTITUTION"; 3. , Restart the mysql service.
The operating environment of this article: Windows 7 system, mysql version 5.5, Dell G3 computer.
What should I do if the mysql user table is gone?
mysql Forgot password, reset password, solution to mysql.user table is empty:
1. There is a user in the user table, change the password directly
1 | ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: YES)
|
Copy after login
Modify the mysql configuration file my.cnf:
Add
in [mysqld] Restart the mysql service, log in directly with an empty password, query the user table, if there are results, modify as follows: The final solution if the result is empty
1 2 3 4 5 6 7 8 9 10 | 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)
|
Copy after login
Modify the password of the corresponding user
1 2 3 4 5 6 7 8 9 10 11 12 | # 重置密码为 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
|
Copy after login
Cancel or comment out the option skip-grant-tables added in the my.cnf configuration file, restart the mysql service, and it's done knock off.
2. If the user table has no data, you need to insert user data
1 2 3 4 5 | mysql> select Host,User,authentication_string from mysql.user;
Empty set (0.00 sec)
# 退出登陆
mysql> exit
Bye
|
Copy after login
Edit the my.cnf configuration file and add or modify the following two items (I have no sql_mode item):
1 2 | sql_mode=NO_ENGINE_SUBSTITUTION
skip-grant-tables
|
Copy after login
- Stop the mysql service and start the safe mode of the database: mysqld_safe &
- Use the root empty password to connect to the database: mysql -uroot -p
Insert root user data
1 2 | mysql> insert into mysql.user(Host,User,authentication_string) values( "%" , "root" ,password( "123456" ));
Query OK, 1 row affected, 4 warnings (0.00 sec)
|
Copy after login
Query the mysql.user table again and an error will be reported
1 2 | mysql> select Host,User,authentication_string from mysql.user;
ERROR 1194 (HY000): Table 'user' is marked as crashed and should be repaired
|
Copy after login
Need to repair the mysql.user table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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)
|
Copy after login
Modify the permissions of the root user:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | 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
|
Copy after login
Recommended study: "mysql video tutorial"
The above is the detailed content of What should I do if the mysql user table is gone?. For more information, please follow other related articles on the PHP Chinese website!