转 MySQL权限管理

###sample:

#####view all user
SELECT user, host from mysql.user;
mysql> SELECT user, host from mysql.user;
+----------+-----------+
| user | host |
+----------+-----------+
| dbmonopr | % |
| svrman | % |
| ubasopr | % |
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
| | ppppdb01 |
| root | ppppdb01 |
+----------+-----------+
9 rows in set (0.00 sec)

MYSQL 用户管理

1.权限表

MYSQL是一个多用户的数据库，MYSQL的用户可以分为两大类：

（1）       超级管理员用户（root），拥有全部权限

（2）       普通用户，由root创建，普通用户只拥有root所分配的权限

1.1 权限表的位置

数据库：mysql

与权限相关的数据表：user,db,host,tables_priv,columns_priv，procs_priv等

1.2 user表

User表存储了：

（1）用户的信息：hots（用户所在的主机）,user（用户名）,password（密码）

（2）用户的权限信息：_priv

（3）安全性相关的信息：ssl_,x509,记录用户登录的验证操作等

（4）与资源分配相关的信息：max_，

max_questions表示用户在一个小时内最多可以进行的查询次数。

max_updates表示用户在一个小时内最多可以进行的更新次数，也包括增加数据、删除数据。

Max_connections：表示用户最多可以建立的连接数

1.2.1 user表的内容

（1）host列

Localhost表示本机的用户或者超级管理员

%表示任何主机上的root用户

说明：_priv权限是全局权限，不受数据库的范围限制

1.3 DB表

（1）与用户相关的字段：hots（用户所在的主机）,user（用户名）,

（2）与权限相关的字段：_priv，DB字段规定了_priv权限的有效范围。

1.4 host表

（1）与用户相关的字段：hots（用户所在的主机）

（2）与权限相关的字段：_priv，DB字段规定了_priv权限的有效范围。

说明：

（1）       记录主机上的用户对数据库拥有的权限，侧重点在主机，而不在用户，例如假设select_priv=Y,那个这个主机上的所有数据库用户都拥有select权限。

（2）       Host表的优先级大于db表，如果db表规定这个用户没有权限，但是host表规定了这台主机的用户有权限，那么db的这个用户也是拥有权限的。

1.5 tables_priv表

设定了用户对某个表拥有的权限，该表记录了用户的信息，以及某个表的权限信息table_priv(select ,lnsert,alter等等)，以及表上的某个列的权限信息column_priv。

1.6 column_priv表

记录某用户对某表的某个列所拥有的权限。

1.7procs_priv 表

    规定了用户关于存储过程及存储函数的操作权限，主要字段：proc_priv

 

2. 创建数据库用户

 

2.1 创建普通用户

2.1.1 CREATE USER

CREATE USER ‘用户名称’ [@’主机名称’]

例：CREATE USER 'user1';

验证是否创建成功：

mysql> SELECT user FROM mysql.user;

+-------+

| user  |

+-------+

| user1 |

| root  |

| root  |

|       |

| pma   |

| root  |

+-------+

6 rows in set (0.00 sec)  说明新创建的用户已经进入user表内

说明：使用CREATE USER来创建的用户，均无任何权限，user表的权限字段的值均为N

（1）创建带有主机名的用户

CREATE USER ‘用户名称’ [@’主机名称’] [INDENTIFIED BY ‘用户密码’]

CREATE USER 'user2' @'localhost';

mysql> select user,host from mysql.user;

+-------+---------------------+

| user  | host      |

+-------+---------------------+

| user1 | %         |

| root  | 127.0.0.1 |

| root  | ::1       |

|       | localhost |

| pma   | localhost |

| root  | localhost |

| user2 | localhost |

+-------+---------------------+

7 rows in set (0.00 sec)

说明：host字段的%表示不受任何主机的限制

（2）创建带密码的用户

CREATE USER 'user3' @'localhost'  [INDENTIFIED BY ‘用户密码’];

例子：

CREATE USER 'user3' @'localhost' IDENTIFIED BY '123333';

验证：

mysql> SELECT user,password,host FROM mysql.user;

+-------+-------------------------------------------------------------------------+-----------+

| user  | password                                  | host      |

+-------+-------------------------------------------------------------------------+-----------+

| root  |                                           | localhost |

| root  |                                           | 127.0.0.1 |

| root  |                                           | ::1       |

|       |                                           | localhost |

| pma   |                                           | localhost |

| user1 |                                           | %         |

| user2 |                                           | localhost |

| user3 | *0166E21E66009700F528CA21179AF9AD81120DA2 | localhost |

+-------+-------------------------------------------------------------------------+-----------+

8 rows in set (0.00 sec)

说明：密码是哈希码的形式显示的

2.1.2 使用GRANT来创建用户，以及授予权限

GRANT是用来给用户授权的，但是也可以用来创建用户，GRANT在给用户进行授权的时候，如果用户是不存在的，那么GRANT会自动创建这个用户，然后再给这个用户进行授权。

