mysqlbinlog备份与恢复,主备复制分析
由于服务器生成的二进制日志文件以二进制格式保存,所以如果想要检查这些文件的文本格式,
就会用到mysqlbinlog日志管理工具。
mysqlbinlog的具体用法如下:
shell> mysqlbinlog [options] log-files1 log-files2. .
option有很多选项,常用的选项如下。
-d, --database=name:指定数据库名称,只列出指定的数据库相关操作。
-o, --offset=#:忽略掉日志中的前 n行命令。
-r, --result-file=name:将输出的文本格式日志输出到指定文件。
-s, --short-form:显示简单格式,省略掉一些信息。
--set-charset=char-name:在输出为文本格式时,在文件第一行加上 set names char-name,这个选项在某些情况下装载数据时非常有用。
--start-datetime=name –stop-datetime=name:指定日期间隔内的所有日志。
--start-position=# --stop-position=#:指定位置间隔内的所有日志。
1 使用不写日志进行优化
--binlog-do-db
--binlog-ignore-db
在执行sql语句之前执行
set session sql_log_bin=0
DML语句。
set session sql_log_bin=1
2 使用show events分析binlog
show binlog events in 'IP111-bin.0000001' from 6912\G
从pos点6912开始查看。
SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
3 使用mysqlbinlog恢复数据
3.利用bin_log恢复数据
1.最长用的就是回复指定数据端的数据了,可以直接恢复到数据库中:
mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 |mysql -uroot -p123456
亦可导出为sql文件,再导入至数据库中:
mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 >d:\1.sql
source d:\1.sql
2.指定开始\结束位置,从上面的查看产生的binary log我们可以知道某个log的开始到结束的位置,我们可以在恢复的过程中指定回复从A位置到B位置的log.需要用下面两个参数来指定:
--start-positon="50" //指定从50位置开始
--stop-postion="100"//指定到100位置结束
最后介绍几个bin_log的操作:
1.产看最后一个bin日志文件是那个,现在位置
2.启用新的日志文件,一般备份完数据库后执行
3.清空现有的所用bin-log
1 不加任何参数的mysqlbinlog
mysqlbinlog lohost.bin.000033
2 参数的mysqlbinlog -o
mysqlbinlog lohost.bin.000033 -o 3
3 参数的mysqlbinlog -o -r
mysqlbinlog lohost.bin.000033 -o 3 -r /home/mysql/123.log
3 参数的mysqlbinlog -o -r -s
mysqlbinlog lohost.bin.000033 -o 3 -s -r /home/mysql/123.log
mysqlbinlog localhost-bin.000033 --start-datetime= '2007/08/30 05:00:00' --stop-datetime='2007/08/30 05:06:20'
mysqlbinlog localhost-bin.000033 --start-position=4 --stop-position=196
mysqlbinlog --start-position=845 --stop-position=1438 /mysql/logs/3306/binlog/21_mysql_bin.000001
mysqlbinlog -v --start-position=845 --stop-position=1438 /mysql/logs/3306/binlog/21_mysql_bin.000001
mysqlbinlog -vv --start-position=845 --stop-position=1438 /mysql/logs/3306/binlog/21_mysql_bin.000001
mysqlbinlog --base64-output=decode-rows --start-position=845 --stop-position=1438 /mysql/logs/3306/binlog/21_mysql_bin.000001
mysqlbinlog -vv --base64-output=decode-rows --start-position=845 --stop-position=1438 /mysql/logs/3306/binlog/21_mysql_bin.000001
4 mysqlbinlog恢复(xtrabackup info就是有位点的)
xtrabackup+mysqlbinlog position位点恢复。
(1)将数据恢复到故障前:
mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 | mysql -u root –pmypwd
(2)跳过故障时的时间点,继续执行后面的BINLOG,完成恢复。
mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456| mysql-u root -pmypwd
(1)在shell下执行如下命令:
shell>mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00"/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
该命令将在/tmp目录创建小的文本文件,编辑此文件,找到出错语句前后的位置号,例如前后位置号分别是368312和368315。
(2)恢复了以前的备份文件后,应从命令行输入下面的内容:
shell>mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd
shell>mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd \