详解Mysql事务和Mysql日志

详解Mysql事务和Mysql⽇志
事务特性
1、原⼦性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。

2、⼀致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。

⽐如A向B转账，不可能A扣了钱，B却没收到。

3、隔离性（Isolation）：同⼀时间，只允许⼀个事务请求同⼀数据，不同的事务之间彼此没有任何⼲扰。

⽐如A正在从⼀张银⾏卡中取钱，在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%'; //数据⽂件⽬录，默认⽇志存在该⽬录
#编辑f来设定binary log⽇志位置(注，配置⼆进制⽇志路径及⽂件名后，系统变量log_bin被⾃动置为on)
log_bin=/var/lib/mysql/binarylog/binlog
#如果在f⾥⾯只设置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启动时或FLU 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 ⽇志的资料请关注其它相关⽂章！。