mysqldump全备+binlog

mysqldump全备+binlog

1.备份的目的

做灾难恢复：对损坏的数据进行恢复和还原
需求改变：因需求改变而需要把数据还原到改变以前
测试：测试新功能是否可用

2.备份考虑的问题

可以容忍丢失多长时间的数据；
恢复数据要在多长时间内完； 
恢复的时候是否需要持续提供服务；
恢复的对象，是整个库，多个表，还是单个库，单个表

3.备份的类型

根据是否需要数据库离线：
冷备（cold backup）：需要关mysql服务，读写请求均不允许状态下进行；
温备（warm backup）： 服务在线，但仅支持读请求，不允许写请求；
热备（hot backup）：备份的同时，业务不受影响
根据备份后文件内容分：
逻辑备份：指备份出的内容是可读的，也就是文本文件。如：mysqldump
物理备份：指备份数据库的物理文件 如：xtarbackup   也叫裸文件备份
按照备份数据库的内容来分：
完全备份：不管是否发生变化，都将全库备份
增量备份：仅备份上次备份到这次备份之间发生变化的部分
日志备份：备份二进制日志
一般的备份策略都是完全备份+增量备份  ******

4.mysqldump备份常用的参数

示例：mysqldump -uroot -pqwe123 --single-transaction --flush-logs --master-data=2 -B cmdb >  /backup/mysql_backup/mysql_cmdb.sql
MyISAM存储引擎：--lock-all-tables
InnoDB存储殷勤:--single-transaction
--flush-logs为结束当前日志，生成新日志文件；
--master-data=2 选项将会在输出SQL中记录下完全备份后新日志文件的名称；
用于日后恢复时参考，例如输出的备份SQL文件中含有：
 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=120;
mysqldump增量备份其他说明：
MySQLdump加上–delete-master-logs 则清除以前的日志，以释放空间。但是如果服务器配置为镜像的复制主服务器，用MySQLdump –delete-master-logs删掉MySQL二进制日志很危险
因为从服务器可能还没有完全处理该二进制日志的内容。在这种情况下，使用 PURGE MASTER LOGS更为安全，理论上，应该在配置文件/etc/my.cnf中加上binlog过期时间的配置项，
expire_logs_days = 10 
全备恢复示例：
[root@DB ~]# mysql -uroot -pqwe123 <  /backup/mysql_backup/mysql_cmdb.sql
恢复增量备份示例：

[root@DB ~]# mysqlbinlog MySQL-bin.000002 … | MySQL -u root -p密码
参数：
--no-create-info，-t
只导出数据，而不添加 CREATE TABLE 语句
--no-data，-d
不导出任何数据，只导出数据库表结构。

  --opt
  这只是一个快捷选项，等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset     选项。本选项能让 MySQLdump 很快的导出数据，并且导出的数据能很快导回。该选项默认开启，但可以用 --skip-opt 禁用。注意，如果运行 MySQLdump 没有指定 --quick 或 --opt 选  项，则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。

  --quick，-q
 该选项在导出大表时很有用，它强制 MySQLdump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。

  --routines，-R
  导出存储过程以及自定义函数。

  --single-transaction
  该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB 和 BDB。本选项和 --lock-tables     选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用 --quick 选项。

  --triggers

  跨主机备份
  使用下面的命令可以将host1上的sourceDb复制到host2的targetDb，前提是host2主机上已经创建targetDb数据库：
  -C 指示主机间的数据传输使用数据压缩
  mysqldump --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb

 5.简单示例

#!/bin/bash
echo -e "\033[32;40mBegin backup mysql cmdb  `date +"%Y-%m-%d %H:%M:%S"` \033[0m"
mysqldump  --defaults-extra-file=/etc/my.cnf  --single-transaction --flush-logs --master-data=2 -B cmdb >  /backup/mysql_backup/cmdb_`date +"%Y-%m-%d %H:%M:%S"`.sql   &&  
echo -e "\033[32;40m backup mysql cmdb successfully `date +"%Y-%m-%d %H:%M:%S"` \033[0m"

6.全量和增量备份脚本

全量

#!/bin/bash
# author:hanhy
# date:2017-8-23
# dec: use use mysqldump to Fully backup mysql data per week!
backupfile=/backup/mysql_backup/
logfile=/backup/mysql.log
back_mysql(){
echo -e "\033[32;40mBegin backup mysql all database  `date +"%Y-%m-%d %H:%M:%S"` \033[0m"   >> $logfile
mysqldump  --defaults-extra-file=/etc/my.cnf  --single-transaction --flush-logs --master-data=2 --all-databases | gzip  >  $backupfile/`date +"%Y-%m-%d %H:%M:%S"`.sql.
gz
if [ $? -eq 0 ]
  then
   echo -e "\033[32;40mbackup mysql database successfully `date +"%Y-%m-%d %H:%M:%S"` \033[0m" >> $logfile
  else
   echo -e "\033[41;40mbackup mysql database defeat `date +"%Y-%m-%d %H:%M:%S"` \033[0m"   >> $logfile
fi
}
rm_oldfile(){
      find  $backupfile -type f -mtime  +20 -exec rm {} \;
}
back_mysql
rm_oldfile