mysql binlog分析

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.19-log MySQL Community Server (GPL)


Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


root@192.168.15.100 [(none)]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000050 |      194 |              |                  | 8170836d-8e48-11e7-ac68-000c29b48f84:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)


root@192.168.15.100 [(none)]>show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000043 |       501 |
| mysql-bin.000044 |       217 |
| mysql-bin.000045 |       870 |
| mysql-bin.000046 |       217 |
| mysql-bin.000047 |       217 |
| mysql-bin.000048 |       217 |
| mysql-bin.000049 |       217 |
| mysql-bin.000050 |       194 |
+------------------+-----------+
8 rows in set (0.00 sec)


root@192.168.15.100 [(none)]>show binlog events in 'mysql-bin.000050';
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                     |
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
| mysql-bin.000050 |   4 | Format_desc    |   1003306 |         123 | Server ver: 5.7.19-log, Binlog ver: 4    |
| mysql-bin.000050 | 123 | Previous_gtids |   1003306 |         194 | 8170836d-8e48-11e7-ac68-000c29b48f84:1-3 |
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
2 rows in set (0.00 sec)


root@192.168.15.100 [(none)]>
root@192.168.15.100 [(none)]>select * from tcyang.tab_skip;
+------+
| id   |
+------+
|    2 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
19 rows in set (0.01 sec)


xbackup 全备数据库


root@192.168.15.100 [(none)]>delete from tcyang.tab_skip where id=10;
Query OK, 2 rows affected (2.01 sec)


root@192.168.15.100 [(none)]>select * from tcyang.tab_skip;
+------+
| id   |
+------+
|    2 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
17 rows in set (0.00 sec)


root@192.168.15.100 [(none)]>
root@192.168.15.100 [(none)]>show binlog events in 'mysql-bin.000050';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000050 |   4 | Format_desc    |   1003306 |         123 | Server ver: 5.7.19-log, Binlog ver: 4                             |
| mysql-bin.000050 | 123 | Previous_gtids |   1003306 |         194 | 8170836d-8e48-11e7-ac68-000c29b48f84:1-3                          |
| mysql-bin.000050 | 194 | Gtid           |   1003306 |         259 | SET @@SESSION.GTID_NEXT= '49eff248-d83b-11e7-bae8-000c29b48f84:1' |
| mysql-bin.000050 | 259 | Query          |   1003306 |         327 | BEGIN                                                             |
| mysql-bin.000050 | 327 | Rows_query     |   1003306 |         390 | # delete from tcyang.tab_skip where id=10                         |
| mysql-bin.000050 | 390 | Table_map      |   1003306 |         443 | table_id: 237 (tcyang.tab_skip)                                   |
| mysql-bin.000050 | 443 | Delete_rows    |   1003306 |         488 | table_id: 237 flags: STMT_END_F                                   |
| mysql-bin.000050 | 488 | Xid            |   1003306 |         519 | COMMIT /* xid=42 */                                               |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
8 rows in set (0.01 sec)


root@192.168.15.100 [(none)]>
root@192.168.15.100 [(none)]>show variables like '%datadir%';
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| datadir       | /data/mysql/mysql3306/data/ |
+---------------+-----------------------------+
1 row in set (0.00 sec)


root@192.168.15.100 [(none)]>
root@192.168.15.100 [(none)]>flush logs;
Query OK, 0 rows affected (0.01 sec)


root@192.168.15.100 [(none)]>show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000043 |       501 |
| mysql-bin.000044 |       217 |
| mysql-bin.000045 |       870 |
| mysql-bin.000046 |       217 |
| mysql-bin.000047 |       217 |
| mysql-bin.000048 |       217 |
| mysql-bin.000049 |       217 |
| mysql-bin.000050 |       566 |
| mysql-bin.000051 |       234 |
+------------------+-----------+
9 rows in set (0.00 sec)


