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