使用gtid方式截取binlog日志恢复数据库
1查看有多少个事务
3号文件里有372个事务
mysql> show master status;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File 3号文件 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set 本机一共运行了372个gtid信息 |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000003 | 264292 | | | d5b1bdc8-4d73-11eb-acba-fa270000fffa:1-372 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)
2查看事物id
每一个事务全局都有一个gtid唯一编号，有了这个编号就可以随意截取我们想要的日志进行数据恢复
mysql> show binlog events in 'mysql-bin.000003';
+------------------+----------+--------------+------------------+--------------------------------------------+
| log_name | Pos | Event_type | Server_id | End_log_pos | Info
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000003 | 263506 | Gtid | 6 | 263571 | SET @@SESSION.GTID_NEXT= 'd5b1bdc8-4d73-11eb-acba-fa270000fffa:368' |
| mysql-bin.000003 | 263571 | Query | 6 | 263658 | FLUSH PRIVILEGES |
| mysql-bin.000003 | 263658 | Gtid | 6 | 263723 | SET @@SESSION.GTID_NEXT= 'd5b1bdc8-4d73-11eb-acba-fa270000fffa:369' |
| mysql-bin.000003 | 263723 | Query | 6 | 263810 | FLUSH PRIVILEGES |
| mysql-bin.000003 | 263810 | Gtid | 6 | 263875 | SET @@SESSION.GTID_NEXT= 'd5b1bdc8-4d73-11eb-acba-fa270000fffa:370' |
| mysql-bin.000003 | 263875 | Query | 6 | 263962 | FLUSH PRIVILEGES |
| mysql-bin.000003 | 263962 | Gtid | 6 | 264027 | SET @@SESSION.GTID_NEXT= 'd5b1bdc8-4d73-11eb-acba-fa270000fffa:371' |
| mysql-bin.000003 | 264027 | Query | 6 | 264127 | use `mysql`; DROP FUNCTION IF EXISTS sys_exec |
| mysql-bin.000003 | 264127 | Gtid | 6 | 264192 | SET @@SESSION.GTID_NEXT= 'd5b1bdc8-4d73-11eb-acba-fa270000fffa:372' |
| mysql-bin.000003 | 264192 | Query | 6 | 264292 | use `mysql`; DROP FUNCTION IF EXISTS sys_exec |
+
3利用gtid截取binlog日志（把/data/binlog/mysql-bin.000003中gtid为1-370的事物进行一个截取到文件/tmp/gtid.sql里）
gtid有一个特性叫幂等性，即：恢复的时候，首席先要检查数据的幂等性，遇见了重复的数据就跳过，因此我们在截取binlog日志的时候要加一个--skip-gtids参数，让恢复的时候使用新的gtid，让执行语句忽略掉旧的gtid。
[root@instance-r5y0pf5d ~]# mysqlbinlog --skip-gtids --include-gtids='d5b1bdc8-4d73-11eb-acba-fa270000fffa:1-370' /data/binlog/mysql-bin.000003 >/tmp/gtids.sql
4登陆数据库将sql_log_bin置为0后使用gtid.sql文件进行恢复数据
[root@instance-r5y0pf5d ~]# mysql -uroot -p123456
mysql> set sql_log_bin=0; #临时关闭日志，因为要数据恢复，不想产生额外的日志
mysql> source /tmp/gtids.sql #恢复数据
5如果要跳过某些gtid不截取需要使用参数--exclude-gtids=' '(例如跳过3和5)
截取binlog文件里的gtid为1-370的日志，但不包括3和5这两个事务的gtid，如何截取？
[root@instance-r5y0pf5d ~]# mysqlbinlog --skip-gtids --include-gtids='d5b1bdc8-4d73-11eb-acba-fa270000fffa:1-370' --exclude-gtids='
d5b1bdc8-4d73-11eb-acba-fa270000fffa:3,
d5b1bdc8-4d73-11eb-acba-fa270000fffa:5
' /data/binlog/mysql-bin.000003 >/tmp/gtids.sql
5日志管理：
binlog日志需要定期备份和删除，备份是为了容灾，删除是为了删除冗余，节省磁盘空间，一般自动清理日志的时间，企业建议至少2个全备周期+1，比如说每周你做一次全备，那么自动清理日志的时间设置成15天比较合适
注意下列方法重启mysql后失效，如果要永久性的配置binlog过期时间需要使用mysql的配置文件。
mysql> select @@binlog_expire_logs_seconds; #查询binlog日志过期清理的天数
+------------------------------+
| @@binlog_expire_logs_seconds |
+------------------------------+
| 0 |
+------------------------------+
1 row in set (0.00 sec)
mysql> set global binlog_expire_logs_seconds=8; #设置过期清理binlog日志的天数
Query OK, 0 rows affected (0.00 sec)
mysql> select @@binlog_expire_logs_seconds; #再次查询过期清理日志的天数
+------------------------------+
| @@binlog_expire_logs_seconds |
+------------------------------+
| 8 |
+------------------------------+
1 row in set (0.00 sec)
mysql>
如果要永久设置binlog日志的过期时间需要在配置文件my.cnf里添加参数binlog_expire_logs_seconds=15
如果mysql在初始化时，你没有在配置文件my.cnf里添加这个日志过期删除参数，而你又不想冒险在生产环境下直接编辑my,cnf
那么你可以手动清理日志：
清除3天前的binlog日志：purge binary logs before now() - interval 3 day;
删除到mysql-bin.000003这个binlog文件为止：purge binary logs to 'mysql-bin.000003';
删除所有binlog，从000001开始计数：mysql> reset master;(注意，此动作非常危险，如果是主从复制，数据库必崩)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 81307 |
| mysql-bin.000003 | 265105 |
+------------------+-----------+
3 rows in set (0.00 sec)
mysql> purge binary logs to 'mysql-bin.000003';
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000003 | 265105 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> reset master; #危险动作主从配置的mysql，必崩
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> exit
Bye
[root@instance-r5y0pf5d ~]# cd /data/binlog/
[root@instance-r5y0pf5d /data/binlog]# ll
total 8
-rw-r----- 1 mysql mysql 154 Feb 1 10:48 mysql-bin.000001
-rw-r----- 1 mysql mysql 30 Feb 1 10:48 mysql-bin.index
[root@instance-r5y0pf5d /data/binlog]#
mysql> select @@expire_logs_days; #日志自动清理天数查询
mysql> set global expire_logs_days=15; #临时设置binlog全备周期为2个周期+1，超出这个周期的日志自动清理。
如果你不小心rm掉了binlog日志造成mysql无法启动的处理办法是：
1在my.cnf里关掉binlog日志
2关闭mysql
3删除掉所有的binlog日志
4启动mysql
查看日志文件的最大值：
一个日志太大的话，分析起来比较麻烦，所以dba会经常性的执行flush logs，生成一个新的重新开始的日志：
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 155 | No |
+------------------+-----------+-----------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 202 | No |
| mysql-bin.000002 | 155 | No |
+------------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql>
日志滚动还有如下方法：
此方法适合定时刷新，但不要太频繁的刷新，物极必反!
[root@instance-atfaffll ~]# ll /data/binlog
total 12
-rw-r----- 1 mysql mysql 202 Jul 5 14:09 mysql-bin.000001
-rw-r----- 1 mysql mysql 155 Jul 5 14:09 mysql-bin.000002
-rw-r----- 1 mysql mysql 60 Jul 5 14:09 mysql-bin.index
[root@instance-atfaffll ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@instance-atfaffll ~]# ll /data/binlog
total 12
-rw-r----- 1 mysql mysql 202 Jul 5 14:14 mysql-bin.000002
-rw-r----- 1 mysql mysql 155 Jul 5 14:14 mysql-bin.000003
-rw-r----- 1 mysql mysql 60 Jul 5 14:14 mysql-bin.index
[root@instance-atfaffll ~]#
默认值是1G，也就是说文件到达1g后，系统就会切换一个新的日志文件，这样有利于后期查看日子，一般建议设置小一点，例如128M
1073741824=1048576*1024
1024/8=128
也就是说：
1073741824/8=134217728(128M)
[root@instance-atfaffll ~]# vim /etc/my.cnf
[mysqld]
max_binlog_size=128M
[root@instance-atfaffll ~]# mysql -uroot -p123456
mysql> show variables like '%max_binlog_size%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| max_binlog_size | 134217728 |
+-----------------+-----------+
1 row in set (0.01 sec)
mysql>
日志默认大小1g如下：
mysql> show variables like '%max_binlog_size%';
mysql> show variables like '%max_binlog_size%';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)
浙公网安备 33010602011771号