What should I do if mysql added user cannot be accessed?

The solution to the problem that mysql added user cannot access: First log in to the MySQL server as an administrator, create a MySQL user; then grant the MySQL user permissions, and run the following command to give the "myuser" user specific permissions; Finally, add resource restrictions for the user and verify them.

##More related free learning recommendations: mysql tutorial(Video)

Mysql added user cannot access the solution:

First log in to the MySQL server as an administrator middle.

$ mysql -u root -p

View the users that have been created

SELECT host, user FROM mysql.user;

Create a MySQL user

  创建一个用户名和密码分别为"myuser"和"mypassword"的用户，可访问地址为%所有，也可以设定ip或者localhost。
  mysql> CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword';
  用户被创建后，包括加密的密码、权限和资源限制在内的所有帐号细节都会被存储在mysql这个库的user的表中。
 ** 运行下列命令，验证帐号是否创建成功**
  mysql> SELECT host, user, password FROM mysql.user WHERE user='myuser';

Give MySQL user permissions

A newly created MySQL user does not have any access permissions, so Meaning you cannot perform any operations in the MySQL database. You have to give the user the necessary permissions. The following are some of the available permissions:

• ALL: All available permissions

• CREATE: Create libraries, tables and indexes

• LOCK_TABLES: Lock table

• ALTER: Modify table

##DELETE: Delete table
INSERT: Insert into a table or column
SELECT: Retrieve data from a table or column

• ##CREATE_VIEW: Create a view

• SHOW_DATABASES: List databases

• DROP: Delete libraries, tables and views
Run The following command gives specific permissions to the "myuser" user.

mysql> GRANT <privileges> ON <database>.<table> TO &#39;myuser&#39;@&#39;localhost&#39;;
  以上命令中，<privileges> 代表着用逗号分隔的权限列表。如果你想要将权限赋予任意数据库（或表），那么使用星号（*）来代替数据库（或表）的名字。
  例如，为所有数据库/表赋予 CREATE 和 INSERT 权限：
  mysql> GRANT CREATE, INSERT ON *.* TO &#39;myuser&#39;@&#39;localhost&#39;;

Verify the full permissions granted to the user:

mysql> SHOW GRANTS FOR 'myuser'@'localhost';
  **将全部的权限赋予所有数据库/表：**
  mysql> GRANT ALL ON *.* TO 'myuser'@'localhost';
  **你也可以将用户现有的权限删除。使用以下命令废除"myuser"帐号的现有权限：**
  mysql> REVOKE <privileges> ON <database>.<table> FROM &#39;myuser&#39;@&#39;localhost&#39;;

Add resource restrictions for the user

In MySQL , you can set MySQL resource usage limits for individual users. The available resource limits are as follows:

MAX_QUERIES_PER_HOUR: 允许的每小时最大请求数量
  MAX_UPDATES_PER_HOUR: 允许的每小时最大更新数量
  MAX_CONNECTIONS_PER_HOUR: 允许的每小时最大连接（LCTT译注：其与 MySQL全局变量： max_user_connections 共同决定用户到数据库的同时连接数量）数量
  MAX_USER_CONNECTIONS: 对服务器的同时连接量

Use the following command to add a resource limit to the "myuser" account:

mysql> GRANT USAGE ON <database>.<table> TO &#39;myuser&#39;@&#39;localhost&#39; WITH <resource-limits>;
  在 <resource-limits> 中你可以指定多个使用空格分隔开的资源限制。
  例如，增加 MAXQUERIESPERHOUR 和 MAXCONNECTIONSPERHOUR 资源限制：
  mysql> GRANT USAGE ON *.* TO &#39;myuser&#39;@&#39;localhost&#39; WITH MAX_QUERIES_PER_HOUR 30 MAX_CONNECTIONS_PER_HOUR 6;

Verify the user's resource limit:

  mysql> SHOW GRANTS FOR 'myuser'@'localhost;

The last important step in creating and setting up a MySQL user:

  mysql> FLUSH PRIVILEGES;

The changes will then take effect. The MySQL user account is now ready for use.

The above is the detailed content of What should I do if mysql added user cannot be accessed?. For more information, please follow other related articles on the PHP Chinese website!