(1)添加权限

grant 权限 on 数据库.表 to '用户名'@'登录主机'  [INDENTIFIED BY ‘用户密码’];

权限： select ,update,delete,insert(表数据)、create,alert,drop(表结构)、references(外键)、create temporary tables(创建临时表)、index(操作索引)、create view,show view(视图)、create routine,alert routine,execute(存储过程)、all,all privileges(所有权限)

数据库：数据库名或者*(所有数据库)

表：表名或者*(某数据库下所有表)，*.*表示所有数据库的所有表

主机:主机名或者%(任何其他主机)

例：grant selec,insert,update,delete on *.* to 'jifei'@'%';

GRANT SELECT ON *.* TO 'user4' @'localhost' IDENTIFIED BY '123333';

mysql> SELECT user,password,host FROM mysql.user;

+-------+-------------------------------------------------------------------------------+-----------+

| user  | password                                    | host      |

+-------+-------------------------------------------------------------------------------+-----------+

| root  |                                             | localhost |

| root  |                                             | 127.0.0.1 |

| root  |                                             | ::1      |

|       |                                            | localhost |

| pma   |                                            | localhost |

| user1 |                                              | %      |

| user2 |                                             | localhost |

| user3 | *0166E21E66009700F528CA21179AF9AD81120DA2   | localhost |

| user4 | *0166E21E66009700F528CA21179AF9AD81120DA2   | localhost |

+-------+-------------------------------------------------------------------------------+-----------+

9 rows in set (0.00 sec)

(2) 为用户授予指定数据库、指定表、指定列的权限：

GRANT UPDATE(cid,cname) ON mysqlpart2.custom TO 'user3'@'localhost';

授权成功后，可以在以下表中查看到授权信息：

数据库: mysql » 表: tables_priv "Table privileges"

数据库: mysql » 表: columns_priv "Column privileges"

(3)用户权限表

位置：数据库: information_schema »表: USER_PRIVILEGES

表的说明：

GRANTEE:授权者

PRIVILEGE_TYPE:权限名称

用户表：数据库: mysql »表: user "Users and global privileges"

说明：user表中，”_priv”的值域USER_PRIVILEGES表的PRIVILEGE_TYPE的值是一一对应的。

（4）权限的层级关系

①权限的层级关系，就是指权限的适用范围。

②权限的最高层级是全局级，所谓全局级就是可以在任何数据库的任何数据表上进行操作。

③数据库级：只能在某个数据库上进行操作。

④表级：权限信息所在位置：数据库: mysql »表: tables_priv "Table privileges"

⑤列级：权限信息所在位置：数据库: mysql »表: columns_priv "Column privileges"

⑥子程序级：权限信息所在位置：数据库: mysql »表: procs_priv "Procedure privileges"

（5）撤销权限

REVOKE 权限 ON 数据库.表 FROM  '用户名'@'登录主机;

说明：赋权与撤销权限的区别，就是REVOKE是将to改为from

例：revoke all on *.* from  ‘jifei’  @’%’;

REVOKE UPDATE(cid,cname) ON mysqlpart2.custom FROM 'user3'@'localhost';

(6) 查看权限

SHOW GRANTS;//自己

SHOW GRANTS FOR  用户名称@主机名称；

例：

SHOW GRANTS FOR  dba@localhost;//指定用户指定host

mysql> SHOW GRANTS FOR user3@localhost;

+---------------------------------------------------------------------------------------------------------------+

| Grants for user3@localhost                                                                                    |

+---------------------------------------------------------------------------------------------------------------+

| GRANT SELECT ON *.* TO 'user3'@'localhost' IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD' |

| GRANT UPDATE (cname, cid) ON `mysqlpart2`.`custom` TO 'user3'@'localhost'                                     |

+---------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

说明：所有SHOW关键字后面的词都是复数，所有CREATE关键字后面的词都是单数

通过mysql.columns_priv表来查看权限：

SELECT * FROM mysql.columns_priv WHERE user='user3' AND host='localhost';

mysql> SELECT * FROM mysql.columns_priv WHERE user='user3' AND host='localhost'G

*************************** 1. row ***************************

       Host: localhost

         Db: mysqlpart2

       User: user3

 Table_name: custom

Column_name: cid

  Timestamp: 0000-00-00 00:00:00

Column_priv: Update

*************************** 2. row ***************************

       Host: localhost

         Db: mysqlpart2

       User: user3

 Table_name: custom

Column_name: cname

  Timestamp: 0000-00-00 00:00:00

Column_priv: Update

2 rows in set (0.00 sec)

2.1.3 关于以直接向user表插入记录的方式来创建用户

可以使用INSERT的方式，直接向user表插入记录，以此来创建用户，但是因为user表的字段很多，而且全部字段均不允许为空，这就需要为每一个列赋值，所以不推荐使用这种方式来创建用户。

2.1.4 CREATE USER 与 GRANT 两种方式创建用户的区别

（1）CREATE USER 创建用户的优点：语法简单

（2）CREATE USER 创建用户的不足：用户无权限

（3）GRANT 创建用户的优势：创建的用户有权限

（4）GRANT 创建用户的不足：语法较CREATE USER 繁琐

3. 删除MYSQL的用户

delete from mysql.user where user='用户名称' and host='主机名称';

例：DELETE FROM mysql.user WHERE user='user3' AND host='localhost';

删除后使用：FLUSH PRIVILEGES 来刷新权限

 

说明：

使用DELETET删除用户后，必须使用FLUSH PRIVILEGES 来刷新权限，否则将无法继续创建用户名与已删用户的用户名相同的用户，即使在user表中看不到已删除的用户，如果不刷新权限，也是无法再新建的。

例：

删除用户user3：

mysql> DELETE FROM mysql.user WHERE user='user3' AND host='localhost';

Query OK, 1 row affected (0.00 sec)

查看用户表，user3已删除成功：

mysql> select user from mysql.user;

+-------+

| user  |

+-------+

| user1 |

| root  |

| root  |

|       |

| pma   |

| root  |

| user2 |

| user4 |

+-------+

8 rows in set (0.00 sec)

创建用户user3失败：

mysql> CREATE USER 'user3' @'localhost' IDENTIFIED BY 'pwd';

ERROR 1396 (HY000): Operation CREATE USER failed for 'user3'@'localhost'

刷新权限：

FLUSH PRIVILEGES;

再次创建用户，成功：

mysql> CREATE USER 'user3' @'localhost' IDENTIFIED BY 'pwd';

Query OK, 0 rows affected (0.00 sec)

4. 修改用户密码：

UPDATE mysql.user SET password=PASSWORD('新密码') WHERE user='用户名

[AND host=’主机名称’]';

UPDATE mysql.user SET password=PASSWORD('111111') WHERE user='root';

注意：

(1)如果不加WHERE 条件，则会把所有用户的密码都修改为’新密码’

(2)密码修改完成后，需要进行权限刷新操作才能生效，FLUSH PRIVILEGES;

例：

UPDATE mysql.user SET password=PASSWORD('111') WHERE user='user1';

（1）修改密码的权限

ROOT用户可以修改自己的密码，也可以修改其他用户的密码

其他用户只能修改自己的密码

（2）PASSWORD函数

用于把密码明文进行加密,所得到的密码为原密码的哈希值。

例：

mysql> SELECT PASSWORD('111');

+-------------------------------------------+

| PASSWORD('111')                           |

+-------------------------------------------+

| *832EB84CB764129D05D498ED9CA7E5CE9B8F83EB |

+-------------------------------------------+

1 row in set (0.07 sec)

（3）ROOT用户、普通用户修改自己的密码

SET PASSWORD=PASSWORD(‘新密码’);

 

（4）ROOT 用户为其他用户修改密码：

SET PASSWORD FOR ‘用户名称’  @’主机名称’  = password(‘新密码’);

例，以下两种修改密码的方式结果相同：

SET PASSWORD FOR 'user1' @'%'=PASSWORD('111');

UPDATE mysql.user SET password=PASSWORD('111') WHERE user='user1';

扩展：http://www.cnblogs.com/hencehong/archive/2013/03/19/mysql_user.html

### MySql: 查看当前登录用户,当前数据库

 

mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select database();
+--------------------+
| database() |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

 

##############

如何实时查看mysql当前连接数?

mysql> show processlist; 可以显示前100条连接信息 show full processlist; 可以显示全部。随便说下，如果用普通账号登录，就只显示这用户的。注意命令后有分号。

