mysqldump实时增量备份,innobackupex

一、数据库备份与恢复
完全备份与恢复 mysqldump 和 innobackupex
增量备份与恢复 binlog 日志和 innobackupex
物理备份
—冷备；cp 、tar、… …
逻辑备份
—mysqldump
—mysql
逻辑备份；在执行逻辑备份操作时，会根据备份数据生成对应的sql 命令，把sql 命令存储在指定的文件，文件记录的就是对应的sql 命令，执行备份文件里的sql 命令来恢复数据

物理备份及恢复:指定备份库和表对应的文件
备份操作
cp -rp /var/lib/mysql/数据库 备份目录/文件名
tar -zvf xxx.tat.gz /var/lib/mysql/数据库/*
恢复操作
cp -rp 备份目录/文件名 /var/lib/mysql/
tar -zxvf xxx.tar.gz -C /var/lib/mysql/数据库名/

逻辑备份及恢复:在执行备份命令时，根据备份的库表及数据生成对应的sql 命令，把sql 存储到指定的文件里
备份操作：
mysqldump -uroot -p密码 -B库名1 库名2 库名3 > 路径/xxx.sql
恢复操作：
mysql -uroot -p密码 库名 < 路径/xxx.sql
mysqldump -uroot -p12346 db3 user > /root/user.sql
cat /root/user.sql

drop table db3.user;
mysql -uroot -p123456 db3 < /root/user.sql
use db3;
show tables;
select * from user;

库名表示方式
–all-databases 或 -A //所有库
数据库名 //单个库
数据库名 表名 //单张表
-B 数据库1 数据库2 //多个库
无论是备份还是恢复，都要验证用户及权限

数据备份策略
完全备份:
备份所有数据（一张表的所有数据 一个库的所有数据 一台数据库的所有数据）
备份新产生数据（差异备份 和 增量备份 都备份新产生的数据）
差异备份：（备份子完全备份后，所有新产生的数据）
增量备份：（备份自上次备份后，所有新产生的数据）
如何对数据做备份
选择备份策略：完全备份+差异备份
完全备份+增量备份
数据备份时间，数据服务器访问量少的时候执行备份
数据备份频率，根据数据产生量，决定备份频率
备份文件的命名，库名-日期.sql
备份文件的存储设置，准备独立的存储设备存储备份文件
使用周期性计划任务执行本机脚本

二、完全备份与完全恢复
启用binlog 日志
采用binlog 日志的好处
–记录除查询之外的所有sql 名令
–可用于数据恢复
–配置mysql 主从同步的必要条件
vim /etc/my.cnf
[mysqld]
… …
log_bin=db50 //启用binlog 日志
server_id=50 //指定id值
binlog_format=”mixed” //日志记录的混合格式
systemctl restart mysqld
binlog 相关文件
主机名-bin.index //记录已有日志文件名
主机名-bin.000001 //第1个二进制日志
主机名-bin.000002 //第2份二进制日志
手动生成新的日志文件；
重启mysql 服务
执行sql 操作 mysql > flush logs;
mysqldump –flush-logs
mysql -uroot -p密码 -e ‘flush logs’
清理binlog 日志
删除早于指定版本的binlog 日志
purge master logs to “binlog文件名”；
删除所有binlog 日志，重建新日志
reset master;

show master status; //显示日志信息

分析binlog日志
使用mysqlbinlog 工具
格式：mysqlbinlog [选项] binlog日志文件名
常用选项
–start-datetime=”yyyy-mm-dd hh:mm:ss”
–stop-datetime=”yyyy-mm-dd hh:mm:ss”
–start-position=数字
–stop-position=数字

xtrabackup 工具
一款强大的在线热备份工具
备份过程中不锁库表，适合生产环境
由专业组织percona 提供（改进mysql 分支）
主要含两个组件
xtrabackup : C程序，支持 inndb/xtradb
innobackupex :以perl 脚本封装xtrabackup,还支持myisam
yum -y install perl-DBD-mysql perl-Digest-MD5
rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
rpm -ql percona-xtrabackup-24
innobackupex基本选项
–host //主机名
–user //用户名
–port //端口号
–password //密码
–databases=“库名” //数据库名
–no-timestamp //不用日期命名备份文件存储的子目录名
–redo-only //日志合并
–apply-log //准备还原(回滚日志)
–copy-back //恢复数据
–incremental 目录名 //增量备份
–incremental-basedir=目录名
//增量备份时,指定上一次备份数据存储的目录名
–incremental-dir=目录名
//准备恢复数据时，指定的增量备份数据存储的目录名
–export //导出表信息
import //导出表空间

使用 innobackupex 完全备份与恢复
数据完全备份
innobackupex –user root –password 123456 /目录名 –no-timestamp
ls /allbak/
数据完全恢复(要求数据库目录必须为空)
systemctl stop mysqld
rm -rf /varr/lib/mysql/*
ls /var/lib/mysql
准备恢复数据
innobackupex –user root –password 123456 –apply-log 目录名
把备份的数据恢复
innobackupex –user root –password 123456 –copy-back /allbak
chown -R mysql:mysql /var/lib/mysql
systemctl restart mysqld

在线热备不锁表
使用 innobackupex 做增量备份和增量恢复
innodb 存储引擎的表支持事务和事务回滚
事务日志文件记录sql 操作
cd /var/lib/mysql/
ibdata1
ib_logfile0
ib_logfile1

执行第一次备份
innobackupex –user root –password 123456 /fullbak –no-timestamp
ls /fullbak
cat /fullbak/xtrabackup_checkpoints

insert into db6.a values(0000);
insert into db6.a values(0000);
执行增量备份
innobackupex –user root –password 123456 –incremental /new1dir –incremental-basedir=/fullbak –no-timestamp
cat /fullbak/xtrabackup_checkpoints
insert into db6.a values(111);
insert into db6.a values(111);
innobackupex –user root –password 123456 –incremental /new2dir –incremental-basedir=/fullbak –no-timestamp
cat /fullbak/xtrabackup_checkpoints

数据增量恢复
innobackupex –user root –password 123456 –apply-log –redo-only /fullbak
cat /fullbak/xtrabackup_checkpoints
innobackupex –user root –password 123456 –apply-log –redo-only /fullbak –incremental-dir=/new1dir
cat /fullbak/xtrabackup_checkpoints
innobackupex –user root –password 123456 –apply-log –redo-only /fullbak –incremental-dir=/new2dir
cat /fullbak/xtrabackup_checkpoints
rm -rf /new1dir
rm -rf /new2dir
把备份目录的数据拷贝回数据库目录下
systemctl stop mysqld
rm -rf /var/lib/mysql/*
innobackupex –copy-back /fullbak
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

innobackupex –user root -password 123456 –databases=”db6” /db6bak

恢复删除的表
导出表信息
innobackupex –user root -password 123456 –apply-log –export /db6bak
ls /db6bak/db6/a.*
按照源表结构创建删除的表

create table db6.b(id int);
system ls /var/lib/mysql/db6/b.*
删除表空间
alter table db6.b discard tablespace;
system ls /var/lib/mysql/db6/b.*
导入表信息
system cp /db6bak/db6/b.{exp,cfg,ibd} /var/lib/mysql/db6/
sysrem ls /var/lib/mysql/db6/a.*

表空间.ibd：用来存储表的数据和索引的文件

在 innodbackupex 的完全备份文件里恢复单个表的数据
innobackupex –user root –password 123456 –incremental /new2dir –incremental-basedir=/new1dir –no-timestamp
ls /new2dir
cat /new2dir/xtrabackup_checkpoints