Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to solve the problem that the user table in mysql does not have. Xiaobian thinks it is quite practical, so share it with you as a reference. I hope you can gain something after reading this article.

mysql user table has no solution: 1. Add "skip-grant-tables" to "[mysqld]";2. Edit my.cnf configuration file and add "sql_mode=NO_ENGINE_SUBSTITUTION";3. Restart mysql service.

Operating environment: Windows 7 system, mysql version 5.5, Dell G3 computer.

mysql forgot password, reset password, mysql.user table is empty solution:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Modify mysql configuration file my.cnf:

vim /etc/my.cnf

Add in [mysqld]

skip-grant-tables

Restart mysql service, log in directly with empty password, query user table, if there is any result, modify it according to the following steps, if the result is empty, directly the final solution

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)

Change the password of the corresponding user

#Reset password to 123456mysql> 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#Refresh permissions to make configuration effective mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec)#Exit mysql> quitBye

Cancel or comment out the skip-grant-tables option added to my.cnf profile, restart mysql service, and call it a day.

mysql> select Host,User,authentication_string from mysql.user;Empty set (0.00 sec)#exit login mysql> exitBye

Edit the my.cnf configuration file and add or modify the following two items (I don't have sql_mode here):

sql_mode=NO_ENGINE_SUBSTITUTIONskip-grant-tables

Stop mysql service and start database safe mode: mysqld_safe &

Connect to database with root empty password: mysql -uroot -p

Insert root user data

mysql> insert into mysql.user(Host,User,authentication_string) values("%","root",password("123456")); Query OK, 1 row affected, 4 warnings (0.00 sec)

Query mysql.user table again, error will be reported

mysql> select Host,User,authentication_string from mysql.user;ERROR 1194 (HY000): Table 'user' is marked as crashed and should be repaired

Need to fix mysql.user table

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 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)

To modify the privileges of the root user:

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';#update privileges mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)#exit login mysql> exitBye

exit mysql and restore the modifications to my.cnf profile

End mysqld_safe process: pkill mysql

Start mysql service: systemctl start mysql

Problem solved!

About "how to solve the problem that user table does not have in mysql" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.