2.mysql用户添加、授权、改密码

1.用户创建

1.语法的格式

CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 

2.语法说明

• username： 你将创建的用户名
• host ：指定该用户在哪个主机上可以登陆
  • 本地用户使用localhost，如果想让该用户可以从任意远程主机登陆,可以使用通配符%
• password：用户的登录密码

3.举例

创建用户test,密码为123456

CREATE USER test IDENTIFIED BY '123456';

2.用户授权

1.语法的格式（1.授权 2.刷新权限到数据库）

GRANT privileges ON databasename.tablename TO 'username'@'host' IDENTIFIED BY 'password'  WITH GRANT OPTION;
flush privileges;

2.语法说明

• privileges： 用户的操作权限,如SELECT , INSERT , UPDATE 等,如果要授予所的权限则使用ALL
• databasename ：数据库名
• tablename：表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 比如所有数据库的所有表[*.*]
• password：用户登录的密码

3.举例

1.让用户在所有ip都可以登录

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;

2.让用户在指定ip可以登录

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.3' IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;

3.如果用户无法从本地登陆，这个时候就执行如下

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost.localdomain' IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;

4.如果用户依然无法从本地登陆，这个时候就执行如下

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;

5.如果还无法本地登陆，并且报错是无法找到 mysql.sock 文件，需要修改一下 my.cnf 文件

添加如下内容：

[mysql.server]
user=mysql
basedir=/opt/tools/mysql

[client]
socket=/opt/tools/mysql/mysql.sock

3.撤销用户权限

1.语法格式

REVOKE privilege ON databasename.tablename FROM 'username'@'host'; 

2.语法说明

同授权部分说明

3.举例

REVOKE SELECT ON *.* FROM 'root'@'%'; 

注意: 假如你在给用户’root’@’%'授权的时候是这样的(或类似的):

GRANT SELECT ON test.user TO 'root'@'%';

则在使用

REVOKE SELECT ON *.* FROM 'pig'@'%';

命令并不能撤销该用户对test数据库中user表的SELECT 操作.相反,如果授权使用的是

GRANT SELECT ON *.* TO 'pig'@'%';

则

REVOKE SELECT ON test.user FROM 'pig'@'%';

命令也不能撤销该用户对test数据库中user表的Select 权限。至于用户的具体授权信息可以用如下命令查看.

SHOW GRANTS FOR 'root'@'%';

4.删除用户

DROP USER 'username'@'host'; 

5.mysql中的权限列表

ALTER	Allows use of ALTER TABLE.
ALTER ROUTINE	Alters or drops stored routines.
CREATE	Allows use of CREATE TABLE.
CREATE ROUTINE	Creates stored routines.
CREATE TEMPORARY TABLE	Allows use of CREATE TEMPORARY TABLE.
CREATE USER	Allows use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEW	Allows use of CREATE VIEW.
DELETE	Allows use of DELETE.
DROP	Allows use of DROP TABLE.
EXECUTE	Allows the user to run stored routines.
FILE	Allows use of SELECT… INTO OUTFILE and LOAD DATA INFILE.
INDEX	Allows use of CREATE INDEX and DROP INDEX.
INSERT	Allows use of INSERT.
LOCK TABLES	Allows use of LOCK TABLES on tables for which the user also has SELECT privileges.
PROCESS	Allows use of SHOW FULL PROCESSLIST.
RELOAD	Allows use of FLUSH.
REPLICATION	Allows the user to ask where slave or master
CLIENT	servers are.
REPLICATION SLAVE	Needed for replication slaves.
SELECT	Allows use of SELECT.
SHOW DATABASES	Allows use of SHOW DATABASES.
SHOW VIEW	Allows use of SHOW CREATE VIEW.
SHUTDOWN	Allows use of mysqladmin shutdown.
SUPER	Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached.
UPDATE	Allows use of UPDATE.
USAGE	Allows connection without any specific privileges.

5.密码重置

1. 先结束mysql 进程

killall mysqld

2. 用mysql 安全模式运行并跳过权限

mysqld_safe --skip-grant-tables

打开一个新窗口

3. 用root 登录 ，此时不需要密码

mysql -u root

4.现在开始修改密码了

mysql> use mysql;

　　Reading table information for completion of table and column names

　　You can turn off this feature to get a quicker startup with -A

　　Database changed

mysql> update user set Password = PASSWORD('your new password') where User = 'root' ;

　　Query OK, 2 rows affected (0.02 sec)

　　Rows matched: 2  Changed: 2  Warnings: 0

mysql> quit

　　Bye

注意：当运行’ mysqld_safe --skip-grant-tables’ 后停在’Starting mysqld daemon with databases from /var/lib/mysql’ ，可以新开一个远程终端，继续操作。