"Accessing MySQL server"

to access aMysqlserver, you need to log in with a user account to proceed. Each MySQL user account has many properties associated with it, such as user name, password, and permissions and resource restrictions. Permissions define the ability of a specific user toMysqlserver, and the resource limit sets a license for the user to use 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 aMysqluser. first log in to the MySQL server as root.

1. $ mysql-u Root-p

Copy Code

when the validation prompt appears, enterMysqlpassword for the root account.

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

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

Copy Code

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

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

Copy Code
give MySQL user permissionsa new MySQL user does not have any access rights, which means that you cannotMysqlany action in the database. You have to give the user the necessary privileges. The following are some of the available permissions:

• All: All available permissions
• Create: Creating libraries, tables, and indexes
• lock_tables: Lock table
• alter: Modify table
• Delete: Deleting a table
• insert: Inserting a table or column
• SELECT: Retrieving data for a table or column
• Create_view: Creating a View
• show_databases: List Database
• Drop: Delete 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 ';

Copy Code
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:

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

Copy Code
Verify full permissions given to the user:

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

Copy Codegive all the permissions to all databases/tables:

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

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

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

Copy Code
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
• max_updates_per_hour : Maximum number of updates allowed per hour
• max_connections_per_hour : Maximum number of connections per hour
• 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>;

Copy Code

in <resource-limits> You can specify multiple resource limits that are separated using 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;

Copy Code
to verify the user's resource limits:

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

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

1. mysql> FLUSH privileges;

Copy Code

This changes will take effect. NowMysqlthe user account can be used.

"Accessing MySQL server"