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 Table ‘performance_ schema.session_ Variables’ doesn’t exist solution
- Error 1146 (42s02): table ‘database name. Table name’ doesn’t exist ‘
- Mysql Error:The user specified as a definer (‘mysql.infoschema‘@‘localhost‘) does not exist
- mysql ERROR 1050 (42S01): Table already exists
- Django. DB. Utils. Programmingerror: (1146, “table * doesn’t exist”) causes and Solutions
- Table ‘sell.hibernate_sequence‘ doesn‘t exist
- 1072 – Key column ‘xxx_id’ doesn’t exist in table
- MySQL Workbench Failed to Connect to MySQL at 127.0.0.1:3306 with user root Bad handshake
- Get connection timeout retry: 1 MySQL errorcode 0, state 08s01 docker container accessing MySQL container is very slow and sometimes interrupted
- [MySQL] error 1396 (HY000): Operation create user failed for ‘MySQL’ @’localhost ‘
- MySQL partitions the existing tables of the data table
- Error 1045 (28000) access denied for user ‘root’ @’localhost ‘appears in MySQL under Windows system
- MySQL skip grant tables add user error 1290
- Solve MySQL error 1698 (28000): access denied for user ‘root’ @’localhost ‘
- MySQL startup problem (ERROR 1045 (28000): Access denied for user’ODBC’@’localhost’ (using password: NO))
- MySQL error set: failed to start mysql.service : Unit mysql.service is masked.
- MySQL connection error access denied for user ‘root’ @ ‘localhost‘
- SQL exception handling – MySQL error 1005 (HY000): can’t create table ‘TMP’ (errno: 13)
- MySQL error: can’t create table ‘..’（ errno:150 ）Solutions
- About error 1005 (HY000) in MySQL: can’t create table ‘_______ ‘(errno: 150) fool’s plan