1. Account Management 1.1 login and exit MySQL server mysql–hhostname|hostip–p port–u username–p[password] databasename–e "SQL statement"
-h-h host name or Ip-p-pport[3306]-u-u[username]-p-p[password]Note: betweenAll
no spaces!-e Execute SQL statement SQL enclosed in double quotesYou can use this statement to work with the operating system's scheduled tasks to automate the processing of table data, such as periodically deleting outdated data from a table.
Example: Login: mysql-h110.110.110.110-uroot-p123Note:Local can directly: Mysql–uroot-p
Exit: exit, quit
1.2. CreateNormal User1.2.1 To create the user without privileges with the creation user syntax:
CREATE USERUser [
identified by[PASSWORD] ' Password ' [, user [identified by [password] ' password '] ... Example: Create user [email protected] identified by ' test ';//creating a username with test, password is test, the user does not have any permissions1.2.2 Using INSERT statements to create a new normal user
INSERT intoMysql.user (Host,user,password,ssl_cipher,x509_issuer,x509_subject)
VALUES(' localhost ', ' Test ', PASSWORD (' Test '), ', ', ');
FLUSH priveleges;
Note: MySQL new setup user or change password after use flush privileges refresh the MySQL system permission related table, otherwise there will be denied access, there is another way, is to restart the MySQL server for the new settings to take effect. -1.2.3 uses the GRANT statement to create a new normal user and authorizes1) Authorization
Grant permissions
on the database.
table to ' username ' @ ' login host '
identified by ' password '
Cases:
GrantSelect,insert,update,delete
on*.*
to' Test ' @ '% ';
Note: 1) Permissions: SELECT, Update,delete,Insert (
table Data );Create,alert,drop (table structure);references (foreign key);Create temporary tables (creates temporary table);Index (Operation index);Create View,show view (view);Create Routine,alert routine,execute (stored procedure);All,all Privileges (
All rights )
2) Database: Database name or * (all databases)3) Table: Table name or * (all tables under a database)4) Host: Host name or% (any other host)
2) View Permissions
Show Grants;//Own example:
Show Grants
for[email protected];//specified user specified HOST3] permission to retract
RevokePermissions
onDatabase. Table
from' User name ' @ ' login host '; Note: To change from example:
RevokeAll
on*.*
from' Test ' @ '% ';
REVOKEAll privileges, GRANT OPTION
fromUser [, User] ...
1.3 Delete a normal user1) Delete the normal user with the drop USER statement
DROP
USERUser [, user] ...;Example:
Drop user ' test ' @ ' localhost ';
2) Delete normal user with DELETE statement
DELETE
fromMysql.user
WHEREUser= ' username ' and host= ' hostname ';
FLUSH PrivilegesCases
Delete fromMysql.user
whereUser= ' and host= ';
1.4 Fix the user password 1.4.1root user to modify their password 1) Use the mysqladmin command to modify the root user's password mysqladmin–u username–p password "New_password"; 2) Modify User table
UPDATEMysql.user
SETPassword=password ("New_password")
WHEREUser= ' root ' and host= ';
FLUSH Privileges; 3) Use the SET statement to modify the root user's password
SETPassword=password ("New_password");1.4.2root user modified password for normal user1) Use the Mysqladmin command is not applicable, mysqladmin can only modify the root user password2) Modify user table
UPDATEMysql.user
SETPassword=password ("New_password")
WHEREUser= ' and host= ';
FLUSH Privileges; 3) Use the SET statement to modify the password of the normal user
SETPASSWORD
for' User ' @ ' localhost ' =password ("New_password"); 4) Use the GRANT statement to modify the password of the normal user
GRANTPriv_type
onDatabase.table
toUser [
identified by[PASSWORD] ' PASSWORD[User[identified by [PASSWORD] ' PASSWORD '] ...1.4.3 Normal User Password change
SETPassword=password ("New_password"); 1.4.4, root user password Loss Resolution 1) Use the-skip-grant-tables option to start the MySQL service >mysqld– Skip-grant-tables #/etc/init.d/mysql start–mysqld–skip-grant-tables 2) login root, set new password Mysql–u rootupdate mysql.user set Password=password ("New_password") where user= ' root ' and host= ' localhost '; 3) Load Permissions table flush privileges;
Links: 1, http://blog.csdn.net/kimsoft/article/details/67473792, HTTP://WWW.CNBLOGS.COM/HENCEHONG/ARCHIVE/2013/03/19 /mysql_user.html
From for notes (Wiz)
"MySQL Notes" user management
