• 欢迎访问本站,本站启用全新域名www.wangi.cc,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 本站部份内容收集自网络,用于记录生活、学习研究、网络体验、电脑技术、软件教程、代码资料、网站技术
  • 本站虚拟主机由阿里云提供www.aliyun.com
  • 关注建站、网络技术,如果您喜欢本站,就按下CTRL + D 收藏我们

MySQL用户、权限、缓存、索引、日志管理

技术分享 Lee 3年前 (2016-07-06) 987次浏览 已收录 0个评论 扫描二维码

一、mysql 权限管理

1、权限类别

库级别

表级别

字段级别

管理类

程序类

管理类:

CREATE TEMPORARY TABLES

CREATE USER

FILE                            #导出导入数据

SUPER                        #高级别管理操作比如:授权,复制

SHOW DATABASES

RELOAD                     #重新装载授权表

SHUTDOWN              #关闭数据库

REPLICATION SLAVE  #

REPLICATION CLIENT #

LOCK TABLES

PROCESS                  #线程

程序类:

FUNCTION

PROCEDURE

TRIGGER            #触发器

CREATE, ALTER, DROP, EXCUTE(执行)

库和表级别:TABLE or DATABASE

ALTER

CREATE

CREATE VIEW

DROP

INDEX

SHOW VIEW

GRANT OPTION:能够把自己获得的权限赠经其他用户一个副本;

数据操作:

SELECT

INSERT

DELETE

UPDATE

字段级别:

SELECT(col1,col2,…)

UPDATE(col1,col2,…)

INSERT(col1,col2,…)

所有权限:ALL [PRIVILEGES]

元数据数据库:mysql

授权表:

db, host, user

columns_priv, tables_priv, procs_priv, proxies_priv

二、mysql 用户管理

用户账号:‘USERNAME’@’HOST’

@’HOST’:主机名,IP 地址或 Network;

通配符:%, _:,172.16.%.%

创建用户:CREATE USER

CREATE USER ‘USERNAME’@’HOST’ [IDENTIFIED BY ‘password’];

查看用户获得的授权:SHOW GRANTS FOR

SHOW GRANTS FOR ‘USERNAME’@’HOST’

用户重命名:RENAME USER

RENAME USER old_user_name TO new_user_name

删除用户:DROP USER ‘USERNAME’@’HOST’

三、修改密码

修改密码:

(1) SET PASSWORD FOR

 SET PASSWORD FOR ‘root’@’%’=PASSWORD(‘123456’);

例如:set password for ‘xj’@’192.168.100.0’=password(‘123’);

(2) UPDATE mysql.user SET password=PASSWORD(‘your_password’) WHERE clause;

这种方式必须是先用 root 帐户登入 mysql,然后执行:

 UPDATE user SET password=PASSWORD(‘123456′) WHERE user=’root’ and host=’ ‘;

 FLUSH PRIVILEGES;

(3) mysqladmin password

mysqladmin [OPTIONS] command command….

-u, -h, -p

格式如下(其中,USER 为用户名,PASSWORD 为新密码):

 mysqladmin -u USER -p password PASSWORD;

 该命令之后会提示输入原密码,输入正确后即可修改。

 例如,设置 root 用户的密码为 123456,则

mysqladmin -u root -p password 123456

忘记管理员密码的解决办法:

(1) 启动 mysqld 进程时,为其使用:–skip-grant-tables(跳过授权表)  –skip-networking(禁止远程登录功能)

#在服务脚本 start 中添加

或在配置文件/etc/my.cnf 中添加配置

[mysqld]

skip-networking 

socket=/tmp/mysql-recovery.sock

(2) 使用 UPDATE 命令修改管理员密码

(3) 关闭 mysqld 进程,移除上述两个选项,重启 mysqld;

四、授权

授权:GRANT

GRANT priv_type(权限类型)[,…] ON [{table|function|procedure}] db.{table|routine} TO ‘USERNAME’@’HOST’ [IDENTIFIED BY ‘password’]

[REQUIRE SSL] [WITH with_option]

with_option:

