mysql之事务

mysql事务简单来说就是数据库的数据进行一系列的操作。例如insert,delete等等，而这些语句组成的对数据库的操作就是事务。

mysql事务有4大特性ACID:原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性(Atomicity)：一个事务的操作要不全部完成，要不全部不完成。不会结束在某个环节。事务会在发生错误时回滚(rollback)到事务前状态。
• 一致性(Consistency)：在事务开始前和结束后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合数据库表预设的规则。包含资料精确度，串联型等等。
• 隔离性(Isolation)：数据库允许多个并发事务同时对数据进行读取和修改的能力。隔离性可以防止多个事务并发执行时由于交叉执行导致数据的不一致。
• 持久性(Durability)：事务在提交之后，其修改的数据将永久保留在数据库中。

事务控制

使用事务功能也只能是支持事务的引擎才可以。

• 启动：START TRANSACTION 或者begin
• 提交：COMMIT
• 回滚：ROLLBACK

mysql默认的是自动提交功能，也就是说在使用insert，delete，update的时候，执行一条，提交一条。 如果操作取消的话，我们还将一条条的删除。事务就是解决这一问题，我们一次性执行多条语句，如果执行完觉得不行，我们还可以回滚到事务执行前的状态

设置不自动提交事务。

MariaDB [(none)]> SET  autocommit=OFF ;  # 直接设置变量，不过是临时的，如果退出重进的话就失效了

要想永久有效。需要将变量写到配置文件中。

[root@ansible ~]# cat /etc/my.cnf
[mysqld]
autocommit=0

事务测试

MariaDB [(none)]> START TRANSACTION;
MariaDB [(none)]> USE hellodb
MariaDB [hellodb]> INSERT students VALUES (27,'Guan yin',200,'F',2,3);
MariaDB [hellodb]> COMMIT;
commit直接提交事务。一旦事务提交来就表示一个事务的结束


MariaDB [hellodb]> START TRANSACTION;
MariaDB [hellodb]> INSERT students VALUES (28,'Ru lai',200,'M',2,3);
MariaDB [hellodb]> ROLLBACK;
rollback，回滚到事务开始前的状态。28不会生效

事务节点

在一个事务中执行多个语句的时候，如果我们需要对其中某一个语句进行回滚或者提交的话，就可以使用事务节点的特性。
语法：

• SAVEPOINT identifier：保存某个事务节点
• ROLLBACK [WORK] TO [SAVEPOINT] identifier：将某事务节点回滚
• RELEASE SAVEPOINT identifier：删除某事务节点

示例：

MariaDB [(none)]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> INSERT students VALUES (28,'Ru lai','300','M','1','2');
MariaDB [hellodb]> SAVEPOINT test1;
Query OK, 0 rows affected (0.01 sec)
# 插入一条，保存事务节点test1

MariaDB [hellodb]> INSERT students VALUES (29,'Sha seng','150','M','3','4');
Query OK, 1 row affected (0.02 sec)
MariaDB [hellodb]> SAVEPOINT test2;
Query OK, 0 rows affected (0.00 sec)
#插入第二条，保存事务节点2

MariaDB [hellodb]> select * from students;
    27 | Guan yin      | 200 | F      |       2 |         3 |
|    28 | Ru lai        | 255 | M      |       1 |         2 |
|    29 | Sha seng      | 150 | M      |       3 |         4 |

目前有28，29的记录。  将test1事务节点回滚。 


MariaDB [hellodb]> ROLLBACK TO test1;  # 将事务回滚到test1节点的状态。
MariaDB [hellodb]> SELECT * FROM students WHERE Stuid>26;
+-------+----------+-----+--------+---------+-----------+
| StuID | Name     | Age | Gender | ClassID | TeacherID |
+-------+----------+-----+--------+---------+-----------+
|    27 | Guan yin | 200 | F      |       2 |         3 |
|    28 | Ru lai   | 255 | M      |       1 |         2 |
+-------+----------+-----+--------+---------+-----------+


