Mariadb 分布式事务两阶段提交 binlog日志 查询日志 都记录了一些什么内容 以及恢复被丢失数据方式

数据库各种日志配置如下：

[root@contoso ~]# cat /etc/my.cnf.d/server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]
character-set-server=utf8
lower-case-table-names=1

log-bin=/var/log/mariadb/mariadb-log-bin
log-bin-index=/var/log/mariadb/mariadb-log-bin.index
log-error=/var/log/mariadb/mariadb-error.log

general-log=ON
general-log-file=/var/log/mariadb/queries.log
log-output=file

slow-query-log=ON
slow-query-log-file=/var/log/mariadb/mariadb-slow.log
long_query_time=1

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.1 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.1]

[root@contoso ~]#

分布式式事务两阶段提交实验：

[myth@contoso ~]$ mysql -uroot -h192.168.10.20 -p123456

MariaDB [None]> CREATE DATABASE IF NOT EXISTS `testdb1` DEFAULT CHARACTER SET utf8;

MariaDB [testdb1]> USE testdb1;

MariaDB [testdb1]> CREATE TABLE `think_user` (
  `user_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) NOT NULL COMMENT '用户姓名',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

MariaDB [testdb1]> XA START 'mariadb1027';
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb1]> INSERT INTO think_user VALUES(1,'jason');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb1]> XA END 'mariadb1027';
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb1]> XA PREPARE 'mariadb1027';
Query OK, 0 rows affected (0.00 sec)

通过上面的操作，用户创建了一个分布式事务，并且prepare没有返回错误，
说明该分布式事务可以被提交。通过命令XA RECOVER查看显示如下结果：

MariaDB [testdb1]> XA RECOVER;
+----------+--------------+--------------+-------------+
| formatID | gtrid_length | bqual_length | data        |
+----------+--------------+--------------+-------------+
|        1 |           11 |            0 | mariadb1027 |
+----------+--------------+--------------+-------------+
1 row in set (0.00 sec)

-- 如果不执行 XA COMMIT 'mariadb1027' 提交事务，直接退出数据库连接；再次连接数据库查看分布式事务mariadb1027
-- 那么mariadb 10.2.7这个版本数据库分布式事务mariadb1027不见了，提交的分布式SQL全部会丢
-- 失（binlog日志里根本就没有记录分布式事务SQL，请看下面打开第2个客户端查询binlog日
-- 志文件内容，直观地看到没有记录分布式日志事务SQL），但在查询日志里可以看到提交了那些SQL
-- 注意mysql 5.7 这个序列的版本解决了这个问题，

MariaDB [testdb1]> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| mariadb-log-bin.000015 |      854 |              |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [testdb1]> show master logs;
+------------------------+-----------+
| Log_name               | File_size |
+------------------------+-----------+
| mariadb-log-bin.000001 |      1909 |
| mariadb-log-bin.000002 |       371 |
| mariadb-log-bin.000003 |       371 |
| mariadb-log-bin.000004 |       371 |
| mariadb-log-bin.000005 |       371 |
| mariadb-log-bin.000006 |       371 |
| mariadb-log-bin.000007 |     15827 |
| mariadb-log-bin.000008 |      9416 |
| mariadb-log-bin.000009 |       371 |
| mariadb-log-bin.000010 |       371 |
| mariadb-log-bin.000011 |       371 |
| mariadb-log-bin.000012 |       371 |
| mariadb-log-bin.000013 |       371 |
| mariadb-log-bin.000014 |       348 |
| mariadb-log-bin.000015 |       854 |
+------------------------+-----------+
15 rows in set (0.00 sec)

MariaDB [testdb1]> show binlog events in 'mariadb-log-bin.000015'\G;
*************************** 1. row ***************************
   Log_name: mariadb-log-bin.000015
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 256
       Info: Server ver: 10.2.7-MariaDB-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mariadb-log-bin.000015
        Pos: 256
 Event_type: Gtid_list
  Server_id: 1
End_log_pos: 299
       Info: [0-1-97]
*************************** 3. row ***************************
   Log_name: mariadb-log-bin.000015
        Pos: 299
 Event_type: Binlog_checkpoint
  Server_id: 1
End_log_pos: 348
       Info: mariadb-log-bin.000015
*************************** 4. row ***************************
   Log_name: mariadb-log-bin.000015
        Pos: 348
 Event_type: Gtid
  Server_id: 1
End_log_pos: 390
       Info: GTID 0-1-98
*************************** 5. row ***************************
   Log_name: mariadb-log-bin.000015
        Pos: 390
 Event_type: Query
  Server_id: 1
End_log_pos: 526
       Info: CREATE DATABASE IF NOT EXISTS `testdb1` DEFAULT CHARACTER SET utf8
*************************** 6. row ***************************
   Log_name: mariadb-log-bin.000015
        Pos: 526
 Event_type: Gtid
  Server_id: 1
End_log_pos: 568
       Info: GTID 0-1-99
*************************** 7. row ***************************
   Log_name: mariadb-log-bin.000015
        Pos: 568
 Event_type: Query
  Server_id: 1
End_log_pos: 854
       Info: use `testdb1`; CREATE TABLE `think_user` (
  `user_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) NOT NULL COMMENT '用户姓名',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