GRANT OPTION

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count          #每小时一个帐号连接数

取消授权:REVOKE

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] … ON [object_type] priv_level FROM user [, user] …

SHOW GRANTS FOR ‘username’@’host’;查看用户权限

REVOKE  pri1,pri2,… ON db_name.tb_name FROM ‘username’@’host’;

五、缓存

如何判断是否命中:

通过查询语句的哈希值判断:哈希值考虑的因素包括:

查询本身、要查询的数据库、客户端使用协议版本,…

查询语句任何字符上的不同,都会导致缓存不能命中;

哪此查询可能不会被缓存?

查询中包含 UDF、存储函数、用户自定义变量、临时表、mysql 库中系统表、或者包含列级权限的表、有着不确定值的函数(Now());

查询缓存相关的服务器变量:

query_cache_min_res_unit: 查询缓存中内存块的最小分配单位;

较小值会减少浪费,但会导致更频繁的内存分配操作;

较大值会带来浪费,会导致碎片过多;

query_cache_limit:能够缓存的最大查询结果;

对于有着较大结果的查询语句,建议在 SELECT 中使用 SQL_NO_CACHE

query_cache_size:查询缓存总共可用的内存空间;单位是字节,必须是 1024 的整数倍;

query_cache_type: ON, OFF, DEMAND(明显写明 cache 才会缓存)

query_cache_wlock_invalidate:如果某表被其它的连接锁定,是否仍然可以从查询缓存中返回结果;默认值为 OFF,表示可以在表被其它连接锁定的场景中继续从缓存返回数据;ON 则表示不允许;

查询相关的状态变量:

SHOW GLOBAL STATUS LIKE ‘Qcache%’;

+————————-+———-+

| Variable_name           | Value    |

+————————-+———-+

| Qcache_free_blocks      | 1        |

| Qcache_free_memory      | 16759688 |

| Qcache_hits             | 0        |

| Qcache_inserts          | 0        |

| Qcache_lowmem_prunes    | 0        |

| Qcache_not_cached       | 0        |

| Qcache_queries_in_cache | 0        |

| Qcache_total_blocks     | 1        |

+————————-+———-+

缓存命中率的评估:Qcache_hits/(Qcache_hits+Com_select)

六、索引

MySQL 中的索引:

基本法则:索引应该构建在被用作查询条件的字段上;

索引类型:

B+ Tree 索引:顺序存储,每一个叶子节点到根结点的距离是相同的;属于左前缀索引,适合查询范围类的数据;

可以使用 B-Tree 索引的查询类型:全键值、键值范围或键前缀查找;

全值匹配:精确某个值, “Jinjiao King”;

匹配最左前缀:只精确匹配起头部分,”Jin%”

匹配范围值:

精确匹配某一列并范围匹配另一列:

只访问索引的查询

不适合使用 B-Tree 索引的场景:

如果不从最左列开始,索引无效; (Age,Name)

不能跳过索引中的列;(StuID,Name,Age)

如果查询中某个列是为范围查询,那么其右侧的列都无法再使用索引优化查询;(StuID,Name)

Hash 索引基于哈希表实现,特别适用于精确匹配索引中的所有列;

注意:只有 Memory 存储引擎支持显式 hash 索引;

适用场景:

只支持等值比较查询,包括=, IN(), <=>;

不适合使用 hash 索引的场景:

存储的非为值的顺序,因此,不适用于顺序查询;

不支持模糊匹配;

空间索引(R-Tree):

MyISAM 支持空间索引;

全文索引(FULLTEXT):

在文本中查找关键词;

索引优点:

索引可以降低服务需要扫描的数据量,减少了 IO 次数;

索引可以帮助服务器避免排序和使用临时表;

索引可以帮助将随机 I/O 转为顺序 I/O;

高性能索引策略:

独立使用列,尽量避免其参与运算;

左前缀索引:索引构建于字段的左侧的多少个字符,要通过索引选择性来评估

索引选择性:不重复的索引值和数据表的记录总数的比值;

多列索引:

AND 操作时更适合使用多列索引;

选择合适的索引列次序:将选择性最高放左侧;

