When we were testing the service and restarting it, we failed and reported an error of “Table ‘mysqlm. user’ doesn’t exist”. This user Table was created before, and it saved the data of users and permissions of the whole database. How could it not exist?
The database login is still normal, indicating that the user is still available, but the query is executed after entering
SELECT * FROM mysql. The user;
ERROR 1146 (42S02): Table ‘mysql.user’ doesn’t exist
Do something!!
When I checked the status of the cluster, I found that it was not normal. The USER table of 3 Mqsql in the cluster was gone, and only Master node was left working alone. Although the other two were RUNNING, they were working independently from the cluster, which was so strange…
Looking for a circle on the Internet, there are some good ways to try;
1 stop service
2 data directory mysql folder rename
3 execute mysql_install_db regenerate authorization table
4 start service
So since the Master can’t move, so I’m going to try slave first, execute mysql_install_DB
The user table is generated, but just like when it was installed, there is no data. The login password has to be reconfigured. In this way, if you join the cluster, there will be some problems.
I had to consult my database-savvy colleagues, and at their suggestion, COPY a user table from somewhere else, and then re-add the user information to the database user table according to the various existing services (fortunately, most of the services in this cluster were applications under test). When you rejoin the cluster, it’s back to normal…
Also, although the user data for the service is not in the table copied, when I restart the service and recreate the user to create the table, It tells me that the user already exists. DROP USER ‘username ‘@’ %’ first; And then “flush privileges”; Refresh permission operation; Recreate the user again, and that’s fine. Does MySQL have a cache of places to store this information??
But why does the mysql.user table disappear?I didn’t see any drop table operation through the MySQL log. It’s so strange… Can only restore the service first, the problem first record and then slowly check…
The database login is still normal, indicating that the user is still available, but the query is executed after entering
SELECT * FROM mysql. The user;
ERROR 1146 (42S02): Table ‘mysql.user’ doesn’t exist
Do something!!
When I checked the status of the cluster, I found that it was not normal. The USER table of 3 Mqsql in the cluster was gone, and only Master node was left working alone. Although the other two were RUNNING, they were working independently from the cluster, which was so strange…
Looking for a circle on the Internet, there are some good ways to try;
1 stop service
2 data directory mysql folder rename
3 execute mysql_install_db regenerate authorization table
4 start service
So since the Master can’t move, so I’m going to try slave first, execute mysql_install_DB
The user table is generated, but just like when it was installed, there is no data. The login password has to be reconfigured. In this way, if you join the cluster, there will be some problems.
I had to consult my database-savvy colleagues, and at their suggestion, COPY a user table from somewhere else, and then re-add the user information to the database user table according to the various existing services (fortunately, most of the services in this cluster were applications under test). When you rejoin the cluster, it’s back to normal…
Also, although the user data for the service is not in the table copied, when I restart the service and recreate the user to create the table, It tells me that the user already exists. DROP USER ‘username ‘@’ %’ first; And then “flush privileges”; Refresh permission operation; Recreate the user again, and that’s fine. Does MySQL have a cache of places to store this information??
But why does the mysql.user table disappear?I didn’t see any drop table operation through the MySQL log. It’s so strange… Can only restore the service first, the problem first record and then slowly check…
Read More:
- mysql ERROR 1050 (42S01): Table already exists
- [MySQL] error 1396 (HY000): Operation create user failed for ‘MySQL’ @’localhost ‘
- Prompt SQL server error 15023 solution memo when modifying user mapping
- MySQL password setting error message: error 1054 (42s22): unknown column ‘password’ in ‘field list’
- MySQL operation and maintenance slave_ skip_ errors
- Export and import method of MySQL under Linux
- MySQL ERROR 1054 (42S22): Unknown column’password’ in’field list’ error
- MYSQL login error: mysqladmin: connect to server at’localhost’ failed
- MySQL error: error 1010 (HY000) when deleting database
- Solution to SQL Server Error: 4064
- Error 1045 (28000): access denied for user ‘root’ @’localhost ‘(using password: no)
- After installing MySQL again under Linux, the solution of ‘MySQL module failed to start’ appears
- Oracle database file is damaged, Error:ORA-01033 :ORACLE initialization or shutdown in progress
- MySQL startup problem (ERROR 1045 (28000): Access denied for user’ODBC’@’localhost’ (using password: NO))
- Solution to MySQL Error 1130 problem
- MySQL error — multiple methods of failed to find valid data directory and MySQL setting password appear
- Error: can’t locate revision identified by ‘xxx’
- After the two hosts set up the master-slave replication of MySQL, the show slave status displays: last_ IO_ Error: error connecting to master ……
- [error handling] when logging into MySQL with CentOS command, an error 1045 (28000) is reported
- An error occurred when opensips started the service“ ERROR:core :db_ check_ table_ Version: invalid version 7 for Table dialog found “