mysql数据库备份

通过对mysql数据库进行备份，当数据丢失或错误操作后，能对数据库进行恢复

备份类型

• 物理备份
  记录mysql的数据库数据文件，恢复速度快
• 逻辑备份
  以sql语句的形式备份数据库，恢复数据库需要执行备份的sql语句，恢复速度缓慢
• 完全备份
  备份整个数据库
• 增量备份
  基于完全备份或上一个增量备份，备份其修改的部分
• 热备份
  服务器在线执行备份，客户端程序能正常进行读写操作
  *温备份
  服务器在线执行备份，客户端只能进行读操作
• 冷备份
  服务器停机进行备份

备份账号

//备份账号需要有，select , show view, lock tables,event,  reload, replication client, process 权限

备份工具

mysqldump

mysql官方自带的逻辑备份工具
能实现innidb热备份和myisam的温备
只能作全备份

//备份所有数据库
mysqldump --all-databases
//备份指定的数据库
mysqldump --databases db1 db2 db3 
//备份指定数据库的某张表
mysqldump dbname table1 table2 table3
//常用参数
--host //主机名
--port //端口号
--user //登陆账号
--password //登陆密码

--events//备份事件调度器
--routines//备份存储过程/函数
--triggers//备份触发器

--single-transaction //开启一个大事物，inndob专用，进行热备份
--lock-all-tables //所有表进行加读锁，myisam专用，进行温备份

--master-data=[1|2] //记录当前二进制日志偏移位置，1记录change master to 语句，2记录被注释的change master to 语句
--flush-logs //备份前作日志滚动

--tab=/path/ //将表结构和表数据分开存储到指定目录

innobackupex

Percona 提供的mysql物理备份工具
能够对innodb进行热备份，myisam温备份
能够进行增备份

//全量备份
innobackupex --username=root --password=root /backup/path

输入命名后会在指定的备份目录中生成一个以日期命名的数据目录

backup-my.cnf //备份的配置文件
ibdata1 //innodb 系统表空间
ib_logfile0,ib_logfile1// 事物日志redo-log
mysql,performanece_schema,sys,test,//数据库数据文件
xtrabackuo_binlog_info// 二进制日志文件及其位置偏移
xtrabackup_binlog_pos_innodb// 二进制日志文件及其位置偏移
xtrabackup_checkpoints//记录了备份类型（全量|增量）

//备份恢复
备份文件需要进行处理后，才能进行恢复。
因为备份中存在未提交和已提交但为同步到数据文件的情况。需要把未提交的事物回滚，已提交的事物进行数据同步到数据文件中。
innobackupex --apply-log /path/to/backup
//处理完成后，需要停止mysql服务器，恢复备份。备份恢复完成后需要检测mysql是否有权限对数据目录进行读写
innobackupex --copy-back  /path/to/backup

//增量备份
innobackupex --user=root --password=root --incremental <备份保存路径> --incremental-basedir=<指定以完全备份/上一增量备份为基础，作增力备份>  

//备份恢复
//依次对备份进行 同步已提交的事物到数据文件中 的操作
innobackupex  --apply-log --redo-only <全备份名>
innobackupex  --apply-log --redo-only <全备份名> --incremental-dir=<增量备份名>

//最后一个增量备份需要需要把未提交的事物回滚，已提交的事物进行数据同步到数据文件中。
innobackupex  --apply-log  <全备份名> --incremental-dir= <最后一个增量备份>

//恢复全量备份
innobackupex --copy-back <全备份名> 

//innodb支持导出部分数据库、表，创建部分备份有三种方式
一、--include 使用则者表达式匹配需要导出表
innobackupex --user=inno_bak --password=inno_bak --include="^mc_userdb\..+" . //备份mc_userdb的所有表
二、--tables-file 指定一个文件，文件中包含了需要备份的表名字，一张表占一行
innobackupex --user=inno_bak --password=inno_bak --host=127.0.0.1 --tables-file=/tmp/bak/bak.txt  .
三、--databases 指定需要被备份的库，多个数据库用空格隔开
innobackupex --user=inno_bak --password=inno_bak --host=127.0.0.1 --databases="mc_userdb" .

//流压缩功能，能将备份的数据输出到标准输出，重定向到gzip进行压缩
innobackupex --user=inno_bak --password=inno_bak --host=127.0.0.1 --stream=tar . | gzip > `date +%Y-%m-%d_%H-%I-%S`.tar.gz

//保存到远程主机
innobackupex --user=inno_bak --password=inno_bak --host=127.0.0.1 --stream=tar . | ssh root@192.168.100.10  "cat - > /root/`date +%Y-%m-%d_%H-%I-%S`.tar;"

备份二进制日志

二进制日志能用做时间点恢复，所有需要进行备份

//使用mysqlbinlog 工具进行备份
//需要创建一个具有复制权限的登陆账号
grant replication slave ON *.* to "bak_binlog"@"%" identified by 'bak_binlog';
//在命令行下输入命令
mysqlbinlog --raw --read-from-remote-server --stop-never --host=127.0.0.1 --user=bak_binlog --password=bak_binlog "binlog.000004"
//--read-from-remote-server 从远程服务器读取二进制日志
//--stop-never 不停止，一直监控远程服务器二进制日志是否更新
//--raw 将结果输出到文件