linux学习之Mysql数据备份与恢复

数据备份方式
1、物理备份
–冷备：cp、tar、
2、逻辑备份
–mysqlump
–mysql

数据备份策略
1、完全备份
–备份所有数据
2、增量备份
–备份上次备份以后所产生的新数据
3、差异备份
–在完全备份后，只备份所有新产生的数据

物理备份和恢复
备份：

cp -rp /var/lib/mysql/数据库  备份目录
tar -zcvf xxx.tar.gz /var/lib/mysql/数据库/*

恢复：

cp -rp 备份目录/文件名 /var/lib/mysql/
tar -zxvf xxx.tar.gz -C /var/lib/mysql/数据库名/

逻辑备份和恢复

备份：

mysqldump -uroot -p 库名  > /路径/xxx.sql

恢复

mysql -uroot -p 库名 < 路径/xxx.sql

库名表示方式：

–all-databases 或 -A 指所有库

mysqldump  -uroot -p --all-databases >  alldb.sql

数据库名指单个库

mysqldump -uroot -p userdb > userdb.sql

数据库名 表名 指某库某表

mysqldump -uroot -p 库名 表名

-B 数据库1 数据库2 指多个库

mysqldump -uroot -p -B 数据库1 数据库2 > double.sql

binlog日志

binlog日志概述：
binlog日志是二进制日志，记录所有更改数据的操作
【配置】
log_bin[=dir/name]
server_id=数字
max_binlog_size=数字m

用处：记录查询之外的所有SQL命令，可用于数据恢复，是配置主从同步的必要条件

启用binlog日志

vim /etc/my.cnf
[mysqld]
log_bin     //启用binlog日志
server_id=100   //指定id值

systemctl restart mysqld  //重启服务

binlog相关文件：
主机名-bin.index 记录已有日志文件名
主机名-bin.000001 第一个二进制日志
主机名-bin.000002 第二个二进制日志

手动生成新的日志文件：
1、重启mysql
2、在mysql中使用命令flush logs;
3、使用mysqldump --flush-logs命令
4、生成新日志文件mysql -uroot -p密码 -e ‘flush logs’

清理binlog日志
删除早于制定版本的日志文件

mysql> puurge master logs to 'binlog文件名';
mtsql>reset master;

binlog日志的三种记录格式：
1、statement：每一条修改数据的sql命令都会记录在binlog日志中
2、row：不记录sql语句上下文相关信息，仅保存哪条记录被修改
3、mixed：是以上两种格式的混合使用

查看目前日志记录格式：

mysql>show variables like "binlog_format";

修改日志记录格式

vim /etc/my.cnf
[mysql]
binlog_format=mixed

systemctl restart mysqld

分析binlog日志
使用mysqlbinlog工具
格式：mysqlbinlog [选项] binlog日志文件名
常用选项

--start-datetime="yyyy-mm-dd hh:mm:ss"
--stop-datetime="yyyy-mm-dd hh:mm:ss"
--start-position=数字
--stop-position=数字

使用binlog恢复数据
使用mysqlbinlog提取历史SQL操作，在管道给mysql执行命令
（相当于把执行过的命令再执行，达到恢复数据的效果）

cd /var/lib/mysql
mysqlbinlog mysql-bin.000001 | mysql -uroot -p密码

mysql备份工具特点
物理备份缺点：
跨平台性差，备份时间长、冗余备份、浪费存储空间
mysqldump备份缺点：
效率过低，备份还原速度慢、备份过程会导致数据插入更新被挂起

XtraBackup工具：
在线热备份工具，备份过程中不锁表。由Percona提供
包含两个组件：
xtrabackup：C程序，支持InnoDb/XtraDB
innobackupex:以Perl脚本封装xtrabackup，还支持MyISAM

安装percona
1、下载安装rpm包
percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

yum -y install perl-Digest-MD5.x86_64 rsync perl-DBD-MySQL   #依赖包
rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm   
rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

/usr/bin/innobackupex ：备份innodb、xtrdb、myisam引擎的表
/usr/bin/xtrabackup ：备份innodb、xtrdb引擎的表

innobackupex基本选项

常用选项	含义
–host	主机名
–user	用户名
–password	密码
–databases	数据库名
–no-timestamp	不用日期命名备份文件存储的子目录名
–redo-only	日志合并
–apply-log	准备还原
–copy-back	恢复数据
–incremental目录名	增量备份
–incremental-basedir=目录名	增量备份时，指定上一次备份数据存储的目录名
–export	导出表信息
import	导入表空间

ps：
–databases=“库名” #单个库
–databases=“库1 库2” #多个库
–databases=“库.表” #单个表

innobk应用案例

完全备份与恢复

1、完全备份

innobackupex --user root --password 密码   /backup  --no-timestamp   #完全备份

2、恢复数据

innobackupex --user root -password 密码 --apply-log /back  #准备恢复数据
rm -rf /var/lib/mysql/*   #恢复时要求库目录为空
innobackupex --user root --password 密码 --copy-back /backup #恢复数据

systemctl restart mysqld #重启服务,完成恢复

增量备份与恢复

在增量备份前，必须有一次备份，通常是完全备份
增量备份：

innobackupex --user root --password 密码 --databases =“库名列表” /fullbak  --no-timestamp  #完全备份

innobackupex --user root --password 密码 --databases="库名列表" --incremental /new1 --incremental-basedir="/fullbak" --no-timestamp   #第一次增量备份

innobackupex --user root --password 密码 --databases=“库名列表” --incremental /new2 --incremental-basedir="/new1" --no-timestamp #第二次增量

恢复：（数据丢失后）

innobackupex --user root --password 密码 --databases="库名列表" --apply-log --redo-only /fullbak  #完全恢复

innobackupex --user root --password 密码 --databases="库名列表" --apply-log --redo-only /fullback --incremental-dir="/new1"  #第一次增量恢复

innobackupex --user root --password 密码 --databases="库名列表" --apply-log --redo-only /fullbak --incremental-dir="/new2"  #第二次增量恢复

innobackupex --user root --password 密码 --databases="库名列表" --copy-back /fullbak  #拷贝文件

chown -R mysql:mysql /var/lib/mysql/    #数据库目录权限
重启服务

在完全备份中恢复单个表

innobackupex --user root --password 密码 --databases="test" /allbak --no-timestramp   #备份test库
mysql> drop table test.a;   #删掉test库中的a表
innobackupex --user root --password 密码 --databases="test" --apply-log --export /allbak  #导出表信息
mysql>create table test.a(id int……);    #创建表
mysql>alter table test.a discard tablespace;  #删除表空间

恢复单个表

mysql>system cp /allbak/test/a.{ibd,cfg,exp} /var/lib/mysql/test  #拷贝表信息文件
mysql>system chown mysql:mysql /var/lib/mysql/bbsdb/a.*  #修改所有者
mysql> alter table test.a import tablespace; #导入表空间
恢复完成