冗余和重复索引:不好的索引使用策略

通过 EXPLAIN 来分析索引的有效性:

EXPLAIN SELECT clause

获取查询执行计划信息,用来查看查询优化器如何执行查询;

输出:

id: 当前查询语句中,每个 SELECT 语句的编号;

复杂类型的查询有三种:

简单子查询;

用于 FROM 中的子查询;

联合查询:UNION;

注意:UNION 查询的分析结果会出现一外额外匿名临时表;

select_type:

简单查询为 SIMPLE

复杂查询:

SUBQUERY: 简单子查询;

DERIVED: 用于 FROM 中的子查询;

UNION:UNION 语句的第一个之后的 SELECT 语句;

UNION RESULT: 匿名临时表;

table:SELECT 语句关联到的表;

type:关联类型,或访问类型,即 MySQL 决定的如何去查询表中的行的方式;

ALL: 全表扫描;

index:根据索引的次序进行全表扫描;如果在 Extra 列出现“Using index”表示了使用覆盖索引,而非全表扫描;

range:有范围限制的根据索引实现范围扫描;扫描位置始于索引中的某一点,结束于另一点;

ref: 根据索引返回表中匹配某单个值的所有行;

eq_ref:仅返回一个行,但与需要额外与某个参考值做比较;

const, system: 直接返回单个行;

possible_keys:查询可能会用到的索引;

key: 查询中使用了的索引;

key_len: 在索引使用的字节数;

ref: 在利用 key 字段所表示的索引完成查询时所有的列或某常量值;

rows:MySQL 估计为找所有的目标行而需要读取的行数;

Extra:额外信息

Using index:MySQL 将会使用覆盖索引,以避免访问表;

Using where:MySQL 服务器将在存储引擎检索后,再进行一次过滤;

Using temporary:MySQL 对结果排序时会使用临时表;

Using filesort:对结果使用一个外部索引排序;

七、日志

1、MySQL 日志类别

查询日志:不建议开启。

慢查询日志:查询执行时长超过指定时长的查询操作所记录日志

错误日志:

二进制日志:二进制格式,重做日志,引起数据库改变的操作都会记录下来

中继日志:从主服务器中复制过来的二进制日志临时存放文件,然后慢慢执行

事务日志:ACID, 将随机 I/O 转换为顺序 I/O

2、各种日志各类别详解

 1)查询日志

log_output = {TABLE|FILE|NONE}  #查询日志信息的输出位置

log_output = TABLE,FILE  #可以同时记录在表中和文件中

gerenal_log    #保存在文件中,文件名为 gerenal_log

general_log = {ON|OFF}:   是否启用查询日志,默认不启用

general_log_file = http://www.log:当 log_output 有 FILE 类型时,日志信息的记录位置;

 2)慢查询日志

SELECT @@GLOBAL.long_query_time;

+————————–+

| @@GLOBAL.long_query_time |

+————————–+

|                10.000000 |                      #查询时间超过 10s 就是慢查询

+————————–+

slow_query_log = {ON|OFF}:是否启用慢查询日志

slow_query_log_file = /path/to/somefile: 日志文件路径

log_slow_filter(过滤器) =   admin(管理),filesort(文件排序),filesort_on_disk,full_join(完全连接),full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

log_slow_rate_limit =

log_slow_verbosity =         #是否以详细格式记录日志

 3)错误日志

mysqld 启动和关闭过程中输出的信息;

mysqld 运行中产生的错误信息;

event scheduler(事件调度器,类似计划任务)运行一个 event 时产生的日志信息;

在主从复制架构中的从服务器上启动从服务器线程时产生的日志信息;

log_error = /path/to/somefile

log_warnings = {ON|OFF}:是否记录警告信息于错误日志中;

 4)二进制日志

SHOW {BINARY | MASTER} LOGS:查看主服务器端处于由 mysqld 维护状态中的二进制日志文件;

SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]:显示指定的二进制日志文件中的相关事件

日志记录格式:

基于“语句”记录;statement

基于“行”记录;row

混合” :mixed

二进制日志文件的构成:

