1# 创建用户的一些限制和注意点用户名长度必须不超过16个字符用户名是大小写敏感的2# 创建用户语法：(root@localhost)[(none)]> help create userName: '
1# 创建用户的一些限制和注意点
用户名长度必须不超过16个字符
用户名是大小写敏感的
2# 创建用户
语法：
(root@localhost)[(none)]> help create userName: 'CREATE USER'Description:Syntax:CREATE USER user_specification [, user_specification] ...user_specification: user [ identified_option ]auth_option: { IDENTIFIED BY 'auth_string'| IDENTIFIED BY PASSWORD 'hash_string'| IDENTIFIED WITH auth_plugin| IDENTIFIED WITH auth_plugin AS 'hash_string'}The CREATE USER statement creates new MySQL accounts. An error occursif you try to create an account that already exists.按照语法，最简答的创建用户的方法：
c(root@localhost)[mysql]> create user test1;Query OK, 0 rows affected (0.00 sec)(root@localhost)[mysql]> select user,host,password from user;+-------+-----------+-------------------------------------------+| user | host | password |+-------+-----------+-------------------------------------------+| root | localhost | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B || test1 | % | |+-------+-----------+-------------------------------------------+2 rows in set (0.00 sec)2 rows in set (0.00 sec)reate user test1;这个时候其实密码是空的，可以空密码登录的。
[mysql@mysql01 ~]$ mysql -S /data/mysqldata/3306/mysql.sock -utest1Welcome to the MySQL monitor. Commands end with ; or /g.Your MySQL connection id is 3Server version: 5.6.31-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.(test1@localhost)[(none)]> 但是没有任何权限：（USAGE这个权限，是代表废物的意思！嗯，就是这样）
(test1@localhost)[(none)]> show grants;+-----------------------------------+| Grants for test1@% |+-----------------------------------+| GRANT USAGE ON *.* TO 'test1'@'%' |+-----------------------------------+1 row in set (0.00 sec)3# 给用户设置密码：
命令
(root@localhost)[mysql]> help set passwordName: 'SET PASSWORD'Description:Syntax:SET PASSWORD [FOR user] = password_optionpassword_option: { PASSWORD('auth_string') | OLD_PASSWORD('auth_string') | 'hash_string'}给test1设置一个密码：
(root@localhost)[mysql]> set password for test1=password('passwordtest');Query OK, 0 rows affected (0.00 sec)(root@localhost)[mysql]> select user,host,password from user where user='test1';+-------+------+-------------------------------------------+| user | host | password |+-------+------+-------------------------------------------+| test1 | % | *A76A397AE758994B641D5C456139B88F40610926 |+-------+------+-------------------------------------------+1 row in set (0.00 sec)至于OLD_PASSWORD()函数，是为了兼容老版本的密码而存在，古老的mysql4。
然而，set password for <user>=password('string'); 这种修改方式已经被设置为要弃用，所以需要使用标准的修改密码方式：
(root@localhost)[mysql]> alter user test1 identified by 'password4test1';ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'password4test1'' at line 1(root@localhost)[mysql]> 以上可见报错了。原因是5.6还不支持这种密码修改方式：
(root@localhost)[mysql]> help alter user;Name: 'ALTER USER'Description:Syntax:ALTER USER user_specification [, user_specification] ...user_specification: user PASSWORD EXPIRE这里只有一个子句，就是设置密码过期
3# 账号的密码过期：
(root@localhost)[mysql]> alter user test1 password expire;Query OK, 0 rows affected (0.00 sec)(root@localhost)[mysql]> select user,host,password,password_expired from user;+-------+-----------+-------------------------------------------+------------------+| user | host | password | password_expired |+-------+-----------+-------------------------------------------+------------------+| root | localhost | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B | N || test1 | % | *A76A397AE758994B641D5C456139B88F40610926 | Y |+-------+-----------+-------------------------------------------+------------------+2 rows in set (0.00 sec)可以看到账号密码已经过期。
但是过期以后还是可以登录，但是什么都干不了，会提示马上更改密码：
[mysql@mysql01 ~]$ mysql -S /data/mysqldata/3306/mysql.sock -utest1 -p'passwordtest'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 4Server version: 5.6.31-logCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.(test1@localhost)[(none)]> select 1 -> ;ERROR 1820 (HY000): You must SET PASSWORD before executing this statement(test1@localhost)[(none)]>#修改当前账户的密码：(test1@localhost)[(none)]> set password = password('password4test1');Query OK, 0 rows affected (0.00 sec)(test1@localhost)[(none)]> #再次尝试登录，并做查询测试[mysql@mysql01 ~]$ mysql -S /data/mysqldata/3306/mysql.sock -utest1 -p'password4test1'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 5Server version: 5.6.31-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.(test1@localhost)[(none)]> select 1;+---+| 1 |+---+| 1 |+---+1 row in set (0.00 sec)#查询成功，说明密码更改成功。用管理账号查询use表查看账号状态：(root@localhost)[mysql]> select user,host,password,password_expired from user;+-------+-----------+-------------------------------------------+------------------+| user | host | password | password_expired |+-------+-----------+-------------------------------------------+------------------+| root | localhost | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B | N || test1 | % | *CFA887C680E792C2DCF622D56FB809E3F8BE63CC | N |+-------+-----------+-------------------------------------------+------------------+2 rows in set (0.00 sec)4# 远程登录
在user表中，test1的host列值为%，代表可以从任意位置登录mysql
[mysql@mysql01 ~]$ mysql -utest1 -p'password4test1' -h 192.168.199.101 -P 3306Warning: 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 11Server version: 5.6.31-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.([email protected])[(none)]> 5# 比较完整方式创建用户
(root@localhost)[mysql]> create user test2@'%' identified by 'password4test2';Query OK, 0 rows affected (0.00 sec)(root@localhost)[mysql]> create user test2@'192.168.199.101' identified by 'test2local';Query OK, 0 rows affected (0.00 sec)(root@localhost)[mysql]> select user,host,password from user where user='test2';+-------+-----------------+-------------------------------------------+| user | host | password |+-------+-----------------+-------------------------------------------+| test2 | 192.168.199.101 | *74F386E8F5EEC7648BABDD0FCBA4524B97344856 || test2 | % | *5AB2E18AD9EE76F76E1C02E4DBF97BC7C3B4588B |+-------+-----------------+-------------------------------------------+2 rows in set (0.00 sec)(root@localhost)[mysql]> 建立了两个test2，这两个test2是不同的，实际上应该说，用户test2@'192.168.199.101' 和用户test2@'%' 是两个不同的用户。
[mysql@mysql01 ~]$ mysql -utest2 -p'test2local' -h 192.168.199.101 -P 3306Warning: 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 14Server version: 5.6.31-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.([email protected])[(none)]> [mysql@mysql01 ~]$ mysql -utest2 -S /data/mysqldata/3306/mysql.sock -p'password4test2'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 21Server version: 5.6.31-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.(test2@localhost)[(none)]> 5# 修改密码：
1，set password 方式：
(root@localhost)[mysql]> set password for test1=password('password4test1'); Query OK, 0 rows affected (0.00 sec)2，直接update系统表user，这种方式需要刷新权限列表
(root@localhost)[mysql]> update user set password=password('password4test1') where user='test1';Query OK, 0 rows affected (0.00 sec)Rows matched: 1 Changed: 0 Warnings: 0(root@localhost)[mysql]> flush privileges;Query OK, 0 rows affected (0.00 sec)3，grant 方式
(root@localhost)[mysql]> grant usage on *.* to test1 identified by 'password4test1';Query OK, 0 rows affected (0.01 sec)