MySQL 学习（四）---数据备份与恢复

数据备份与恢复
一、数据库备份相关概念
1.1 数据备份的目的：数据被误删或者损害导致数据数据丢失，是备份文件恢复数据
1.2 数据备份方式：
物理备份：指定备份库和表对应的文件
cp -r /var/lib/mysql /opt/mysql.bak
物理备份：----冷备： cp 、tar
备份：
– cp -rp /var/lib/mysql/数据库 备份目录/文件名
– tar -zcvf xxx.tar.gz /var/lib/mysql/数据库/*
恢复：
– cp -rp 备份目录/文件名 /var/lib/mysql/
– tar -zcvf xxx.tar.gz -C /var/lib/mysql/数据库名/

逻辑备份及恢复：----mysqldump 、mysql
备份： mysqldump -uroot -p 密码 库名 > 路径/xxx.sql
恢复： mysql -uroot -p 密码 库名 < 路径/xxx.sql
库名表达方式： --all-databases 或 -A 所有库
——数据库名 单个库
——数据库名 表名 单张表
-—— -B 数据库1 数据库2 多个库
注意：无论是备份还是恢复，都要验证用户权限
逻辑备份：在执行备份命令时，根据备份的库表及数据生成对应的sql命令，把sql存储到指定的文件。
1.3数据备份策略
完全备份：备份所有数据（一张表的所有数据，一个库的所有数据，一台数据库的所有数据）
备份新产生的数据：差异备份；增量备份
差异备份：备份自完全备份后，所有新产生的数据；
增量备份：备份自上次备份后，所有新产生的数据；

1.4工作中如何对数据做备份
1.4.1 选择备份策略：
完全备份+差异备份
完全备份+增量备份
1.4.2数据备份时间 ：数据服务访问量少的时候执行备份。
1.4.3数据备份频率 ： 根据数据产生的量决定备份频率。
1.4.5备份文件的命名 ： 库名-日期.sql
1.4.6备份文件的存储设置 ：准备独立的存储设备存储备份设备
1.4.7如何执行备份： 使用周期性计划任务执行本机脚本

二、完全备份与完全恢复
完全备份数据的命令
#rpm -qf /usr/bin/mysqldump
#man mysqldump
目录要先创建好，
数据库名的表示方式：
一张表的所有数据 ： 库名 表名
一个库的所有数据： 库名
备份一台数据库的所有数据 ： --all-databases 或-A
-B 库名1 库名2 库名N ：把多个库的所有数据备份到一个文件中去
完全恢复数据命令
#mysql -uroot -p123456 数据库名 < 目录名/文件名.sql
完全备份的缺点:
备份和恢复数据会给表加写锁；使用完全备份文件恢复数据，只能把数据恢复到备份时的状态，
完全备份后新写入的数据无法恢复；数据量大时，备份和恢复数据都要受磁盘I/O

例子：完全备份#mkdir -p /mydatabak
#mysqldump -uroot -p123456 home >/mydatabak/home.sql
#ls -l /mydatabak
#du -sh /mydatabak/home.sql
#cat /mydatabak/home.sql
#mysqldump -uroot -p123456 db3 usertab > /mydatabak/db3-usertab.sql
#cat /mydatabak/db3-usertab.sql
完全恢复：#mysqldump -uroot -p123456 home < /mydatabak/home.sql
#mysqldump -uroot -p123456 db3 usertab < /mydatabak/db3-usertab.sql
使用source 命令恢复数据：
mysql > create database bbsdb;
mysql > use bbsbd;
mysql > source /mydatabak/studb.sql
例子：要求 每周一晚上18：00备份home 库的所有数据到本机的/dbbak目录下，备份文件名称为： 日期_库名.sql
#vim /root/bakhome.sh
#!/bin/bash
day=data + %F
if [ ! -e /dbbak ] ;then
mkdir /dbbak
fi
mysqldump -uroot -p 123456 home >/dbbak/${day}_home.sql
#chmod +x /root/bakhome.sh
#/root/bakhome.sh
#ls /dbbak/*.sql
#crontab -e
00 18 * * 1 /root/bakhome.sh &> /dev/null

三、增量备份与增量恢复
3.1 启动mysql 数据库服务的binlog日志文件 实现实时增量备份
3.1.1 binlog日志介绍
：是mysql数据库服务日志文件的一种，默认没有启用，二进制日志。
优势：记录除查询之外更改数据的sql命令；可用于数据恢复；配置mysql主从同步的必要条件；
查询命令例如： select show desc
写命令例如： insert update delete create drop
3.1.2 启用binlog 日志
vim /etc/my.cnf
log_bin[=dir/name] 启用binlog日志
server_id =数字[1-255] 指定id值
max_binlog_size=数字m
binlog-format=“mixed”
#systemctl restart mysqld
----主机名-bin.index 记录已有日志文件名
----主机名-bin.000001 第一个二进制日志
查看日志当前记录格式： mysql > show variables like “binlog_format”;
三种记录格式：1-- statement :每一条修改数据的sql命令都会记录在binlog日志中；
2-- row ：不记录sql语句上下文相关信息，仅保存哪条记录被修改；
3-- mxied : 是以上两种格式的混合使用；
]# ls /var/lib/mysql/主机名-bin.000001
]# cat /var/lib/mysql/主机名-bin.index
3.1.3 查看binlog日志文件内容
]#mysqlbinlog /var/lib/mysql/pc52-bin.000001
查询binlog信息： mysql > show binary logs
3.1.4 手动生成新的日志文件方法
*****默认日志文件大于500M时自动创建新日志文件
方法一： 重启mysql 服务 #systemctl restart mysqld
方法二： 执行sql操作 mysql > flush logs;
方法三： mysqldump --flush-logs;
方法四： mysql -uroot -p 密码 -e ‘flush logs’
3.1.5 删除已有的binlog日志文件
—purge master logs to “binlog文件名”； 删除早于指定版本的binlog日志
—reset master; 删除所有binlog日志 ，重建新日志；
mysql> show master status; 显示当前正在使用的binlog日志信息

3.1.6 分析binlog日志记录
使用mysqlbinlog 工具
格式： mysqlbinlog [选项] binlog 日志文件名
选项： – start-datetime=“yyyy-mm-dd hh:mm:ss”
–stop-datetime=“yyyy-mm-dd hh:mm:ss” 指定时间范围选项
– start-position=数字 – stop-position=数字 偏移量
3.1.7 使用binlog日志记录恢复数据
思路：使用mysqlbinlog 提取历史SQL操作，通过管道交给mysql 命令执行
命令格式： #mysqlbinlog [选项] 日志文件名 | mysql -uroot -p密码
#cd /var/lib/mysql/
#mysqlbinlog pc51-bin.000001 | mysql -u root -p 123456
注意：如果误删除performance_schema库 ？
解决办法： 重新创建performance_schema库，并在mysql的bin目录下执行升级命令：mysql_upgrade -u root -p --force
四、MySQL 备份工具
4.1 常用的MySQL备份工具特点：
物理备份缺点：跨平台性差； 备份时间长，冗余备份，浪费存储空间；
mysqlbinlog备份缺点：效率较低，备份和还原速度慢；备份过程中，数据插入和更新操作会被挂起。
4.2 XtraBackup 工具
特点：一款强大的在线热备份工具，备份过程中不锁表，适合生成环境，由专业组织percona提供；
主要含两个组件：–xtrabackup :C程序，支持InnoDB /XtraDB;
–innobackuppex :以perl脚本封装xtarbackup，还支持myisam
4.3 安装percona
软件：wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/
binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
： libev-4.15-7.el7.x86_64.rpm
#rpm -ivh libev-4.15-7.el7.x86_64.rpm 依赖关系包
#yum -y install perl-DBD-mysql perl-Digest-MD5 依赖关系包
#rpm -ivh percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
#rpm -ql percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
/usr/bin/xtrabackup 只备份innodb 、xtardb表；
/usr/bin/innobackupex 可以备份MyISAM, InnoDB, XtraDB表；
xbcrypt：用来加密或解密备份的数据；
xbstream：用来解压或压缩xbstream格式的压缩文件。
4.4 备份命令
innobackupex备份命令的使用格式？
#innobackupex <选项>
]# man innobackupex
常用选项？
–user 用户名
–password 密码
–databases=“库名” “库名1 库名2” “库名.表名”
–no-timestamp 不使用时间戳做备份文件的子目录名
–redo-only 日志回滚合并（最后一次增量备份日志回滚不需要此选项 --apply-log-only）
–apply-log 准备还原（回滚日志）
–copy-back 恢复数据
–incremental 目录名 增量备份
–incremental-basedir=目录名 增量备份时，指定上一次备份数据存储的目录名
–incremental-dir=目录名 准备恢复数据时，指定增量备份数据存储的目录名
–export 导出信息表
import 导入表空间 **
++++innobackupex完全备份
# innobackupex --user root --password 123456
–databases="mysql performance_schema sys " /allbak --no-timestamp
++++innobackupex完全恢复 --copy-back
# rm -rf /var/lib/mysql 恢复时要求空的库目录
]# mkdir /var/lib/mysql
]# innobackupex --user root --password 123456 --copy-back /allbak
]# chown -R mysql:mysql /var/lib/mysql
]# systemctl restart mysqld
]#mysql -uroot -p123456
mysql> show databases;
4.5 事务日志文件
LSN 日志序列号
4.6 增量备份的工作过程
每一个备份目录下，都有记录当前目录备份信息的配置文件。
注意：增量备份时，必须先有一次备份，通常是完全备份。
]# cat 备份目录名/xtrabackup_checkpoints (记录备份类型 和lsn范围)
++++innobackupex增量备份与恢复
–incremental 目录名 #增量备份
–incremental-basedir=目录名 #增量备份时，指定上一次备份文件存储的目录名
先要有一次完全备份 存放目录 /fullbak
]#innobackupex --user root --password 123456 --databases=“gamedb mysql performance_schema sys”
/fullbak --no-timestamp
#cat /fullbak/xtrabackup_chechkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 2556873
last_lsn = 2556882
compact = 0
recover_binlog_info = 0
插入新记录，执行增量备份 存放目录 /new1bak
mysql> insert into gamedb.t1 values(40,“tom”,18,“hua1”) values(41,“tom1”,19,“hua2”) ;
]# innobackupex --user root --password 123456 --databases=“gamedb mysql performance_schema sys”
–incremental /new1dir --incremental-basedir=/fullbak --no-timestamp
#du -sh /fullbak/ /new1dir/ 查看完全备份与增量备份的文件大小
插入新记录，执行增量备份 存放目录 /new2bak
mysql> insert into gamedb.t1 values(43,“tom3”,22,“hua4”),(44,“tom4”,23,“hua5”);
]# innobackupex --user root --password 123456 --databases=“gamedb mysql performance_schema sys”
–incremental /new2dir --incremental-basedir=/new1dir --no-timestamp
++++++增量恢复
–apply-log 准备恢复数据
–redo-only 合并日志
–incremental-dir=目录名 #增量恢复数据时，指定备份目录名称
–copy-back 恢复数据
清空数据库目录
]# rm -rf /var/lib/mysql （这里不能重启服务，否在服务启动会失败）
]# mkdir /var/lib/mysql
准备恢复数据
]# innobackupex --user root --password 123456 --databases=“gamedb mysql performance_schema sys”
–apply-log --redo-only /fullbak
合并日志 （xtrabackup_checkpoints 存储了合并日志的信息）
]# innobackupex --user root --password 123456 --databases=“gamedb mysql performance_schema sys”
–apply-log --redo-only /fullbak --incremental-dir=/new1dir
#innobackupex --user root --password 123456 --databases=“gamedb mysql performance_schema sys”
–apply-log --redo-only /fullbak --incremental-dir=/new2dir
#cat /fullbak/xtrabackup_chechkpoints
拷贝备份文件到数据库目录
]# innobackupex --user root-- --password 123456 --databases=“gamedb mysql performance_schema sys”
–copy-back /fullbak
# ls /var/lib/mysql -l
修改文件的所有者/组mysql
]# chown -R mysql:mysql /var/lib/mysql
重启数据库服务
]# systemctl restart mysqld
]# mysql -uroot -p123456
#rm -rf /new1dir
#rm -rf /new2dir
++++++++++使用完全备份文件恢复单个表
–export 导出表信息 --import 导入表空间
mysql> alter table 库.表 discard tablespace; ------------ >删除表空间
mysql> alter table 库.表 import tablespace; ------>导入表空间
1完全备份
mysql> create database studb;
mysql> create table a(name char(20));
mysql> insert into a values (“bob”),(“bob”),(“bob”),(“bob”),(“bob”);
mysql> create table b(id int(4));
mysql> insert into b values (20),(20),(20),(20),(20),(20),(20);
]# innobackupex --user root --password 123456 --databases=“studb” /allbakstudb --no-timestamp
2查看备份目录文件列表
]# ls /allbakstudb
]# ls /allbakstudb/studb
#ls /var/lib/mysql/studb
3误删除a表: mysql> drop table studb.a;
4 使用完全备份文件恢复单个表
4.1 按照备份时的表结构创建删除的表
create table studb.a(name char(10));
4.2 删除创建表的表空间文件
#ls /var/lib/mysql/studb/a.
mysql> alter table studb.a discard tablespace;
#ls /var/lib/mysql/studb/a.

4.3 使用备份文件导出表信息
#ls /allbakstudb/studb/
]#innobackupex --user root --password 123456 --databases=“studb” --apply-log --export /allbakstudb
#ls /allbakstudb/studb/
4.4 把导出的表信息文件，拷贝到对应的数据库目录下，并修改所有者和组为mysql
]# cp /allbakstudb/studb/a.{cfg,exp,ibd} /var/lib/mysql/studb/
#ls  /var/lib/mysql/studb/a.*
]# chown mysql:mysql /var/lib/mysql/studb/a.*
4.5 导入表空间
mysql> alter table studb.a import tablespace;
[root@mysql51 ~]# rm -rf /var/lib/mysql/studb/a.cfg
[root@mysql51 ~]# rm -rf /var/lib/mysql/studb/a.exp
4.6 查看记录
mysql> select * from studb.a;