mysqlbinlog恢复bin-log数据
Binlog日志即binary log,是二进制日志文件,有两个作用,一个是增量备份,另一个是主从复制,即从节点同步主节点数据时获取的即是bin-log,也可以通过bin-log日志来进行本机数据恢复。
1、可以登录mysql客户端查看bin-log有没有开启:
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.02 sec)
log_bin为ON时,binlog即开启,为OFF,未开启。以上已开启
2、开启mysql binlog日志:
进入mysql主配置文件(vim /etc/my.cnf),在mysqld模块下增加以下参数
server-id = 1(单个节点id)
log-bin= /var/lib/mysql/mysql-bin(位置一般和mysql库文件所在位置一样)
expire_logs_days= 10(表示此日志保存时间为10天;在mysql 8的版本中已经不建议使用此参数)
3、binlog日志包括两类文件;第一个是二进制索引文件(后缀为.index),第二个为日志文件(后缀名称为.0000*),记录所有的DML(除SELECT)及DDL语句事件
[root@TestCentos7 ~]# ls /var/lib/mysql/mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock
4、查看binlog日志列表:show master logs;
mysql>show master logs;+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 2412 | No |
| mysql-bin.000002 | 445 | No |
+------------------+-----------+-----------+
2 rows in set (0.00 sec)
5、查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值:show master status;
mysql>show master status;+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000002 | 445 | | | c9fba9e2-db3b-11eb-81d4-000c298d8da1:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
注意:如果mysql没有开启GTID的话,不会有pos结束点的值,可以使用 show variables like '%gtid_mode%'; 检查是否开启。
6、flush logs 刷新日志,生成一个新编号的binlog文件:
mysql>flush logs;
Query OK,0 rows affected (0.00sec)
mysql>show master logs;+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------