MySQL binary log 配置及使用简介
2019年03月01日
原文
mysql binlog相关内容,MySQL5.7.25,ubuntu 16.04
Mysql的binlog日志作用是用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录(对数据库的改动),对数据库的查询select或show等不会被binlog日志记录;主要用于数据库的主从复制以及增量恢复。本文关于binlog的讲解基于ubuntu16.04安装的MySQL 5.7.25 。
配置binlog
在mysql 5.7之后,已经没有my.cnf的模板。具体关于binlog的配置请自行搜索。
配置binlog的主要参数如下:
[mysqld] server-id = 1 log_bin = mysql-bin
检查binlog是否已经开启
登录mysql后,使用如下命令:
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.01 sec)
查看binlog名称
查看binlog名称的命令如下:
mysql> show binary logs;
结果如下:
+------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 177 | | mysql-bin.000002 | 462 | +------------------+-----------+ 2 rows in set (0.00 sec)
查看binlog具体内容
查看binlog具体内容的命令如下:
mysql> show binlog events in 'mysql-bin.000001';
结果如下:
+------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.25-log, Binlog ver: 4 | | mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000001 | 154 | Stop | 1 | 177 | | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 3 rows in set (0.00 sec)