MySQL database user permission table loss Solution

When installing software, you need to useMysql databaseTo create a database and corresponding users in the database, execute the database creation script. The database is successfully created, but an error occurs during user authorization:

 
 

  
  
createdatabasecactidb;  
  
  
 
  
  
grantalloncactidb.*toroot@localhost;  
  
  
 
  
  
grantalloncactidb.*tocactiuser; 
 
 

This is nothing, but I alreadyRoot @ localhostThis user has been deleted. I have left only the user with the username "root" and the host name "%". For convenience, I only use the root user to log on to the local machine, my mysql only listens to the address 127.0.0.1. Result 1: grantalloncactidb is executed. * toroot @ localhost; the command is finished immediately. I can only log on to the root account of the root @ localhost user in this instance, instead of the common root @ % user, however, root @ localhost basically has no permissions. According to the Mysql authorization mechanism, I can use root @ % only when logging on to other hosts, and I cannot log on from other addresses at all, I am in a hurry. Due to the strict firewall policy, it is impossible for me to open the mysql port. Later, I found relevant information on the Internet and used the following methods to solve the problem:

1. Close the mysql service: servicemysqldstop.

2../mysqld_safe -- skip-grant-tables to restart mysql.

3. log on to the Apsara stack console again with the root account. No Password is required.

4. After logging on, delete the root @ local user.

5. Use ps to find the mysqld process and kill it to stop the mysql process.

6. Restart the mysql process: servicemysqldstart.

7. You can also reset the root password by using this method: setpasswordforroot = passwordyourpass ');

8. You can also reset the root user permission.

 
 

  
  
updatedbsetSelect_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetInsert_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetUpdate_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetDelete_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetCreate_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetDrop_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetReferences_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetGrant_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetIndex_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetAlter_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetCreate_tmp_table_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetLock_tables_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetCreate_view_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetGrant_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetShow_view_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetCreate_routine_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetAlter_routine_priv='Y'whereuser='root';  
  
  
 
  
  
updatedbsetExecute_priv='Y'whereuser='root'; 
 
 

Set all fields of the root user in the mysql. user table to 'y '.

Note:When you use -- skip-grant-tables to start mysql, you cannot use the grant command. Therefore, you only need to manually set the permission table.

For more information about the MySQL permission table