mysql的日志及利用mysqldump备份及还原

时间:2022-03-10 10:17:00
日志文件:6类
      一般查询日志:log,general_log,log_output
      慢查询日志:
      错误日志
      二进制日志
      中继日志
      事务日志


 二进制日志:
     记录了当前服务器的数据修改和有潜在可能性影响数据修改的语句
     默认在数据目录下,通常情况下为mysql-bin
     可以通过mysqlbinlog来查看
     时间记录 time
     偏移位置 position
     show master status 查看当前使用的二进制日志和下一个事件开始时的基于的位置


     日志滚动:当超过1G,日志会滚动
        可以按照大小定义
                时间定义
                执行flush logs


     查看当前正在使用的log日志
     show binary logs


mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       996 |
| mysql-bin.000002 |       126 |
| mysql-bin.000003 |     39892 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       194 |
+------------------+-----------+




二进制日志的功用
     即时点恢复:
     复制:


清除日志
PURGE { BINARY | MASTER } LOGS
    { TO 'log_name' | BEFORE datetime_expr(某个时间之前) }


查看mysql的日志
SHOW BINLOG EVENTS


mysql> show binlog events\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 107
       Info: Server ver: 5.5.33-log, Binlog ver: 4




service-id:服务器身份标识


INSERT INTO t1 value(current_date())


Mysql 记录二进制日志的格式
   基于语句:statement
   基于行:row
   混合模式:mixed


指定从那个位置开始读取
mysql> show binlog events in 'mysql-bin.000001' from 371\G
或者
# mysqlbinlog --start-position=371 mysql-bin.000005


mysqlbinlog
   --start-time
   --stop-time
   --start-position
   --stop-position


    二进制日志文件内容格式
      事件发生的日期和时间
      服务器ID
      事件结束位置
      事件的类型
      原服务器生成此事件时的线程ID号
      语句时间戳和写入二进制文件的时间差,单位为秒
      错误代码,0表示正常执行
      事件内容
      事件位置(相当于下一事件的开始位置)


服务器参数:
      log_bin = {ON|OFF},还可以是一个文件路径                          
      log_bin_trust_function_creators  不阻止任何存储函数
      sql_log_bin = {ON|OFF} 当前会话是否将二进制文件进入进二进制文件
      sql_log_off 是否将一般查询日志记入查询日志
      sync_binlog 同步缓冲中的二进制到硬盘的时间,0不基于时间同步,只在事件提交时同步
      binlog_format = {|statement|row|mixed}
      max_binlog_cache_size = mysql二进制日志的缓冲区大小,仅用于缓存事务类的语句
      max_binlog_stmt_cache_size 语句缓冲区大小,即事务类和非事务类公用的大小
      max_binlog_size 二进制日志文件的上限,单位为字节


  建议:切勿将二进制日志与数据文件放在同一设备


  设置二进制日志文件目录:
  # mkdir /data/binlog
  # chown mysql.mysql /data/binlog




  vim /etc/my.cnf


  加入 log-bin=/data/binlog/mysql-bin
mysql的日志及利用mysqldump备份及还原

重启mysql服务生效
  # service mysqld restart


  可以临时通过sql_log_bin来控制二进制的写入


中继日志:
  对于非从服务器 中继日志没有启用
  relay_log_purge = {ON|OFF} 是否自动清理不在需要的中继日志
  relay_log_space_limit 中继大小是否限制


备份和恢复:
    1.做灾难恢复
    2.做审计
    3.测试


  备份:目的用于恢复;对备份数据做恢复测试


  备份类型:


      根据备份时,数据库服务器是否在线
        冷备:cold  backup
        温备:warm backup 全局施加共享锁,只能读,不能写
        热备:hot backup 


      根据备份的数据集:
         完全备份 full backup
         部分备份 partial backup


      根据备份时的接口(直接备份数据文件还是通过mysql服务器导出数据)
          物理备份:直接复制(归档)数据文件的备份方式
          逻辑备份:把数据从库中提取出来保存为文本文件


      根据备份时是备份整个数据还是仅备份变化的数据
          完全备份:full backup
          增量备份:incremental backup
          差异备份:differential backup 从上一个完全备份开始备份


备份策略:
   选择备份方式
   选择备份时间
   考虑到恢复成本
       恢复时长
   考虑备份成本
       锁时间
       备份时长
       备份负载


备份对象:
    数据
    配置文件:
    代码:存储过程、存储函数、触发器
    OS相关的配置文件,如crontab配置计划及相关脚本


    跟复制相关的配置信息:
    二进制日志文件


