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语句后加一个;号,不然执行时会报错。
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语句后加一个;号,不然执行时会报错。