MySQL
平臺:Linux
[mysqld]
server_id = 1
log_bin = /data/mysql/log/binary/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
select:需要讀取server端information_schema.COLUMNS表,獲取表結構的元信息,拼接成可視化的sql語句
super/replication client:兩個權限都可以,需要執行SHOW MASTER STATUS, 獲取server端的binlog列表
replication slave:通過BINLOG_DUMP協議獲取binlog內容的權限
mysql連接配置
解析模式
解析范圍控制
對象過濾
mysql> show global variables like binlog_format;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000107 | 120 |
+------------------+----------+
1 row in set (0.00 sec)
mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 2 | bb |
+------+------+
2 rows in set (0.00 sec)
mysql>
mysql> insert into tb1 values (3,cc);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1 values (4,dd);
Query OK, 1 row affected (0.00 sec)
mysql> update tb1 set name=new_aa where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from tb1 where id=2;
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+------+--------+
| id | name |
+------+--------+
| 1 | new_aa |
| 3 | cc |
| 4 | dd |
+------+--------+
3 rows in set (0.00 sec)
mysql>
[root@db_server_xuanzhi ~]#python binlog2sql.py -uroot -h127.0.0.1 -proot -dxuanzhi --start-file=mysql-bin.000107 > xuanzhi.sql
[root@db_server_xuanzhi ~]#cat xuanzhi.sql
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (3, cc); #start 4 end 290 time 2022-01-23 10:41:34
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (4, dd); #start 321 end 491 time 2022-01-23 10:41:38
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`=new_aa WHERE `id`=1 AND `name`=aa LIMIT 1; #start 522 end 705 time 2022-01-23 10:41:42
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`=bb LIMIT 1; #start 736 end 906 time 2022-01-23 10:41:50
[root@db_server_xuanzhi ~]#
[root@db_server_xuanzhi ~]#python binlog2sql.py -uroot -h127.0.0.1 -proot -dxuanzhi --start-file=mysql-bin.000107 -B > rollback_xuanzhi.sql
[root@db_server_xuanzhi ~]#cat rollback_xuanzhi.sql
INSERT INTO `xuanzhi`.`tb1`(`id`, `name`) VALUES (2, bb); #start 736 end 906 time 2022-01-23 10:41:50
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`=aa WHERE `id`=1 AND `name`=new_aa LIMIT 1; #start 522 end 705 time 2022-01-23 10:41:42
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=4 AND `name`=dd LIMIT 1; #start 321 end 491 time 2022-01-23 10:41:38
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=3 AND `name`=cc LIMIT 1; #start 4 end 290 time 2022-01-23 10:41:34
[root@db_server_xuanzhi ~]#
mysql> use xuanzhi
Database changed
mysql> source /tmp/rollback_xuanzhi.sql
文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。
轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/129634.html
摘要:基本上每個跟數據庫打交道的程序員當然也可能是你同事都會碰一個問題,誤操作后如何快速回滾比如,一張表,忘加限制條件,整張表都沒了。誤操作后,能快速回滾數據是非常重要的。登錄確認,數據回滾成功。所以,誤操作的話一般只能通過備份來恢復。 基本上每個跟數據庫打交道的程序員(當然也可能是你同事)都會碰一個問題,MySQL誤操作后如何快速回滾?比如,delete一張表,忘加限制條件,整張表都沒了。...
閱讀 1346·2023-01-11 13:20
閱讀 1684·2023-01-11 13:20
閱讀 1132·2023-01-11 13:20
閱讀 1858·2023-01-11 13:20
閱讀 4100·2023-01-11 13:20
閱讀 2704·2023-01-11 13:20
閱讀 1385·2023-01-11 13:20
閱讀 3597·2023-01-11 13:20