[Access the MySQL server]

[Access the MySQL server]
To access a MySQL server, you must use a user account to log on to it. Each MySQL user account has many related attributes, such as the user name, password, permissions, and resource restrictions. "Permission" defines what a specific user can do on the MySQL server, and "Resource Restriction" sets a series of server resource usage licenses for the user. Creating or updating a user involves managing all attributes of the user account.


The following shows how to create and set a MySQL user in Linux. First, log on to the MySQL server as the root user.
$ Mysql-u root-p copy code

When the verification prompt appears, enter the password of the MySQL root Account.

Create a MySQL user and use the following command to create a user with the username and password "myuser" and "mypassword" respectively.
Mysql> create user 'myuser' @ 'localhost' identified by 'mypassword'; copy the code

Once a user is created, all account details, including encrypted passwords, permissions, and resource restrictions, will be stored in a table named "user, this table exists in the special mysql database. Run the following command to verify that the account is successfully created
Mysql> SELECT host, user, password FROM mysql. user WHERE user = 'myuser'; copy the code
Grant MySQL user permissions. A new MySQL user does not have any access permissions, which means that you cannot perform any operations in the MySQL database. You must grant necessary permissions to the user. The following are some available permissions:
ALL: ALL available Permissions
CREATE: Creates databases, tables, and indexes.
LOCK_TABLES: locking a table
ALTER: modify a table
DELETE: DELETE a table
INSERT: INSERT table or column
SELECT: Retrieves table or column data.
CREATE_VIEW: Create a view
SHOW_DATABASES: list databases
DROP: deletes databases, tables, and views.
Run the following command to grant specific permissions to the "myuser" user.
Mysql> GRANT ON . TO 'myuser' @ 'localhost'; copy the code
In the preceding command, A list of permissions separated by commas. If you want to grant permissions to any database (or table), use the asterisk (*) instead of the database (or table) Name. For example, grant CREATE and INSERT permissions to all databases/tables:
Mysql> grant create, insert on *. * TO 'myuser' @ 'localhost'; copy the code
Verify the full permissions granted to the user:
Mysql> show grants for 'myuser' @ 'localhost'; copy the code to grant all permissions to all databases/tables:
Mysql> grant all on *. * TO 'myuser' @ 'localhost'; copy the code and Delete the existing permissions of the user. Run the following command to revoke the existing permissions of the "myuser" account:
Mysql> REVOKE ON .

FROM 'myuser' @ 'localhost'; copy the code
Add resource limits to MySQL for users. You can set resource limits for MySQL for individual users. The following restrictions apply to available resources:
MAX_QUERIES_PER_HOUR: Maximum number of requests per hour
MAX_UPDATES_PER_HOUR: Maximum number of updates per hour allowed
MAX_CONNECTIONS_PER_HOUR: Maximum number of connections allowed per hour
MAX_USER_CONNECTIONS: number of simultaneous connections to the server
Run the following command to add a resource limit for the "myuser" account:
Mysql> GRANT USAGE ON . TO 'myuser' @ 'localhost' ; Copy code

In You can specify multiple resource limits separated by spaces. For example, add MAXQUERIESPERHOUR and MAXCONNECTIONSPERHOUR resource restrictions:
Mysql> grant usage on *. * TO 'myuser' @ 'localhost' WITH MAX_QUERIES_PER_HOUR 30 MAX_CONNECTIONS_PER_HOUR 6; copy the code
Verify the user's resource restrictions:
Mysql> show grants for 'myuser' @ 'localhost; copy the code
The last important step to create and set a MySQL user is:
Mysql> flush privileges; copy the code

The change takes effect. Now the MySQL user account can be used.