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