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…