root@192.168.15.100 [(none)]>
root@192.168.15.100 [(none)]>delete from tcyang.tab_skip where id=9;
Query OK, 2 rows affected (0.00 sec)


root@192.168.15.100 [(none)]>show binlog events in 'mysql-bin.000051';
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                             |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------+
| mysql-bin.000051 |   4 | Format_desc    |   1003306 |         123 | Server ver: 5.7.19-log, Binlog ver: 4                                            |
| mysql-bin.000051 | 123 | Previous_gtids |   1003306 |         234 | 49eff248-d83b-11e7-bae8-000c29b48f84:1,
8170836d-8e48-11e7-ac68-000c29b48f84:1-3 |
| mysql-bin.000051 | 234 | Gtid           |   1003306 |         299 | SET @@SESSION.GTID_NEXT= '49eff248-d83b-11e7-bae8-000c29b48f84:2'                |
| mysql-bin.000051 | 299 | Query          |   1003306 |         367 | BEGIN                                                                            |
| mysql-bin.000051 | 367 | Rows_query     |   1003306 |         429 | # delete from tcyang.tab_skip where id=9                                         |
| mysql-bin.000051 | 429 | Table_map      |   1003306 |         482 | table_id: 237 (tcyang.tab_skip)                                                  |
| mysql-bin.000051 | 482 | Delete_rows    |   1003306 |         527 | table_id: 237 flags: STMT_END_F                                                  |
| mysql-bin.000051 | 527 | Xid            |   1003306 |         558 | COMMIT /* xid=48 */                                                              |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------+
8 rows in set (0.00 sec)


root@192.168.15.100 [(none)]>




开始恢复


innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --apply-log /tmp/2017-12-11_23-48-15


innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --copy-back /tmp/2017-12-11_23-48-15




[root@vm-test1 2017-12-11_23-48-15]# cat xtrabackup_info
uuid = ba2fb272-de8a-11e7-a33e-000c29b48f84
name = 
tool_name = innobackupex
tool_command = --defaults-file=/etc/my.cnf --host=192.168.15.100 --port=3306 --user=root --password=... /tmp/
tool_version = 2.4.8
ibbackup_version = 2.4.8
server_version = 5.7.19-log
start_time = 2017-12-11 23:48:15
end_time = 2017-12-11 23:48:26
lock_time = 0
binlog_pos = filename 'mysql-bin.000050', position '194', GTID of the last change '8170836d-8e48-11e7-ac68-000c29b48f84:1-3'
innodb_from_lsn = 0
innodb_to_lsn = 502055295
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
[root@vm-test1 2017-12-11_23-48-15]# cat xtrabackup_binlog_info
mysql-bin.000050        194     8170836d-8e48-11e7-ac68-000c29b48f84:1-3
[root@vm-test1 2017-12-11_23-48-15]# 


利用innobackupex结合全备和全备以后的binlog可以把mysql恢复到指定的时间点
查看data目录下xtrabackup_binlog_info文件中记录的GTID：
[root@vm-test1 2017-12-11_23-48-15]# more xtrabackup_binlog_info 
mysql-bin.000050        194     8170836d-8e48-11e7-ac68-000c29b48f84:1-3
[root@vm-test1 2017-12-11_23-48-15]# 


http://imysql.com/2017/02/26/mysql-tools-mysql-binlog-flashback.shtml


http://www.linuxidc.com/Linux/2016-01/128006.htm


http://www.cnblogs.com/billyxp/p/3460682.html




########################################################################################################


--查看binlog文件
--1
/usr/local/mysql/bin/mysqlbinlog -vv --base64-output=DECODE-ROWS /data/mysql/mysql3306/logs/mysql-bin.000050 
--2
/usr/local/mysql/bin/mysqlbinlog --no-defaults -vv --base64-output=DECODE-ROWS --skip-gtids /data/mysql/mysql3306/logs/mysql-bin.000050
--执行恢复
/usr/local/mysql/bin/mysqlbinlog --no-defaults --skip-gtids /data/mysql/mysql3306/logs/mysql-bin.000051 |mysql -h192.168.15.100 -P3306 -uroot -p123456


