Mysql事务、日志、用户管理

2017年2月22日00:05:35 发表评论 2,434 ℃

mysql事务管理

RDBMS : ACID (原子性、一致性、隔离性、持久性)

automicity:原子性,事务所引起的数据操作,要么都完成,要么都不执行

consistency : 一致性,

isolation : 隔离性

事务调度:事务之间影响最小

MVCC:多版本并发控制

durability : 一旦事务成功完成,系统必须保证任何故障都不会影响事务表现出不一致性

1、事务提交之前就已经写出数据至持久性存储

2、结合事务日志完成

事务日志:顺序IO

数据文件:随机IO

事务的状态:

活动的:active

部分提交的:最后一条语句执行后

失败的:

终止的:
提交的:

事务:并发执行

1、提高吞吐量和资源利用率

2、减少等待时间

事务调度:

可恢复调度:
无级联调度:

并发控制依赖的技术手段:

饿死

死锁

时间戳

多版本快照隔离

多事务同时执行:彼此之间互不影响的方式进行并行

事务之间交互

通过数据集

事务日志:

重做日志

redo log

撤销日志

undo log

隔离性

隔离级别

read uncommitted :读未提交

read committed : 读提交

repatable read:可重读

seriablizable:可串行

start tansaction:启动事务

commit:提交

rollback : 回滚

如果没有明确启动事务:

autocommit :能实现自动提交,每一个操作都直接提交

建议:明确使用事务,并且关闭自动提交:

保存点:savepoint sid

回滚至保存点: rollback to  sid

mysql用户和权限管理

user: Contains user accounts, global privileges, and other non-privilege columns.
user: 用户帐号、全局权限
db: Contains database-level privileges.
db: 库级别权限
host: Obsolete.
host: 废弃
tables_priv: Contains table-level privileges.
表级别权限
columns_priv: Contains column-level privileges.
列级别权限
procs_priv: Contains stored procedure and function privileges.
存储过程和存储函数相关的权限
proxies_priv: Contains proxy-user privileges.
代理用户权限

用户账号:

用户名@主机

用户名:16字符以内

主机:

主机名:www.test.com,mysql

IP:172.16.10.1

网络地址:172.16.0.0/255.255.0.0

通配符:%,_

172.16.%.%

%.test.com

--skip-name-resolve 略过名称解析

权限级别

全局级别

表:delete、alter、trigger、

列:select、insert、update

存储过程和存储函数

临时表:内存表

heap:16MB

触发器,主动数据库

创建用户:

create user 'usernam'@'hostname' identified by 'password'

insert into mysql.user

grant

grant all privileges on [object_type] db.* to username@'%'

object_type

table

function

procedure

grant all privileges on function db.abc to username@'localhost'

resource_option: 

MAX_QUERIES_PER_HOUR count 限制资源使用权限

MAX_UPDATES_PER_HOUR count 限制每小时update使用次数

MAX_CONNECTIONS_PER_HOUR count 限制新的链接请求

MAX_USER_CONNECTIONS count 某一个用户最多允许链接多少次

删除用户:drop user 'uername'@'host'

重命名用户:rename user old_name to new_name

取消授权:revoke all privileges ,grant option from user@host

重置mysql root密码

启动mysqld_safe时传递两个参数

--skip-grant-tables

--skip-networking

通过更新授权表方式直接修改其密码,后移出此两个选项重启服务

mysql日志管理

错误日志:

log_error

log_warnigs

服务器启动和关闭过程总的信息

服务器运行过程中的错误信息

事件调度器运行一个事件时产生的信息

在从服务器上启动从服务器进程时产生的信息

一般查询日志

general_log

general_log_file

log_output=[file|table|none]

log

慢查询日志

long_query_time

log_slow_queries={yes|no}

slow_query_log

slow_query_log_file

二进制日志:DML,DDL 任何引起或可能引起数据库变化的操作

复制、即时点恢复

mysqlbinlog命令:查看mysql二进制日志

--start-datetime 'yyyy-mm-dd hh:mm:ss'

--stop-datetime

--start-position

--stop-position

二进制日志的格式:

基于语句:statement

基于行:row

混合模式:mixed

二进制日志事件:

产生的时间

相对位置

二进制日志文件:

索引文件

二进制日志文件

查看当前正在使用的二进制日志文件

show master status;

查看所有的二进制日志文件

show binary logs;

删除日志文件

purge binary logs to '二进制日志文件'

查看事件

show binlog events in '二进制日志文件名' [from pos]

flush logs:让日志滚动

中继日志

从主服务器的二进制日志文件中复制而来的事件,并保存为日志

事务日志

事务性储存引擎用于保证原子性、一致性、隔离性和持久性

innodb_flush_log_at_trx_commit:

0:每秒同步,并执行磁盘flush操作

1:没事务同步,并执行磁盘flush操作

2:每事务同步,但不执行磁盘flush操作

数据库引擎

MyISAM

不支持事务

表锁

不支持外键

B树索引、fulltext索引、空间索引

支持表压缩

InnoDB

支持事务

行级锁

B数索引、聚簇索引、自适应hash索引

表空间、raw磁盘设备

MRG_MYISAM:

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时,每条语句的执行都会引起二进制日志同步,否则,每个事务的提交会引起二进制日志同步。

【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: