binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。 一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景: 其一:MySQL Replication在Master端开启binlog,Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。 其二:自然就是数据恢复了,通过使用mysqlbinlog工具来使恢复数据。 二进制日志包括两类文件:二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件,二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句)语句事件。
一、开启binlog日志: vi编辑打开mysql配置文件 # vi /usr/local/mysql/etc/my.cnf 在[mysqld] 区块 设置/添加 log-bin=mysql-bin 确认是打开状态(值 mysql-bin 是日志的基本名或前缀名); 重启mysqld服务使配置生效 # pkill mysqld # /usr/local/mysql/bin/mysqld_safe --user=mysql & 二、也可登录mysql服务器,通过mysql的变量配置表,查看二进制日志是否已开启 单词:variable[ˈvɛriəbəl] 变量 登录服务器 # /usr/local/mysql/bin/mysql -uroot -p123456 mysql> show variables like 'log_%'; +----------------------------------------+---------------------------------------+ | Variable_name | Value | +----------------------------------------+---------------------------------------+ | log_bin | ON | ------> ON表示已经开启binlog日志 | log_bin_basename | /usr/local/mysql/data/mysql-bin | | log_bin_index | /usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_error | /usr/local/mysql/data/martin.err | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | log_throttle_queries_not_using_indexes | 0 | | log_warnings | 1 | +----------------------------------------+---------------------------------------+
binary logs的mysql命令:
1. Binary logs 位置:
1)查看/etc/my.cnf (Windows下为my.ini), 例如:
log_bin=/var/mydb/bin-log
如果该设置没带路径,就放在datadir=/var/lib/mysql 下
2) 到数据库查看是否开启binary log 功能:
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.01 sec)
如果是OFF就没有以下工作了:)
2. 查看当前工作的logfile名及大小: show binary logs /show master logs;
mysql> show binary logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| bin-log.000011 | 148329 |
+-----------------+-----------+
mysql> show master logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| bin-log.000011 | 148329 |
+-----------------+-----------+
mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging ###没有开启binary logs
3. 清除所有binary logs;
1)
mysql> show master logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| log-bin.000001 | 259548 |
| log-bin.000002 | 37200 |
| log-bin.000003 | 74219 |
+-----------------+-----------+
3 rows in set (0.00 sec)
mysql> reset master; (在slave上,用reset slave,之前应stop slave,之后再start slave)
Query OK, 0 rows affected (0.00 sec)
mysql> show master logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| log-bin.000001 | 98 |
+-----------------+-----------+
1 row in set (0.00 sec)
4. 清除指定部分logs:
mysql>purge binary logs to 'log-bin.000012';
将log-bin.000012之前的binary logs清掉;
mysql>purge binary logs before '2011-05-28 12:05:38';
将指定时间之前的binary logs清掉;
5.查看当前binary log的情况:
mysql>show master status;
6. 查看binary logs的内容:
mysql>show binlog events;
命令行下:
#mysqlbinlog /var/log/mysql/log-bin.000140; 或者
#mysqlbinlog --start-datetime='2011-07-01 00:00:00' --stop-datetime='2010-07-15 00:00:00' /var/log/mysql/log-bin.000020 > ./tmp.log
7. 在my.cnf/my.ini中设定binary logs回滚天数:
expire_logs_days = 7
8.刷新log日志,自此开始产生一个新的编号binlog日志
mysql>flush logs;
注:每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加-F选项也会刷新binlog日志。
# 提取指定的binlog日志 (参数需要用绝对路径)
mysqlbinlog /opt/data/APP01bin.000001
# 根据位置提取
mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001
# 根据开始结束时间提取
mysqlbinlog --start-datetime="2014-12-15 20:15:23" --stop-datetime="2014-12-15 20:30:23" /opt/data/APP01bin.000002 --result-file=extra02.sql
# 对多个binlog文件, 提取指定数据库, 并转换字符集
mysqlbinlog --database=test --set-charset=utf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 >test.sql
# 提取远程的binlog, 并输出到本地
mysqlbinlog -u someone -p -P3306 -h192.168.1.177 --read-from-remote-server -vv inst3606bin.000005 >row.sql