MariaDB [hellodb]> RELEASE SAVEPOINT test1;
#删除test1节点

事务隔离性

数据库支持并发事务，为防止多个事务引起冲突，mysql定义了事务隔离。有以下几种级别。

• READ UNCOMMITTED：可读取到未提交数据，就是说，一个事务发生时，在另一台客户端上可以查看未提交的事务。数据发生改变，但是没有提交的事务，称之为脏读。
• READ COMMITTED：可读提交数据，未提交数据不可读，称为不可重复读，即可读取到多个提交数据，导致每次读取的数据不同
• REPEATABLE READ：可重复读，多次读取的数据都一致，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为mysql默认设置
• SERIALIZABLE：可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务，导致并发性能差。

设定隔离级别

由服务器变量tx_isolation指定，默认为REPEATABLE READ，可在GLOBAL或session级进行设置

READ UNCOMMITTED示例

MariaDB [hellodb]> SET  GLOBAL tx_isolation='READ-UNCOMMITTED';
MariaDB [hellodb]> SHOW VARIABLES LIKE 'tx%';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+

MariaDB [hellodb]> START TRANSACTION; # 开启一个事务
MariaDB [hellodb]> INSERT students VALUES (30,'A li','20','M','5','6');
# 插入一条记录，但是不提交事务

MariaDB [hellodb]> SELECT * FROM students WHERE Stuid=30;
+-------+------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------+-----+--------+---------+-----------+
|    30 | A li |  20 | M      |       5 |         6 |
+-------+------+-----+--------+---------+-----------+

#在其它主机上查看该students的表，会发现30号记录已经存在


MariaDB [hellodb]> ROLLBACK;  # 取消事务

MariaDB [hellodb]> SELECT * FROM students WHERE Stuid=30;
Empty set (0.01 sec)
#其它主机上也没有了该记录

READ COMMITTED示例

MariaDB [hellodb]> SET GLOBAL tx_isolation='READ-COMMITTED';
MariaDB [hellodb]> START TRANSACTION;
MariaDB [hellodb]> INSERT students VALUES (31,'Teng xun','30','M','7','8');
Query OK, 1 row affected (0.01 sec)



# 在另台主机上查看
MariaDB [hellodb]> SELECT * FROM students WHERE Stuid=31;
Empty set (0.00 sec)

# 在执行事务的主机上提交事务
MariaDB [hellodb]> COMMIT;
Query OK, 0 rows affected (0.00 sec)

#在其它主机上查看
MariaDB [hellodb]> SELECT * FROM students WHERE Stuid=31;
+-------+----------+-----+--------+---------+-----------+
| StuID | Name     | Age | Gender | ClassID | TeacherID |
+-------+----------+-----+--------+---------+-----------+
|    31 | Teng xun |  30 | M      |       7 |         8 |
+-------+----------+-----+--------+---------+-----------+

REPEATABLE READ示例

MariaDB [(none)]> SET tx_isolation='REPEATABLE-READ';

MariaDB [hellodb]> START TRANSACTION;

MariaDB [hellodb]> INSERT students VALUES (32,'Mei tuan','30','M','9','10');

MariaDB [hellodb]> COMMIT;

# 在另台主机上查看students表
MariaDB [hellodb]> SELECT  * FROM students ORDER BY Stuid DESC LIMIT 1;
+-------+----------+-----+--------+---------+-----------+
| StuID | Name     | Age | Gender | ClassID | TeacherID |
+-------+----------+-----+--------+---------+-----------+
|    31 | Teng xun |  30 | M      |       7 |         8 |
+-------+----------+-----+--------+---------+-----------+
# 两个事务完全不相隔。也无法查看

SERIALIZABLE示例

MariaDB [(none)]> SET GLOBAL tx_isolation='SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> START TRANSACTON;
MariaDB [hellodb]> SELECT * FROM students;   # 在开启事务的时候使用查询功能

