数据备份 恢复
数据库备份概述
数据备份目的
- 误操作(管理者,使用者)
- 硬件磁盘损坏
数据备份方式:
物理备份:
-
冷备: cp tar 直接拷贝库和对应的系统文件
innodb 引擎不适合冷备,事务日志在mysql根目录
逻辑备份:
- mysqldump
- mysql
生产环境,备份时,根据对应库表产生相应的sql命令,把sql命令存储到指定的文件里.
数据备份策略
完全备份
- 备份所有数据
增量备份
- 备份上次备份后,所有新产生的数据
差异备份
- 备份完全备份后,所有新产生的数据
物理备份
备份操作
cp -rp /var/lib/mysql/数据库 备份目录/文件名
tar -zcvf 文件名.tgz /var/lib/mysql/数据库/*
恢复操作
cp -rp 备份目录/文件名 /var/lib/mysql/
tar -zxvf 文件名.tgz -C /var/lib/mysql/数据库名/
示例
#拷贝整个mysql数据目录
cp -rp /var/lib/mysql /opt/mysqldir.bak
cp -rp /var/lib/mysql/mysql /opt/mysql.bak
cp -rp /var/lib/mysql/bbsdb/t1.* /opt
#打包mysql数据目录
tar -zcvf /opt/mysql.tgz /var/lib/mysql/*
#冷备恢复
rm -rf /var/lib/mysql/mysql
cp /opt/mysql.bak /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql/mysql
systemctl restart mysqld
逻辑备份
备份操作
mysqldump -uroot -p密码 [options] > 路径/xxx.sql
恢复操作
mysql -uroot -p密码 库名 < 路径/xxx.sql
mysql> source 文件.sql
库名表示方式
| Options | 说明 |
|---|---|
| --all-databases 或 -A | 所有库 |
| 数据库名 | 单个库 |
| 数据库名 表名 | 单张表 |
| -B 数据库1 数据库2 | 多个库 |
- 无论备份还是恢复,都要验证用户权限
示例
#将所有的库备份为 all.sql 文件
mysqldump -uroot -p -A > /opt/all.sql
#将buydb库备份为buydh.sql
mysqldump -uroot -p buydb > buydb.sql
#从备份文件buydb.sql重建buydb.sql
mysql> create database buydb;
mysql -uroot -p buydb <buydb.sql
生产环境数据备份
使用脚本备份
crond 执行 备份脚本( shell python go ruby perl )
-
备份频率
周
-
备份策略
完全+增量
完全+差异
-
备份时间
数据库服务访问量少的时候
-
备份文件名
日期_xx.sql
存储空间 lv + raid
使用集群
搭建MySQL主从结构实现数据的自动备份
- 优点 数据实时同步
- 缺点 误操作删除也会同步
生产环境一般使用集群负载,并使用脚本定期对从库进行备份,备份方式多采用增量备份
增量备份 恢复
启用mysql服务的binlog日志 实现增量备份
安装perconer xtrabackup软件,做增量备份
binlog简介
- binlog日志 也叫二进制日志.
| 类型 | 用途 |
|---|---|
| 二进制日志 | 记录所有更改数据的操作 |
使用 binlog 日志
binlog 日志模式
| 模式 | 说明 |
|---|---|
| STATEMENT | 每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题) |
| ROW | 不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。 |
| MIXED | 以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。 |
启用 binlog 日志
优点
- 记录除查询之外的所有SQL命令
- 可用于数据恢复
- 配置 MySQL 主从同步的必要条件
配置binlog日志
[mysqld]
server_id=101
#不写路径,会默认存放在数据目录,主机名-bin.000001作为文件名
log-bin[=dir/name]
binlog_format="mixed"
#查看 mysql binlog_format模式
mysql> show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
重启后,binglog日志
test1-bin.000001 #binlog日志,记录除查询外所有操作记录
test1-bin.000002 #重启后增加1
test1-bin.index #binlog索引,记录所有binlog日志文件名
查看binlog日志内容
[root@test1 mysql]# mysqlbinlog test1-bin.000002 |grep insert
insert into user(name) values("zhuxixi")
重新生成binlog日志
- flush logs;
- mysql -uroot -p -e "flush logs"
- systemctl restart mysqld
- mysqldump -uroot -p --flush-logs db1 > db1.sql #备份完db1,重新生成binlog日志
清理binlog日志
- 删除早于制定版本的binlog日志
purge master logs to "binlog文件名";
mysql> purge master logs to "rz.000002"; #删除文件名之前的日志文件,不包含本身
- 删除所有binlog日志,重建新日志
reset master;
reset master; #删除当前所有binlog日志,重新生成第一个binlog日志
分析binlog日志
查看日志当前记录格式
mysql> show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
三支记录格式
| 格式 | 说明 |
|---|---|
| statement | 每一条修改数据的sql命令都会记录在binlog日志中 |
| row | 不记录sql语句上下文相关信息,仅保存哪条记录被修改 |
| mixed | 是以上两种格式的混合使用 |
binlog日志恢复数据
show master status; #查看当前binlog日志 和偏移量
通过偏移量恢复
mysqlbinlog 选项 日志文件名
--start-position=数值 #偏移量
--stop-position=数值
mysqlbinlog --start-position=296 --stop-position=433 rz.000001 | mysql -uroot -p
通过时间恢复
mysqlbinlog 选项 日志文件名
--start-datetime="yyyy-mm-dd hh:mm:ss"
--stop-datetime="yyyy-mm-dd hh:mm:ss"
mysqlbinlog --start-datetime="2019-01-25 15:30:51" --stop-datetime="2019-01-25 15:30:53" rz.000001|mysql -uroot -p
