binlog数据恢复 日志备份

# 1. 查看是否开启

show variables like 'log_bin';
 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

# 2. 开启
a. 在 xx.cnf 中配置
    log_error=mysql_error.log
    
    binlog_format           = MIXED  // binlog日志格式，mysql默认采用statement，建议使用mixed
    log-bin                 = /data/mysql/mysql-bin.log    // binlog日志文件
    expire_logs_days        = 7                           // binlog过期清理时间
    max_binlog_size         = 100m                       // binlog每个日志文件大小
    binlog_cache_size       = 4m                        // binlog缓存大小
    max_binlog_cache_size   = 512m                     // 最大binlog缓存大小
    
b. 命令
    set global log_bin=mysql_bin
    
# 注意
    请注意，每次重启MySQL服务也会生成一个新的二进制日志文件，相当于二进制日志切换。
    切换二进制日志时，你会看到这些number会不断递增。
    另外，除了这些二进制日志文件外，你会看到还生成了一个mysql_bin_log.index的文件，
    这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

# 3. 二进制日志删除
    # 查看日志清单
        show binary logs;
    # 删除某个日志之前的所有二进制日志文件
        purge binary logs to 'mysql_bin_log.000002';
    # 清除某个时间点以前的二进制日志文件
        purge binary logs before '2017-03-10 10:10:00';
    # 清除7天前的二进制日志文件
        purge master logs before date_sub( now( ), interval 7 day);
    # 也可以设置expire_logs_days参数，设置自动清理，其默认值为0,表示不启用过期自动删除功能。
    # 如果启用了自动清理功能，表示超出此天数的二进制日志文件将被自动删除，
    # 自动删除工作通常发生在MySQL启动时或FLUSH日志时。
    
# 4. 查看二进制内容  character-set-server = utf8         a. 使用show binlog events方式可以获取当前以及指定binlog的日志，不适宜提取大量日志
    SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
    
    b. 命令查看  需要参数解码：--base64-output=decode-rows
        ./bin/mysqlbinlog ./mysql-bin.000003
        ./bin/mysqlbinlog --no-defaults ./mysql-bin.000003 > a.sql
        
##### 生成新的日志
    flush logs;
    
###### 日志使用数据恢复
    --skip-gtids=true
    
    # 指定查看某个库
    mysqlbinlog ./mysql-bin.xxx -d database_name > a.sql
    
    # 过滤sql语句
    cat a.sql | grep -v "#"  | tr "\r\n" " " | tr ";" "\n" | grep -iE "create" | sed "s/\/\*\!\*\//;/g" > t.sql
    
    # 常用参数选项解释：
    --start-position=875 起始pos点
    --stop-position=954 结束pos点
    --start-datetime="2016-9-25 22:01:08" 起始时间点
    --stop-datetime="2019-9-25 22:09:46" 结束时间点
    --database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)
    --base64-output=decode-rows 如果binlog为row，需要参数解码：--base64-output=decode-rows -v

    # 恢复
    1. 从指定一个日志恢复
    mysqlbinlog ./mysql-bin.000003 --skip-gtids=true | mysql -uroot -p -P3307
    
    2. 指定节点恢复
    mysqlbinlog ./mysql-bin.000004 --start-position=251 --stop-position=381 --skip-gtids=true | mysql -uroot -p -P3307
    
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000004 |   4 | Format_desc    |         2 |         123 | Server ver: 5.7.18-log, Binlog ver: 4                              |
| mysql-bin.000004 | 190 | Gtid           |         2 |         251 | SET @@SESSION.GTID_NEXT= 'fb3f6789-4209-11e8-b23e-525400b13db7:19' |
| mysql-bin.000004 | 251 | Query          |         2 |         381 | CREATE DATABASE `test` /*!40100 COLLATE 'utf8_general_ci' */       |
| mysql-bin.000004 | 381 | Gtid           |         2 |         442 | SET @@SESSION.GTID_NEXT= 'fb3f6789-4209-11e8-b23e-525400b13db7:20' |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+


###### 访问远程binlog
    mysqlbinlog -uroot -p111111 -h192.168.5.120 -P3307 --read-from-remote-server mysql-bin.000001##### 从远程备份到本地    mysqlbinlog --read-from-remote-server --raw --host=192.168.5.120 --port=3307 --user=root --password=xxxxx --stop-never mysql-bin.000001