创建用户：
mysql> create user 'cai'@'localhost' identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; +---------------+-----------+ | user | host | +---------------+-----------+ | cai | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | | wordpress | localhost | +---------------+-----------+ 5 rows in set (0.00 sec)
[root@cairui ~]# mysql -ucai -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 275 Server version: 5.7.21-debug Source distribution Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
授权：
命令：GRANT privileges ON databasename.tablename TO 'username'@'host'
mysql> grant select,delete,create,insert,update on aa.* to 'cai'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for cai@localhost; +-----------------------------------------------------------------------------+ | Grants for cai@localhost | +-----------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'cai'@'localhost' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `aa`.* TO 'cai'@'localhost' | +-----------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
撤销用户权限：
命令：REVOKE privilege ON databasename.tablename FROM 'username'@'host';
mysql> show grants for cai@localhost; +-----------------------------------------------------------------------------+ | Grants for cai@localhost | +-----------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'cai'@'localhost' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `aa`.* TO 'cai'@'localhost' | +-----------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> revoke insert on aa.* from 'cai'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for cai@localhost; +---------------------------------------------------------------------+ | Grants for cai@localhost | +---------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'cai'@'localhost' | | GRANT SELECT, UPDATE, DELETE, CREATE ON `aa`.* TO 'cai'@'localhost' | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec)
设置与修改用户密码：
方法一：
[root@cairui ~]# mysqladmin -ucai -p'123456' password '123' mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. [root@cairui ~]# mysql -ucai -p123 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 279 Server version: 5.7.21-debug Source distribution Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
方法二：
mysql> update mysql.user set authentication_string=password('cai') where user='cai' ;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
[root@cairui ~]# mysql -ucai -pcai;
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 284
Server version: 5.7.21-debug Source distribution
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
方法三：
mysql> set password=password('root');
Query OK, 0 rows affected, 1 warning (0.00 sec)
[root@cairui ~]# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 288
Server version: 5.7.21-debug Source distribution
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
忘记密码情况下的登录：
[root@cairui mysql]# bin/mysqld_safe --skip-grant-tables & #登录时不需要密码，进入可修改密码
删除用户：
mysql> select user,host from mysql.user; +---------------+-----------+ | user | host | +---------------+-----------+ | cai | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | | wordpress | localhost | +---------------+-----------+ 5 rows in set (0.00 sec) mysql> drop user 'cai'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; +---------------+-----------+ | user | host | +---------------+-----------+ | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | | wordpress | localhost | +---------------+-----------+ 4 rows in set (0.00 sec)