数据库数据的备份与恢复

mysqldump备份/恢复
数据库备份概述
数据库备份方式
物理备份
--冷备：cp、tar、...
逻辑备份：
--mysqldump
--mysql
数据库备份策略
完全备份
--备份所有数据
--一台服务器的所有数据
--一个库的所有数据
--一张表的所有数据
增量备份
--备份上次备份后，所有新产生的数据
差异备份
--备份完全备份后，所有新产生的数据
物理热备
物理备份与恢复
备份操作
--cp -pr /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/数据库/

例：
cp -r /var/lib/mysql/* /db_bak/
scp -r /db_bak 192.168.4.51:/
rm -rf /var/lib/mysql
mv /db_bak /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl restart mysqld

拓展：
重新初始化数据库，只适合刚创建的没有数据的数据库
rm -rf /var/lib/mysql
systemctl restart mysqld
tail -100 /var/log/mysqld.log | grep password
alter user user() identified by "123qqq...A";

逻辑备份：备的是产生数据的命令，备的是数据对应的SQL命令
逻辑备份与恢复
备份操作
--mysqldump -uroot -p密码 库名 > 路径/xxx.sql
恢复操作
--mysql -uroot -p密码 库名 < 路径/xxx.sql
库名表示方式
---A或--all-databases 所有库
--数据库名 单个库
--数据库名 表名 当张表
---B 数据库1 数据库2 多个库
无论备份还是恢复，都要验证用户权限

例：
备份：
]# mysqldump -uroot -p123qqq...A -A > /mydata/all.sql
]# mysqldump -uroot -p123qqq...A db3 > /mydata/db3.sql
]# mysqldump -uroot -p123qqq...A db3 user > /mydata/db3_user.sql
]# mysqldump -uroot -p123qqq...A -B mysql db3 > /mydata/twodb_mysql_db3.sql
恢复：
mysql> drop database db3;
mysql> create database db3;
]# mysql -uroot -p123qqq...A db3 < /mydata/db3.sql
或
mysql> create database db4;
mysql> use db4
mysql> source /mydata/db3.sql

#增量备份
#实时增量备份
binlog日志简介
二进制日志的用途及配置方式
--用途：记录所有更改数据的操作（除查询之外的SQL命令）
--配置（默认没有启用）
log_bin[=dir/name]（不写的话使用默认日志名称）
server_id=数字(1~255之间)
max_binlog_size=数字m
使用binlog日志
启用binlog日志
采用binlog日志的好处
--记录除查询之外的所有SQL命令
--可用于数据恢复
--配置mysql主从同步的必要操作

例：
vim /etc/my.cnf
[mysqld]
server_id=50 #指定I值
log-bin #开启binlog日志
binlog_format="mixed" #设置日志记录格式
systemctl restart mysqld
ls /var/lib/mysql
/var/lib/mysql/dba50-bin.000001 #日志文件
/var/lib/mysql/dba50-bin.index #日志索引文件

binlog相关文件
--主机名-bin.index #记录已有日志文件名
--主机名-bin.000001 #第1个二进制日志
--主机名-bin.000002 #第2个二进制日志
--。。。
手动生成新的日志文件（默认日志文件大小超过500M时自动生成新的日志文件）
1.重启mysql服务
2.执行sql操作flush logs;
3.mysqldump --flush-logs
4.mysql -uroot -p密码 -e 'flush logs'

例：
mysql> show master status; #查看现在使用的日志文件和偏移量
mysql> flush logs; #生成新的日志文件
mysql> show master status;
或
]# mysqldump --flush-logs -uroot -p123qqq...A db3 user > /mydata/db3.sql
或
]# mysql -uroot -p123qqq...A -e "flush logs"

分析binlog日志
查看日志当前记录格式
--show variables like "binlog_format";
修改日志记录格式：主配置文件/etc/my.cnf中修改
--1.statement：每一条修改数据的SQL命令都会记录在binlog日志中
--2.row：不记录SQL语句上下文相关信息，仅保存那条记录被修改
--3.mixed：是以上两种格式的混合使用
使用mysqlbinlog工具
--格式：mysqlbinlog [选项] binlog日志文件名
常用选项
-- --start-datetime="yyyy-mm-dd hh:mm:ss"
-- --stop-datetime="yyyy-mm-dd hh:mm:ss"
-- --start-position=数字（偏移量）
-- --stop-position=数字（偏移量）

例：
]# mysqlbinlog /var/lib/mysql/dba50-bin.000001
]# mysqldump -uroot -p123qqq...A db3 user > /mydata/user1.sql
]# mysql -uroot -p123qqq...A
mysql> use db3
mysql> insert into user(name,uid) values("kenji",778);
mysql> insert into user(name,uid) values("bob",878);
mysql> insert into user(name,uid) values("chihiro",978);
mysql> select * from db3.user;
mysql> delete from user where uid in (778,878,978);
]# mysqlbinlog /var/lib/mysql/dba50-bin.000001 | grep insert
]# mysqlbinlog /var/lib/mysql/dba50-bin.000001 | grep select
]# mysqlbinlog /var/lib/mysql/dba50-bin.000001 | grep delete

 

binlog恢复数据
基本思路
--使用mysqlbinlog提取例时sql操作
--通过管道交给SQL命令值型
应用示例
--执行第1份binlog所记录的更改操作

]# mysqlbinlog --start-position=328 --stop-position=1264 /var/lib/mysql/dba50-bin.000001 | mysql -uroot -p123qqq...A

管理日志文件
1.指定日志名称和存储位置
]# mkdir /mylog
]# chown mysql /mylog
]# vim /etc/my.cnf
[mysqld]
#log-bin #开启binlog日志
log-bin=/mylog/dblog #开启binlog日志并指定日志路径，日志名
]# systemctl restart mysqld
]# ls /mylog/
dblog.000001 dblog.index

清理binlog日志（删除后同步索引文件，系统命令rm删除，索引文件不同步）
删除早于指定版本之前的binlog日志
--purge master logs to "binlog文件名"；
删除所有binlog日志，重建新日志
--reset master;

例：
mysql> show master status;
mysql> purge master logs to "dblog.000004";
mysql> show master status;
]# ls /mylog/
]# cat /mylog/dblog.index
mysql> reset master;
mysql> show master status;
]# ls /mylog/
]# cat /mylog/dblog.index

#使用第3方软件percona提供的命令innobackupex做增量备份
1、完全备份和恢复（备份时不锁表）
2、增量备份和恢复
3、在完全备份里恢复一个表的所有数据

下载适配的 RPM 包并安装– percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
]# scp libev-4.15-1.el6.rf.x86_64.rpm 192.168.4.50:/root/ #依赖包，系统光盘没有
]# scp percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm 192.168.4.50:/root/
]# yum -y install libev-4.15-1.el6.rf.x86_64.rpm
]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
# rpm -ql percona-xtrabackup-24
/usr/bin/innobackupex #备份 innodb 、 xtrdb 、 myisam 引擎的表
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup #备份 innodb 、 xtrdb 引擎的表

mysql备份工具
常用的mysql备份工具
物理备份缺点
--跨平台性差
--备份时间长、冗余备份、浪费存储空间
mysqldump备份缺点
--效率较低，备份和还原速度慢
--备份过程中，数据插入和更新操作会被挂起
XtaBackup工具
一款强大的在线热备份工具
--备份过程中不锁库表，适合生产环境
--由专业组织Percona提供（改进MySQL分支）
主要含两个组件
--xtrabackup：C程序，支持InnoDB/XtraDB
--innobackupex：以Perl脚本封装xtrabackup，还支持MyISAM
innobackupex 基本选项
常用选项 含 义
--host 主机名
--user 用户名
--port 端口号
--password 密 码
--databases 数据库名 --databases="库名"#单个库--databases="库1 库2"#多个库--databases="库.表"#单个表
--no-timestamp 不用日期命名备份文件存储的子目录名
--redo-only 日志合并
--apply-log 准备还原 ( 回滚日志 )
--copy-back 恢复数据
--incremental 目录名 增量备份
--incremental-basedir=目录名 增量备份时,指定上一次备份数据存储的目录名
--incremental-dir= 目录名 准备恢复数据时,指定增量备份数据存储的目录名
--export 导出表信息
import 导入表空间

例：

完全备份
]# innobackupex --user root --password 123qqq...A /allback --no-timestamp #完全备份
]# ls /allback/
完全恢复
[root@dba50 ~]# systemctl stop mysqld
[root@dba50 ~]# rm -rf /var/lib/mysql/* #恢复时要求空的库目录
[root@dba50 ~]# ls /var/lib/mysql/
]# innobackupex --user root --password 123qqq...A --apply-log /allback #准备恢复数据
]# innobackupex --user root --password 123qqq...A --copy-back /allback #恢复数据
]# ls /var/lib/mysql/
]# chown -R mysql:mysql /var/lib/mysql/
]# systemctl restart mysqld

增量备份
工作过程:
好处：在线热备不锁表
对Innodb存储引擎的表时增量备份
Innodb存储引擎有事务日志和支持事务回滚
]# cd /var/lib/mysql 事务日志文件
ib_logfile0
ib_logfile1 #已经存在的数据
ibdata1 #正在写入的数据，未提交的数据

LSN日志序列号

备份目录下的相关配置文件说明：
xtrabackup_checkpoints 文件
[root@dba2 ~]# cat /allback/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0 #日志序列号开始
to_lsn = 2547242 #日志序列号结束
last_lsn = 2547251
compact = 0
recover_binlog_info = 0
[root@dba2 ~]# cat /new1dir/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2547242 #日志序列号开始
to_lsn = 2554226 #日志序列号结束
last_lsn = 2554235
compact = 0
recover_binlog_info = 0

ibdata1 #备份时未提交的数据
ibdata1.mata #增量备份时未提交的数据
xtrabackup_info #备份时已提交的数据

应用示例
– 必须先有一次完全备份 , 备份到 /allbak
– 第 1 次增量备份到 /new1
– 第 2 次增量备份到 /new2
mysql> create table a(id int);
mysql> insert into a values(666);
...
]# innobackupex --user root --password 123qqq...A /allback --no-timestamp #增量备份前需一次完全备份
mysql> insert into db5.a values(777);
...
]# innobackupex --user root --password 123qqq...A --incremental /new1dir --incremental-basedir=/allback --no-timestamp
#第一次增量备份
mysql> insert into db5.a values(888);
...
]# innobackupex --user root --password 123qqq...A --incremental /new2dir --incremental-basedir=/new1dir --no-timestamp
#第二次增量备份
周一：完全备份 周二～周日：增量备份

恢复备份
1.准备恢复数据
2.合并日志
3.把备份数据拷贝到数据库目录下
4.修改数据库目录所有者和组为mysql
5.启动数据库服务
6.登陆查看数据

]# rm -rf /var/lib/mysql/* #恢复时要求空目录
]# innobackupex --user root --password 123qqq...A --apply-log --redo-only /allback/ #合并日志
]# innobackupex --user root --password 123qqq...A --apply-log --redo-only /allback/ --incremental-dir=/new1dir #合并日志
]# innobackupex --user root --password 123qqq...A --apply-log --redo-only /allback/ --incremental-dir=/new2dir #合并日志
]# innobackupex --user root --password 123qqq...A --copy-back /allback/ #拷贝文件
]# ls /var/lib/mysql
]# chown -R mysql:mysql /var/lib/mysql #给全属性
]# systemctl restart mysqld
]# mysql -uroot -p123qqq...A
mysql> show databases;
mysql> select * from db5.a;

在完全备份文件中恢复单个表
• 应用示例
– 完全备份数据库到 /allbak 目录
– 导出表信息

备份表
]# innobackupex --user root --password 123qqq...A --databases="db5" /db5full --no-timestamp
删除表
mysql> drop table b;
恢复表
1.导出表信息
2.创建删除的表
3.删除表空间（存储数据的表文件 表.idb）
4.拷贝表信息文件到数据库目录下
5.修改表文件的所有者和所有组未mysql
6.导入表信息
7.查看表记录

]# innobackupex --user root --password 123qqq...A --databases="db5" --apply-log --export /db5full #到处表信息
]# ls /db5full/db5/b.*
/db5full/db5/b.cfg #表信息文件
/db5full/db5/b.exp #表信息文件
/db5full/db5/b.frm #表结构文件
/db5full/db5/b.ibd #表空间文件

mysql> create table b(name char(10)); #创建表
]# ls /var/lib/mysql/db5/b.*
/var/lib/mysql/db5/b.frm #表结构文件
/var/lib/mysql/db5/b.ibd #表空间文件
mysql> alter table db5.b discard tablespace; #删除表空间文件
]# ls /var/lib/mysql/db5/b.*
/var/lib/mysql/db5/b.frm
]# cp /db5full/db5/b.{cfg,exp,ibd} /var/lib/mysql/db5/ #拷贝表信息文件
]# chown mysql:mysql /var/lib/mysql/db5/b.* #修改阿所有者
]# ll /var/lib/mysql/db5/b.*
mysql> alter table db5.b import tablespace; #导入表空间
mysql> select * from db5.b;
]# rm -rf /var/lib/mysql/db5/b.{cfg,exp} #删除表信息文件