mysql 开发进阶篇系列 33 工具篇(mysqlbinlog日志管理工具)

一.概述

        
由于服务器生成的二进制日志文件以二进制格式保存,所以如果要想检查这些文件的文本格式,就会用到mysqlbinlog日志管理工具。
    mysqlbinlog的语法如下:
    mysqlbinlog [options] log-files log-files2…

    其中options有很多选项,常用如下:

选项

说明

-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.1 开启binlog日志

    默认情况下是未打开binlog日志,可以通过以下二种方式查看:

mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging
--或者这样查看
mysql> show variables like '%log_bin%    

    图片 1

  开启binlog日志,修改my.cnf文件重启mysql服务,如下所示:
    图片 2
   再次查询binlog日志状态:
    图片 3
    图片 4

操作命令:

逻辑备份:
1.mysqldump(数据导出工具)
  mysqldump options db_name[table_name]//备份单个数据库
  mysqldump 选项 –database database-name1
[databases-name2]….//备份指定的数据库一个或者多个
  mysqldump 选项 –all-database //备份所有的数据库
  链接选项:
  -u :指定用户名
   -p:指定密码
   -h:指定服务器ip或者域名
   -P(大写):指定端口
eg:/usr/bin/mysqldump -u root -h 202.194.132.237 -P 3306 -p BBS
user>/home/wuxiaoxiao/user.txt
输出内容选项:
–add-drop-database:每个数据库创建语句之前加上drop database语句
–add-drop-table:每个表创建语句之前加上drop table语句
-n:不包含数据库的创建语句
-t:不包含数据表的创建语句
-d:不包含数据
输出格式选项:
 –compact:使输出结果简洁
 -c –compact-insert:使输出文件中的insert语句包含字段名
 -T:将数据库表中的数据备份为单纯的数据文本和建表sql俩个文件
   –fields-terminated-by=name(域分割符)
   –fields-enclosed-by=name(域引用符)
   –fields-optionally-enclosed-by=name(域可选引用符)
   –fields-escaped-by=name(转移字符)
eg:/usr/bin/mysqldump -u root -h 202.194.132.237 -P 3306 -p BBS user -T
./bak
字符集选项:
–default-character-set=name:设置导出的客户端字符集
eg:mysql -u root -p –compact –default-character-set=utf8 BBS user >
test.txt
其他选项:
-F:备份前刷新日志
-l:给所有表加读锁(备份期间使用,使备份的数据保持一致性)

二. 使用mysqlbinlog查看日志

--先在test表中插入一条数据退出。
mysql> insert into a values('testbinlog');

-- 查看binlog位置
[root@hsr mysql]# cd /var/lib/mysql
[root@hsr mysql]# ls
mysql-bin.000001  mysql-bin.index    

   2.1 使用mysqlbinlog查看日志 不加任何options参数

[root@hsr ~]# cd /usr/local/mysql/bin
[root@hsr bin]# ./mysqlbinlog /var/lib/mysql/mysql-bin.000001    

    图片 5

     
  上面的日志文件除了创建表和删除表的sql外,操作数据库的语句都加密了。

  2.2 使用参数”–base64-output=decode-row
-v”查看具体的sql语句,如下命令

[root@hsr bin]# ./mysqlbinlog --base64-output=decode-row -v  /var/lib/mysql/mysql-bin.000001

    图片 6
    上图中 at 291是插入语句的开始位置, at
384是插入语句的结束位置。如果后续该表数据丢失,可以根据这两个地方执行恢复。也可以根据开始时间和结束时间来恢复,后面再讲日志时具体介绍。

  2.3  加-d选项,将只显示对test数据库的操作日志

                   [root@hsr bin]# ./mysqlbinlog  
/var/lib/mysql/mysql-bin.000001 -d test

  2.4  加-o选项, 忽略掉日志中的前n个操作。演示下

-- 插入三条数据
INSERT INTO a VALUES('testbinlog2')
INSERT INTO a VALUES('testbinlog3')
INSERT INTO a VALUES('testbinlog4')

[root@hsr bin]# ./mysqlbinlog --base64-output=decode-row -v  /var/lib/mysql/mysql-bin.000001 -d test  -o 14

    图片 7
    上图显示日志生成后,从at 898行开始显示。
14个操作代表:日志显示跳过14个at。
  2.5 加-r选项
    将输出的文本格式日志输出到指定文件,下面将文件结果输出到文件resultfile中。

[root@hsr bin]# ./mysqlbinlog --base64-output=decode-row -v  /var/lib/mysql/mysql-bin.000001 -r resultfile
[root@hsr bin]# more resultfile

    图片 8
图片 9

  2.6 加-s 将内容进行简单显示

[root@hsr bin]# ./mysqlbinlog --base64-output=decode-row -v  /var/lib/mysql/mysql-bin.000001 -s

  图片 10

    如上图所示,简单显示后,没有了详细的sql语句。
  2.7 加”–start-datetime–stop-datetime”显示9:00 ~12:00之间的日志