日志文件:文件名前缀.文件名后缀

索引文件:文件名前缀(在配置文件中指定用 log-bin 指定).index

服务器变量:

log_bin = /path/to/somefile

binlog_format = MIXED

sql_log_bin = ON

max_binlog_size = 1073741824  #1G,二进制日志文件的单文件上限;

max_binlog_cache_size = 18446744073709547520   #缓存空间最大大小

max_binlog_stmt_cache_size = 18446744073709547520 #二进制语句缓存大小,决定缓存空间的大小

sync_binlog = 0:设定多久同步一次二进制日志文件;0 表示不同步;任何正值都表示记录多少个语句后同步一次;

二进制日志的格式:

# at 19364

#140829 15:50:07 server id 1  end_log_pos 19486 Query  thread_id=13  exec_time=0error_code=0

SET TIMESTAMP=1409298607/*!*/;

GRANT SELECT ON tdb.* TO tuser@localhost

/*!*/;

# at 19486

事件发生的日期和时间;(140829 15:50:07)

事件发生在服务器的标识(server id)

事件的结束位置:(end_log_pos 19486)

事件的类型:(Query)

事件发生时所在的服务器执行此事件的线程的 ID:(thread_id=13)

语句的时间戳与将其写入二进制文件中的时间差:(exec_time=0)

错误代码:(error_code=0)

事件内容:(SET TIMESTAMP=1409298607/*!*/;

GRANT SELECT ON tdb.* TO tuser@localhost)

GTID 事件专属:

事件所属的全局事务的 GTID:(GTID 0-1-2)

二进制日志的查看命令:

mysqlbinlog

-j, –start-position=#:从指定的事件位置查看

–stop-position=#:只显示到指定的事件位置

–start-datetime=’时间’

–stop-datetime=’时间’

时间格式:YYYY-MM-DD hh:mm:ss

-u

-h

-p

基于位置点的增量备份

指定开始位置和结束位置

mysqlbinlog mysql-bin.000020 –start-position=510 –stop-position -r pos.sql   #-r 相当于重定向,按照位置截取

mysqlbinlog mysql-bin.000020 –start-datetime=’2015-12-04 17:15:12′ –stop-datetime=’2015-12-04 17:18:12′ -r time.sql   #按照时间截取

 5)中继日志:

6)事务日志(innodb 存储引擎)

练习:启用慢查询日志

补充材料:日志相关的服务器参数详解:

expire_logs_days={0..99}

设定二进制日志的过期天数,超出此天数的二进制日志文件将被自动删除。默认为 0,表示不启用过期自动删除功能。如果启用此功能,自动删除工作通常发生在 MySQL 启动时或 FLUSH 日志时。作用范围为全局,可用于配置文件,属动态变量。

general_log={ON|OFF}

设定是否启用查询日志,默认值为取决于在启动 mysqld 时是否使用了–general_log 选项。如若启用此项,其输出位置则由–log_output 选项进行定义,如果 log_output 的值设定为 NONE,即使用启用查询日志,其也不会记录任何日志信息。作用范围为全局,可用于配置文件,属动态变量。

 

general_log_file=FILE_NAME

查询日志的日志文件名称,默认为“hostname.log”。作用范围为全局,可用于配置文件,属动态变量。

binlog-format={ROW|STATEMENT|MIXED}

指定二进制日志的类型,默认为 STATEMENT。如果设定了二进制日志的格式,却没有启用二进制日志,则 MySQL 启动时会产生警告日志信息并记录于错误日志中。作用范围为全局或会话,可用于配置文件,且属于动态变量。

log={YES|NO}

是否启用记录所有语句的日志信息于一般查询日志(general query log)中,默认通常为 OFF。MySQL 5.6 已经弃用此选项。

 

log-bin={YES|NO}

是否启用二进制日志,如果为 mysqld 设定了–log-bin 选项,则其值为 ON,否则则为 OFF。其仅用于显示是否启用了二进制日志,并不反应 log-bin 的设定值。作用范围为全局级别,属非动态变量。

 

log_bin_trust_function_creators={TRUE|FALSE}

