文章目录
- 数据库备份的分类
- 从物理与逻辑备份
- 常见的备份方法
- MySQL完全备份与恢复
- mysqldump备份数据库
- 数据恢复的两种方法
- 使用mysqldump进行完全备份存在的问题
- MySQL增量备份与恢复
- 什么是MySQL增量备份
- 增量备份有什么优缺点
- MySQL数据库增量恢复
- 如何能查看到日志文件中的操作语句?
- 一般恢复
- 断点恢复
- 基于时间点恢复
数据库备份的分类
- 程序错误
- 人为错误(大部分原因)
- 计算机失败
- 磁盘失败
- 灾难(如火灾,地震)和偷窃
从物理与逻辑备份
物理备份
- 冷备份:是在关闭数据库的时候进行的
- 热备份:数据库处于运行状态,这种备份方法依赖于数据库的日志文件
- 温备份(联机备份):数据库锁定表格(不可写入但可读)的状态下进行备份操作
逻辑备份
- 完全备份:每次对数据进行完整的备份
- 差异备份:备份那些自从上次完全备份之后被修改过的文件
- 增量备份:只有那些在上次完全备份或者增量备份后被修改的文件才会被备份
常见的备份方法
(一)物理冷备
- 备份时数据库处于关闭状态,直接打包数据库文件
- 备份速度快,恢复时也是最简单的
(二)专用备份工具mydump或mysqlhotcopy
- mysqldump常用的逻辑备份工具
- mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
(三)启用二进制日志进行增量备份
- 进行增量备份,需要刷新二进制日志
(四)第三方工具备份
- 免费的MySQL热备份软件Percona XtraBackup
MySQL完全备份与恢复
物理冷备份
(先关闭数据库,之后打包备份)
[root@localhost opt]# systemctl stop mysqld #关闭MySQL服务
[root@localhost ~]# mkdir /opt/backup
[root@localhost opt]# tar zcvf /opt/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data/
恢复数据库
恢复数据库,采用将备份数据mv成线上库文件夹的方式
#创建恢复目录
[root@localhost ~]# mkdir restore
[root@localhost data]# tar zxvf mysql_all_2020-08-23.tar.gz -C restore/ (解压到源目录)
[root@localhost ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/
#重启服务
[root@localhost ~]# systemctl start mysqld
# mysqldump备份数据库
mysqldump备份数据库
备份单个库
语法:
mysqldump -u 用户名 -p [密码] [选项] [库名] > /备份路径/备份文件名
例如:
[root@localhost ~]# mysqldump -uroot -p school > /opt/school.sql
多库备份
语法:
mysqldump -u 用户名 -p [密码] [选项] --databases 库名1 [库名2] ... > /备份路径/备份文件名
例如:
mysqldump -uroot -pabc123 --databases school student > /opt/schoo_sta.sql
对所有数据库完全备份
语法: mysqldump -u 用户名 -p [密码] [选项] *--all-databases > /备份路径/备份文件名* 例如:
举例子
mysqldump -uroot -pabc123 --all-databases > /opt/all.sql
mysqldump -u root -p *--opt --all-databases > /backup/ku.sql*
对库内特定的表进行备份
mysqldump -u 用户名 -p [密码] [选项] 数据库名 表名 > /备份路径/备份文件名
[root@localhost opt]# mysqldump -uroot -pabc123 school tmp > /opt/tmp.sql;
使用备份表的结构
mysqldump -u 用户名 -p [密码] [选项] -d 数据库名 表名 > /备份路径/备份文件名
例如
mysqldump -u root -p -d yiku yibiao > /backup/yiku-yibiao.sql
数据恢复的两种方法
- 使用 mysqldump命令导出的SQL备份脚本,在进行数据恢复时可使用以下方法导入
- source命令
- mysql命令
source命令恢复数据库
1 登录到MySQL数据库
2 执行source备份sql脚本的路径
3 mysql -u 用户名 -p [密码] < 表备份脚本路径
mysql> source /opt/schoo_sta.sql;
重启mysql服务,登录mysql,查看数据是否恢复
[root@localhost mysql]# systemctl start mysqld.service
使用mysql命令恢复数据
mysql -u 用户名 -p [密码] < 库备份脚本的路径
mysql -u root < /backup/all-data.sql
使用mysqldump进行完全备份存在的问题
1 备份数据中有重复数据
2 备份时间与恢复时间过长
MySQL增量备份与恢复
什么是MySQL增量备份
- 只有那些在上次完全备份或者增量备份后被修改的文件才会被备份
- 增量备份就是备份自上一次备份之后增加或变化的文件或者内容
增量备份有什么优缺点
- 优点:
- 没有重复数据,效率高,空间利用率最大化
- 备份量不大,时间短
- 缺点:
- 恢复麻烦:需要上次完全备份及完全备份之后所有的增量备份才能恢复,而且要对所有增量备份进行逐个反推恢复
- 安全性较低
MySQL没有提供直接的增量备份方法
- 可通过MySQL提供的二进制日志文件间接实现增量备份
- MySQL二进制日志文件对备份的意义开去二进程日志功能
开启二进制日志
[root@localhost data]# vim /etc/my.cnf
socket = /usr/local/mysql/mysql.sock
log-bin=mysql-bin //添加
[root@localhost ~]# systemctl restart mysqld.service
备份
[root@localhost opt]# mysqldump -uroot -p123123 zxc benat > /opt/benat.sql
实验误操作
mysql> insert into benat values (3,'wangwu',60,'nanjing');
mysql> delete from benat where id=1;
mysql> insert into benat values (4,'zhaoliu',90,'nanjing');
增量备份
[root@localhost data]# mysqladmin -uroot -p123123 flush-logs
会多一个mysql-bin.000002 文件
64位解码器
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 > /opt/bbk02.txt
mysql> source /opt/benat.sql;
mysql> select * from benat;
+----+----------+-------+----------+
| id | name | score | address |
+----+----------+-------+----------+
| 1 | zhangsan | 80.00 | sahnghai |
| 2 | lisi | 70.00 | beijing |
+----+----------+-------+----------+
2 rows in set (0.00 sec)
开始备份
[root@localhost data]# mysqlbinlog --no-defaults --stop-position='824' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p123123
[root@localhost data]# mysqlbinlog --no-defaults --stop-position='941‘ /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p123123
mysql> select * from inds;
+----+---------+-------+----------+
| id | name | score | address |
+----+---------+-------+----------+
| 1 | zhangsan | 80.00 | sahnghai |
| 2 | wangwu | 80.00 | shanghai |
| 3 | lisi | 60.00 | beijing |
| 4 | zhaoliu | 60.00 | nanjing |
+----+---------+-------+----------+
MySQL数据库增量恢复
如何能查看到日志文件中的操作语句?
mysqlbinlog --no-defaults --base64-output=decode-rows -v 日志文件名称 /opt/aaa.txt
一般恢复
将所有备份的二进制日志内容全部恢复
语法
mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p
断点恢复
基于位置恢复
- 就是将某个起始时间的二进制日志导入数据库中,从而跳过某个发生错误的时间点实现数据的恢复
恢复数据到指定位置
mysqlbinlog --stop-position='操作id' 二进制日志 |mysql -u 用户名 -p 密码
举例:mysqlbinlog --no-defaults --stop-position='293' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -pabc123
从指定的位置开始恢复数据
mysqlbinlog --start-position='操作id' 二进制日志 |mysql -u 用户名 -p 密码
举例:mysqlbinlog --no-defaults --start-position='293' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -pabc123
基于时间点恢复
- 使用基于时间点的恢复,可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作,所以我们需要一种更为精确的恢复方式
从日志开头截止到某个时间点的恢复
mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码
从某个时间点到日志结尾的恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码
从某个时间点到某个时间点的恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年
解码器(64位解码)
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 > /opt/bk02.txt