二进制日志(binary log)

1、二进制日志(binary log)介绍

  二进制日志(binary log):记录数据库里的数据被修改。

  (insert,update,delete,create,drop,alter)的相关语句;

  作用:增量数据恢复和主从复制;

2、二进制日志(binary log)调整

1

2

3

4

5

6

7

8

9

10

11

[root@db01-51 ~]# mysql -S /data/3306/mysql.sock -e "show variables like '%log_bin%';"

+---------------------------------+-----------------------------+

| Variable_name                   | Value                       |

+---------------------------------+-----------------------------+

| log_bin                         | ON                          |记录binlog开关

| log_bin_basename                | /data/3306/oldboy-bin       |

| log_bin_index                   | /data/3306/oldboy-bin.index |binlog文件

| log_bin_trust_function_creators | OFF                         |

| log_bin_use_v1_row_events       | OFF                         |

| sql_log_bin                     | ON                          |临时不记录binlog开关(增量恢复)某个时间点某些语句不记录binlog

+---------------------------------+-----------------------------+

临时不记录binlog(增量恢复)做主从同步的时候关闭会出现错误

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

mysql> set session sql_log_bin = OFF;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%log_bin%';

+---------------------------------+-----------------------------+

| Variable_name                   | Value                       |

+---------------------------------+-----------------------------+

| log_bin                         | ON                          |

| log_bin_basename                | /data/3306/oldboy-bin       |

| log_bin_index                   | /data/3306/oldboy-bin.index |

| log_bin_trust_function_creators | OFF                         |

| log_bin_use_v1_row_events       | OFF                         |

| sql_log_bin                     | OFF                         |

+---------------------------------+-----------------------------+

6 rows in set (0.00 sec)

 binlog文件切割的条件:

  a.数据库重启自动切割binlog为新文件。

  b.执行mysqldump -F或mysqladmin flush-logs切割binlog为新文件。

  c.binlog文件达到1.1G,自动切割binlog为新文件。

  d.人为配置切割及调整

删除binlog日志文件方法:

  1.设置参数自动删除

1

2

3

4

5

6

7

8

[root@db01-51 ~]# grep expire_logs_days /data/3306/my.cnf

expire_logs_days = 7

[root@db01-51 ~]# mysql -S /data/3307/mysql.sock -e "show variables like 'expire_logs_days%';"

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| expire_logs_days | 7 |

+------------------+-------+

  2.从头删除到指定的文件位置

1

2

3

4

5

[root@db01-51 ~]# mysql -S /data/3307/mysql.sock -e "purge binary logs to 'xusx-bin.000002';"

[root@db01-51 ~]# ll /data/3307/data/

drwx------ 2 mysql mysql      4096 Mar 19 08:11 xusx

-rw-rw---- 1 mysql mysql    654021 Mar 20 18:17 xusx-bin.000002  ##--->01删到02

-rw-rw---- 1 mysql mysql        32 Mar 20 23:44 xusx-bin.index

  3.删除指定的时间

1

mysql> PURGE MASTER LOGS BEFORE '2017-03-20 00:00:00';

  4.删除所有,重置

1

2

mysql> reset master;

Query OK, 0 rows affected (0.03 sec)

MySQL binlog三种模式

二进制日志log-bin作用:

  1、以二进制形式记录更改数据库的SQL语句(insert,update,delete,create,drop,alter等)。

  2、用于MySQL主从复制。

  3、增量数据备份及恢复

Row Level:

  日志中会记录成每一行数据被修改的情况,然后在slave端再对相同的数据进行修改。

  优点:在row level模式下,bin-log中可以不记录执行的sql语句的上下文相关的信息,仅仅只需要记录哪一条记录被修改了,修改成什么样。所以row level的日志内容会非常清楚的记录下每一行数据修改的细节,非常容易理解,而且不会出现某些特定情况下的存储过程或function,以及trigger的调用和触发无法被正确复制问题。

Statement Level(默认语句模式)

  每一条被修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。

  优点:statement level下的优点首先就是解决了row level下的缺点,不需要记录每一行数据的变化,减少bin-log日志量,节约磁盘IO,提高性能。因为他只需要记录在Master上所有执行的语句的细节,以及执行语句时候的上下文的信息。

Mixed:

  实际上就是前两种模式的结合。在Mixed模式下,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。新版本中的Statment level还是和以前一样,仅仅记录执行的语句。新版本的MySQL中对row level模式也被做了优化,并不是所有的修改都会以row level来记录像遇到表结构变更的时候就会以statement模式来记录,如果sql语句确实就是update或者delete等修改数据的语句,那么还是会记录所有行的变更。

总结:

Statement level (默认语句模式)按照执行的SQL语句记录

  不需要记录每行数据的变化,减少bin-log日志量,节约磁盘IO,提高性能

  缺点:主从复制特殊功能会导致无法正常复制。存储过程、触发器、函数。

Row Level

  按行记录日志,细致,主从复制容易保持一致

  缺点:数据量大

mixed:智能模式

  数据量大选择Statement Level,特殊可能引起数据不一致就选行模式

  企业场景如何选择binlog的模式

1、互联网公司、使用MySQL的功能相对少(存储过程、触发器、函数)

  选择默认的语句模式,Statement Level(默认)

2、公司如果用到使用MySQL的特殊功能(存储过程、触发器、函数)

  选择Mixed模式

3、公司如果用到使用MySQL的特殊功能(存储过程、触发器、函数),又希望数据最大化,此时最好Row level模式 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

mysql> show variables like '%binlog_format%';

+---------------+-----------+

| Variable_name | Value     |

+---------------+-----------+

| binlog_format | STATEMENT |

+---------------+-----------+

1 row in set (0.00 sec)

mysql> set global binlog_format = 'ROW';

Query OK, 0 rows affected (0.00 sec)

[root@db01-51 ~]# grep binlog_format /data/3306/my.cnf

binlog_format = 'ROW'

mysql> show variables like '%binlog_format%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | ROW   |

+---------------+-------+

1 row in set (0.00 sec)

修改3306中的数据

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

mysql> select from test;

+----+------+

| id | name |

+----+------+

|  1 | a    |

|  2 | b    |

+----+------+

2 rows in set (0.00 sec)

mysql> update test set name='kk';

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

mysql> select from test;

+----+------+

| id | name |

+----+------+

|  1 | kk   |

|  2 | kk   |

+----+------+

2 rows in set (0.00 sec)

查看binlog日志文件

[root@mysql-db03 ~]# mysqlbinlog --help|grep rows
'decode-rows' decodes row events into commented

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

[root@db01-51 3306]# mysqlbinlog --base64-output='decode-rows' -v oldboy-bin.000002

# at 904

#170321  1:17:07 server id 6  end_log_pos 970 CRC32 0xb53c660d  Update_rows: table id 70 flags: STMT_END_F

### UPDATE `xusx`.`test`

### WHERE

###   @1=1

###   @2='a'

### SET

###   @1=1

###   @2='kk'

### UPDATE `xusx`.`test`

### WHERE

###   @1=2

###   @2='b'

### SET

###   @1=2

###   @2='kk'

# at 970

上一篇:go-mysql-elasticsearch安装部署


下一篇:【spring bean】spring中bean的懒加载和depends-on属性设置