此参数仅在启用二进制日志时有效,用于控制创建存储函数时如果会导致不安全的事件记录二进制日志条件下是否禁止创建存储函数。默认值为 0,表示除非用户除了 CREATE ROUTING 或 ALTER ROUTINE 权限外还有 SUPER 权限,否则将禁止创建或修改存储函数,同时,还要求在创建函数时必需为之使用 DETERMINISTIC 属性,再不然就是附带 READS SQL DATA 或 NO SQL 属性。设置其值为 1 时则不启用这些限制。作用范围为全局级别,可用于配置文件,属动态变量。

 

log_error=/PATH/TO/ERROR_LOG_FILENAME

定义错误日志文件。作用范围为全局或会话级别,可用于配置文件,属非动态变量。

 

log_output={TABLE|FILE|NONE}

定义一般查询日志和慢查询日志的保存方式,可以是 TABLE、FILE、NONE,也可以是 TABLE 及 FILE 的组合(用逗号隔开),默认为 TABLE。如果组合中出现了 NONE,那么其它设定都将失效,同时,无论是否启用日志功能,也不会记录任何相关的日志信息。作用范围为全局级别,可用于配置文件,属动态变量。

 

log_query_not_using_indexes={ON|OFF}

设定是否将没有使用索引的查询操作记录到慢查询日志。作用范围为全局级别,可用于配置文件,属动态变量。

 

log_slave_updates

用于设定复制场景中的从服务器是否将从主服务器收到的更新操作记录进本机的二进制日志中。本参数设定的生效需要在从服务器上启用二进制日志功能。

 

log_slow_queries={YES|NO}

是否记录慢查询日志。慢查询是指查询的执行时间超出 long_query_time 参数所设定时长的事件。MySQL 5.6 将此参数修改为了 slow_query_log。作用范围为全局级别,可用于配置文件,属动态变量。

 

log_warnings=#

设定是否将警告信息记录进错误日志。默认设定为 1,表示启用;可以将其设置为 0 以禁用;而其值为大于 1 的数值时表示将新发起连接时产生的“失败的连接”和“拒绝访问”类的错误信息也记录进错误日志。

long_query_time=#

设定区别慢查询与一般查询的语句执行时间长度。这里的语句执行时长为实际的执行时间,而非在 CPU 上的执行时长,因此,负载较重的服务器上更容易产生慢查询。其最小值为 0,默认值为 10,单位是秒钟。它也支持毫秒级的解析度。作用范围为全局或会话级别,可用于配置文件,属动态变量。

max_binlog_cache_size{4096 .. 18446744073709547520}

二进定日志缓存空间大小,5.5.9 及以后的版本仅应用于事务缓存,其上限由 max_binlog_stmt_cache_size 决定。作用范围为全局级别,可用于配置文件,属动态变量。

max_binlog_size={4096 .. 1073741824}

设定二进制日志文件上限,单位为字节,最小值为 4K,最大值为 1G,默认为 1G。某事务所产生的日志信息只能写入一个二进制日志文件,因此,实际上的二进制日志文件可能大于这个指定的上限。作用范围为全局级别,可用于配置文件,属动态变量。

max_relay_log_size={4096..1073741824}

设定从服务器上中继日志的体积上限,到达此限度时其会自动进行中继日志滚动。此参数值为 0 时,mysqld 将使用 max_binlog_size 参数同时为二进制日志和中继日志设定日志文件体积上限。作用范围为全局级别,可用于配置文件,属动态变量。

innodb_log_buffer_size={262144 .. 4294967295}

设定 InnoDB 用于辅助完成日志文件写操作的日志缓冲区大小,单位是字节,默认为 8MB。较大的事务可以借助于更大的日志缓冲区来避免在事务完成之前将日志缓冲区的数据写入日志文件,以减少 I/O 操作进而提升系统性能。因此,在有着较大事务的应用场景中,建议为此变量设定一个更大的值。作用范围为全局级别,可用于选项文件,属非动态变量。

 

innodb_log_file_size={108576 .. 4294967295}

