Create a user and assign permissions and problem solutions to MySQL under Linux

Source: Internet
Author: User

To install MySQL under Linux, please refer to installing MySQL server in Linux system detailed steps

1. Create a new user

Log in to MySQL
@>mysql-u root-p
@> Password
Create user
mysql> INSERT INTO Mysql.user (Host,user,password) VALUES (' localhost ', ' JEECN ', Password (' JEECN '));
Refresh System Permissions Table
Mysql>flush privileges;
This creates a user named: JEECN Password: JEECN.
Log in after exiting
mysql>exit;
@>mysql-u jeecn-p
@> Enter password
Mysql> Login Successful
2. Authorizing the user
Log in to MySQL (with root privileges). I am logged in as root.
@>mysql-u root-p
@> Password
First create a database for the user (JEECNDB)
Mysql>create database jeecndb;
Authorizing JEECN users to have all rights to the JEECN database
@>grant all privileges in jeecndb.* to[email protected]Identified by ' JEECN ';
Refresh System Permissions Table
Mysql>flush privileges;
Mysql> Other operations
If you want to specify partial permissions to a user, you can write:
Mysql>grant select,update on jeecndb.* to[email protected]Identified by ' JEECN ';
Refreshes the System permissions table.
Mysql>flush privileges;
Mysql> grant permissions 1, permissions 2,... Permission n on the database name. Table name to User name @ user address identified by ' connection password ';
Permissions 1, Permissions 2,... Permission n represents 14 permissions, such as Select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file.
When permissions 1, permissions 2,... Permission n is replaced by all privileges or all to give the user full permission.
When the database name. The table name is replaced by *. * to give the user permission to manipulate all tables on all databases on the server.
The user address can be localhost, or it can be an IP address, a machine name, and a domain name. You can also use '% ' to indicate connections from any address.
' Connection password ' cannot be empty, otherwise the creation failed.
For example:
Mysql>grant Select,insert,update,delete,create,drop on Vtdc.employee to[email protected]identified by ' 123′;
The user Jee from 10.163.225.87 is assigned the ability to perform operations such as Select,insert,update,delete,create,drop on the employee table of the database VTDC, and set the password to 123.
Mysql>grant all privileges in vtdc.* to[email protected]identified by ' 123′;
The user Jee from 10.163.225.87 is assigned permission to perform all operations on the database VTDC all tables, and the password is set to 123.
Mysql>grant all privileges on * * to[email protected]identified by ' 123′;
The user Jee from 10.163.225.87 is assigned permission to perform all operations on all tables in all databases, and the password is set to 123.
Mysql>grant all privileges on * * to[email protected]identified by ' 123′;
The native user Jee is assigned permission to perform all operations on all tables in all databases, and the password is set to 123.
3. Delete a user
@>mysql-u root-p
@> Password
Mysql>delete from Mysql.user WHERE user= "JEECN"
Mysql>flush privileges;
Delete a user's database
Mysql>drop database jeecndb;
4. Modify the specified user password
@>mysql-u root-p
@> Password
Mysql>update Mysql.user Set Password=password (' New password ') where user= "JEECN" and host= "localhost";
Mysql>flush privileges;

mysql>quit;

5. If you cannot connect to a local library using root:

Hint: 1045 Access denied for user ' root ' @ ' localhost ' using password Yes
a few days ago, did not work yesterday, the results of this evening to remind you of the above error, restart MySQL or not
methods on the Internet to see a few but do not know how to get
Method one:  
#/etc/init.d/mysql stop 
# Mysqld_safe--user=mysql-- Skip-grant-tables--skip-networking & 
# mysql-u root mysql 
mysql> UPDATE user SET password= PASSWORD (' NewPassword ') where user= ' root ';  
mysql> FLUSH privileges; 
mysql> quit 
#/ Etc/init.d/mysql restart 
# mysql-uroot-p 
Enter Password: < Enter the newly created password newpassword> 
mysql> 
Method Two:  
directly using the user name and password provided in the [Client] section of the/etc/mysql/debian.cnf file:  
# mysql- UDEBIAN-SYS-MAINT-P&NBSP
Enter Password: < Enter the password for the [client] section > 
mysql> UPDATE mysql.user SET Password=password (' NewPassword ') where user= ' root ';  
mysql> FLUSH privileges; 
Mysql> quit &NBSP
# mysql-uroot-p 
Enter Password: < Enter the new password newpassword> 
mysql> 

Create a user and assign permissions and problem solutions to MySQL under Linux

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.