Mysql备份：二进制日志备份与全备

1.备份的原因

• 天灾人祸、误操作、安全机制等
• 付出代价（费用、人力）

---

2.备份类型

• 热备：mysqld服务是运行的情况下备份
  • 常用方法：mysqldump、SQLyog、mysql workbench等工具
• 冷备：mysql服务需要停止，然后去拷贝数据
  • 常用方法：offline、cp、tar
• 异地备份
  • 常用方法：脚本执行、计划任务

---

3.备份方案

• 完全备份：所有的内容全部备份
• 增量备份：上一次备份后新产生的数据，增加的数据
• 差异备份：每次备份和完全备份进行差异比较
• 备份方案
  • 

---

4.mysqldump实现全备

4.1原理

• mysqldump是MySQL自带的备份工具
• 它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。

4.2命令格式

• mysqldump -u username -p dbname [tbname …]> filename.sql
• 备份库
  • 备份所有库
    • mysqldump -uroot -p’password’ --all-databases > /backup/all.db
  • 备份指定库test
    • mysqldump -uroot -p’password’ test > /backup/databases01.sql
• 备份表
  • mysqldump -uroot -p’password’ db table > /backup/db_tb.sql

---

5.二进制日志

5.1作用：

• 二进制日志记录了整个mysql进程里所有的库的操作；
• 所有的操作都会记录到一个二进制文件里
• 常用来恢复数据；主从复制

5.2 查看二进制日志信息

• show variables like “%log_bin%”;
• 二进制日志默认未开启

5.3 查看二进制日志最大存储量

• show variables like “%max_binlog%”;
  

5.4 开启二进制日志

• 修改配置文件
  • 配置文件位置：/etc/my.cnf
• 在[mysqld]中添加字段
  • log_bin
  • server_id = 1 server_id必须唯一
    
• 重启mysqld服务：service mysqld restart
• 再次查看二进制日志信息
  

5.5查看当前正在使用哪个日志文件

• show master status
  
• show binary logs
  

5.6产生二进制日志文件情况

• 刷新服务 service mysqld restart
• flush logs
• 当日志文件达到最大值的时候

5.7清除二进制日志文件

• 手动清除
  • reset master 清除所有的二进制文件
    
  • purge binary logs to ‘localhost-bin.000002’
  • 清除000002二进制日志之前的日志‘
    
• 自动清除：修改配置文件
• 添加字段 expire_logs_days=3

5.8 查看二进制日志文件

• mysqlbinlog localhost-bin.000002

---

6.备份和还原操作

6.1 产生一个全新的二进制文件

• flush logs
• 便于知道二进制文件起始位置，便于后期还原操作
  

6.2 给数据库做全备

• mysqldump -uroot -p’Jiangzihan123#’ --all-databases > /backup/all_databases.sql

6.3 让数据发生变化，进行insert和删除delete等操作

• 在空表studenet中进行操作
  
• 进行操作，产生数据
  • insert into student(id,name,age) values (1,‘jack’,16);
  • insert into student(id,name,age) values (2,‘Ann’,26);
  • insert into student(id,name,age) values (3,‘Ben’,43);
    

6.4模拟出现故障

• 删除表所在数据库操作
• drop database tennis;
  

6.5 恢复数据

• 恢复全备

  • mysql -uroot -p’Jiangzihan123#’ </backup/all_databases.sql
  • 此时数据库tennis以及student表已经恢复，但是student表里所新添加的数据并未还原

• 根据二进制日志去恢复

• 查看二进制日志找到删除操作的位置号

  • mysqlbinlog -v localhost-bin.000005 | grep -C 12 “drop database tennis”
  • 查看二进制日志中删除数据库tennis操作的前后12行
    

• 得到位置号：1042

• 进行还原操作

  • mysqlbinlog --start-position=154 --stop-position=1042 localhost-bin.000005 | mysql -uroot -p’Jiangzihan123#’
  • 恢复成功