场景1：某一时刻某一schema误删数据（delete）
准备工作：
拟对rha_acct数据库中的表acct_deal_record做误删除操作
root@db 15:30: [rha_acct]> delete from acct_deal_record limit 10;
Query OK, 10 rows affected (0.01 sec)
误删除前表数据
root@db 15:20: [rha_acct]> select * from acct_deal_record limit 10;
+------+----------------------+----------------------+------------------+----------+-----------+-----------------------+-----------------------+----------+------------+-------------+----------------------------+------------------------+-----------------+----------------+----------------+---------------------------+--------------+-------------+----------------+----------+-------------+-----------+---------------------+-----------+---------------------+------------+
| N_ID | C_TRANS_CODE | C_PERSON_ID | C_ACCOUNT_CODE | C_APP_ID | D_AMOUNT | C_REQUEST_ID | C_ORDER_SEQID | C_DEALID | C_BIZ_TYPE | C_DIRECTION | C_ACCT_BAL_CODE | C_ACCOUNT_BALANCE_TYPE | C_CURRENCY_CODE | C_VOUCHER_CODE | C_TO_PERSON_ID | C_TO_ACCOUNT_BALANCE_TYPE | C_IS_REVERSE | C_DEAL_STAT | C_DEAL_MESSAGE | C_REMARK | C_IS_DELETE | C_CRT_CDE | T_CRT_TM | C_UPD_CDE | T_UPD_TM | C_COMMENTS |
+------+----------------------+----------------------+------------------+----------+-----------+-----------------------+-----------------------+----------+------------+-------------+----------------------------+------------------------+-----------------+----------------+----------------+---------------------------+--------------+-------------+----------------+----------+-------------+-----------+---------------------+-----------+---------------------+------------+
| 1 | 20191127025905000001 | 584044P831508L598784 | 1020191127000001 | 88 | 2000.0000 | 201911270259053924056 | 201911270259053924056 | | 2001 | 0 | 584044P831508L598784000001 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:05 | SYS | 2019-11-27 02:59:05 | NULL |
| 2 | 20191127025905000002 | X48732872327C8368768 | 1020191127000002 | 88 | 2000.0000 | 201911270259057124010 | 201911270259057124010 | | 2001 | 0 | X48732872327C8368768000002 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:05 | SYS | 2019-11-27 02:59:05 | NULL |
| 3 | 20191127025905000003 | 48732989806C2794W752 | 1020191127000003 | 88 | 2000.0000 | 201911270259059494372 | 201911270259059494372 | | 2001 | 0 | 48732989806C2794W752000003 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:05 | SYS | 2019-11-27 02:59:05 | NULL |
| 4 | 20191127025906000004 | 4A8733D2055623536640 | 1020191127000004 | 88 | 2000.0000 | 20191127025906107606 | 20191127025906107606 | | 2001 | 0 | 4A8733D2055623536640000004 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:06 | SYS | 2019-11-27 02:59:06 | NULL |
| 5 | 20191127025906000005 | T5282975761152081W92 | 1020191127000005 | 88 | 2000.0000 | 20191127025906732497 | 20191127025906732497 | | 2001 | 0 | T5282975761152081W92000005 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:06 | SYS | 2019-11-27 02:59:06 | NULL |
| 6 | 20191127025906000006 | 52E751420022188D8512 | 1020191127000006 | 88 | 2000.0000 | 201911270259061379268 | 201911270259061379268 | | 2001 | 0 | 52E751420022188D8512000006 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:06 | SYS | 2019-11-27 02:59:06 | NULL |
| 7 | 20191127025914000007 | 584044P831508L598784 | 1020191127000001 | 88 | 2000.0000 | 201911270259144084371 | 201911270259144084371 | | 2001 | 0 | 584044P831508L598784000001 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:14 | SYS | 2019-11-27 02:59:14 | NULL |
| 8 | 20191127025914000008 | X48732872327C8368768 | 1020191127000002 | 88 | 2000.0000 | 201911270259144732470 | 201911270259144732470 | | 2001 | 0 | X48732872327C8368768000002 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:14 | SYS | 2019-11-27 02:59:14 | NULL |
| 9 | 20191127025914000009 | 48732989806C2794W752 | 1020191127000003 | 88 | 2000.0000 | 201911270259145305256 | 201911270259145305256 | | 2001 | 0 | 48732989806C2794W752000003 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:14 | SYS | 2019-11-27 02:59:14 | NULL |
| 10 | 20191127025914000010 | 4A8733D2055623536640 | 1020191127000004 | 88 | 2000.0000 | 201911270259145849590 | 201911270259145849590 | | 2001 | 0 | 4A8733D2055623536640000004 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:14 | SYS | 2019-11-27 02:59:14 | NULL |
+------+----------------------+----------------------+------------------+----------+-----------+-----------------------+-----------------------+----------+------------+-------------+----------------------------+------------------------+-----------------+----------------+----------------+---------------------------+--------------+-------------+----------------+----------+-------------+-----------+---------------------+-----------+---------------------+------------+
误删除表数据后
root@db 15:31: [rha_acct]> select * from acct_deal_record limit 10;
+------+----------------------+--------------------------------+------------------+----------+-----------+-----------------------+-----------------------+----------+------------+-------------+--------------------------------------+------------------------+-----------------+----------------+----------------+---------------------------+--------------+-------------+----------------+----------+-------------+-----------+---------------------+-----------+---------------------+------------+
| N_ID | C_TRANS_CODE | C_PERSON_ID | C_ACCOUNT_CODE | C_APP_ID | D_AMOUNT | C_REQUEST_ID | C_ORDER_SEQID | C_DEALID | C_BIZ_TYPE | C_DIRECTION | C_ACCT_BAL_CODE | C_ACCOUNT_BALANCE_TYPE | C_CURRENCY_CODE | C_VOUCHER_CODE | C_TO_PERSON_ID | C_TO_ACCOUNT_BALANCE_TYPE | C_IS_REVERSE | C_DEAL_STAT | C_DEAL_MESSAGE | C_REMARK | C_IS_DELETE | C_CRT_CDE | T_CRT_TM | C_UPD_CDE | T_UPD_TM | C_COMMENTS |
+------+----------------------+--------------------------------+------------------+----------+-----------+-----------------------+-----------------------+----------+------------+-------------+--------------------------------------+------------------------+-----------------+----------------+----------------+---------------------------+--------------+-------------+----------------+----------+-------------+-----------+---------------------+-----------+---------------------+------------+
| 11 | 20191127025914000011 | T5282975761152081W92 | 1020191127000005 | 88 | 2000.0000 | 201911270259146364106 | 201911270259146364106 | | 2001 | 0 | T5282975761152081W92000005 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:14 | SYS | 2019-11-27 02:59:14 | NULL |
| 12 | 20191127025914000012 | 52E751420022188D8512 | 1020191127000006 | 88 | 2000.0000 | 201911270259147038640 | 201911270259147038640 | | 2001 | 0 | 52E751420022188D8512000006 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:14 | SYS | 2019-11-27 02:59:14 | NULL |
| 13 | 20191127040323000013 | X48732872327C8368768 | 1020191127000002 | 88 | 2000.0000 | 201911270403238779381 | 9191127020372 | | 2006 | 1 | X48732872327C8368768000002 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 04:03:23 | SYS | 2019-11-27 04:03:23 | NULL |
| 14 | 20191127040755000014 | 584044P831508L598784 | 1020191127000001 | 88 | 2000.0000 | 201911270407551249803 | 9191127020375 | | 2006 | 1 | 584044P831508L598784000001 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 04:07:55 | SYS | 2019-11-27 04:07:55 | NULL |
| 15 | 20191127041505000015 | X48732872327C8368768 | 1020191127000002 | 88 | 2000.0000 | 201911270415053361783 | 9191127020380 | | 2006 | 1 | X48732872327C8368768000002 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 04:15:05 | SYS | 2019-11-27 04:15:05 | NULL |
| 16 | 20191127042858000016 | 52E751420022188D8512 | 1020191127000006 | 88 | 2000.0000 | 201911270428583905910 | 9191127020383 | | 2006 | 1 | 52E751420022188D8512000006 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 04:28:58 | SYS | 2019-11-27 04:28:58 | NULL |
| 17 | 20191127201443000017 | 584044P831508L598784 | 1020191127000001 | 88 | 2000.0000 | 201911272014431622672 | 9191127020432 | | 2006 | 1 | 584044P831508L598784000001 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 20:14:43 | SYS | 2019-11-27 20:14:43 | NULL |
| 18 | 20191129110209000018 | H649Y7XP08094L45410406QIRJBNZ5 | 1020191129000007 | 88 | 2000.0000 | 201911291102099412241 | 201911291102099412241 | | 2001 | 0 | H649Y7XP08094L45410406QIRJBNZ5000007 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-29 11:02:09 | SYS | 2019-11-29 11:02:09 | NULL |
| 19 | 20191129110210000019 | 64JO99XGN17V18R36J6PK852VM7105 | 1020191129000008 | 88 | 2000.0000 | 20191129110210245735 | 20191129110210245735 | | 2001 | 0 | 64JO99XGN17V18R36J6PK852VM7105000008 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-29 11:02:10 | SYS | 2019-11-29 11:02:10 | NULL |
| 20 | 20191129110210000020 | 6WYEK4J9KRLJ9171843312271WR3M7 | 1020191129000009 | 88 | 2000.0000 | 20191129110210769906 | 20191129110210769906 | | 2001 | 0 | 6WYEK4J9KRLJ9171843312271WR3M7000009 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-29 11:02:10 | SYS | 2019-11-29 11:02:10 | NULL |
+------+----------------------+--------------------------------+------------------+----------+-----------+-----------------------+-----------------------+----------+------------+-------------+--------------------------------------+------------------------+-----------------+----------------+----------------+---------------------------+--------------+-------------+----------------+----------+-------------+-----------+---------------------+-----------+---------------------+------------+
恢复工作
1.查看delete操作的start position,利用bin2sql反向解析出对应的回滚语句。
root@db 16:05: [(none)]> root@db 16:05: [(none)]> show binlog events in 'binlog-mysql.000004' from 86888688\G;
*************************** 1. row ***************************
Log_name: binlog-mysql.000004
Pos: 86888688
Event_type: Query
Server_id: 3306110
End_log_pos: 86888764
Info: BEGIN
*************************** 2. row ***************************
Log_name: binlog-mysql.000004
Pos: 86888764
Event_type: Table_map
Server_id: 3306110
End_log_pos: 86888906
Info: table_id: 213 (rha_acct.acct_deal_record)
*************************** 3. row ***************************
Log_name: binlog-mysql.000004
Pos: 86888906
Event_type: Delete_rows
Server_id: 3306110
End_log_pos: 86890970
Info: table_id: 213 flags: STMT_END_F
*************************** 4. row ***************************
Log_name: binlog-mysql.000004
Pos: 86890970
Event_type: Xid
Server_id: 3306110
End_log_pos: 86891001
Info: COMMIT /* xid=3151 */
4 rows in set (0.00 sec)
2.利用误操作的大概时间点过滤出delete操作影响的行数，找出delete操作的start_postion(86888688),end_position(86890970)
python ./binlog2sql.py -uroot -p -d rha_acct -t acct_deal_record --start-file='binlog-mysql.000004' --start-datetime='2020-09-14 15:25:00' --stop-datetime='2020-09-14 15:32:00' > /tmp/rollback0914.sql
DELETE FROM `rha_acct`.`acct_deal_record` WHERE `C_TO_ACCOUNT_BALANCE_TYPE` IS NULL AND `C_DEAL_MESSAGE`='成功' AND `N_ID`=1 AND `T_CRT_TM`='2019-11-27 02:59:05' AND `C_UPD_CDE`='SYS' AND `C_CRT_CDE`='SYS' AND `C_DIRECTION`='0' AND `C_CURRENCY_CODE`='RHC' AND `C_IS_DELETE`='0' AND `T_UPD_TM`='2019-11-27 02:59:05' AND `C_DEAL_STAT`='1' AND `D_AMOUNT`=2000.0000 AND `C_ORDER_SEQID`='201911270259053924056' AND `C_COMMENTS` IS NULL AND `C_REQUEST_ID`='201911270259053924056' AND `C_ACCOUNT_CODE`='1020191127000001' AND `C_IS_REVERSE`='0' AND `C_DEALID`='' AND `C_BIZ_TYPE`='2001' AND `C_PERSON_ID`='584044P831508L598784' AND `C_APP_ID`='88' AND `C_REMARK` IS NULL AND `C_ACCOUNT_BALANCE_TYPE`='BLBS0001' AND `C_ACCT_BAL_CODE`='584044P831508L598784000001' AND `C_TO_PERSON_ID` IS NULL AND `C_TRANS_CODE`='20191127025905000001' AND `C_VOUCHER_CODE` IS NULL LIMIT 1; #start 86888688 end 86890970 time 2020-09-14 15:31:06
DELETE FROM `rha_acct`.`acct_deal_record` WHERE `C_TO_ACCOUNT_BALANCE_TYPE` IS NULL AND `C_DEAL_MESSAGE`='成功' AND `N_ID`=2 AND `T_CRT_TM`='2019-11-27 02:59:05' AND `C_UPD_CDE`='SYS' AND `C_CRT_CDE`='SYS' AND `C_DIRECTION`='0' AND `C_CURRENCY_CODE`='RHC' AND `C_IS_DELETE`='0' AND `T_UPD_TM`='2019-11-27 02:59:05' AND `C_DEAL_STAT`='1' AND `D_AMOUNT`=2000.0000 AND `C_ORDER_SEQID`='201911270259057124010' AND `C_COMMENTS` IS NULL AND `C_REQUEST_ID`='201911270259057124010' AND `C_ACCOUNT_CODE`='1020191127000002' AND `C_IS_REVERSE`='0' AND `C_DEALID`='' AND `C_BIZ_TYPE`='2001' AND `C_PERSON_ID`='X48732872327C8368768' AND `C_APP_ID`='88' AND `C_REMARK` IS NULL AND `C_ACCOUNT_BALANCE_TYPE`='BLBS0001' AND `C_ACCT_BAL_CODE`='X48732872327C8368768000002' AND `C_TO_PERSON_ID` IS NULL AND `C_TRANS_CODE`='20191127025905000002' AND `C_VOUCHER_CODE` IS NULL LIMIT 1; #start 86888688 end 86890970 time 2020-09-14 15:31:06
中间省略若干行....................................................................................................................................................
DELETE FROM `rha_acct`.`acct_deal_record` WHERE `C_TO_ACCOUNT_BALANCE_TYPE` IS NULL AND `C_DEAL_MESSAGE`='成功' AND `N_ID`=10 AND `T_CRT_TM`='2019-11-27 02:59:14' AND `C_UPD_CDE`='SYS' AND `C_CRT_CDE`='SYS' AND `C_DIRECTION`='0' AND `C_CURRENCY_CODE`='RHC' AND `C_IS_DELETE`='0' AND `T_UPD_TM`='2019-11-27 02:59:14' AND `C_DEAL_STAT`='1' AND `D_AMOUNT`=2000.0000 AND `C_ORDER_SEQID`='201911270259145849590' AND `C_COMMENTS` IS NULL AND `C_REQUEST_ID`='201911270259145849590' AND `C_ACCOUNT_CODE`='1020191127000004' AND `C_IS_REVERSE`='0' AND `C_DEALID`='' AND `C_BIZ_TYPE`='2001' AND `C_PERSON_ID`='4A8733D2055623536640' AND `C_APP_ID`='88' AND `C_REMARK` IS NULL AND `C_ACCOUNT_BALANCE_TYPE`='BLBS0001' AND `C_ACCT_BAL_CODE`='4A8733D2055623536640000004' AND `C_TO_PERSON_ID` IS NULL AND `C_TRANS_CODE`='20191127025914000010' AND `C_VOUCHER_CODE` IS NULL LIMIT 1; #start 86888688 end 86890970 time 2020-09-14 15:31:06
3.最后再利用前面的语句加上一个 -B 参数，解析出对应的回滚语句
python ./binlog2sql.py -uroot -p -d rha_acct -t acct_deal_record --start-file='binlog-mysql.000004' --start-pos=86888688 --stop-pos=86890970 -B > /tmp/rollback091402.sql
4.检查对应的回滚语句
INSERT INTO `rha_acct`.`acct_deal_record`(`C_TO_ACCOUNT_BALANCE_TYPE`, `C_DEAL_MESSAGE`, `N_ID`, `T_CRT_TM`, `C_UPD_CDE`, `C_CRT_CDE`, `C_DIRECTION`, `C_CURRENCY_CODE`, `C_IS_DELETE`, `T_UPD_TM`, `C_DEAL_STAT`, `D_AMOUNT`, `C_ORDER_SEQID`, `C_COMMENTS`, `C_REQUEST_ID`, `C_ACCOUNT_CODE`, `C_IS_REVERSE`, `C_DEALID`, `C_BIZ_TYPE`, `C_PERSON_ID`, `C_APP_ID`, `C_REMARK`, `C_ACCOUNT_BALANCE_TYPE`, `C_ACCT_BAL_CODE`, `C_TO_PERSON_ID`, `C_TRANS_CODE`, `C_VOUCHER_CODE`) VALUES (NULL, '成功', 10, '2019-11-27 02:59:14', 'SYS', 'SYS', '0', 'RHC', '0', '2019-11-27 02:59:14', '1', 2000.0000, '201911270259145849590', NULL, '201911270259145849590', '1020191127000004', '0', '', '2001', '4A8733D2055623536640', '88', NULL, 'BLBS0001', '4A8733D2055623536640000004', NULL, '20191127025914000010', NULL); #start 86888688 end 86890970 time 2020-09-14 15:31:06
INSERT INTO `rha_acct`.`acct_deal_record`(`C_TO_ACCOUNT_BALANCE_TYPE`, `C_DEAL_MESSAGE`, `N_ID`, `T_CRT_TM`, `C_UPD_CDE`, `C_CRT_CDE`, `C_DIRECTION`, `C_CURRENCY_CODE`, `C_IS_DELETE`, `T_UPD_TM`, `C_DEAL_STAT`, `D_AMOUNT`, `C_ORDER_SEQID`, `C_COMMENTS`, `C_REQUEST_ID`, `C_ACCOUNT_CODE`, `C_IS_REVERSE`, `C_DEALID`, `C_BIZ_TYPE`, `C_PERSON_ID`, `C_APP_ID`, `C_REMARK`, `C_ACCOUNT_BALANCE_TYPE`, `C_ACCT_BAL_CODE`, `C_TO_PERSON_ID`, `C_TRANS_CODE`, `C_VOUCHER_CODE`) VALUES (NULL, '成功', 9, '2019-11-27 02:59:14', 'SYS', 'SYS', '0', 'RHC', '0', '2019-11-27 02:59:14', '1', 2000.0000, '201911270259145305256', NULL, '201911270259145305256', '1020191127000003', '0', '', '2001', '48732989806C2794W752', '88', NULL, 'BLBS0001', '48732989806C2794W752000003', NULL, '20191127025914000009', NULL); #start 86888688 end 86890970 time 2020-09-14 15:31:06
..................................................................................................................................................................
INSERT INTO `rha_acct`.`acct_deal_record`(`C_TO_ACCOUNT_BALANCE_TYPE`, `C_DEAL_MESSAGE`, `N_ID`, `T_CRT_TM`, `C_UPD_CDE`, `C_CRT_CDE`, `C_DIRECTION`, `C_CURRENCY_CODE`, `C_IS_DELETE`, `T_UPD_TM`, `C_DEAL_STAT`, `D_AMOUNT`, `C_ORDER_SEQID`, `C_COMMENTS`, `C_REQUEST_ID`, `C_ACCOUNT_CODE`, `C_IS_REVERSE`, `C_DEALID`, `C_BIZ_TYPE`, `C_PERSON_ID`, `C_APP_ID`, `C_REMARK`, `C_ACCOUNT_BALANCE_TYPE`, `C_ACCT_BAL_CODE`, `C_TO_PERSON_ID`, `C_TRANS_CODE`, `C_VOUCHER_CODE`) VALUES (NULL, '成功', 1, '2019-11-27 02:59:05', 'SYS', 'SYS', '0', 'RHC', '0', '2019-11-27 02:59:05', '1', 2000.0000, '201911270259053924056', NULL, '201911270259053924056', '1020191127000001', '0', '', '2001', '584044P831508L598784', '88', NULL, 'BLBS0001', '584044P831508L598784000001', NULL, '20191127025905000001', NULL); #start 86888688 end 86890970 time 2020-09-14 15:31:06
5.确认无误后，执行生成的回滚语句。
mysql -uroot -p < rollback091402.sql
root@db 16:14: [rha_acct]> select * from acct_deal_record limit 10;
+------+----------------------+----------------------+------------------+----------+-----------+-----------------------+-----------------------+----------+------------+-------------+----------------------------+------------------------+-----------------+----------------+----------------+---------------------------+--------------+-------------+----------------+----------+-------------+-----------+---------------------+-----------+---------------------+------------+
| N_ID | C_TRANS_CODE | C_PERSON_ID | C_ACCOUNT_CODE | C_APP_ID | D_AMOUNT | C_REQUEST_ID | C_ORDER_SEQID | C_DEALID | C_BIZ_TYPE | C_DIRECTION | C_ACCT_BAL_CODE | C_ACCOUNT_BALANCE_TYPE | C_CURRENCY_CODE | C_VOUCHER_CODE | C_TO_PERSON_ID | C_TO_ACCOUNT_BALANCE_TYPE | C_IS_REVERSE | C_DEAL_STAT | C_DEAL_MESSAGE | C_REMARK | C_IS_DELETE | C_CRT_CDE | T_CRT_TM | C_UPD_CDE | T_UPD_TM | C_COMMENTS |
+------+----------------------+----------------------+------------------+----------+-----------+-----------------------+-----------------------+----------+------------+-------------+----------------------------+------------------------+-----------------+----------------+----------------+---------------------------+--------------+-------------+----------------+----------+-------------+-----------+---------------------+-----------+---------------------+------------+
| 1 | 20191127025905000001 | 584044P831508L598784 | 1020191127000001 | 88 | 2000.0000 | 201911270259053924056 | 201911270259053924056 | | 2001 | 0 | 584044P831508L598784000001 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:05 | SYS | 2019-11-27 02:59:05 | NULL |
| 2 | 20191127025905000002 | X48732872327C8368768 | 1020191127000002 | 88 | 2000.0000 | 201911270259057124010 | 201911270259057124010 | | 2001 | 0 | X48732872327C8368768000002 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:05 | SYS | 2019-11-27 02:59:05 | NULL |
| 3 | 20191127025905000003 | 48732989806C2794W752 | 1020191127000003 | 88 | 2000.0000 | 201911270259059494372 | 201911270259059494372 | | 2001 | 0 | 48732989806C2794W752000003 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:05 | SYS | 2019-11-27 02:59:05 | NULL |
| 4 | 20191127025906000004 | 4A8733D2055623536640 | 1020191127000004 | 88 | 2000.0000 | 20191127025906107606 | 20191127025906107606 | | 2001 | 0 | 4A8733D2055623536640000004 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:06 | SYS | 2019-11-27 02:59:06 | NULL |
| 5 | 20191127025906000005 | T5282975761152081W92 | 1020191127000005 | 88 | 2000.0000 | 20191127025906732497 | 20191127025906732497 | | 2001 | 0 | T5282975761152081W92000005 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:06 | SYS | 2019-11-27 02:59:06 | NULL |
| 6 | 20191127025906000006 | 52E751420022188D8512 | 1020191127000006 | 88 | 2000.0000 | 201911270259061379268 | 201911270259061379268 | | 2001 | 0 | 52E751420022188D8512000006 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:06 | SYS | 2019-11-27 02:59:06 | NULL |
| 7 | 20191127025914000007 | 584044P831508L598784 | 1020191127000001 | 88 | 2000.0000 | 201911270259144084371 | 201911270259144084371 | | 2001 | 0 | 584044P831508L598784000001 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:14 | SYS | 2019-11-27 02:59:14 | NULL |
| 8 | 20191127025914000008 | X48732872327C8368768 | 1020191127000002 | 88 | 2000.0000 | 201911270259144732470 | 201911270259144732470 | | 2001 | 0 | X48732872327C8368768000002 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:14 | SYS | 2019-11-27 02:59:14 | NULL |
| 9 | 20191127025914000009 | 48732989806C2794W752 | 1020191127000003 | 88 | 2000.0000 | 201911270259145305256 | 201911270259145305256 | | 2001 | 0 | 48732989806C2794W752000003 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:14 | SYS | 2019-11-27 02:59:14 | NULL |
| 10 | 20191127025914000010 | 4A8733D2055623536640 | 1020191127000004 | 88 | 2000.0000 | 201911270259145849590 | 201911270259145849590 | | 2001 | 0 | 4A8733D2055623536640000004 | BLBS0001 | RHC | NULL | NULL | NULL | 0 | 1 | 成功 | NULL | 0 | SYS | 2019-11-27 02:59:14 | SYS | 2019-11-27 02:59:14 | NULL |
+------+----------------------+----------------------+------------------+----------+-----------+-----------------------+-----------------------+----------+------------+-------------+----------------------------+------------------------+-----------------+----------------+----------------+---------------------------+--------------+-------------+----------------+----------+-------------+-----------+---------------------+-----------+---------------------+------------+
场景1：某一时刻某一schema误更新数据（update）
准备工作：
拟对rha_payment数据库中的bank_info的机构编码bank_code做更新操作，由于update操作没有附带where语句导致对所有记录都做了误更新。
root@db 14:25: [rha_payment]> update bank_info t set t.bank_code='IKBC';
误更新前的记录：
+----+-----------------------+-----------------------+-------------------+--------+-----------+
| id | bank_name | bank_abb_name | bank_code | is_use | is_delete |
+----+-----------------------+-----------------------+-------------------+--------+-----------+
| 1 | 工商银行 | 工商银行 | ICBC | 1 | 0 |
| 2 | 农业银行 | 农业银行 | ABC | 1 | 0 |
| 3 | 中国银行 | 中国银行 | BOC | 1 | 0 |
| 4 | 建设银行 | 建设银行 | CBC | 1 | 0 |
| 5 | 交通银行 | 交通银行 | BCM | 1 | 0 |
| 6 | 招商银行 | 招商银行 | CMB | 1 | 0 |
| 7 | 邮政储蓄银行 | 邮政储蓄银行 | PSBC | 1 | 0 |
| 8 | 广发银行 | 广发银行 | GDB | 1 | 0 |
| 9 | 兴业银行 | 兴业银行 | CIB | 1 | 0 |
| 10 | 光大银行 | 光大银行 | CEB | 1 | 0 |
| 11 | 平安银行 | 平安银行 | SPABANK | 1 | 0 |
| 12 | 民生银行 | 民生银行 | CMBC | 1 | 0 |
| 13 | 中信银行 | 中信银行 | CITIC | 1 | 0 |
| 14 | 浦发银行 | 浦发银行 | SPDB | 1 | 0 |
| 15 | 华夏银行 | 华夏银行 | HXBANK | 1 | 0 |
| 16 | 北京银行 | 北京银行 | BJBANK | 1 | 0 |
| 17 | 上海银行 | 上海银行 | SHBANK | 1 | 0 |
| 18 | 广州银行 | 广州银行 | GCB | 1 | 0 |
| 19 | 渤海银行 | 渤海银行 | BOHAIB | 1 | 0 |
| 20 | 恒丰银行 | 恒丰银行 | EGBANK | 1 | 0 |
| 21 | 广州农商行 | 广州农商行 | GRCB | 1 | 0 |
| 22 | 宁夏银行 | 宁夏银行 | NXBANK | 1 | 0 |
| 23 | 顺德农商行 | 顺德农商行 | SDEB | 1 | 0 |
| 24 | 东莞银行 | 东莞银行 | BOD | 1 | 0 |
| 25 | 东莞农商行 | 东莞农商行 | DRCBCL | 1 | 0 |
| 26 | 常熟农商行 | 常熟农商行 | CSRCB | 1 | 0 |
| 27 | 四川天府银行 | 四川天府银行 | SICHUANTIANFUBANK | 1 | 0 |
| 28 | 江西信用联合社 | 江西信用联合社 | JXRCU | 1 | 0 |
| 29 | 长安银行 | 长安银行 | CHANG’ANBANK | 1 | 0 |
| 30 | 西安银行 | 西安银行 | XABANK | 1 | 0 |
+----+-----------------------+-----------------------+-------------------+--------+-----------+
误更新后的数据:
+----+-----------------------+-----------------------+-----------+--------+-----------+
| id | bank_name | bank_abb_name | bank_code | is_use | is_delete |
+----+-----------------------+-----------------------+-----------+--------+-----------+
| 1 | 工商银行 | 工商银行 | IKBC | 1 | 0 |
| 2 | 农业银行 | 农业银行 | IKBC | 1 | 0 |
| 3 | 中国银行 | 中国银行 | IKBC | 1 | 0 |
| 4 | 建设银行 | 建设银行 | IKBC | 1 | 0 |
| 5 | 交通银行 | 交通银行 | IKBC | 1 | 0 |
| 6 | 招商银行 | 招商银行 | IKBC | 1 | 0 |
| 7 | 邮政储蓄银行 | 邮政储蓄银行 | IKBC | 1 | 0 |
| 8 | 广发银行 | 广发银行 | IKBC | 1 | 0 |
| 9 | 兴业银行 | 兴业银行 | IKBC | 1 | 0 |
| 10 | 光大银行 | 光大银行 | IKBC | 1 | 0 |
| 11 | 平安银行 | 平安银行 | IKBC | 1 | 0 |
| 12 | 民生银行 | 民生银行 | IKBC | 1 | 0 |
| 13 | 中信银行 | 中信银行 | IKBC | 1 | 0 |
| 14 | 浦发银行 | 浦发银行 | IKBC | 1 | 0 |
| 15 | 华夏银行 | 华夏银行 | IKBC | 1 | 0 |
| 16 | 北京银行 | 北京银行 | IKBC | 1 | 0 |
| 17 | 上海银行 | 上海银行 | IKBC | 1 | 0 |
| 18 | 广州银行 | 广州银行 | IKBC | 1 | 0 |
| 19 | 渤海银行 | 渤海银行 | IKBC | 1 | 0 |
| 20 | 恒丰银行 | 恒丰银行 | IKBC | 1 | 0 |
| 21 | 广州农商行 | 广州农商行 | IKBC | 1 | 0 |
| 22 | 宁夏银行 | 宁夏银行 | IKBC | 1 | 0 |
| 23 | 顺德农商行 | 顺德农商行 | IKBC | 1 | 0 |
| 24 | 东莞银行 | 东莞银行 | IKBC | 1 | 0 |
| 25 | 东莞农商行 | 东莞农商行 | IKBC | 1 | 0 |
| 26 | 常熟农商行 | 常熟农商行 | IKBC | 1 | 0 |
| 27 | 四川天府银行 | 四川天府银行 | IKBC | 1 | 0 |
| 28 | 江西信用联合社 | 江西信用联合社 | IKBC | 1 | 0 |
| 29 | 长安银行 | 长安银行 | IKBC | 1 | 0 |
| 30 | 西安银行 | 西安银行 | IKBC | 1 | 0 |
+----+-----------------------+-----------------------+-----------+--------+-----------+
恢复工作：
1.找到当前最新的binlog，根据误操作的大概时间点过滤出update事务的start position与stop position
root@db 14:43: [rha_payment]> show binlog events in 'binlog-mysql.000004' from 86895817;
+---------------------+----------+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+----------+-------------+-----------+-------------+---------------------------------------+
| binlog-mysql.000004 | 86895817 | Query | 3306110 | 86895896 | BEGIN |
| binlog-mysql.000004 | 86895896 | Table_map | 3306110 | 86895970 | table_id: 219 (rha_payment.bank_info) |
| binlog-mysql.000004 | 86895970 | Update_rows | 3306110 | 86898574 | table_id: 219 flags: STMT_END_F |
| binlog-mysql.000004 | 86898574 | Xid | 3306110 | 86898605 | COMMIT /* xid=3422 */ |
+---------------------+----------+-------------+-----------+-------------+---------------------------------------+
[root@localhost binlog2sql]# python ./binlog2sql.py -uroot -p'密码不能告诉你' -d rha_payment -t bank_info --start-file='binlog-mysql.000004' --start-datetime='2020-09-16 14:23:00' --stop-datetime='2020-09-16 14:25:00'
UPDATE `rha_payment`.`bank_info` SET `bank_name`='工商银行', `is_delete`='0', `bank_abb_name`='工商银行', `bank_code`='IKBC', `is_use`='1', `id`=1 WHERE `bank_name`='工商银行' AND `is_delete`='0' AND `bank_abb_name`='工商银行' AND `bank_code`='ICBC' AND `is_use`='1' AND `id`=1 LIMIT 1; #start 86895817 end 86898574 time 2020-09-16 14:24:44
UPDATE `rha_payment`.`bank_info` SET `bank_name`='农业银行', `is_delete`='0', `bank_abb_name`='农业银行', `bank_code`='IKBC', `is_use`='1', `id`=2 WHERE `bank_name`='农业银行' AND `is_delete`='0' AND `bank_abb_name`='农业银行' AND `bank_code`='ABC' AND `is_use`='1' AND `id`=2 LIMIT 1; #start 86895817 end 86898574 time 2020-09-16 14:24:44
.........................................中间省略若干行..........................................................
UPDATE `rha_payment`.`bank_info` SET `bank_name`='西安银行', `is_delete`='0', `bank_abb_name`='西安银行', `bank_code`='IKBC', `is_use`='1', `id`=30 WHERE `bank_name`='西安银行' AND `is_delete`='0' AND `bank_abb_name`='西安银行' AND `bank_code`='XABANK' AND `is_use`='1' AND `id`=30 LIMIT 1; #start 86895817 end 86898574 time 2020-09-16 14:24:44
2.根据上一步确定误操作update操作的start position与stop position，精确回滚update操作,并重定向到一个文件中。
[root@localhost binlog2sql]# python ./binlog2sql.py -uroot -p'密码不能告诉你' -d rha_payment -t bank_info --start-file='binlog-mysql.000004' --start-position='86895817' --stop-position='86898574' -B > /tmp/rollback0916.sql
UPDATE `rha_payment`.`bank_info` SET `bank_name`='西安银行', `is_delete`='0', `bank_abb_name`='西安银行', `bank_code`='XABANK', `is_use`='1', `id`=30 WHERE `bank_name`='西安银行' AND `is_delete`='0' AND `bank_abb_name`='西安银行' AND `bank_code`='IKBC' AND `is_use`='1' AND `id`=30 LIMIT 1; #start 86895817 end 86898574 time 2020-09-16 14:24:44
UPDATE `rha_payment`.`bank_info` SET `bank_name`='长安银行', `is_delete`='0', `bank_abb_name`='长安银行', `bank_code`='CHANG’ANBANK', `is_use`='1', `id`=29 WHERE `bank_name`='长安银行' AND `is_delete`='0' AND `bank_abb_name`='长安银行' AND `bank_code`='IKBC' AND `is_use`='1' AND `id`=29 LIMIT 1; #start 86895817 end 86898574 time 2020-09-16 14:24:44
.........................................中间省略若干行..............................................................
UPDATE `rha_payment`.`bank_info` SET `bank_name`='兴业银行', `is_delete`='0', `bank_abb_name`='兴业银行', `bank_code`='CIB', `is_use`='1', `id`=9 WHERE `bank_name`='兴业银行' AND `is_delete`='0' AND `bank_abb_name`='兴业银行' AND `bank_code`='IKBC' AND `is_use`='1' AND `id`=9 LIMIT 1; #start 86895817 end 86898574 time 2020-09-16 14:24:44
3.执行回滚语句
[root@localhost tmp]# mysql -uroot -p --default-character-set=utf8 rha_payment < rollback0916.sql
场景3：某一时刻误drop某一shema数据库
采用物理备份+binlog恢复
- 模拟误删除某数据库，登陆数据库查看最新的binlog，drop操作前最后的stop-position为334004 ，所以我们从最近的一次物理备份为基线开始恢复。
drop database ipcis_mdm;
| binlog-mysql.000002 | 332671 | Xid | 3306110 | 332702 | COMMIT /* xid=1234 */ |
| binlog-mysql.000002 | 332702 | Query | 3306110 | 332779 | BEGIN |
| binlog-mysql.000002 | 332779 | Table_map | 3306110 | 332947 | table_id: 132 (ipcis_mdm.web_mdm_user) |
| binlog-mysql.000002 | 332947 | Write_rows | 3306110 | 333331 | table_id: 132 flags: STMT_END_F |
| binlog-mysql.000002 | 333331 | Xid | 3306110 | 333362 | COMMIT /* xid=1235 */ |
| binlog-mysql.000002 | 333362 | Query | 3306110 | 333439 | BEGIN |
| binlog-mysql.000002 | 333439 | Table_map | 3306110 | 333607 | table_id: 132 (ipcis_mdm.web_mdm_user) |
| binlog-mysql.000002 | 333607 | Write_rows | 3306110 | 334004 | table_id: 132 flags: STMT_END_F |
| binlog-mysql.000002 | 334004 | Xid | 3306110 | 334035 | COMMIT /* xid=1236 */ |
| binlog-mysql.000002 | 334035 | Query | 3306110 | 334142 | drop database ipcis_mdm
- Prepare 备份文件
[root@localhost bak]# xtrabackup --prepare --target-dir=/opt/bak/2020-08-31_12-51-27
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=3306110 --redo-log-version=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/opt/bak/2020-08-31_12-51-27
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 281665045
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 281665740
200901 16:09:36 completed OK!
- 关闭mysql数据库服务器，清空datadir目录
[root@localhost 2020-08-31_12-51-27]# ps -ef| grep mysql
root 12993 1 0 8月17 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
mysql 13223 12993 0 8月17 ? 00:13:04 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/log/error.log --pid-file=/data/mysql/tmp/mysql.pid --socket=/data/mysql/tmp/mysql.sock --port=3306
root 22065 21210 0 16:13 pts/4 00:00:00 grep --color=auto mysql
[root@localhost 2020-08-31_12-51-27]# mysqladmin -uroot -p shutdown
Enter password:
[root@localhost 2020-08-31_12-51-27]# ps -ef| grep mysql
root 22072 21210 0 16:16 pts/4 00:00:00 grep --color=auto mysql
[root@localhost 2020-08-31_12-51-27]# rm -rf /data/mysql/data
- 将备份文件拷贝到数据库的datadir目录，并重新更改对应目录权限
[root@localhost mysql]# xtrabackup --copy-back --target-dir=/opt/bak/2020-08-31_12-51-27/ --datadir=/data/mysql/dataxtrabackup: recognized server arguments: --datadir=/data/mysql/data --server-id=3306110 --log_bin=/data/mysql/log/binlog-mysql --datadir=/data/mysql/data
xtrabackup: recognized client arguments: --port=3306 --socket=/data/mysql/tmp/mysql.sock --copy-back=1 --target-dir=/opt/bak/2020-08-31_12-51-27/
xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
200901 16:20:51 [01] Copying ib_logfile0 to /data/mysql/data/ib_logfile0
200901 16:20:51 [01] ...done
200901 16:20:51 [01] Copying ib_logfile1 to /data/mysql/data/ib_logfile1
200901 16:20:51 [01] ...done
200901 16:20:51 [01] Copying ibdata1 to /data/mysql/data/ibdata1
200901 16:20:52 [01] ...done
200901 16:20:52 [01] Copying ./mysql/innodb_table_stats.ibd to /data/mysql/data/mysql/innodb_table_stats.ibd
200901 16:20:52 [01] ...done
200901 16:20:52 [01] Copying ./mysql/innodb_index_stats.ibd to /data/mysql/data/mysql/innodb_index_stats.ibd
200901 16:20:52 [01] ...done
200901 16:20:56 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/data/xtrabackup_binlog_pos_innodb
200901 16:20:56 [01] ...done
200901 16:20:56 [01] Copying ./xtrabackup_master_key_id to /data/mysql/data/xtrabackup_master_key_id
200901 16:20:56 [01] ...done
200901 16:20:56 [01] Copying ./ibtmp1 to /data/mysql/data/ibtmp1
200901 16:20:56 [01] ...done
200901 16:20:56 completed OK!
[root@localhost mysql]# chown -R mysql:mysql data/
[root@localhost mysql]# ll
总用量 0
drwxr-xr-x 6 mysql mysql 232 9月 1 16:20 data
drwxr-xr-x 2 mysql mysql 103 8月 17 20:51 log
drwxr-xr-x 2 mysql mysql 6 9月 1 16:16 tmp
[root@localhost mysql]# cd data
[root@localhost data]# ll
总用量 188440
-rw-r----- 1 mysql mysql 79691776 9月 1 16:20 ibdata1
-rw-r----- 1 mysql mysql 50331648 9月 1 16:20 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 9月 1 16:20 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 9月 1 16:20 ibtmp1
drwxr-x--- 2 mysql mysql 4096 9月 1 16:20 ipcis_mdm
drwxr-x--- 2 mysql mysql 4096 9月 1 16:20 mysql
drwxr-x--- 2 mysql mysql 4096 9月 1 16:20 performance_schema
drwxr-x--- 2 mysql mysql 20 9月 1 16:20 test
-rw-r----- 1 mysql mysql 25 9月 1 16:20 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 473 9月 1 16:20 xtrabackup_info
-rw-r----- 1 mysql mysql 1 9月 1 16:20 xtrabackup_master_key_id
- 重启mysql服务
[root@localhost log]# mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --datadir=/data/mysql/data &
[1] 22092
[root@localhost log]# 200901 16:29:49 mysqld_safe Logging to '/data/mysql/log/error.log'.
200901 16:29:49 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data
[root@localhost log]# ps -ef| grep mysql
root 22092 21210 0 16:29 pts/4 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cn --user=mysql --datadir=/data/mysql/data
mysql 22342 22092 7 16:29 pts/4 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/sr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/msql/log/error.log --pid-file=/data/mysql/tmp/mysql.pid --socket=/data/mysql/tmp/mysql.sock --port=3306
root 22365 21210 0 16:29 pts/4 00:00:00 grep --color=auto mysql
- 利用上次备份结束时的position与drop操作钱的postion做闪回恢复
[root@localhost ~]# mysqlbinlog --start-position=3219 --stop-position=334004 binlog-mysql.000002 | mysql -uroot -p'密码不能给你知道' ipcis_mdm
Warning: Using a password on the command line interface can be insecure.
root@db 16:30: [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ipcis_mdm |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
root@db 16:30: [(none)]> use ipcis_mdm;
Database changed
root@db 16:30: [ipcis_mdm]> select count(*) from web_mdm_user;
+----------+
| count(*) |
+----------+
| 22335 |
+----------+
1 row in set (0.04 sec)
root@db 16:31: [ipcis_mdm]> show master logs;
+---------------------+-----------+
| Log_name | File_size |
+---------------------+-----------+
| binlog-mysql.000001 | 4115 |
| binlog-mysql.000002 | 334165 |
| binlog-mysql.000003 | 120 |
+---------------------+-----------+
3 rows in set (0.00 sec)
root@db 16:35: [ipcis_mdm]> show binlog events in 'binlog-mysql.000003';
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
| binlog-mysql.000003 | 4 | Format_desc | 3306110 | 120 | Server ver: 5.6.34-log, Binlog ver: 4 |
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.01 sec)
root@db 16:36: [ipcis_mdm]> relect count(*) from web_mdm_user;
+----------+
| count(*) |
+----------+
| 22334 |
+----------+
1 row in set (0.01 sec)
网友评论