常用的备份工具
  mysqldump(单线程):逻辑备份工具
     InnoDB热备、MyISAM温备、Aria温备
     备份和恢复过程较慢
  mysqldumper:多线程的mysqldump


     很难实现差异或增量备份


  基于lvm-snapshot
         接近于热备的工具,因为要先请求全局锁,而后创建快照,并在创建完成后释放全局锁
         使用cp、tar等工具进行物理备份
         备份和恢复速度较快
         很难实现增量备份,并且请求全局锁需要等待一段时间,在繁忙的服务器上尤其如此
  SELECT clause INTO OUTFILE ‘/path/to/somefile’
  LOAD DATA INFILE ‘/path/from/somefile’
      部分备份工具,不会备份关系定义,仅备份表中的数据
      是个逻辑备份工具,快于mysqldump


   Innobase:商业备份工具,innobackup 
   Xtarbackup:有Percona提供的开源备份工具
      InnoDB热备,增量备份
      MyISAM热备,不支持增量
      物理备份,速度快


   mysqlhotcopy:几乎冷备


mysqldump:仅适用于数据集较小
    使用格式:mysqldump [options] [db_name [tbl_name ...]]
         还原时库应该存在
         -u uername
         -h 主机名
         -p 密码
    恢复时应该使用mysql < /path/to/file.mysql


   备份单个库 mysqldump [options] db_name > /path/to/file.mysql
      恢复时如果目标不存在,应先创建数据库,如果不想创建,这使用--databases
    
    # mysqldump -uroot -hlocalhost -p hellodb > /tmp/hello.sql




   备份所有的库
        mysqldump --all-databases > /path/to/file.mysql


# mysqldump --all-databases > /tmp/alldb.sql


    备份指定的多个库
       --databases tb1 tb2 .....


       # mysqldump --databases hellodb mydb > /tmp/hellandmydb.sql


       恢复:
       # mysql < /tmp/hellandmydb.sql


    注意:备份前要加锁


    --lock-all-tables :请求锁定所有表之后在备份,对MyISAM,InnoDB,Aria做温备


    --single-transaction:能够对InnoDB存储引擎实现热备


    备份代码
    --events:备份事件调度器代码
    --routines:备份存储过程和存储函数
    --triggers:备份触发器
    备份时滚动日志:
         --flush-logs:备份前、请求到锁之后滚动日志
    复制时的同步位置标记
         --master-data={0|1|2}
         0表示不记录,
         1表示记录为change master语句
         2表示记录为注释的change master语句


    使用mysqldump备份
        请求锁:--lock-all-tables 或使用--single-transaction进行innodb热备
        滚动日志:--flush-logs
        选定要备份的库 --databases
  记录二进制文件及位置 --master-date                                                                                                


    手动请求全局锁
        
        FLUSH [NO_WRITE_TO_BINLOG | LOCAL]  flush_option [, flush_option] ...


        flush tables with read lock


        flush logs
        
        unlock tables


    恢复:
       建议:关闭二进制日志,关闭其他用户的写操作
       应该用souce 恢复


    备份策略:基于mysqldump


    备份:mysqldump+二进制日志文件
         周日做一次完全备份,备份的同时滚动日志
         周一至周六:备份二进制日志
         
         myadmin flush-logs


    恢复:完全备份+各二进制文件至此刻的事件 


    对于MYsql配置文件,以及与mysql相关的OS配置文件在每次修改后都应该直接进行备份


案例:   对hellodb数据库进行还原操作

mysql的日志及利用mysqldump备份及还原
    1.查看hellodb数据库表使用的引擎情况,有myisam数据库
    mysql> show table status from hellodb\G


    备份hellodb数据库,--master-data-2标记日志文件的位置
    # mysqldump --databases hellodb --lock-all-tables --flush-logs --master-data=2 > /tmp/hello.a.sql
  

    通过命令可以看到此时的位置107,
    mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      107 |              |                  |
+------------------+----------+--------------+------------------+
    或者vim /tmp/hellodb.20160114.sql,也可以看到二进制文件标记
mysql的日志及利用mysqldump备份及还原

    2.模拟失误操作
    创建newtb表,并插入数据
    mysql> create table newtb(name char(30));
    mysql> insert into newtb(name) values('tom'),('lily'),('lucy');

mysql的日志及利用mysqldump备份及还原
    清空表数据
    mysql> truncate table newtb;
    查看二进制从107位置开始的日志记录
    # mysqlbinlog --start-position=107 /data/binlog/mysql-bin.000004

mysql的日志及利用mysqldump备份及还原
   将日志中记录这一部分记录截取出来生成增量的sql
   # mysqlbinlog --start-position=107 --stop-position=712 mysql-bin.000005 > /tmp/hello.a.inc.sql


    3.还原
    关闭还原操作的日志记录
    mysql> set session sql_log_bin=0;
    还原完整备份
    mysql> source /tmp/hellodb.a.sql
    验证数据库是否存在
    


    还原增量备份
    mysql> source /tmp/hello.a.inc.sql