另一台主机上试着对students插入一张表
MariaDB [hellodb]> INSERT students VALUES (33,'Da zhong',28,'M','11','12');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
#会发现卡住，这就是读阻塞写。

MariaDB [hellodb]> START TRANSACTION;#在开启一个事务
MariaDB [hellodb]> INSERT students VALUES (33,'Da zhong',28,'M','11','12');
Query OK, 1 row affected (49.65 sec)

#在另台主机上查看students表
MariaDB [hellodb]> SELECT * FROM students;
# 同样会发现卡住，这就是写阻塞读

以上方式都是在变量中直接命名，也可以写入配置文件中。

[root@ansible ~]# cat /etc/my.cnf
transaction-isolation=REPEATABLE-READ
[root@ansible ~]# systemctl restart mariadb
MariaDB [(none)]> show variables like 'tx%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

日志

mysql在对数据进行修改操作时，不是直接写入的磁盘的，而是按顺序先写入事务日志中，实现redo和undo操作。比如数据库发生故障需要重启时，之前所产生的事务，数据库会根据事务日志将已完成的事务进行redo，将没有提交的事务进行undo操作。

事务日志相当重要，它是备份数据和恢复数据重要的组件。

存放日志的地方：/var/lib/mysql/ib_logfile0和/var/lib/mysql/ib_logfile1

mysql日志分类：

• 事务日志
• 错误日志
• 查询日志
• 慢查询日志
• 二进制日志
• 中级日志

事务日志

Innodb事务日志的相关配置：

MariaDB [(none)]> show variables like 'innodb_log%';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_log_block_size     | 512     |  # 日志块大小
| innodb_log_buffer_size    | 8388608 | 
| innodb_log_file_size      | 5242880 |  # 日志文件大小
| innodb_log_files_in_group | 2       |  # 日志成员数
| innodb_log_group_home_dir | ./      |   #日志文件，默认是/var/lib/mysql
+---------------------------+---------+
[root@ansible mysql]# ll ib_log*
-rw-rw---- 1 mysql mysql 5242880 2月  29 10:15 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 2月  17 14:29 ib_logfile1
这两个日志是循环覆盖的。

错误日志

mysqld启动和关闭过程中产生的错误信息，在my.cnf配置文件中可以定义文件的存放位置以及warning警告是否放置到错误日志当中，warning默认是不放在错误日志当中的。

warning警告存放日志:log_warnings=1|0 默认值1 1表示记录

错误日志记录信息：服务器启动关闭信息、运行错误信息、时间调度器运行一个事件时产生的信息、在服务器上启动进程产生的信息。

MariaDB [(none)]> show variables like 'log_error';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| log_error     | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+

查询日志

查询日志默认是关闭的，因为所有的操作记录全部会记录到查询日志中，这会对数据库的并发性能有严重影响。因为增加了磁盘的io。所以一般是不开启的。

MariaDB [hellodb]> show global variables like 'general%';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| general_log      | OFF         |  # 是否开启日志
| general_log_file | ansible.log |  # 日志名称
+------------------+-------------+


MariaDB [hellodb]> show global variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |   # 输出日志格式
+---------------+-------+

慢查询日志

超过变量long_query_time指定的时间，会被认为是慢查询。但是查询获取锁(包括锁等待)的时间不计入查询时间内。

mysql慢查询日志是在执行完毕且完全释放完锁之后才记录的，因此慢查询日志记录的顺序和执行的SQL查询语句可能不一致。（例如执行语句1很慢，可能没有通过索引，执行语句2很快，是通过索引查询。那可能记录的顺序就不一样）。

慢查询相关配置

 slow_query_log=OFF   #是否开启慢日志
 slow_query_log_file=ansible-slow.log #慢日志默认存放的名字
 log_slow_rate_limit = 1 #多少次查询才记录，mariadb特有
 log_slow_queries=OFF  #也是是否开启慢日志，但是新版已经废弃
 long_query_time=10.000000 #慢查询的阈值，单位秒
 log_queries_not_using_indexes=OFF # 不适用索引也没有达到慢查询阈值的语句是否记录日志 

