How to create and set up a MySQL user from the command line

I want to create a new user account on the MySQL server and give him the appropriate permissions and resource restrictions. How do I create and set up a MySQL user from the command line?

To access a MySQL server, you need to log in with a user account in order to proceed. Each MySQL user account has many properties associated with it, such as user name, password, and permissions and resource restrictions. Permissions define what a particular user can do in the MySQL server, and resource restrictions set a user license for a range of server resources. Creating or updating a user involves the management of all attributes of the user account.

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

First log in to the MySQL server as root.

$ mysql-u Root-p

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

Create a MySQL user

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

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 is present in the special MySQL database.

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

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

Give MySQL user permissions

A new MySQL user does not have any access rights, which means that you cannot do anything in the MySQL database. You have to give the user the necessary privileges. The following are some of the available permissions:

All : all the available permissions

CREATE: creating libraries, tables, and Indexes

Lock_tables: Lock Table

ALTER: Modify Table

DELETE: Delete a table

INSERT: Insert a table or column

SELECT: retrieving data for a table or column

Create_view: Create a View

show_databases: List Databases

DROP: Delete libraries, tables, and views

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

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

The,<privileges> in the above command represents a comma-delimited list of permissions. If you want to assign permissions to any database (or table), use an asterisk (*) instead of the name of the database (or table).

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

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

Verify full permissions given to the user:

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

Give all the permissions to all databases/tables:

Mysql> GRANT All on * * to ' myuser ' @ ' localhost ';

You can also delete the user's existing permissions. Use the following command to revoke the existing permissions for the "MyUser" account:

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

Add Resource limits to users

In MySQL, you can set MySQL resource usage limits 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 allowable connections per hour (with MySQL global variables: max_user_connections together determine the number of simultaneous connections to the database by the user)

max_user_connections: simultaneous connections to the server

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

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

In <resource-limits> you can specify multiple resource limits that are separated using spaces.

For example, increase the Maxqueriesperhour and Maxconnectionsperhour resource limits:

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

To verify the user's resource limits:

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

One final important step in creating and setting up a MySQL User:

mysql> FLUSH privileges;

This changes will take effect. Now the MySQL user account is ready to use.

Free pick up Brother Lian IT Education Original Linux Operations Engineer video/Detailed Linux tutorials, details of the website customer service: http://www.lampbrother.net/linux/

or hooking up with Q2430675018.

Welcome to the Linux Communication Group 478068715

How to create and set up a MySQL user from the command line