作用
数据恢复和主从配置
开启二进制日志
代码语言：javascript复制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)
代码语言：javascript复制statement：SBR,语句模式记录日志,做什么命令,记录什么命令.
可读性较强,对于范围操作日志量少,但是可能会出现记录不准确的情况：insert into xx values (1,'sa',now()).
row ：RBR,行模式,数据行的变化。可读性较弱,对于范围操作日志大,不会出现记录错误.对高可用环境中的新特性要依赖于RBR(5.7版本默认)
mixed ：MBR,混合模式查看二进制日志位置：
代码语言：javascript复制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)查看现存的二进制日志
代码语言：javascript复制show binary logs;查看二进制日志事件
代码语言：javascript复制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事件的开始和结束号码，它可以方便我们从日志中截取想要的日志事件
查看二进制日志内容
代码语言：javascript复制[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*/;查看和分析binlog
代码语言：javascript复制mysql> 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模拟
数据恢复
代码语言：javascript复制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)