如何实时查看mysql当前连接数? 1、查看当前所有连接的详细资料: ./mysqladmin -uadmin -p -h10.140.1.1 processlist 2、只查看当前连接数(Threads就是连接数.): ./mysqladmin -uadmin -p -h10.140.1.1 status 、查看当前所有连接的详细资料: mysqladmin -uroot -proot processlist D:MySQLin>mysqladmin -uroot -proot processlist +-----+------+----------------+---------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+----------------+---------+---------+------+-------+------------------+ | 591 | root | localhost:3544 | bbs | Sleep | 25 | | | | 701 | root | localhost:3761 | | uery | 0 | | show processlist | +-----+------+----------------+---------+---------+------+-------+------------------+ 2、只查看当前连接数(Threads就是连接数.): mysqladmin -uroot -proot status D:MySQLin>mysqladmin -uroot -proot status Uptime: 2102 Threads: 3 Questions: 15531 Slow queries: 0 Opens: 0 Flush tab les: 1 Open tables: 61 Queries per second avg: 7.389 3、修改mysql最大连接数： 打开my.ini，修改max_connections=100(默认为100)。 今天有一台mysql服务器突然连接数暴增，并且等待进程全部被锁...因为问题解决不当，导致被骂...OTL 总结：以后要快速定位错误，布置解决方案 登录到mysql客户端后，使用status命令也能获得thread连接数以及当前连接的id 或者用 show full processlist 看一下所有连接进程，注意查看进程等待时间以及所处状态 是否locked 如果进程过多，就把进程打印下来，然后查看 mysql -e 'show full processlist;' > 111 查找非locked的进程，一般就是当前执行中卡死，导致后面的进程排队的原因。 另外，修改mysql最大连接数的方法： 编辑MySQL(和PHP搭配之最佳组合)配置文件　 my.cnf 或者是 my.ini 在[MySQL(和PHP搭配之最佳组合)d]配置段添加： max_connections = 1000 保存，重启MySQL(和PHP搭配之最佳组合)服务。 然后用命令： MySQL(和PHP搭配之最佳组合)admin -uroot -p variables 输入root数据库账号的密码后可看到 | max_connections | 1000 | 查看MySQL连接数和当前用户Mysql连接数 先用管理员身份进入mysql提示符。 #mysql -uroot -pxxxx mysql> show processlist; 可以显示前100条连接信息 show full processlist; 可以显示全部。随便说下，如果用普通账号登录，就只显示这用户的。注意命令后有分号。 如果我们想查看这台服务器设置。 #vi /etc/my.cnf set-variable=max_user_connections=30 这个就是单用户的连接数 set-variable=max_connections=800 这个是全局的限制连接数

###mysql> show grants for pppopr;

##七、赋予权限

##mysql> grant select on pppopr.* to bzfys;

###view all table_schema
show databases

###view all column in table_schema
SELECT
TABLE_NAME, -- 表名
COLUMN_NAME, -- 字段名
DATA_TYPE, -- 字段类型
COLUMN_COMMENT -- 字段注释
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'mysql_ppp'

###view all table in table_schema
select table_name from information_schema.tables where table_schema='mysql_ppp' and table_type='base table';

select * from mysql_ppp.t_merchant

#########begin to grant
grant select on mysql_ppp.t_merchant to dbmonopr ;
grant select on mysql_ppp.t_user_account to dbmonopr ;
grant select on mysql_ppp.t_channel to dbmonopr ;
grant select on mysql_ppp.api_pay_channel to dbmonopr ;
grant select on mysql_ppp.t_transaction_total to dbmonopr ;
grant select on mysql_ppp.t_pay_api_single to dbmonopr ;
grant select on mysql_ppp.t_mer_daily_statistics to dbmonopr ;
grant select on mysql_ppp.t_sys_check_account to dbmonopr ;
grant select on mysql_ppp.t_sys_check_payment_account to dbmonopr ;
grant select on mysql_ppp.t_merchant_support_pay_type to dbmonopr ;

###cheing:
mysql>
mysql> show grants for dbmonopr;
+---------------------------------------------------------------------------------------------------------+
| Grants for dbmonopr@% |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dbmonopr'@'%' IDENTIFIED BY PASSWORD '*17A98E1A692D1AFB64C60635DE1072259101A794' |
| GRANT SELECT ON `mysql_ppp`.`t_transaction_total` TO 'dbmonopr'@'%' |
| GRANT SELECT ON `mysql_ppp`.`t_sys_check_account` TO 'dbmonopr'@'%' |
| GRANT SELECT ON `mysql_ppp`.`t_merchant` TO 'dbmonopr'@'%' |
| GRANT SELECT ON `mysql_ppp`.`t_user_account` TO 'dbmonopr'@'%' |
| GRANT SELECT ON `dbmon`.`mon_conn` TO 'dbmonopr'@'%' |
| GRANT SELECT ON `mysql_ppp`.`t_channel` TO 'dbmonopr'@'%' |
| GRANT SELECT ON `mysql_ppp`.`t_merchant_support_pay_type` TO 'dbmonopr'@'%' |
| GRANT SELECT ON `mysql_ppp`.`t_pay_api_single` TO 'dbmonopr'@'%' |
| GRANT SELECT ON `mysql_ppp`.`api_pay_channel` TO 'dbmonopr'@'%' |
| GRANT SELECT ON `mysql_ppp`.`t_mer_daily_statistics` TO 'dbmonopr'@'%' |
| GRANT SELECT ON `mysql_ppp`.`t_sys_check_payment_account` TO 'dbmonopr'@'%'

注意 ： mysql_ppp这是mysql 的数据库，跟oracle的用法还不一样。

 注意 ： Mysql 的所有表都属于root 用户下的，其他非root 用户权限的用户只有表的读写权限。

############

MySql 常用命令集

　　Mysql常用命令

　　show databases; 显示数据库

　　create database name; 创建数据库

　　use databasename; 选择数据库

　　drop database name 直接删除数据库，不提醒

　　show tables; 显示表

　　describe tablename; 显示具体的表结构

