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
  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值