7 rows in set (0.00 sec)

ERROR: No query specified

MariaDB [testdb1]> show binlog events in 'mariadb-log-bin.000015' from 568\G;
*************************** 1. row ***************************
   Log_name: mariadb-log-bin.000015
        Pos: 568
 Event_type: Query
  Server_id: 1
End_log_pos: 854
       Info: use `testdb1`; CREATE TABLE `think_user` (
  `user_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) NOT NULL COMMENT '用户姓名',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [testdb1]> show binlog events in 'mariadb-log-bin.000015' from 854\G;
Empty set (0.00 sec)

ERROR: No query specified

MariaDB [testdb1]>


再打开第2个客户端查看上面的SQL语句产生的binlog日志：
[myth@contoso ~]$ su -
Password: 123

[root@contoso ~]# find / -name mariadb-log-bin.000015
/var/log/mariadb/mariadb-log-bin.000015

[root@contoso ~]# mysqlbinlog /var/log/mariadb/mariadb-log-bin.000015
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170808 14:02:26 server id 1  end_log_pos 256 CRC32 0x98462eec  Start: binlog v 4, server v 10.2.7-MariaDB-log created 170808 14:02:26 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
8lOJWQ8BAAAA/AAAAAABAAABAAQAMTAuMi43LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADyU4lZEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgHsLkaY
'/*!*/;
# at 256
#170808 14:02:26 server id 1  end_log_pos 299 CRC32 0x9e4f8e4a  Gtid list [0-1-97]
# at 299
#170808 14:02:26 server id 1  end_log_pos 348 CRC32 0x16d08d52  Binlog checkpoint mariadb-log-bin.000015
# at 348
#170808 16:11:35 server id 1  end_log_pos 390 CRC32 0xd3a73737  GTID 0-1-98 ddl
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=1*//*!*/;
/*!100001 SET @@session.gtid_seq_no=98*//*!*/;
# at 390
#170808 16:11:35 server id 1  end_log_pos 526 CRC32 0xf6d7b3a3  Query   thread_id=13    exec_time=0     error_code=0
SET TIMESTAMP=1502179895/*!*/;
SET @@session.pseudo_thread_id=13/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE DATABASE IF NOT EXISTS `testdb1` DEFAULT CHARACTER SET utf8
/*!*/;
# at 526
#170808 16:13:01 server id 1  end_log_pos 568 CRC32 0x81ff251d  GTID 0-1-99 ddl
/*!100001 SET @@session.gtid_seq_no=99*//*!*/;
# at 568
#170808 16:13:01 server id 1  end_log_pos 854 CRC32 0xd883daaf  Query   thread_id=13    exec_time=0     error_code=0
use `testdb1`/*!*/;
SET TIMESTAMP=1502179981/*!*/;
CREATE TABLE `think_user` (
  `user_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) NOT NULL COMMENT '用户姓名',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@contoso ~]#



查看1分钟内执行了那些SQL语句
[root@contoso ~]# mysqlbinlog --start-datetime="2017-08-08 16:13:01" --stop-datetime="2017-08-08 16:14:01" --database=testdb1 /var/log/mariadb/mariadb-log-bin.000015


接下来将这1分钟内提交的SQL再执行一遍，也就是指定时间区间恢复数据(部分数据恢复)
[root@contoso ~]# mysqlbinlog --start-datetime="2017-08-08 16:13:01" --stop-datetime="2017-08-08 16:14:01" --database=testdb1 /var/log/mariadb/mariadb-log-bin.000015 | mysql -uroot -p123456 -v testdb1



[root@contoso ~]# find / -name queries.log
/var/log/mariadb/queries.log

[root@contoso ~]# cat /var/log/mariadb/queries.log  --  再参考查询日志，可以把全部丢失的数据都恢复回来
...        ...      ...
...        ...      ...
170808 16:18:49    13 Query     XA START 'mariadb1027'
170808 16:19:19    13 Query     INSERT INTO think_user VALUES(1,'jason')
170808 16:19:54    13 Query     XA END 'mariadb1027'
170808 16:20:15    13 Query     XA PREPARE 'mariadb1027'
170808 16:21:02    13 Query     XA RECOVER
...        ...      ...
...        ...      ...