[root@hsr bin]# ./mysqlbinlog --base64-output=decode-row -v  /var/lib/mysql/mysql-bin.000001  
--start-datetime='2018/08/30 09:00:00' --stop-datetime='2018/08/30 12:00:00'

   图片 11
    如上图所示:最后显示的时间截是1535600091, 转换后是2018-08-30
11:34:51。 开始和结束日期可以只写一个。
  2.8 加”–start-position=#和–stop-position=#”
和日期范围类似,不过更精确

[root@hsr bin]# ./mysqlbinlog --base64-output=decode-row -v  /var/lib/mysql/mysql-bin.000001  --start-position=944

    图片 12
  最后官网文档
mysqlbinlog
  

复制代码 代码如下:

备份:
备份所有数据库:
mysqldump -u root -p  –all-database  >  test.sql
备份数据库test
mysqldump -u root -p  test  >  test.sql
 备份数据库test下的temp表:
mysqldump -u root -p  test demp >  test.sql
备份数据库下的所有表为逗号分割的文本,备份到/temp
mysqldump -u root -p test -T /temp –fields-terminated-by ‘,’
完全恢复:
msyql -u root -p < bakfile
注意:将备份恢复后数据并不完整,还需要将备份后执行的日志进行重做
mysqlbinlog binlog-file | mysql -u root -p***
举个完整的mysqldump备份和恢复的例子:
上午9点备份数据库
mysqldump -u root -p -l -F test > test.dmp
9点半备份完毕,然后想数据库中插入数据
10点数据库突然故障,数据无法访问,需要恢复备份
mysql -u root -p test < test.dmp
恢复后的数据并不完整,9点半插入的数据并没有恢复
使用mysqlbinlog恢复自mysqldump备份以来的binlog
mysqlbinlog binlogfilename | mysql -u root -p test
基于时间点恢复:
如果上午10点发生了误操作.可以用下面语句进行备份和binlog将数据库恢复到故障前:
mysqlbinlog –stop-date=”2005-04-20 9:59:59″ binlogfile | mysql -u root
-p test
跳过故障的时间点,继续执行后面的binlog,完成恢复
mysqlbinlog –start-date=”2005-04-20 9:59:59″ binlogfile | mysql -u root
-p test
基于位置恢复:
mysqlbinlog –start-date=”2005-04-20 9:55:59″ –stop-date=”2005-04-20
10:05:00″ binlogfile > test.sql
查看此文件,找出出错语句前后的位置号,例如是368312,368315
mysqlbinlog –stop-position=”368312″ binlogfile | mysql -u root -p
test
mysqlbinlog –start-position=”368315″ binlogfile | mysql -u root -p test

show binlog events in ‘mysql-bin.000016’ limit 10;

表的导入和导出:
导出:
    mysqldump -u username -p -T target_dir dbname tablename
[options]
options:
   –fields-terminated-by=name(域分割符)
   –fields-enclosed-by=name(域引用符)
   –fields-optionally-enclosed-by=name(域可选引用符)
   –fields-escaped-by=name(转移字符)
备份数据库下的所有表为逗号分割的文本,备份到/temp
mysqldump -u root -p -T /temp test –fields-terminated-by ‘,’
–fields-optionally-enclosed-by ‘”‘
导入:
   msyqlimport -u root -p [LOCAL] dbname order_tab.txt [options]
OPTIONS:
   –fields-terminated-by=name(域分割符)
   –fields-enclosed-by=name(域引用符)
   –fields-optionally-enclosed-by=name(域可选引用符)
   –fields-escaped-by=name(转移字符)
eg:mysqlimport -u root -p test order.txt –fields-terminated-by=’,’
–fields-enclosed-by='”‘

reset master 删除所有的二进制日志
flush logs  产生一个新的binlog日志文件

本文来自CSDN博客,转载请标明出处:

show master logs; 或者 show binary logs; 查看二进制文件列表和文件大小

复制代码 代码如下:

./mysqlbinlog –start-datetime=”2012-05-21 15:30:00″
–stop-datetime=”2012-05-21 16:40:00″ /binlog/mysql-bin.000005 >
a.log

  1. 要想通过日志恢复数据库,在你的 my.cnf
    文件里应该有如下的定义,log-bin=mysql-bin,这个是必须的
    binlog-do-db=db_test
    ,这个是指定哪些数据库需要日志,每行一个,如果不指定的话默认就是所有数据库.

复制代码 代码如下:

[mysqld]
 log-bin=mysql-bin
 binlog-do-db=db_test
 binlog-do-db=db_test2 

3.删除二进制日志:

复制代码 代码如下:

mysql> reset master  (清空所有的二进制日志文件)
purge master logs to ‘mysql-bin.000006’;
 (删除mysql-bin.000006之前的二进制日志文件)
purge master logs before ‘2007-08-10 04:07:00’  (删除该日期之前的日志)

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website