　　select 中加上distinct去除重复字段

　　mysqladmin drop databasename 删除数据库前，有提示。

　　显示当前mysql版本和当前日期

　　select version(),current_date;

　　修改mysql中root的密码：

　　shell>mysql -h localhost -u root -p //登录

　　mysql> update user set password=password("xueok654123") where user='root';

　　mysql> flush privileges //刷新数据库

　　mysql>use dbname; 打开数据库：

　　mysql>show databases; 显示所有数据库

　　mysql>show tables; 显示数据库mysql中所有的表：先use mysql;然后

　　mysql>describe user; 显示表mysql数据库中user表的列信息);

　　grant

　　创建用户firstdb(密码firstdb)和数据库，并赋予权限于firstdb数据库

　　mysql> create database firstdb;

　　mysql> grant all on firstdb.* to firstdb identified by 'firstdb'

　　会自动创建用户firstdb

      验证： 加入登录IP 进行验证，

      mysql -h 10.198.227.29 -ufirstdb  -p

　　mysql默认的是本地主机是localhost,对应的IP地址就是127.0.0.1，所以你用你的IP地址登录会出错，如果你想用你的IP地址登录就要先进行授权用grant命令。

　　mysql>grant all on *.* to root@202.116.39.2 identified by "123456";

　　说明:grant 与on 之间是各种权限，例如:insert,select,update等

　　on 之后是数据库名和表名,第一个*表示所有的数据库，第二个*表示所有的表

　　root可以改成你的用户名，@后可以跟域名或IP地址，identified by 后面的是登录用的密码，可以省略，即缺省密码或者叫空密码。

###########0

 https://jingyan.baidu.com/album/380abd0a3aef431d90192c33.html?picindex=4

查看用于远程访问的mysql用户权限是否正确。

在本地登入mysql服务器，

use mysql;

SELECT user, host from mysql.user; 查看用于远程访问的mysql用户host的权限，%表示允许所有机器访问。若host为127.0.0.1/localhost，那么这个用户就只能本机访问，则需要将host改为%，可以使用update user set host='%' where user='root';

##########1

https://blog.csdn.net/bzfys/article/details/53332530

用户和权限管理：

语法

grant 权限 on 数据库.数据表 to '用户' @ '主机名';

例：给 xiaogang 分配所有的权限

grant all on *.* to 'xiaogang'@'%';

这个时候 xiaogang 就拥有了 所有权限了

权限列表

权限	说明	举例
usage	连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授予）。	mysql>  grant usage on *.* to 'root′@'localhost' identified by '123';
	该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收，也即REVOKE用户并不能删除用户。
file	拥有file权限才可以执行  select ..into outfile和load data infile…操作，但是不要把file, process,  super权限授予管理员以外的账号，这样存在严重的安全隐患。	mysql>  grant file on *.* to root@localhost;
		mysql> load data infile '/home/mysql/pet.txt' into table pet;
super	这个权限允许用户终止任何查询；修改全局变量的SET语句；使用CHANGE  MASTER，PURGE MASTER LOGS。	mysql>  grant super on *.* to root@localhost;
		mysql> purge master logs before 'mysql-bin.000006′;
select	必须有select的权限，才可以使用select  table	mysql>  grant select on pyt.* to 'root′@'localhost';
		mysql> select * from shop;
insert	必须有insert的权限，才可以使用insert  into ….. values….	mysql>  grant insert on pyt.* to 'root′@'localhost';
		mysql> insert into shop(name) values('aa');
update	必须有update的权限，才可以使用update  table	mysql>  update shop set price=3.5 where article=0001 and dealer='A';
delete	必须有delete的权限，才可以使用delete  from ….where….(删除表中的记录)	mysql>  grant delete on pyt.* to 'root′@'localhost';
		mysql> delete from table where id=1;
alter	必须有alter的权限，才可以使用alter  table	mysql>  alter table shop modify dealer char(15);
alter routine	必须具有alter  routine的权限，才可以使用{alter |drop} {procedure|function}	mysql>grant  alter routine on pyt.* to 'root′@' localhost ‘;
		mysql> drop procedure pro_shop;
		Query OK, 0 rows affected (0.00 sec)
create	必须有create的权限，才可以使用create  table	mysql>  grant create on pyt.* to 'root′@'localhost';
drop	必须有drop的权限，才可以删除库、表、索引、视图等	mysql>  drop database db_name;
		mysql> drop table tab_name;
		mysql> drop view vi_name;
		mysql> drop index in_name;
create routine	必须具有create  routine的权限，才可以使用{create |alter|drop} {procedure|function}	mysql>  grant create routine on pyt.* to 'root′@'localhost';
		当授予create routine时，自动授予EXECUTE, ALTER ROUTINE权限给它的创建者：
create temporary tables	(注意这里是tables，不是table)	必须有create  temporary tables的权限，才可以使用create temporary tables.
		mysql> grant create temporary tables on pyt.* to  'root′@'localhost';
		[mysql@mydev ~]$ mysql -h localhost -u root -p pyt
		mysql> create temporary table tt1(id int);
