-
-
哈哈哈哈哈哈
搜索到
1
篇与
二进制文件
的结果
-
MySQL二进制日志截取和恢复 作用数据恢复和主从配置开启二进制日志vim /etc/my.conf [mysqld] server-id=1 #（1~65535） log-bin=/var/lib/mysql/mysql-bin log_bin_index=/var/lib/mysql/mysql-bin.index binlog_format=row sync_binlog=1 //每次事务提交都立即刷写binlog到磁盘 mysql> show variables like '%log_bin%'; +---------------------------------+--------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/mysql-bin | | log_bin_index | /var/lib/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+--------------------------------+ 6 rows in set (0.00 sec)PS:binlog_format=(row、statement、mixed)statement：SBR,语句模式记录日志,做什么命令,记录什么命令. 可读性较强,对于范围操作日志量少,但是可能会出现记录不准确的情况：insert into xx values (1,'sa',now()). row ：RBR,行模式,数据行的变化。可读性较弱,对于范围操作日志大,不会出现记录错误.对高可用环境中的新特性要依赖于RBR(5.7版本默认) mixed ：MBR,混合模式查看二进制日志位置：mysql> show variables like '%log_bin%'; # flush logs; 每执行一次就多一个日志 +---------------------------------+--------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/mysql-bin | | log_bin_index | /var/lib/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+--------------------------------+ 6 rows in set (0.01 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 177 | | mysql-bin.000002 | 154 | +------------------+-----------+ 2 rows in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 177 | | mysql-bin.000002 | 201 | | mysql-bin.000003 | 154 | +------------------+-----------+ 3 rows in set (0.00 sec)查看现存的二进制日志show binary logs;查看二进制日志事件show binlog events in 'mysql-bin.000003' limit 5; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000003 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.34-log, Binlog ver: 4 | | mysql-bin.000003 | 123 | Previous_gtids | 1 | 154 | | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 2 rows in set (0.00 sec)在打印出来的信息中可以看到event事件的开始和结束号码，它可以方便我们从日志中截取想要的日志事件查看二进制日志内容[root@cs mysql]# mysqlbinlog mysql-bin.000003 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #210822 10:27:13 server id 1 end_log_pos 123 CRC32 0x98688dd8 Start: binlog v 4, server v 5.7.34-log created 210822 10:27:13 # Warning: this binlog is either in use or was not closed properly. BINLOG ' AbYhYQ8BAAAAdwAAAHsAAAABAAQANS43LjM0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AdiNaJg= '/*!*/; # at 123 #210822 10:27:13 server id 1 end_log_pos 154 CRC32 0x1ae7a1b6 Previous-GTIDs # [empty] 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*/;查看和分析binlogmysql> show binary logs; mysql> show master status ; mysql> show binlog events in 'binlog.000003'; [root@cs mysql]# mysqlbinlog mysql-bin.000003 [root@cs mysql]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000003模拟数据恢复mysql> flush logs; mysql> create database cs; mysql> use cs; Database changed mysql> create table t1 (id int); Query OK, 0 rows affected (0.05 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.01 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values(2); Query OK, 1 row affected (0.01 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values(3); Query OK, 1 row affected (0.01 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> drop cs; 开始恢复 mysql> show master status; #当前的二进制文件 +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 1364 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show binlog events in 'mysql-bin.000004'; #查看二进制日志事件（重要的是创建数据库和删除数据库的Pos值） +------------------+------+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000004 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.34-log, Binlog ver: 4 | | mysql-bin.000004 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000004 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 219 | Query | 1 | 307 | create database cs | | mysql-bin.000004 | 307 | Anonymous_Gtid | 1 | 372 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 372 | Query | 1 | 466 | use `cs`; create table t1 (id int) | | mysql-bin.000004 | 466 | Anonymous_Gtid | 1 | 531 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 531 | Query | 1 | 601 | BEGIN | | mysql-bin.000004 | 601 | Table_map | 1 | 644 | table_id: 115 (cs.t1) | | mysql-bin.000004 | 644 | Write_rows | 1 | 684 | table_id: 115 flags: STMT_END_F | | mysql-bin.000004 | 684 | Xid | 1 | 715 | COMMIT /* xid=349 */ | | mysql-bin.000004 | 715 | Anonymous_Gtid | 1 | 780 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 780 | Query | 1 | 850 | BEGIN | | mysql-bin.000004 | 850 | Table_map | 1 | 893 | table_id: 115 (cs.t1) | | mysql-bin.000004 | 893 | Write_rows | 1 | 933 | table_id: 115 flags: STMT_END_F | | mysql-bin.000004 | 933 | Xid | 1 | 964 | COMMIT /* xid=351 */ | | mysql-bin.000004 | 964 | Anonymous_Gtid | 1 | 1029 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 1029 | Query | 1 | 1099 | BEGIN | | mysql-bin.000004 | 1099 | Table_map | 1 | 1142 | table_id: 115 (cs.t1) | | mysql-bin.000004 | 1142 | Write_rows | 1 | 1182 | table_id: 115 flags: STMT_END_F | | mysql-bin.000004 | 1182 | Xid | 1 | 1213 | COMMIT /* xid=353 */ | | mysql-bin.000004 | 1213 | Anonymous_Gtid | 1 | 1278 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 1278 | Query | 1 | 1364 | drop database cs | +------------------+------+----------------+-----------+-------------+---------------------------------------+ 23 rows in set (0.00 sec) [root@cs mysql]# mysqlbinlog --start-position=219 --stop-position=1278 /var/lib/mysql/mysql-bin.000004 >/tmp/bin.sql mysql> set sql_log_bin=0; #下面操作不会被记录到二进制文件（慎用） Query OK, 0 rows affected (0.00 sec) mysql> source /tmp/bin.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Charset changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec) mysql> use cs; Database changed mysql> show tables; +--------------+ | Tables_in_cs | +--------------+ | t1 | +--------------+ 1 row in set (0.00 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec)
