MySQL用户账户管理

查看账户管理的帮助信息

mysql> help account management
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the 
following topics:
   CREATE USER
   DROP USER
   GRANT
   RENAME USER
   REVOKE
   SET PASSWORD

MySQL用户名和密码

用用户名和客户端或主机定义MySQL账户，用户可以根据这些名称来连接服务器。账户也有密码。MySQL和操作系统使用用户名和密码的方式有几处区别：

1. MySQL账户和密码与系统账户和密码没有关系
2. MySQL的加密密码使用自己的算法，该算法不同于Unix登陆过程使用的算法

使用MySQL账户和密码登陆MySQL数据库服务器

shell> mysql --user=monty --password=guess db_name
shell> mysql -u monty -pguess db_name #<span>-p</span>选项和后面的密码值之间绝对不能有空格
shell> mysql --user=monty --password db_name
shell> mysql -u monty -p db_name

向MySQL增加新账户

通常情况下有两种方法来创建MySQL账户，一是直接使用GRANT语句，另一种是直接操作MySQL授权表。也可以使用第三方程序，例如，phpMyAdmin

GRANT语句：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
        ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
        ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

其中前面两个账户有相同的名字(monty)和密码some_pass，均为超级用户账户，具有完全的权限可以做任何事情。一个账户 ( 'monty'@'localhost' )只用于从本机连接时。另一个账户 ( 'monty'@'%' )可用于从其它主机连接。一个账户有用户名 admin，没有密码。该账户只用于从本机连接。授予了 RELOAD和 PROCESS管理权限。一个账户有用户名 admin，没有密码。该账户只用于从本机连接。授予了 RELOAD和 PROCESS管理权限。
INSERT语句：

shell> mysql --user=root mysql
mysql> INSERT INTO user
        ->     VALUES('localhost','monty',PASSWORD('some_pass'),
       ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user
       ->     VALUES('%','monty',PASSWORD('some_pass'),
       ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin',
       ->     Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
     ->     VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;  

当用 INSERT创建账户时使用 FLUSH PRIVILEGES的原因是告诉服务器重读授权表。否则，只有重启服务器后更改方会被注意到。使用 GRANT，则不需要使用 FLUSH PRIVILEGES。

查看当前MySQL服务器上面有多少MySQL用户：

mysql> select user,host,password from mysql.user;
+-------+-----------------------+-------------------------------------------+
| user  | host                  | password                                  |
+-------+-----------------------+-------------------------------------------+
| root  | localhost             | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root  | localhost.localdomain | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root  | 127.0.0.1             | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
|       | localhost             |                                           |
|       | localhost.localdomain |                                           |
| test1 | localhost             |                                           |
+-------+-----------------------+-------------------------------------------+

删除MySQL账户

使用DROP语句，一般格式为：DROP USER user [,usr] ... 一次可以删除多个MySQL账户

等多帮助信息可以查看DROP的帮助文档：

mysql> help drop;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   ALTER TABLE
   ALTER TABLESPACE
   DEALLOCATE PREPARE
   DROP DATABASE
   DROP EVENT
   DROP FUNCTION
   DROP FUNCTION UDF
   DROP INDEX
   DROP PROCEDURE
   DROP SERVER
   DROP TABLE
   DROP TABLESPACE
   DROP TRIGGER
   DROP USER
   DROP VIEW

mysql> help drop user
Name: 'DROP USER'
Description:
Syntax:
DROP USER user [, user] ...

The DROP USER statement removes one or more MySQL accounts and their
privileges. It removes privilege rows for the account from all grant
tables. To use this statement, you must have the global CREATE USER
privilege or the DELETE privilege for the mysql database. Each account
name uses the format described in
http://dev.mysql.com/doc/refman/5.1/en/account-names.html. For example:

DROP USER 'jeffrey'@'localhost';

If you specify only the user name part of the account name, a host name
part of '%' is used.

URL: http://dev.mysql.com/doc/refman/5.1/en/drop-user.html

设置账户密码

可以用mysqladmin命令在命令行指定密码：

shell> mysqladmin -u user_name -h host_name password "newpwd"

用MySQL的root去更新其他人的密码：

mysql> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');

MySQL账户更改自己的密码：

mysql> SET PASSWORD = PASSWORD('biscuit');
mysql> GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit'; #另外一种方法

想要更新已有账户的密码，可以用UPDATE来设置Password列值：

shell> mysql -u root mysql
mysql> UPDATE user SET Password = PASSWORD('bagel')
       -> WHERE Host = '%' AND User = 'francis';
mysql> FLUSH PRIVILEGES;

查考：http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#adding-users