binlog介绍
- binlog,即二进制日志,它记录了数据库上的所有改变.
- 改变数据库的SQL语句执行结束时,将在binlog的末尾写入一条记录,同时通知语句解析器,语句执行完毕.
- binlog格式
- 基于语句,无法保证所有语句都在从库执行成功,比如update ... limit 1;
- 基于行,将每一次改动记为binlog中的一行.在执行一个特别复杂的update或者delete操作时,基于行的格式会有优势.
- mysql.ini开启log_bin
[mysqld] # log_bin #回滚日志记录 server-id = 1 log_bin = "E:/phpStudy/MySQL/logbin/mysql-bin.log" max_binlog_size = 100M binlog-format = row binlog_row_image = full
登录到mysql查看binlog
- 只查看第一个binlog文件的内容
show binlog events;
- 查看指定binlog文件的内容
show binlog events in 'mysql-bin.000002';
- 查看当前正在写入的binlog文件
show master status\G
- 获取binlog文件列表
show binary logs;
用mysqlbinlog工具查看
注意:
- 不要查看当前正在写入的binlog文件
- 不要加--force参数强制访问
- 如果binlog格式是行模式的,请加 -vv参数
本地查看
- 基于开始/结束时间
mysqlbinlog --start-datetime='2013-09-10 00:00:00' --stop-datetime='2013-09-10 01:01:01' -d 库名 二进制文件
- 基于pos值
mysqlbinlog --start-postion=107 --stop-position=1000 -d 库名 二进制文件
远程查看
- 指定开始/结束时间,并把结果重定向到本地t.binlog文件中.
mysqlbinlog -u username -p password -hl-db1.dba.beta.cn6.qunar.com -P3306 \ --read-from-remote-server --start-datetime='2013-09-10 23:00:00' --stop-datetime='2013-09-10 23:30:00' mysql-bin.000001 > t.binlog
- 基于pos
- 略
实例:
在mysql有时执行了错误的update或者delete时导致大量数据错误恢复的办法。执行时没有开启事务,也没有对数据进行。这时就需要使用到sqlbinlog工具。
sqlbinlog需要开启,具体的打开方法就不说了。
使用sqlbinlog会产生bin文件,恢复就需要用到这些文件。文件中记录着数据库的所有操作。(此方法的操作是将数据库之前所执行的语句重新执行一次,以达到恢复效果)
具体步骤:(注:mysqlbinlog 命令直接在cmd控制台xxbin.000001文件路径下操作,不要进mysql)
1,先找到bin文件,一般都是在mysql的data文件夹中,结尾以.00000X等形式结束。
2,寻找需要还原的时间点 使用语句 mysqlbinlog 文件名 例(MySQLbinlog xxbin.000001)来查看内容,然后找到对应的具体时间
3,导出sql语句,使用语句 mysqlbinlog 文件名>sql文件路径 例(mysqlbinlog xxxbin,00001>a.sql | mysql -u root -p )
如果需要指定时间导出--start--date -stop='' --date='' 来导出指定时间执行的语句例(sqlbinlog --start-stop='2015-11-22 10:00:00' xxbin.000001>a.sql | mysql -u root -p )这句意思是导出在2015-11-22 10点之前的语句,反之start是导出时间之后的。 start和stop可以同时使用。
如果存在多个bin文件,则按照需要导出。
4,使用mysql将导出的语句执行一次。
其它:
1、还有个sql_log
mysql> show variables like 'sql_log_%';
Mysql开启关闭sql二进制日志:
mysql> set sql_log_bin=0; //关闭
set session sql_log_bin=0;
2、查找文件位置:
find / -name my.cnf
3、linux 查看当前所在目录的全路径
pwd命令:
/var/lib/mysql
4、查看当前binary log的情况:
mysql>show master status;
5、在my.cnf/my.ini中设定binary logs回滚天数:
expire_logs_days = 7
6、查看Master的bin log日志
mysql> show master logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| mysql-bin.000001 | 98 |
+-----------------+-----------+
1 row in set (0.00 sec)