众所周知,binlog日志对于mysql数据库来说是十分重要的。在数据丢失的紧急情况下,我们往往会想到用binlog日志功能进行数据恢复(定时全备份+binlog日志恢复增量数据部分),化险为夷!
废话不多说,下面是梳理的binlog日志操作解说:
一、初步了解binlog
MySQL的二进制日志binlog可以说是MySQL最重要的日志,它记录了所有的DDL和DML语句(除了数据查询语句select),以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。
----------------------------------------------------------------------------------------------------------------------------------------------
DDL
----Data Definition Language 数据库定义语言
主要的命令有CREATE、ALTER、DROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用。
DML
----Data Manipulation Language 数据操纵语言
主要的命令是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言
----------------------------------------------------------------------------------------------------------------------------------------------
mysqlbinlog常见的选项有以下几个:
--start-datetime | 从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间 |
--stop-datetime | 从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样 |
--start-position | 从二进制日志中读取指定position 事件位置作为开始。 |
--stop-position | 从二进制日志中读取指定position 事件位置作为事件截至 |
----------------------------------------------------------------------------------------------------------------------------------------------
一般来说开启binlog日志大概会有1%的性能损耗。
binlog日志有两个最重要的使用场景:
主从复制 | MySQL Replication在Master端开启binlog,Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。 |
数据恢复 | 通过使用mysqlbinlog工具来使恢复数据 |
binlog日志包括两类文件:
二进制日志索引文件 | (文件名后缀为.index)用于记录所有的二进制文件 |
二进制日志文件 | (文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。 |
二、开启binlog日志:
1)编辑打开mysql配置文件/etc/mysql/mysql.cnf
[root@vm-002 ~]# vim /etc/mysql/mysql.cnf
在[mysqld] 区块添加
server-id = 1 # 确保在整个Mysql集群中唯一
log-bin = /var/log/mysql/mysql-bin.log # 日志存放位置
log-bin-index = binlog.index
2)重启mysqld服务使配置生效
service mysql restart
3)查看binlog日志是否开启
mysql> show variables like 'log_%';
+----------------------------------------+-----------------------------+
| Variable_name | Value |
+----------------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/log/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+----------------------------------------+-----------------------------+
三、常用的binlog日志操作命令
1)查看所有binlog日志列表
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.01 sec)
2)查看master状态,即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3)flush刷新log日志,自此刻开始产生一个新编号的binlog日志文件
mysql> flush logs;
Query OK, 0 rows affected (0.13 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 154 |
+------------------+-----------+
2 rows in set (0.00 sec)
注意:
每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;4)重置(清空)所有binlog日志