MySQL事务与日志

MySQL事务

    在数据库日常工作环境中，通常需要对数据库进行大量重复的读写操作。但是假设在对数据库操作过程中出现故障，一部分数据被修改，但与其相关的另一部分数据还未来得及修改时，数据库服务应该如何处理这种情况呢。对于支持事务的关系型数据库来说，它必须满足（或者说通过）ACID测试，以保证数据操作的安全性。
ACID

Atomicity： 原子性，一个事务包含多个操作，要么完全执行所有部分，要么不执行任何操作。如果执行过程发生意外错误，应该支持回滚操作。
Consistency： 一致性，数据库操作前后应该保持某个特征不变
Isolation： 隔离性：事务提交之前，相关数据不能被其他事务访问
Durability： 持久性，事务提交后，修改将被永久保存于数据库中

事务相关操作
    事务操作主要为两个步骤：启动事务和结束事务；而结束事务又有两种方式：提交（表示期间做出的修改将被提交保存）和回滚（回滚至事务启动时刻状态）。事务期间可以保存节点（类似于虚拟机状态保存），当回滚时可以指明回滚至这一状态而非启动事务时候的最初状态。
    事务的自动提交功能若开启，默认会把每一条sql语句当作一个事务，在每条语句执行完成后就会自动提交。若该功能关闭，则会在一个事务开始时（上一事务提交后或者手动指明启动事务）开始记录，手动提交或回滚时结束事务，并启动一个新事务。
注：事务功能需要存储引擎支持

mysql> SET VARIABLE autocimmit=OFF;	建议关闭事务自动提交
mysql> START TRANSACTION;	手动明确指定启动新事务
mysql> 一系列命令
mysql> SAVEPOINT sp_name;	创建保存点，可以有多个
mysql> RELEASE SAVEPOINT sp_name;	删除保存点
mysql> ROLLBACK TO sp_name;	回滚至某一保存点
mysql> ROLLBACK；	回滚到事务创建时刻，当前事务结束
mysql> 一系列命令
mysql> COMMIT;	提交事务

    事务回滚操作是不可逆的，即假如有保存点依次为sp1，sp2，sp3，手动回滚至sp2后，无法回滚至sp3，事务也并非结束，可以继续回滚至sp1或者事务的最初状态。仅有回滚至事务最初状态或者手动提交时才会结束事务，并又开启一个新事务。
 
事务隔离
    前面在介绍ACID时提到了事务的隔离性，事务隔离分为几个等级，不同隔离级别可能带来的问题也不一样。隔离级别越低，表示事务对当前操作数据的独占能力越弱，能够带来较好的并发效果，但是数据可靠性较差；隔离级别越高，表示事务对当前操作数据的独占能力越强，不可靠的数据更不易被其他事务获取，但是并发能力较弱。
常见的问题：

脏读： 实时读取其他事务未提交的数据，数据随时可能会发生修改
不可重复读： 读取（上一次）提交后的数据，不可读其他事务正在修改的数据，但是对方修改提交后，该事务再次读取时结果与最初不一致
幻读： 可重读，读取实时数据，但是读取后只要自己未修改数据，下次读取时仍然与首次读取的数据一致，引起读取数据与实际数据不符
加锁读： 串行化，当事务对某个数据访问、操作时，其他事务对该数据的访问与操作会被阻塞，直到事务提交后放行操作

不同隔离界别及存在的问题：

隔离级别	存在的问题
READ-UNCOMMITTED	脏读、不可重复读、幻读
READ COMMITTED	不可重复读、幻读
REPEATABLE-READ	幻读
SERIALIZABLE	加锁读

事务隔离级别调整：
    通过设置变量tx_isolation值更改隔离级别，InnoDB引擎默认隔离级别为REPEATIBLE-READ。

mysql> SET tx_isolation="isolation_level";	仅对当前会话有效

 
事务日志
    事务日志是磁盘上的一段连续空间，用于在事务提交时立即保存操作记录，并修改内存缓冲区中数据内容，随后才将修改同步到磁盘数据文件中，事务日志的设计极大程度上避免了在写入数据文件时硬盘寻道过程中（数据存放可能时未连续磁道上，所以需要消耗较长的寻道时间）发生意外崩溃，导致操作丢失，同样事务日志还可以作为数据恢复和数据回滚的依据。
 
 

MariaDB日志

    MariaDB有许多日志文件，它们记录MariaDB运行过程中的各种事件处理记录。可以通过这些日志实现分析数据库请求、数据的恢复等功能，因此按照其记录类型与功能的不同可以将MariaDB日志分为如下几类：
注： 日志相关的部分变量显示值为ON|OFF，但是设置值时通常要求值为字符串，它会开启相应功能并且以此值为文件名，如下变量没有一一测试，请以实际为准。

查询日志： 记录查询请求，支持记录于文件或者是数据库表中，通常会关闭

• 相关变量：
  general_log    ON|OFF    关闭或开启功能
  general_log_file    hostname.log    以文件形式存储时的文件名
  log_output    TABLE|FILE|NONE    定义日志输出方式，NONE等同于关闭日志

慢查询日志： 由于某种原因导致查询操作超时，可根据此文件分析、优化数据库查询

