MySQL---事务、日志（修改版1.0）

MySQL事务Transaction

注意：只有事务型存储引擎支持此类操作

事务的特性：ACID，各含义如下：

A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚

C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态

I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发

D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

MySQL事务的开启与结束，在mysql数据库中执行如下操作：

start  transaction;

………（执行操作内容）

commit（表示提交并结束）或者 rollback（表示取消之前操作并结束）

默认情况下，事务是自动提交的，为了保证在对数据进行操作后有回环余地，建议设置为不自动提交，可在mysql数据库中设置如下变量：

set autocommit = 0 （若是需要长久有效，可以将“autocommit = 0”写入配置文件/etc/my.cnf中）

事务也支持保存点，可以在每个操作执行后，对其进行标签标记，这样便于后续后悔操作时，不需要全部取消，可取消至某个保存点：

savepoint TAG_NAME 设置标签

rollback to TAG_NAME 表示回到指定标签保存点的操作后

release savepoint TAG_NAME 表示取消某个标签

---

MySQL事务有不同的隔离级别，分为以下四种，并且逐级提高严格性

在已经有开启数据库使用的情况下，并对某表进行修改操作时，另开端口进入到数据库中查询该表时：

第一种：READ-UNCOMMITTED

   可读取到修改信息后的数据，包括未提交的，产生脏读

第二种：READ-COMMITTED

   会读取提交后的数据，若多次提交，则会读取到不同的数据，产生不可重复读

第三种：REPEATABLE-READ（默认使用）

   会一直读取修改前的旧数据，产生幻读（重复读）

第四种：SERIALIZABILE

   串行化形式，已开端口未提交的读事务，会阻碍其他端口的写操作；未提交的写事务，会阻碍其他端口的读事务

（注意：前三种都是无锁状态，第四种是加上了写锁）

当出现上述第四种事务隔离级别时，根据实时情况，利用show processlist查看占用事务的用户信息以及ID号，然后利用“kill ID”强制结束其事务进程

在mysql数据库中可通过设定变量值来修改事务类型，设置如下：

set tx_isolation='TRANSACTION_LEVEL'（进入数据库单次有用）

若需要永久生效，则在/etc/my,cnf中[ mysqld ]块中加入如下：

transaction-isolation=TRANSACTION_LEVEL

---

MySQL日志

1.事务日志

只适用于事务型存储引擎（本文介绍的InnoDB存储引擎），记录整个数据库的修改操作过程，包括已提交和未提交的事务

默认的存放路径是 /var/lib/mysql/ib_logfile*

以下几个事务日志相关的变量选项可根据生产情况进行修改：

Innodb_log_file_size=VALUE 事务日志的文件存储大小

Innodb_log_file_in_group=VALUE 事务日志的成员个数

Innodb_log_group_home_dir=VALUE 事务日志的文件路径

mysql数据库中变量 profiling 开启后

show profiles 可以查看统计执行的什么语句以及消耗的时间

show profile for query ID 可具体分析每个语句各阶段消耗时间

---

2.二进制日志：（二进制日志可作为备份数据的重要步骤）

记录提交后的改变数据的SQL语句，以二进制方式存储，并且开启此项功能后，日志一直累积叠加保存操作记录

启用二进制日志则需要启用两项内容：

sql_log_bin变量设为 on 状态（默认为on）

log_bin选项加入到配置文件/etc/my,cnf中[ mysqld ]块中

二进制记录的格式分为以下三种：

statement  基于语句方式记录，同一类型语句只记录一次（默认使用）

row             基于表内每行都记录（可用于备份使用）

mixed         系统自行判断

可在mysql数据库中执行如下操作查看正在使用的二进制记录格式：

show variables like 'binlog_format';

二进制日志文件的构成分为两类文件：（默认存放在 /var/lib/mysql/ 目录下）

日志文件：mysql|mariadb-bin.文件名后缀 文件是二进制格式，如： mariadb-bin.000001

    （二进制日志文件的初始大小为245 bites，并且在文件写满后，自动生成下一个）

    （为防止突发事件的发生，一般将二进制日志与数据文件分开存放）

     使用flush logs可生成新的日志文件

索引文件：mysql|mariadb-bin.index 文件是文本格式，记录了日志文件的个数信息

---

二进制日志客户端命令工具：mysqlbinlog

此命令的作用在于：

1.查看指定的二进制日志文件内容信息

mysqlbinlog OPTIONS +二进制日志文件  

OPTIONS包含以下：（也可省略）

--start-position=……    起始位置

--stop-position=……     结束位置

--start-datetime=……    起始时间

--stop-datetime=……     结束时间

2.可将mysql里执行的语句通过重定向导入到文件中，然后在需要时利用客户端命令“mysql < 此文件”重新作用于数据库

---

---