Mysql-3数据完全备份,表备份,增量备份

                         数据备份方式
          备份概述
                         物理备份及恢复
 完全备份
                         数据备份策略
          逻辑备份
                         完全备份及恢复

数据备份方式

• 物理备份
  冷备 : cp ,tar,
  备份操作
  cp r /var/lib/mysql 备份目录/mysql.bak
  tar zcvf /root/mysql.tar.gz /var/lib/mysql/*
  恢复操作
  cp -r 备份目录/mysql.bak /var/lib/mysql/
  tar -zxvf /root/mysql.tar.gz -C /var/lib/mysql/
  chown -R mysql.mysql /var/lib/mysql
• 逻辑备份
  mysqldump //备份命令
  mysql //恢复命令
  完全备份:
  mysqldump -uroot -p密码 库名 > 目录/xxx.sql //备份一个库
  mysqldump -uroot -p密码 库名.表名 > db_user.sql //备份单张表
  mysqldump -uroot -p密码 -A > allbak.sql // -A:备份所有库
  mysqldump -uroot -p密码 -B 库1 库2 > db2.sql // -B:备份多个库
  完全恢复:
  mysql -uroot -p密码 [库名] < 目录/xxx.sql
  1,创建空库
  mysql> create database db;
  2,导入备份文件
  mysql -uroot -p密码 db < db.sql

binlog日志

二进制日志;
记录除查询之外的所有SQL命令;
可用于数据备份和恢复;
配置mysql主从同步的必要条件

• binlog相关文件
  主机名-bin.index 索引文件
  主机名-bin.000001 第一个二进制日志
  主机名-bin.00000n 第二个二进制日志
• 生成日志
  1,重启服务 systemctl restart mysqld
  2,刷新日志 mysql> flush logs;
  3,备份数据 mysqldump

启用日志
vim /etc/my.cnf
[mysqld]
log_bin[=/mylog/db]  //启用binlog日志,指定日志存放位置及名
server_id=100 //指定id值,数值随意设置
systemctl restart mysqld //重启服务
mysql -uroot -p密码
mysql>show master status;  //查看日志信息
>flush logs; //刷新日志
>flush logs;
>system ls /mylog/ //查看日志文件
>purge master logs to "db.000003"; //删除3号之前的日志
>system ls /mylog
>system cat /mylog/db.index  //索引中不含3号之前的索引

使用binlog恢复数据

日志的记录方式"binlog_format"

• statement 报表模式
• row 行模式
• mixed 混合模式
  查看当前的日志记录方式
  mysql> show variables like “binlog_format”
  分析日志
  查看日志内容
  mysqlbinlog [选项] binlog日志文件名

---

选项	用途
- -start-datetime=“yyyy-mm–dd hh:mm:ss”	起始时间
- -stop-datetime=“yyyy-mm-dd hh:mm:ss”	结束时间
- -start-position=数字	起始偏移量
- -stop-position	结束偏移量

恢复数据
mysqlbinlog [选项] 日志文件 | mysql -uroot -p密码

修改配置文件,修改日志记录格式
vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=mysql-bin   //定义日志名默认在/var/lib/mysql/
binlog_format="mixed"
systemctl restart mysqld
ls /var/lib/mysql/mysql-bin.*  //查看新生成的日志
**对数据库进行增删改查,刷新日志**
**binlog日志会记录所有的数据库,表更改的操作**
**找到删除表记录的时间点,只要恢复之前的SQL操作即可**
mysqlbinlog /var/lib/mysql/mysql-bin.000002 //查看binlog日志,查找时间点
一,时间节点
mysqlbinlog \
   --start-datetime="2017-04-12 12:06:55" \ 
   --stop-datetime="2017-04-12 12:07:23" \
   /var/lib/mysql/mysql-bin.000002 | mysql -u root -p密码
//<font color=red> 恢复指定时间段的数据 <font>
二,位置节点
mysqlbinlog \
   --start-position=567 --stop-position=800
   /var/lib/mysql/mysql-bin.000002 | mysql -u root -p密码
 //恢复指定节点的数据

常用的MYSQL备份工具

• 物理备份缺点
  跨平台性差
  备份时间长,冗余备份,浪费存储空间
• mysqldump备份缺点
  效率低,备份和还原速度慢,锁表
  备份过程中,数据插入和更新操作被阻塞

XtraBackup备份工具

• 在线热备工具
  备份过程中,不锁表,适合生产环境
  由专业组织Percona提供
• 主要两个组件
  xtrabackup: C程序,支持InnoDb/XtraDB
  innobackupex: perl脚本封装xtrabakup,还支持MYISAM

rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm //依赖包
 yum -y  install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm //软件包

innobackpup常用选项

---

常用选项	含义
- -host	主机名
- -user	用户名
- -port	端口号
- -password	密码
- -databases	数据库名
- -databases=“库名”	–databases=“库1 库2” --databases=“库1.表”
- -no-timestamp	不用日期命名备份文件存储的子目录名
- -redo-only	日志合并
- -apply-log	准备恢复数据
- -incremental 目录名	增量备份
- -incremental-basedir=目录名	增量备份时,指定上一次备份数据存储的目录名
incremental-dir=目录名	准备恢复数据时,指定增量备份数据存储的目录名
- -export	导入表信息
import	导入表空间

命令格式

• 完全备份
  innobackupex --user 用户名 --password 密码 备份目录名 --no-timestamp
• 完全恢复
  innobackupex–apply-log 目录名 //准备恢复数据
  innobackupex --copy-back 目录名 //恢复数据
• 增量备份
  innobackupex --user 用户名 --password 密码 incremental 增量目录 --incremental-basedir=目录名 --no-timestamp
• 增量恢复
• innobackupex --apply-log --redo-only 目录名 --incremental-dir=目录名 //准备恢复数据
• innobackupex --copy-back 目录名 //恢复数据
  案例1–完全备份与恢复

innobackupex --user root --password 123456 /allbak --no-timestamp //完全备份
ls /allbak/  //查看备份文件
scp -r /allbak root@192.168.4.51:/ //远程拷贝到备份服务器
备份服务器192.168.4.51上操作
systemctl stop mysqld
rm -rf /var/lib/mysql*    //清空目录
innobackupex --apply-log /allback  //准备
innobackupex --copy-back /allbak  //恢复数据
chown -R mysql:mysql /var/lb/mysql  //赋予进程执行权限
systemctl start mysqld  //重启服务

案例2–回复单张表
步骤:
1,删除表空间
2,导出表信息
3,拷贝表信息文件到数据库目录下
4, 修改表信息的所有者及组用户为mysql
5,导入表空间
6,删除数据库目录下的表信息文件
7,查看表记录

统一主机上操作
mysql>alter table db.user discard tablespces;  //删除表空间
innobackupex --apply-log --export /allbak  //导出表信息
cp /allback/db/user.{cfg,exp,idb} /var/lib/mysql/db/ //拷贝表信息到数据库目录下
chown mysql.mysql /var/lib/mysql/db/user.* //更改权属
mysql> alter table db.user import tablespaces;  //导入表空间
rm -rf /var/lib/mysql/db/user.{cfg,exp}  //删除表信息
mysql> selcet * from db.user; //查看表记录

案例3–增量备份

数据库服务器
innobackupex --user root --password 123456 /fullbak --no-timestamp //完全备份
mysql> insert into db.user values(5,"lucy"); //插如数据,多插入几条数据
innobackupex --user root --password 123456 --incremental /new1dir --incremental-basedir=/fullbak --no-timestamp  //第一次增量备份
mysql> insert into db.user values (34,"XM");
innobackupex --user root --password 123456 --incremental /new2dir --incremental-basedir=/new1dir --no-timestamp  //第二次增量备份
scp -r /fullback root@192.168.4.51:/ //远程拷贝给备份服务器
scp -r /new1dir root@192.168.4.51:/
scp -r /new2dir root@192.168.4.51:/
在备份服务器恢复数据
systemctl stop mysqld 
rm -rf /varlib/mysql/*
innobackupex --apply-log --redo-only /fullbak //准备恢复数据
innobackupex --apply-log --redo-only /fullbak --incremental-dir=/new1dir //合并日志
innobackupex --apply-log --redo-only /fullbak --incremental-dir=/new2dir
innobakupex --copy-back /fullbal //拷贝数据到数据库目录下
chown -R mysql.mysql /var/lib/mysql  //修改所有者
systemctl start mysqld //重启服务