• 相关变量
  slow_query_log    ON|OFF    开启或关闭慢查询日志功能
  slow_query_log_file    hostname.slow.log    日志文件名
  long_query_time    10    设定慢查询超时时长
  log_slow_filter    VALUE    过滤器，选择记录哪些操作的慢查询
  log_slow_rate_limit    1    慢查询记录速率，1个/s
  log_slow_verbosity    VALUE    慢查询记录信息详细级别

错误日志： 服务运行过程中产生的错误信息记录，服务启动、关闭产生的信息等

• 相关变量
  log_error    ON|OFF|/path    开启或关闭错误日志，ON时有默认
  log_warning    1|0    是否记录警告信息

二进制日志： 有可能引起数据库数据改变的语句以二进制形式保存的记录

• 相关变量
  sql_log_bin    ON|OFF    开启或关闭二进制日志功能
  log_bin    filename    同上，开启二进制日志并以此值为二进制文件名
  binlog_format    STATEMENT|ROW|MIXED    记录格式，分别为基于 语句|行|混合 记录
  max_binlog_size    1073741824    二进制文件单个文件最大值，单位字节
  sync_binlog    1|0    是否开启同步记录二进制日志

• 相关命令

MariaDB [(none)]> SHOW BINARY LOGS;	查看当前系统二进制日志文件列表
MariaDB [(none)]> SHOW MASTER LOGS;	同上
MariaDB [(none)]> SHOW MASTER STATUS;	查看正在使用的二进制日志文件
MariaDB [(none)]> SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row];	查看二进制文件内容，类似于SELECT语法

## mysqlbinlog工具
# mysqlbinlog [OPTIONS] log_file
选项：
    --start-position=#	查看指定位置范围内的内容，约束条件起始位置
    --stop-position=#	约束条件结束位置
    --start-datetime=YYYY-MM--DD hh:mm:ss    查看指定时间范围内的内容，约束条件起始时间
    --stop-datetime=YYYY-MM-DD hh:mm:ss	约束条件结束时间

中继日志： 从服务器保存从主服务器读取的二进制文件中的事件

• 相关变量
  relay_log    hostname-relay-bin.nnnnnn    中继日志文件位置，为空则为默认
  relay_log_index    host_name-relay-bin.index    中继日志的索引文件
  relay_log_info_file       相关的信息文件（该文件记录主服务器二进制文件位置和中继日志位置等信息）的位置与名称
  relay_log_purge    ON|OFF    是否启用自动清除中继日志
  relay_log_recovery    ON|OFF    当从服务器发生意外，是否自动从主服务器获取日志
  relay_log_space_limit    VALUE    中继日志文件上限大小

事务日志： MySQL事务部分已有介绍

• 相关变量
  innodb_log_file_size    单个事务日志大小
  innodb_log_files_in_group    单个族内事务日志个数
  innodb_log_group_home_dir    事务日志存放位置

 
 

编译安装MariaDB (CentOS 7)

1、编译源码
# yum install ncurses-devel openssl-devel libevent-devel jemalloc-devel cmake
# useradd -r mysql
# tar -xf /tmp/mariadb-5.5.46.tar.gz
# cd /tmp/mariadb-5.5.46
# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb-5.5.46
	  -DMYSQL_DATADIR=/dbdata \
	  -DSYSCONFDIR=/etc \
	  -DWITH_INNOBASE_STORAGE_ENGINE=1 \
	  -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
	  -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
	  -DWITH_ARIA_STORAGE_ENGINE=1 \
	  -DWITH_PARTITION_STORAGE_ENGINE=1 \
	  -DWITH_READLINE=1 \
	  -DWITH_SSL=system \
	  -DWITH_ZILB=system \
	  -DWITH_LIBWRAP=0 \
	  -DMYSQL_UNIX_ADDR=/tmp/mysql.sock
	  -DDEFAULT_CHARSET=utf8 \
	  -DDEFAULT_COLLATTION=utf8_general_ci
# make && make install


2、初始化配置及提供相关文件
# cd /usr/local
# ln -sv mariadb-5.5.46 mariadb
# chown -R :mysql ./mariadb
# cd ./mariadb
# ./scripts/mysql_install_db --user=mysql --datadir=/dbdata

## 提供配置文件
# mkdir /etc/mysql
# cp ./support-files/my-large.cnf /etc/mysql/my.cnf
# vim /etc/my.cnf
/* 添加选项
    datadir= /dbdata
    innodb_dile_per_table = ON
    skip_name_resolve = ON
*/

## 提供service unit文件
# vim /etc/systemd/system/mariadb.service
/* 
    [Unit]
    Description=MariaDB Database Server
    After=syslog.target
    After=network.target
    #
    [Service]
    Type=simple
    User=mysql
    Group=mysql
    ExecStart=/usr/local/mariadb/bin/mysql_safe --basedir=/usr/local/mariadb	指明启动程序目录和安装目录
    TimeoutSec=300PrivateTmp=false	# 程序启动超时时长
    PrivateTmp=false	# 是否将mariadb私临时文件放至私有目录而非公共临时目录
    #
    [Install]
    WanterBy=multi-user.target
*/
# systemctl daemon-reload

## 导出变量
# vim /etc/profile.d/mariadb.sh
/*
    export PATH=/usr/local/mariadb/bin:$PATH
*/
# chmod +x ./mariadb.sh

## 数据库初始化
# system start mariadb.service
# mysql_secure_installation