create view	必须有create  view的权限，才可以使用create view	mysql>  grant create view on pyt.* to 'root′@'localhost';
		mysql> create view v_shop as select price from shop;
create user	要使用CREATE  USER，必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。	mysql>  grant create user on *.* to 'root′@'localhost';
		或：mysql> grant insert on *.* to root@localhost;
show database	通过show  database只能看到你拥有的某些权限的数据库，除非你拥有全局SHOW DATABASES权限。	mysql>  show databases;
	对于root@localhost用户来说，没有对mysql数据库的权限，所以以此身份登陆查询时，无法看到mysql数据库：
show view	必须拥有show  view权限，才能执行show create view	mysql>  show create view name;
index	必须拥有index权限，才能执行[create  |drop] index	mysql>  grant index on pyt.* to root@localhost;
		mysql> create index ix_shop on shop(article);
		mysql> drop index ix_shop on shop;
excute	执行存在的Functions,Procedures	mysql>  call pro_shoroot(0001,@a)；
event	event的使用频率较低建议使用root用户进行创建和维护。	mysql>  show global variables like 'event_scheduler';
	要使event起作用，MySQL的常量GLOBAL event_scheduler必须为on或者是1
lock tables	必须拥有lock  tables权限，才可以使用lock tables	mysql>  grant lock tables on pyt.* to root@localhost;
		mysql> lock tables a1 read;
		mysql> unlock tables;
references	有了REFERENCES权限，用户就可以将其它表的一个字段作为某一个表的外键约束。
reload	必须拥有reload权限，才可以执行flush  [tables | logs | privileges]	mysql>  grant reload on pyt.* to root@localhost;
		ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
		mysql> grant reload on *.* to 'root′@'localhost';
		Query OK, 0 rows affected (0.00 sec)
		mysql> flush tables;
replication client	拥有此权限可以查询master  server、slave server状态。	mysql>  grant Replication client on *.* to root@localhost;
		或：mysql> grant super on *.* to root@localhost;
		mysql> show master status;
replication slave	拥有此权限可以查看从服务器，从主服务器读取二进制日志。	mysql>  grant replication slave on *.* to root@localhost;
		mysql> show slave hosts;
		Empty set (0.00 sec)
		mysql>show binlog events;
Shutdown	关闭mysql权限	[mysql@mydev  ~]$ mysqladmin shutdown
grant option	拥有grant  option，就可以将自己拥有的权限授予其他用户（仅限于自己已经拥有的权限）	mysql>  grant Grant option on pyt.* to root@localhost;
		mysql> grant select on pyt.* to p2@localhost;
process	通过这个权限，用户可以执行SHOW  PROCESSLIST和KILL命令。默认情况下，每个用户都可以执行SHOW PROCESSLIST命令，但是只能查询本用户的进程。	mysql>  show processlist;
all privileges	所有权限。with  grant option 可以连带授权	mysql>  grant all privileges on pyt.* to root@localhost with grant option;

·  管理权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟 *.*

·   有人会问truncate权限呢，其实truncate权限就是create+drop，这点需要注意

查看用户授权信息

mysql> show grants for bzfys;

+-------------------------------------------------------------------------------------------------------+

| Grants for bzfys@%                                                                                   |

+-------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO bzfys@'%' IDENTIFIED BY PASSWORD '*A399693A49F7EC7C548D0FC376FA52AD293A552F' |

+-------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

一般情况赋予的权限

用户管理

mysql>use mysql;

一、查看

mysql> select host,user,password from user ;

二、创建

mysql> create user  bzfys   IDENTIFIED by 'xxxxx';   //identified by 会将纯文本密码加密作为散列值存储

三、修改

mysql>rename   user  bzfys to   buzaifengyaosha；//mysql 5之后可以使用，之前需要使用update 更新user表

四、删除

mysql>drop user buzaifengyaosha;   //mysql5之前删除用户时必须先使用revoke 删除用户权限，然后删除用户，mysql5之后drop 命令可以删除用户的同时删除用户的相关权限

五、更改密码（如果找回root密码必须用这种方式）

mysql> set password for bzfys =password('xxxxxx');

 mysql> update  mysql.user  set  password=password('xxxx')  where user=’bzfys’;5.8以后需要修改的列为authentication_string列update  mysql.user  set  authentication_string=password('xxxx')  where user=’bzfys’

六、查看用户权限

mysql> show grants for bzfys;

七、赋予权限

mysql> grant select on bzfys_db.*  to bzfys;

回收权限

mysql> revoke  select on bzfys_db.*  from  bzfys;  //如果权限不存在会报错

 上面的命令也可使用多个权限同时赋予和回收，权限之间使用逗号分隔

mysql> grant select，update，delete  ，insert  on bzfys_db.*  to  bzfys;

如果想立即看到结果使用

flush  privileges ;

命令更新 

设置权限时必须给出一下信息

1，要授予的权限

2，被授予访问权限的数据库或表

3，用户名

grant和revoke可以在几个层次上控制访问权限

1，整个服务器，使用 grant ALL  和revoke  ALL

2，整个数据库，使用on  database.*

3，特点表，使用on  database.table

4，特定的列

5，特定的存储过程

user表中host列的值的意义

%              匹配所有主机

localhost    localhost不会被解析成IP地址，直接通过UNIXsocket连接

127.0.0.1      会通过TCP/IP协议连接，并且只能在本机访问；

::1                 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。

grant select on testdb.* to common_user@’%’

grant insert on testdb.* to common_user@’%’

grant update on testdb.* to common_user@’%’

grant delete on testdb.* to common_user@’%’

或者，用一条 MySQL 命令来替代：

grant select, insert, update, delete on testdb.* to common_user@’%’

9>.grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限。

grant 创建、修改、删除 MySQL 数据表结构权限。

grant create on testdb.* to developer@’192.168.0.%’;

grant alter on testdb.* to developer@’192.168.0.%’;

grant drop on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 外键权限。

grant references on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 临时表权限。

grant create temporary tables on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 索引权限。

grant index on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 视图、查看视图源代码 权限。

grant create view on testdb.* to developer@’192.168.0.%’;

grant show view on testdb.* to developer@’192.168.0.%’;

grant 操作 MySQL 存储过程、函数 权限。

grant create routine on testdb.* to developer@’192.168.0.%’; -- now, can show procedure status

grant alter routine on testdb.* to developer@’192.168.0.%’; -- now, you can drop a procedure

grant execute on testdb.* to developer@’192.168.0.%’;

10>.grant 普通 DBA 管理某个 MySQL 数据库的权限。

grant all privileges on testdb to dba@’localhost’

其中，关键字 “privileges” 可以省略。

11>.grant 高级 DBA 管理 MySQL 中所有数据库的权限。

grant all on *.* to dba@’localhost’

12>.MySQL grant 权限，分别可以作用在多个层次上。

1. grant 作用在整个 MySQL 服务器上：

grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。

grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库

2. grant 作用在单个数据库上：

grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。

3. grant 作用在单个数据表上：

grant select, insert, update, delete on testdb.orders to dba@localhost;

4. grant 作用在表中的列上：

grant select(id, se, rank) on testdb.apache_log to dba@localhost;

5. grant 作用在存储过程、函数上：

grant execute on procedure testdb.pr_add to ’dba’@’localhost’

grant execute on function testdb.fn_add to ’dba’@’localhost’

注意：修改完权限以后 一定要刷新服务，或者重启服务，刷新服务用：FLUSH PRIVILEGES。

grant create routine, alter routine, execute ON `blacklist`.* TO 'blacklist'@'%';

create routine创建存储过程

alter routine, 修改存储过程

execute：执行存储过程

###2

 https://blog.csdn.net/sunhuaqiang1/article/details/52887209

#####3

https://www.cnblogs.com/sunmengbbm/p/5827576.html

mysql（MySQL客户端连接工具）

2016-08-31 21:05 by 踏雪寻熊, 4428 阅读, 0 评论, 收藏, 编辑

在MySQL提供的工具中，DBA使用最频繁的莫过于mysql。这里的mysql不是指MySQL服务，也不是mysql数据库，而是连接数据库的客户端工具。类似于Oracle的sqlplus。

1 2	语法： mysql [options][database]

options是mysql的可用选项，一次可以写一个或者多个，甚至可以不写。database表示连接的数据库，一次只能写一个或者不写，如果不写，在登录数据库后还需要使用use dbname选择数据库。

mysql的选项通常有两种表达方式：

1. -+选项单词的缩写+选项值**；
2. --+选项的完整单词+=+选项的实际值；

1 2 3

例如: mysql -uroot -ppassword mysql --user=root --password=password

1.连接选项

-u,--user=name 指定连接的用户名 
-p,--password=password 指定连接密码 
-h,--host=name 指定服务器IP或域名 
-P,--port=3308 指定连接端口

在默认情况下，如果这些选项都不写，mysql将会使用'用户'@'localhost'和空密码连接本机的3306端口。空用户会在mysql安装完毕后自动生成，这也就是仅使用mysql命令就能连到数据库的原因。

如果客户端和服务器在用一台机器上，通常不需要指定-h选项，否则需要指定mysql服务所在的IP或主机名。如果不指定端口，默认连接到3306端口。示例如下：　

1 2 3 4 5 6 7 8 9 10 11

# mysql -h 10.10.200.202 -uroot -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or g. Your MySQL connection id is 75520 Server version: 5.6.28 Source distribution Copyright (c) 2000, 2013, 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>

2.指定客户端字符集

