mysql-日志备份

1.binlog

用于数据恢复，
用于数据复制。

mysql>  show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |  开关
| log_bin_basename                | /var/lib/mysql/binlog       | // 存放路径
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | ON                          |// 
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |//变更sql记录下来
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)

2. 查看binlog日志

查看当前的二进制日志文件列表及大小。指令如下：

mysql> SHOW BINARY LOGS;
+--------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+--------------------+-----------+-----------+
| atguigu-bin.000001 | 156 | No |
+--------------------+-----------+-----------+
1 行于数据集 (0.02 秒)

mysqlbinlog

-v 伪sql显示 --base64-output=DECODE-ROWS

mysqlbinlog -v "/var/lib/mysql/binlog/atguigu-bin.000002"



# 前面的命令同时显示binlog格式的语句，使用如下命令不显示它
mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/atguigu-bin.000002"

show binlog events

上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

IN ‘log_name’：指定要查询的binlog文件名（不指定就是第一个binlog文件）
FROM pos：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
LIMIT [offset]：偏移量(不指定就是 0 )
row_count :查询总条数（不指定就是所有行）

binlog 格式

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 行于数据集 (0.02 秒)

除此之外，binlog还有 2 种格式，分别是 Statement 和 Mixed

Statement
记录变更的sql 比如 update user set … 这一条语句
每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
Row
记录 update user set。。。。
每一行数据改成什么了。
5.1.5版本的MySQL才开始支持row level 的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点：row level 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。
Mixed

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。

binlog format 说明

statement 模式

优点
因为 binlog 中只写入了变更操作的 statements，所以日志量将会很小；
缺点
使用一些函数比如now()，user()，uuid()等，无法被复制。

row 模式

优点
所有的数据变更操作，都可以正确的被复制。
缺点
需要记录更多的日志信息。

3 binlog 使用场景

二进制日志可以通过数据库的全量备份和二进制日志中保存的增量信息，完成数据库的无损失恢复。但是，如果遇到数据量大、数据库和数据表很多（比如分库分表的应用）的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止位置不容易管理。
在这种情况下，一个有效的解决办法是配置主从数据库服务器，甚至是一主多从的架构，把二进制日志文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常等问题。

4 再谈二进制日志

4.1 binlog 写入机制 binlogfile

binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。
我们可以通过binlog_cache_size参数控制单个线程binlog cache大，如果存储内容超过了这个参数，就要
暂存到磁盘(Swap)。binlog日志刷盘流程如下:

上图的write，是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快。
上图的fsync，才是将数据持久化到磁盘的操作

4.2 控制写入时机

sync_binlog =0

write和fsync的时机，可以由参数sync_binlog控制，默认是 0 。
为 0 的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。
虽然性能得到提升，但是机器宕机，page cache里面的binglog 会丢失。如下图：

sync_binlog=1

为了安全起见，可以设置为 1 ，表示每次提交事务都会执行fsync，就如同 redo log 刷盘流程 一样。

sync_binlog > N(n>1)

最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。

在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样的，如果机器宕机，会丢失最近N个事务的binlog日志。

5.binlog redolog 对比

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生的。
- 记录对数据页的内容进行更改
而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层
- 类似于记录sql这条语句
虽然它们都属于持久化的保证，但是则重点不同。
- redo log让InnoDB存储引擎拥有了崩溃恢复能力。
- binlog保证了MySQL集群架构的数据一致性。