time /usr/local/mysql/bin/mysqlbinlog -vv /data/mysql/mysql3306/logs/mysql-bin.000050 |mysql -h192.168.15.100 -P3306 -uroot -p123456


time /usr/local/mysql/bin/mysqlbinlog /data/mysql/mysql3306/logs/mysql-bin.000050 --start-position=194|mysql -u root -p123456 --socket=/tmp/mysql3306.sock
 
show binary logs;
show master status;




-我们找出的position为1260，跳过1260之前的继续追加binlog  
SHELL> mysqlbinlog /data/inst3606/log/bin/inst3606bin.000001 --start-position=1260 \  
|mysql -urobin -pxxx -P3606 -S /tmp/mysql3606.sock  


--如果我们需要继续恢复后面的事务，我们可以找出truncate前后位置，然后跳过这个position  
SHELL> mysqlbinlog /data/inst3606/log/bin/inst3606bin.000001 --start-datetime="2014-12-25 11:53:54"|grep truncate -A5  


/usr/local/mysql/bin/mysqlbinlog /data/mysql/mysql3306/logs/mysql-bin.000050 >50.sql
pt-query-digest --type binlog   50.sql
/usr/local/mysql/bin/mysqlbinlog /data/mysql/mysql3306/logs/mysql-bin.000051 >51.sql




[root@vm-test1 ~]# /usr/local/mysql/bin/mysqlbinlog -vv  /data/mysql/mysql3306/logs/mysql-bin.000050 --base64-output=DECODE-ROWS
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#171211 19:54:49 server id 1003306  end_log_pos 123 CRC32 0xe184f597    Start: binlog v 4, server v 5.7.19-log created 171211 19:54:49 at startup
ROLLBACK/*!*/;
# at 123
#171211 19:54:49 server id 1003306  end_log_pos 194 CRC32 0xcc4d1166    Previous-GTIDs
# 8170836d-8e48-11e7-ac68-000c29b48f84:1-3
# at 194
#171211 23:50:54 server id 1003306  end_log_pos 259 CRC32 0x326cf9bf    GTID    last_committed=0        sequence_number=1       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '49eff248-d83b-11e7-bae8-000c29b48f84:1'/*!*/;
# at 259
#171211 23:50:54 server id 1003306  end_log_pos 327 CRC32 0x8ad1dc50    Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1513007454/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 327
#171211 23:50:54 server id 1003306  end_log_pos 390 CRC32 0xc2c1e527    Rows_query
# delete from tcyang.tab_skip where id=10
# at 390
#171211 23:50:54 server id 1003306  end_log_pos 443 CRC32 0xc9bb37e5    Table_map: `tcyang`.`tab_skip` mapped to number 237
# at 443
#171211 23:50:54 server id 1003306  end_log_pos 488 CRC32 0x1a69c0c5    Delete_rows: table id 237 flags: STMT_END_F
### DELETE FROM `tcyang`.`tab_skip`
### WHERE
###   @1=10 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `tcyang`.`tab_skip`
### WHERE
###   @1=10 /* INT meta=0 nullable=1 is_null=0 */
# at 488
#171211 23:50:54 server id 1003306  end_log_pos 519 CRC32 0x3d433e43    Xid = 42
COMMIT/*!*/;
# at 519
#171211 23:57:17 server id 1003306  end_log_pos 566 CRC32 0xb79086b0    Rotate to mysql-bin.000051  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@vm-test1 ~]#


SELECT from_unixtime('1390727015');




more 50.sql |grep  --ignore-case -E 'insert|update|delete' -A2 -B2|grep tab_skip > toocms_job_log1.sql


执行sed -i 's/)$/);/g' toocms_job_log1.sql，在每句insert语句后加一个;号，不然执行时会报错。