How to create a user for MySQL on the command line of a Linux system

This article mainly introduces the Linux system in the command line for MySQL to create users, including the user's rights management, the need for friends can refer to the

To access a MySQL server, you need to log in with a user account to proceed. Each MySQL user account has a number of properties associated with it, such as user name, password, and permissions and resource restrictions. "Permissions" defines what a particular user can do on a MySQL server, and "Resource throttling" sets the user license for a range of server resources. Creating or updating a user involves managing all attributes of a user account.

The following shows how to create and set up a MySQL user in Linux.

First log on to the MySQL server as root.

?

1	$ mysql-u Root-p

When the validation prompts appear, enter the password for the MySQL root account.

Create a MySQL user

Use the following command to create a user with a username and password of "MyUser" and "MyPassword" respectively.

?

1	mysql> CREATE USER ' myuser ' @ ' localhost ' identified by ' mypassword ';

Once the user is created, all account details, including encrypted passwords, permissions, and resource restrictions, are stored in a table named user, which exists in MySQL, a special database.

Verify that the account was created successfully by running the following command

?

1	Mysql> SELECT Host, user, password from mysql.user WHERE user= ' myuser ';

Give MySQL user permissions

A new MySQL user doesn't have any access rights, which means you can't do anything with the MySQL database. You have to give the user the necessary authority. The following are some of the available permissions:

All: Available Permissions

Create: Creating libraries, tables, and indexes

Lock_tables: Lock Table

ALTER: Modifying a table

Delete: Deleting tables

Insert: Inserting a table or column

SELECT: Retrieve data for a table or column

Create_view: Creating views

Show_databases: Listing databases

Drop: Deleting libraries, tables, and views

Run the following command to give the "myuser" user specific permissions.

?

1	Mysql> GRANT <privileges> on <database>.<table> to ' myuser ' @ ' localhost ';

In the above command, a comma-delimited list of permissions is represented. If you want to give permissions to any database (or table), use an asterisk (*) instead of the name of the database (or table).

For example, give CREATE and INSERT permissions to all databases/tables:

?

1	Mysql> GRANT CREATE, INSERT on *.* to ' myuser ' @ ' localhost ';

Verify the full permissions given to the user:

?

1	Mysql> Show grants for ' myuser ' @ ' localhost ';

Give all the permissions to all databases/tables:

?

1	Mysql> GRANT all on *.* to ' myuser ' @ ' localhost ';

You can also delete the user's existing permissions. Revoke the existing permissions for the "MyUser" account by using the following command:

?

1	Mysql> REVOKE <privileges> on <database>.<table> from ' myuser ' @ ' localhost ';

To add a resource limit to a user

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

Max_queries_per_hour: Maximum number of requests per hour allowed

Max_updates_per_hour: Maximum number of updates allowed per hour

Max_connections_per_hour: Maximum allowed per hour connection (LCTT: The number of simultaneous connections between users and MySQL global variables: max_user_connections)

Max_user_connections: Simultaneous connection to the server

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

?

1	Mysql> GRANT USAGE on <database>.<table> to ' myuser ' @ ' localhost ' with <resource-limits>;

In the , you can specify multiple resource restrictions that are separated by spaces.

For example, increase the Maxqueriesperhour and Maxconnectionsperhour resource limits:

?

1	Mysql> GRANT USAGE on *.* to ' myuser ' @ ' localhost ' with max_queries_per_hour max_connections_per_hour 6;

To verify user resource limits:

?

1	Mysql> Show grants for ' myuser ' @ ' localhost;

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

?

1	mysql> FLUSH privileges;

So the change will take effect. Now the MySQL user account can be used.