[Access MySQL Server], mysql Server

Source: Internet
Author: User

[Access MySQL Server], 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.

  1. $ 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.
  1. Mysql> create user 'myuser' @ 'localhost' identified by 'mypassword ';
Copy 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
  1. Mysql> SELECT host, user, password FROM mysql. user WHERE user = 'myuser ';
Copy 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.
  1. Mysql> GRANT <privileges> ON <database>. <table> TO 'myuser' @ 'localhost ';
Copy code
In the preceding command, <privileges> indicates 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:
  1. Mysql> grant create, insert on *. * TO 'myuser' @ 'localhost ';
Copy code
Verify the full permissions granted to the user:
  1. Mysql> show grants for 'myuser' @ 'localhost ';
Copy the code to grant all permissions to all databases/tables:
  1. Mysql> grant all on *. * TO 'myuser' @ 'localhost ';
You can also delete your existing permissions by copying the code. Run the following command to revoke the existing permissions of the "myuser" account:
  1. Mysql> REVOKE <privileges> ON <database>. <table> FROM 'myuser' @ 'localhost ';
Copy 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:
  1. Mysql> grant usage on <database>. <table> TO 'myuser' @ 'localhost' WITH <resource-limits>;
Copy code

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

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

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.