设定日志组中每个日志文件的大小,单位是字节,默认值是 5MB。较为明智的取值范围是从 1MB 到缓存池体积的 1/n,其中 n 表示日志组中日志文件的个数。日志文件越大,在缓存池中需要执行的检查点刷写操作就越少,这意味着所需的 I/O 操作也就越少,然而这也会导致较慢的故障恢复速度。作用范围为全局级别,可用于选项文件,属非动态变量。

 

innodb_log_files_in_group={2 .. 100}

设定日志组中日志文件的个数。InnoDB 以循环的方式使用这些日志文件。默认值为 2。作用范围为全局级别,可用于选项文件,属非动态变量。

 

innodb_log_group_home_dir=/PATH/TO/DIR

设定 InnoDB 重做日志文件的存储目录。在缺省使用 InnoDB 日志相关的所有变量时,其默认会在数据目录中创建两个大小为 5MB 的名为 ib_logfile0 和 ib_logfile1 的日志文件。作用范围为全局级别,可用于选项文件,属非动态变量。

relay_log=file_name

设定中继日志的文件名称,默认为 host_name-relay-bin。也可以使用绝对路径,以指定非数据目录来存储中继日志。作用范围为全局级别,可用于选项文件,属非动态变量。

relay_log_index=file_name

设定中继日志的索引文件名,默认为为数据目录中的 host_name-relay-bin.index。作用范围为全局级别,可用于选项文件,属非动态变量。

relay-log-info-file=file_name

设定中继服务用于记录中继信息的文件,默认为数据目录中的 relay-log.info。作用范围为全局级别,可用于选项文件,属非动态变量。

relay_log_purge={ON|OFF}

设定对不再需要的中继日志是否自动进行清理。默认值为 ON。作用范围为全局级别,可用于选项文件,属动态变量。

relay_log_space_limit=#

设定用于存储所有中继日志文件的可用空间大小。默认为 0,表示不限定。最大值取决于系统平台位数。作用范围为全局级别,可用于选项文件,属非动态变量。

slow_query_log={ON|OFF}

设定是否启用慢查询日志。0 或 OFF 表示禁用,1 或 ON 表示启用。日志信息的输出位置取决于 log_output 变量的定义,如果其值为 NONE,则即便 slow_query_log 为 ON,也不会记录任何慢查询信息。作用范围为全局级别,可用于选项文件,属动态变量。

slow_query_log_file=/PATH/TO/SOMEFILE

设定慢查询日志文件的名称。默认为 hostname-slow.log,但可以通过–slow_query_log_file 选项修改。作用范围为全局级别,可用于选项文件,属动态变量。

sql_log_bin={ON|OFF}

用于控制二进制日志信息是否记录进日志文件。默认为 ON,表示启用记录功能。用户可以在会话级别修改此变量的值,但其必须具有 SUPER 权限。作用范围为全局和会话级别,属动态变量。

sql_log_off={ON|OFF}

用于控制是否禁止将一般查询日志类信息记录进查询日志文件。默认为 OFF,表示不禁止记录功能。用户可以在会话级别修改此变量的值,但其必须具有 SUPER 权限。作用范围为全局和会话级别,属动态变量。

sync_binlog=#

设定多久同步一次二进制日志至磁盘文件中,0 表示不同步,任何正数值都表示对二进制每多少次写操作之后同步一次。当 autocommit 的值为 1 时,每条语句的执行都会引起二进制日志同步,否则,每个事务的提交会引起二进制日志同步。

linux 系统启动 mysql 日志

以下配置放在 [mysqld] 下面

#查询日志
log=/var/log/mysql_query.log

#慢查询日志
long_query_time = n  慢查询的执行用时上限(默认设置是 10s)。
log-slow-queries=/var/log/mysql_slowquery.log

#更新日志(二进制日志)
server-id = 1
log-bin = binlog
log-bin-index = binlog.index


打开手机支付宝首页搜“545191695”领红包,最高 99 元
http://www.wangi.cc 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明原文链接;MySQL 用户、权限、缓存、索引、日志管理

喜欢 (0)
[lee_cha@qq.com]
分享 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址