mysql的事务操作及日志文件

事务

事务特性

ACID特性：

• A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
• C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态，能量守恒，保持一致
• I：Isolation隔离性；一个事务所做出的操作在提交之前，其它事务是看不到的；隔离有多种隔离级别，实现并发
• D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

事务操作

#可以批量执行指令，提高效率
#以begin标明开始进行事务，未输入提交之前别人看不到，
begin;
insert teachers(name,age) values('x',20);
insert teachers(name,age) values('y',22);

#取消操作，不是所有都可撤销，DML可以，DDL语言无法撤销drop
rollback;

#提交操作，输入begin也会提交结束事务
commit;

不以事务执行指令子句会进行自动提交

set autocommit={1|0}       #可更改值改为是否自动提交，可支持全局，每次执行指令后都要执行commit;才会提交

事务隔离级别

隔离级别	脏读	不可重复读	幻读	加读锁
读未提交	可以出现	可以出现	可以出现	否
读已提交	不允许出现	可以出现	可以出现	否
可重复读	不允许出现	不允许出现	可以出现	否
序列化	不允许出现	不允许出现	不允许出现	是

• READ UNCOMMITTED

  可读取到未提交数据，产生脏读，生产中不推荐

• READ COMMITTED

  可读取到已提交数据，但未提交数据不可读，产生不可重复读，即可读取到其他多个已提交的数据，导致每次读取数据不一致，会在相对应时间段看到别人已提交的数据而不是初始执行的数据，每次读取的数据都不一致

• REPEATABLE READ

  可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置，任何时间段看到的都是最初的数据

• SERIALIZABLE

  可串行化，加锁，未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务），或者未提交的修改事务阻塞读事务（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差

更改默认级别

8.0之前用tx_isolation，8.0之后，包含8.0用transaction_isolation

#更改配置文件
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

systemctl restart mysqld

日志管理

事务日志

仅保留一部分时间

查看事务日志文件相关配置

show variables like '%innodb_log%';

innodb_log_file_size   50331648 每个日志文件大小
innodb_log_files_in_group  2     日志组成员个数
innodb_log_group_home_dir ./ 事务文件路径

事务日志性能优化

innodb_flush_log_at_trx_commit=0|1|2

0 模式，对表添加记录时，和事务提交时都会写入mysql程序的缓冲区，固定每秒再转发写入到磁盘文件，程序损坏几率大，不安全，不停电情况下，有可能会丢失一秒钟的数据

1 模式，对表添加记录会写入程序缓冲区，发起事务提交会写入操作系统的缓冲区，然后再写入磁盘，只要提交就写入磁盘，安全性最强，但性能最差，主要用于金融产业或者其他重要项目使用，保证安全性

2 模式，对表添加记录会写入程序缓冲区，发起事务提交，会写入操作系统的缓冲区，固定每秒固定再转发写入到磁盘文件，内核损坏几率小，比较安全，不停电情况下，有可能会丢失一秒钟的数据

0和2的性能差不多，2比0要安全一丢丢

#更改配置文件
#建议把事务日志文件单独存放在独立目录分区中，减少文件处理碎片化，提高性能
mkdir /data/tran_logs
chmod -R /data/tran_logs

vim /etc/my.cnf
[mysqld]
innodb_flush_log_at_trx_commit=2
innodb_log_file_size=500000000   
innodb_log_files_in_group=3 
innodb_log_group_home_dir=/data/tran_logs      

systemctl restart mysqld

错误日志

主要存放失败的，错误，或者重大事件如无法启动

查看存放路径，可更改，改配置文件log_error的值即可启用并存放再自定义目录下

SHOW GLOBAL VARIABLES LIKE 'log_error'   

通用日志

会记录数据库的各种操作

general_log=ON|OFF                 #是否启用通用日志，启用后会在数据库路径下生成一个主机名.log
general_log_file=HOSTNAME.log      #记录的文件名，也可在此更改文件路径
log_output=TABLE|FILE|NONE         #通用日志的存放形式，表|文件    表形式会存放在mysql数据库的general_log表里，磁盘路径在数据库路径下的.CSV文件里

慢查询日志

记录执行查询时长超出指定时长的操作，慢的定义默认十秒

slow_query_log=ON|OFF                          #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N                              #慢的定义，单位秒
slow_query_log_file=HOSTNAME-slow.log          #慢查询日志文件路径和命名
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk 
#上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON  #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句也都会记录在日志中，默认OFF，即不记录
log_slow_rate_limit = 1                        #多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain         #记录内容
log_slow_queries = OFF                         #同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除

二进制日志（备份）

不依赖于存储引擎类型，建议单独存放

记录格式

• 基于“语句”记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少，只记录语句
• 基于“行”记录：row，记录数据，日志量较大，更加安全，建议使用的格式，会记录语句所更改的信息
• 混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

查看格式

show variables like 'binlog_format';
#可更改配置文件

相关变量

select @@log_bin;
sql_log_bin=ON|OFF：                #是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项，
#例如在大规模批量导入千万行记录，属于测试数据时；数据库还原过程中可临时关闭二进制功能，set更改即可
log_bin=/PATH/BIN_LOG_FILE：        #指定文件位置,需写在配置文件中；默认OFF，表示不启用二进制日志功能，
#当不指定路径时默认存放在数据库文件下的binlog文件
#上述两项都开启才可以

binlog_format=STATEMENT|ROW|MIXED：  #二进制日志记录的格式，默认STATEMENT
max_binlog_size=1073741824：         #单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
#说明：文件达到上限时的大小未必为指定的精确值，重启数据库服务也会生成新的文件
binlog_cache_size=4m                 #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m           #限制用于缓存多事务查询的字节大小。
sync_binlog=1|0：                    #设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：                 #二进制日志可以自动删除的天数。 默认为0，即不自动删除

开启二进制功能，建议单独存放文件

mkdir /data/binlog/
chown mysql. /data/binlog/

vim /etc/my.cnf
[mysqld]
log_bin=/data/binlog/mysql-bin              #需指定日志文件前缀
max_binlog_size=1073741824                  #单个二进制日志文件的最大体积，到达最大值会自动生成新文件

mysql>set sql_log_bin=ON;                   #登录mysql打开变量

#重启服务
systemctl restart mysqld

查看二进制文件中的内容

#需登录mysql
show binlog events in 'mysql-bin.000001' 
from 6516 limit 2,3      #筛选条件

#不用登录查看
mysqlbinlog /data/binlog/mysql-bin.000001 -v

#查看二进制日志列表
show master logs;或者show binary logs;

#当前正在使用的二进制日志文件
show master status;

导出备份及还原

#将编号678到编号752之间所产生的记录挑出，还原到该记录事件发生时的状态
mysqlbinlog --start-position=322 --stop-position=465 /var/lib/mysql/mariadb-bin.000003 -v > binlog.sql

#用导出文件还原
mysql < binlog.sql

同步远程主机的二进制日志

#远程实时将10.0.0.8上的从指定二进制日志文件开始，同步到当前主机的当前目录下，账号注意授权，要求同一版本
mysqlbinlog -R --host=10.0.0.8 --user=wei --password=123456 --raw --stop-never mysql-bin.000001

删除

PURGE MASTER LOGS TO 'mysql-bin.000002';       #将文件2前面的文件删除
RESET MASTER；                                 #清空全部二进制日志，重新开始记录