mysql binlog日志简介及操作详解
一 、binlog日志简介
binlog 基本认识
MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。 |
二进制有两个最重要的使用场景:
其一:MySQL Replication在Master端开启binlog,Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。 其二:自然就是数据恢复了,通过使用mysqlbinlog工具来使恢复数据。 |
二进制日志包括两类文件:
二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件, 二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句)语句事件。 |
二 、开启binlog日志
[root@i-3vsptbun ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 server-id=1 log-bin=/var/lib/mysql/mysql-bin
[mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
mysql> show variables like "%log_bin%"; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | ON | | log_bin_trust_function_creators | OFF | | log_bin_trust_routine_creators | OFF | | sql_log_bin | ON | +---------------------------------+-------+ 4 rows in set (0.00 sec)
|
三、常用binlog操作命令
3.1查看所有binlog日志列表 |
mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 725 | | mysql-bin.000002 | 149 | | mysql-bin.000003 | 149 | | mysql-bin.000004 | 1622 | | mysql-bin.000005 | 11168 | +------------------+-----------+ |
3.2 查看master状态,即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值 |
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 11168 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) |
3.3 刷新log日志,自此刻开始产生一个新编号的binlog日志文件 |
mysql> flush logs; Query OK, 0 rows affected (0.00 sec)
mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 725 | | mysql-bin.000002 | 149 | | mysql-bin.000003 | 149 | | mysql-bin.000004 | 1622 | | mysql-bin.000005 | 11211 | | mysql-bin.000006 | 106 | +------------------+-----------+ 6 rows in set (0.00 sec) |
3.4 重置(清空)所有binlog日志 |
mysql> reset master; Query OK, 0 rows affected (0.02 sec)
mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 106 | +------------------+-----------+ 1 row in set (0.00 sec) |