详解 Mysql 事务和Mysql 日志

详解 Mysql 事务和Mysql 日志

事务特性

1、原子性（Atomicity）：事务开始后所有 *** 作，要么全部做完，要么全部不做，不可能停滞在中间环节。

2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张yhk中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

事务并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚 *** 作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

事务隔离

mysql默认是“可重复读”，串行化后

事务隔离级别 脏读 不可重复读 幻读 读未提交（read-uncommitted） 是 是 是 不可重复读（read-committed） 否 是 是 可重复读（repeatable-read） 否 否 是 串行化（serializable） 否 否 否

#查全局事务隔离级别
SELECT @@global.tx_isolation;
#查当前会话事务隔离级别
SELECT @@session.tx_isolation; 
#查当前事务隔离级别
SELECT @@tx_isolation;
#设置全局隔离级别
set global transaction isolation level read committed;
#设置当前会话隔离级别
set session transaction isolation level read committed;

串行化是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁，在这个级别，可能导致大量的超时现象和锁竞争。

共享锁(Share)：共享锁的代号是S

mysql日志文件系统组成

1、MySQL日志文件系统的组成

   a、错误日志：记录启动、运行或停止mysqld时出现的问题。
   b、通用日志：记录建立的客户端连接和执行的语句。
   c、更新日志：记录更改数据的语句。该日志在MySQL 5.1中已不再使用。
   d、二进制日志：记录所有更改数据的语句。还用于复制。
   e、慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。
   f、Innodb日志：innodb redo log

二进制日志（binlog）：

包含了所有更新了数据或者已经潜在更新了数据(比如没有匹配任何行的一个DELETE)

包含关于每个更新数据库(DML)的语句的执行时间信息

不包含没有修改任何数据的语句，如果需要启用该选项，需要开启通用日志功能

主要目的是尽可能的将数据库恢复到数据库故障点，因为二进制日志包含备份后进行的所有更新

用于在主复制服务器上记录所有将发送给从服务器的语句

启用该选项数据库性能降低1%，但保障数据库完整性，对于重要数据库值得以性能换完整。有些类似于oracle开启归档模式。

show variables like '%version%'; 
show variables like '%log_bin%'; //是否启用 binlog
show variables like '%binlog%'; //binlog 相关参数
show variables like '%datadir%';  //数据文件目录，默认日志存在该目录

#编辑my.cnf来设定binary log日志位置(注，配置二进制日志路径及文件名后，系统变量log_bin被自动置为on) 

log_bin=/var/lib/mysql/binarylog/binlog 

#如果在my.cnf里面只设置log_bin，但是不指定file_name，然后重启数据库。你会发现二进制日志文件名称为${hostname}-bin 这样的格式
#切换日志
show master status;
flush logs;
show master status;

每次重启MySQL服务也会生成一个新的二进制日志文件，相当于二进制日志切换。切换二进制日志时，你会看到这些number会不断递增。另外，除了这些二进制日志文件外，你会看到还生成了一个DB-Server-bin.index的文件，这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引

二进制日志的删除可以通过命令手工删除，也可以设置自动清理。

show binary logs;
mysql> purge binary logs to 'DB-Server-bin.000002';

purge binary logs to xxx; 表示删除某个日志之前的所有二进制日志文件。这个命令会修改index中相关数据
purge binary logs before '2017-03-10 10:10:00'; 清除某个时间点以前的二进制日志文件。
purge master logs before date_sub( now( ), interval 7 day);清除7天前的二进制日志文件
reset master;清除所有的二进制日志文件（当前不存在主从复制关系）

show variables like 'expire_logs_days';我们也可以设置expire_logs_days参数，设置自动清理，其默认值为0,表示不启用过期自动删除功能，如果启用了自动清理功能，表示超出此天数的二进制日志文件将被自动删除，自动删除工作通常发生在MySQL启动时或FLUSH日志时。
set expire_logs_days=7;

二进制日志相关参数

1、系统变量log_bin_trust_function_creators，默认为OFF，这个参数开启会限制存储过程、Function、触发器的创建。

2：系统变量sql_log_bin 用于控制会话级别二进制日志功能的开启或关闭，默认为ON，表示启用二进制日志功能。

3、系统变量binlog_cache_size 表示为每个客户端分配binlog_cache_size大小的缓存，默认值32768。二进制日志缓存使用的前提条件是服务器端使用了支持事务的引擎以及开启了bin log功能，它是MySQL用来提高binlog的效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多或多事务语句，写入量比较大，可适当调高binlog_cache_size。同时，我们可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了。

可以通过查看Binlog_cache_disk_use 与 Binlog_cache_use来判断binlog_cache_size是否需要调整。

4、系统变量max_binlog_cache_size 二进制日志能够使用的最大cache内存大小。当执行多语句事务时，max_binlog_cache_size 如果不够大，系统可能会报出“Multi-statement transaction required more than ‘max_binlog_cache_size' bytes of storage”的错误。

5、 系统变量max_binlog_stmt_cache_size