--default-character-set=charset-name为服务器的字符集选项。该选项可以配置在my.cnf的[mysqld]组中，同样也可以作为客户端字符集选项，也可以配置在[mysql]组中。在使用mysql命令登录数据库时，使用--default-character-set选项可以指定客户端的字符集。例如，如果未使用--default-character-set选项登录数据库时：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

# mysql -h 10.10.200.202 -uroot -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or g. Your MySQL connection id is 75520 Server version: 5.6.28 Source distribution Copyright (c) 2000, 2013, 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> show variables like 'chara%'; +--------------------------+-----------------------------------------+ | Variable_name            | Value                                   | +--------------------------+-----------------------------------------+ | character_set_client     | latin1                                  | | character_set_connection | latin1                                  | | character_set_database   | utf8                                    | | character_set_filesystem | binary                                  | | character_set_results    | latin1                                  | | character_set_server     | utf8                                    | | character_set_system     | utf8                                    | | character_sets_dir       | /usr/local/mysql-5.6.28/share/charsets/ | +--------------------------+-----------------------------------------+

当使用--default-character-set选项登录数据库时：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

# mysql -h 10.10.200.202 -uroot -p --default-character-set=utf8 Enter password: Welcome to the MySQL monitor.  Commands end with ; or g. Your MySQL connection id is 75542 Server version: 5.6.28 Source distribution Copyright (c) 2000, 2013, 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> show variables like 'chara%'; +--------------------------+-----------------------------------------+ | Variable_name            | Value                                   | +--------------------------+-----------------------------------------+ | character_set_client     | utf8                                    | | character_set_connection | utf8                                    | | character_set_database   | utf8                                    | | character_set_filesystem | binary                                  | | character_set_results    | utf8                                    | | character_set_server     | utf8                                    | | character_set_system     | utf8                                    | | character_sets_dir       | /usr/local/mysql-5.6.28/share/charsets/ | +--------------------------+-----------------------------------------+ 8 rows in set (0.01 sec)

3.执行选项

-e,--execute=name 执行sql语句并退出 
此选项可以直接在客户端执行SQL语句，而不用连接到MySQL数据库后再执行，对于一些脚本的执行，使用此法较为便利。可以使用此种方法连续执行多条SQL语句，语句之间用分号(;)分隔例如：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

# mysql -uroot -p mysql -e "select host,user from user" Enter password: +--------------------+--------+ | host               | user   | +--------------------+--------+ | 10.10.200.201      | root   | | 10.10.200.201      | zabbix | | 127.0.0.1          | root   | | ::1                | root   | | localhost          |        | | localhost          | root   | | localhost          | zabbix | | tcxx-ops-mysql-202 |        | | tcxx-ops-mysql-202 | root   | +--------------------+--------+

4.格式化选项

-E,--vertical 将输出按字段顺序垂直显示 
-s,--silent 去掉SQL输出结果中的线条框

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

# mysql -uroot -p mysql -e "select host,user from user" -E Enter password: *************************** 1. row *************************** host: 127.0.0.1 user: root *************************** 2. row *************************** host: ::1 user: root *************************** 3. row *************************** host: localhost user: *************************** 4. row *************************** host: localhost user: root *************************** 5. row *************************** host: test-server user: *************************** 6. row *************************** host: test-server user: root

 

########sample:

 

#################5
mysql添加监控前数据库准备工作
create database dbmon;
use dbmon
create table mon_conn (dbname char(16));
insert into mon_conn values('dbname');
commit;
grant select on dbmon.mon_conn to dbmonopr@'%' identified by 'dbmonopr'

############ 7

[mysql@ppcondb01 ~]$ mysql -h 10.198.216.183 -udbmonopr -pdbmonopr

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 14
Server version: 5.6.25-log Source distribution

Copyright (c) 2000, 2013, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbmon |
| test |
+--------------------+
3 rows in set (0.00 sec)

mysql> use dbmon
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> show tables;
+-----------------+
| Tables_in_dbmon |
+-----------------+
| mon_conn |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from mon_conn;
+--------+
| dbname |
+--------+
| dbname |
+--------+
1 row in set (0.01 sec)

mysql>

 

source /tmp/1.sql

 

 

####8 

 

mysql 命令默认使用localhost ,所以 localhost 默认权限 就是本地登录。

This command invokes mysql without specifying any explicit connection parameters:

mysql

Because there are no parameter options, the default values apply:

• The default host name is localhost. On Unix, this has a special meaning, as described later.

• The default user name is ODBC on Windows or your Unix login name on Unix.

• No password is sent because neither --password nor -p is given.

• For mysql, the first nonoption argument is taken as the name of the default database. Because there is no such argument, mysql selects no default database.

To specify the host name and user name explicitly, as well as a password, supply appropriate options on the command line. To select a default database, add a database-name argument. Examples:

mysql --host=localhost --user=myname --password=password mydb
mysql -h localhost -u myname -ppassword mydb