mysql权限级别：
- 库级别
- 表级别
- 字段级别
- 管理类
- 程序类
管理类
create temporary tables
create user
file
show databases
reload
shutdown
replication slave
replication client
lock tables
process程序类
function
procedure
trigger
create, alter, drop, excute库和表级别
alter
create
create view
drop
index
grant option数据操作
select
insert
delete
update字段级别
select(col1,col2,...)
update(col1,col2,...)
insert(col1,col2,...)所有
all privilege, all
这些权限都是保存在数据的元数据表'mysql'库中
- 授权表
db,host,usercolumns_priv, tables_priv, procs_priv, proxies_priv
用户帐号:
'username'@'host'
创建用户:create user 'user'@'host' [identity by 'password']
查看用户权限: show grants for 'username'@'host'
用户重命名:rename user old to new
删除用户: drop user 'username'@'host'
修改密码:
1. set password for
2. update mysql.user set password=PASSWORD('password')
3. mysqladmin password
忘记管理员密码:
- 启动mysqld时,为其使用
--skip-grant-tables --skip-networking - 使用update命令修改管理员密码
- 关闭mysqld进程,移除上述两个权限,重启mysqld
授权:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]
GRANT PROXY ON user_specification
TO user_specification [, user_specification] ...
[WITH GRANT OPTION]
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
user_specification:
user
[
IDENTIFIED BY [PASSWORD] 'password'
| IDENTIFIED WITH auth_plugin [AS 'auth_string']
]
ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
取消授权
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
REVOKE PROXY ON user
FROM user [, user] ...
练习
授权test用户通过任意主机连接到当前的mysqld,但每秒钟最大查询次数不超过5次:此账户同时连接次数不得超过3次.
查询缓存
如何判断是否命中:
-
通过查询语句的哈希值来判断,哈希值考虑的因素包括
- 查询本身\要查询的数据库\客户端使用的协议版本
- 查询语句任何字符上的不同,都会导致缓存不能命中
-
那些查询可能不会被缓存
- 查询钟包含UDF\存储函数\用户自定义变量\临时表\mysql库钟的是同表\包含列级权限的表\有着不确定的函数(now())
-
查询缓存相关的服务器变量:
- query_cache_min_res_unit: MySQL能够缓存的最大查询结果;如果某查询大于此值,则不会被缓存;
- query_cache_limit: 查询缓存钟分配内存的最小单位;
计算公式:(query_cache_size-Qcache_free_memory)/ Qcache_queries_in_cache - query_cache_size: 查询缓存的总体可用空间,必须为1024的整数倍
- query_cache_type:
- query_cache_wlock_invalidate: 当其它会话锁定此次查询用到的资源时,是否不能再从缓存中返回数据(默认为false)
与缓存相关的状态变量
SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16759656 |
| Qcache_hits | 16 |
| Qcache_inserts | 71 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 57 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
缓存命中率计算公式:Qcache_hits/Com_select
衡量缓存是否足够有效的另一种思路：Qcache_hits/Qcache_inserts,如果此比值大于3:1, 说明缓存也是有效的；如果高于10:1，相当理想；
缓存优化的思路：
1、批量写入比单次写入对缓存的影响要小得多；
2、缓存空间不宜过大，大量缓存的同时失效会导致MySQL假死；
3、必要时，使用SQL_CACHE或SQL_NO_CACHE手动控制缓存；
4、对写密集型的应用场景，禁用缓存反而能提高性能；
碎片整理：FLUSH QUERY_CACHE
清空缓存：RESET QUERY_CACHE
graph LR
Start[开始] --> A{是否命中}
A -->|是| End[结束]
A --> |否| B{大部分查询都不是可缓存的?}
B --> |是| C{query_cache_limit是够大么?}
C --> |是| End
C --> |否| E[增加query_cache_limit]
E --> Start
B --> |否| F{发生了很多验证工作?}
F --> |是| G{缓存被碎片化了么?}
G --> |是| G1[减少query_cache_min_res_unit或者使用FLUSH QUERY CACHE命令减少碎片]
G --> |否| G2{有很多因为内存过低而发生的修整工作?}
G2 --> |是| GG1[增加queru_cache_size]
GG1 --> Start
G2 --> |否| GG2{有很多新语句么?}
GG2 --> |是| End
GG2 --> |否| GGG[有其他的东西配置错了]
F --> |否| F1{缓存已经启动了吗?}
F1 --> |否| FF1[启动缓存]
FF1 --> Start
FF1 --> |是| End