二进制日志

二进制日志主要用来记录导致数据改变或潜在导致数据改变的SQL语句，它不包括查询的语句。

二进制日志是按操作顺序存放在自己的日志当中的。它是在事务提交之后写进二进制日志中的。

我们可以用来通过二进制日志文件来讲事件生成sql脚本，用来还原数据库。默认情况下是关闭的，需要开启一下。最好二进制文件和数据库文件单独放开。

二进制相关配置：

• sql_log_bin：是否记录进二进制日志中
• log_bin=/PATH/TO/FILE：指定文件位置，默认OFF，需要同上面选项一起开启才能生效，不同时开启是不生效的。
• binlog_format=STATEMENT|ROW|MIXED：二进制记录的格式，默认为STATEMENT
• max_binlog_size=1073741824：二进制日志存放的最大体积，默认是1G，是以循环覆盖的方式存储。
• sync_binlog=1|0：是否启动二进制文件即时同步写入到磁盘、默认为0，由操作系统负责同步到磁盘。
• expire_logs_days=N：二进制日志可以自动删除的天数，默认为0，即不自动删除

二进制记录的格式：
STATEMENT：基于语句记录，按照执行的语句原封不动的记录在二进制文件当中。但是这样有一个问题。就是使用某些函数例如NOW()的时候，会导致从二进制日志中导入的数据不一样。
ROW：基于行的记录，会将函数转换成不带函数的SQL语句。建议使用此种格式。
MIXED：系统自动判断使用哪种格式、

开启二进制日志

[root@ansible ~]# cat /etc/my.cnf
[mysqld]
log-bin=/data/mysql/binlog/master
binlog-format=row  # 同时将二进制日志文件格式定义为行记录

[root@ansible ~]# ll /data/mysql/binlog
总用量 8
-rw-rw---- 1 mysql mysql 245 2月  29 16:33 master.000001
-rw-rw---- 1 mysql mysql  33 2月  29 16:33 master.index  # 这里存放的就是二进制日志文件的名字
#目录的权限一定要是mysql用户可以读写的
	

查看mariadb自行管理使用中的二进制日志文件列表

MariaDB [hellodb]> SHOW MASTER logs;

查看使用中的二进制日志文件

MariaDB [hellodb]> SHOW MASTER STATUS;

客户端工具mysqlbinlog
mysqlbinlog [OPTIONS] log_file…

选项：

• -v：显示完整信息
• --start-position=#：指定开始位置
• --stop-position=#：指定结束位置
• --start-datetime=#：指定开始时间
• --stop-datetime=#：指定结束时间
• 时间格式：YYYY-MM-DD hh:mm:ss

[root@ansible binlog]# mysqlbinlog master.000002
# at 400  日志的位置
#200229 16:45:59 #时间 server id 1#服务器编号  end_log_pos 689#结束位置 	Query#时间类型	thread_id=2#执行此事务的线程id	exec_time=0#语句的时间戳与将其写入二进制文件的时间差。因为很短，所以此处为0	error_code=0#错误代码
#以下为事件内容
SET TIMESTAMP=1582965959/*!*/;
CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_testlog`()
begin  
declare i int;
set i = 1; 
while i < 100000 
do  insert into testlog(name,age) values (concat('wang',i),i); 
set i = i +1; 
end while; 
end
/*!*/;
DELIMITER ;
# End of log file
#只截取了一段

清除指定二进制日志

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

MariaDB [hellodb]> PURGE MASTER LOGS TO 'master.000002';  
#删除2前的日志

清除所有日志

MariaDB [hellodb]> RESET MASTER;

切换日志文件

ariaDB [hellodb]> FLUSH LOGS;
Query OK, 0 rows affected (0.03 sec)

MariaDB [hellodb]> show master logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| master.000001 |       285 |
| master.000002 |       245 |   
+---------------+-----------+
#这个表示当前数据库的状态都1上，接下来要修改的事务会通通被记录到02上面。