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

日志文件：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服务生效
  # 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数据库进行还原操作

    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，也可以看到二进制文件标记


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


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


   将日志中记录这一部分记录截取出来生成增量的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