max_binlog_cache_size针对事务语句，max_binlog_stmt_cache_size针对非事务语句，当我们发现Binlog_cache_disk_use或者Binlog_stmt_cache_disk_use比较大时就需要考虑增大cache的大小

6、系统变量max_binlog_size， 表示二进制日志的最大值，一般设置为512M或1GB，但不能超过1GB。该设置并不能严格控制二进制日志的大小，尤其是二进制日志比较靠近为不而又遇到一根比较大事务时， 为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。

7、系统变量binlog_checksum 用作复制的主从校检。 NONE表示不生成checksum，CRC-32表示使用这个算法做校检。

8、系统变量sync_binlog，这个参数对于Mysql系统来说是至关重要的，它不仅影响到二进制日志文件对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。

sync_binlog=0，当事务提交后，Mysql仅仅是将binlog_cache中的数据写入binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，而是让Filesystem自行决定什么时候来做同步。MySQL中默认的设置是 sync_binlog=0，即不作任何强制性的磁盘刷新指令，这个设置性能是最好的，但风险也是最大的。一旦系统崩溃（Crash），在文件系统缓存中的所有二进制日志信息都会丢失。从而带来数据不完整问题。

sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同时文件系统将Binlog文件缓存刷新到磁盘。

可以适当的调整sync_binlog， 在牺牲一定的一致性下，获取更高的并发和性能。

9、系统变量binlog_format 指定二进制日志的类型。分别有STATEMENT、ROW、MIXED三种值。MySQL 5.7.6之前默认为STATEMENT模式。MySQL 5.7.7之后默认为ROW模式。这个参数主要影响主从复制。

基于SQL语句的复制(statement-based replication, SBR)，

基于行的复制(row-based replication, RBR)，

混合模式复制(mixed-based replication, MBR)。

查看二进制日志内容

方法1：使用show binlog events方式可以获取当前以及指定binlog的日志，不适宜提取大量日志。

SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

SHOW BINLOG EVENTS IN 'mysql-bin.000005' \G

方法2: 使用mysqlbinlog命令行查看日志内容(适宜批量提取日志)。

system mysqlbinlog /var/lib/mysql/DB-Server-bin.000013;
mysqlbinlog /var/lib/mysql/DB-Server-bin.000013 > test.sql;

二进制日志的类型

基于段的日志格式

binlog_format=STATEMENT

记录了 *** 作的sql语句。

优点：

日志记录量相对较小，节约磁盘及网络I/O,只对以一条记录修改或插入ROW格式所产生日量小于段产生的日志量。

缺点：

必须记录上下文信息，保证语句在从服务器上的执行结果和在主服务器上相同。

特定函数如UUID,USER()这样非确定性的函数无法复制。

可能造成mysql复制的主备服务器数据不一致，从而中断复制链路。

显示binlog 格式

show variables like 'binlog_format';

set session binlog_format=statement;

基于行的日志格式

将my.ini 二进制格式修改为binlog_format=ROW

row 的优点：row格式可以避免MYSQL复制中出现主从不一致的问题，官方推荐这种格式。同一个sql语句修改了10000条数据的情况下。基于段的日志只会记录这个SQL语句。基于行的日志会有10000条记录，分别记录每一行数据的修改。

1.是mysql主从复制更加安全。

2.对每一行数据修改比基于段的复制高效。如果误 *** 作修改了数据库中的数据，同时没有备份可以恢复时，我们就可以通过分析二进制日志，对日志中记录的数据修改 *** 作做反向处理的方式来达到恢复数据的目的。

row 的缺点：记录日志量较大

binlog_row_image=[full,minimal,noblob]

full : 记录列的所有修改；minimal ：只记录修改的列。noblob :如果是text类型或clob字段，不记录 这些日志。

使用 mysqlbinlog -vv ../data/mysql-bin.000005 查看明细日志。

set session binlog_row_image=minimal

混合日志格式:

binlog_format=MIXED

特点：根据sql语句由系统决定在记录段和基于行的日志格式中进行选择。数据量大小由所执行的SQL决定。

如何选择二进制格式

建议binlog_formart =mixed    or   binlog_format=row;   binlog_row_image=minimal;

复制方式：

1.基于SQL语句的复制（SBR)

优点：生成日志量少，节约网络传输的ID.并不要求对主从数据库的表定义完全相同。

相比于基于行的复制方式更为灵活。

缺点：对于非确定事件，无法保证主从复制数据的一致性。对于存储过程，触发器

2.基于行的复制(RBR)

优点：可以应用于任何SQL的复制包括非确定性函数，存储过程等。可以减少数据库锁的使用。

缺点：要求主从数据库的表结构相同，否则就会中断复制。

3.复制工作方式

1.主服务器将变更写入二进制日志。

2.从读取主的二进制日志变更并写入到relay_log中。

基于日志点的复制，基于GTID的复制。

3.在从上重放relay_log中的日志。

基于SQL段的日志是在从库上重新执行记录的SQL。

基于行的日志则是在从库上直接应用对数据行的修改。

以上就是详解 Mysql 事务和Mysql 日志的详细内容，更多关于Mysql 事务和Mysql 日志的资料请关注脚本之家其它相关文章！