目录
一、数据备份的重要性
备份的主要目的是灾难恢复
在生产环境中,数据的安全性至关重要
任何数据的丢失都可能产生严重的后果
二、造成数据丢失的原因
程序错误
人为操作错误
运算错误
磁盘故障 - 灾难(如火灾、地震)和盗窃
三、数据库备份的分类
1、物理备份方法
冷备份(脱机备份):是在关闭数据库的时候进行的
热备份(联机备份)︰数据库处于运行状态,依赖于数据库的日志文件
温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
2、逻辑备份:对数据库逻辑组件(如表等数据库对象)的备份
从数据库的备份策略角度,备份可分为
完全备份:每次对数据库进行完整的备份
差异备份:备份自从上次完全备份之后被修改过的文件
增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份
四、常见的备份方法
1、物理冷备
备份时数据库处于关闭状态,直接打包数据库文件。备份速度快,恢复时也是最简单的
2、专用备份工具mydump或mysqlhotcopy,
mysqldump常用的逻辑备份工具,mysqlhotcopy仅拥有备份MylSAM和ARCHIVE表
3、启用二进制日志进行增量备份
进行增量备份,需要刷新二进制日志
4、第三方工具备份
免费的MySQL热备份软件Percona XtraBackup
五、MySQL完全备份
是对整个数据库、数据库结构和文件结构的备份,保存的是备份完成时刻的数据库,是差异备份与增量备份的基础
优点
备份与恢复操作简单方便
缺点
数据存在大量的重复
占用大量的备份空间
备份与恢复时间长
六、数据库完全备份分类
1、物理冷备份与恢复
关闭MySQL数据库
使用tar命令直接打包数据库文件夹
直接替换现有MySQL目录即可
2、mysqldump备份与恢复
MySQL自带的备份工具,可方便实现对MySQL的备份
可以将指定的库、表导出为SQL脚本
使用命令mysql导入备份的数据
七、MySQL增量备份
1、使用mysqldump进行完全备份存在的问题
备份数据中有重复数据
备份时间与恢复时间过长
是自上一次备份后增加/变化的文件或者内容
2、特点
没有重复数据,备份不大,时间短
恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复,且要对所有增备份进行逐个反推恢复
MySQL没有提供直接的增量备份方法
可通过MySQL提供的二进制日志间接实现增量备份
MySQL二进制日志对备份的意义
二进制日志保存了所有更新或者可能更新数据库的操作
二进制日志在启动MySQL服务器后开始记录,并在文件达到max_ binlog_ size所设 置的大小或者接收到flush logs命令后重新创建新的日志文件
只需定时执行flush logs方法重新创建新的日志,生成二进制文件序列,并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份
八、MySQL数据库增量恢复
1、一般恢复
将所有备份的二进制日志内容全部恢复
2、基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
3、基于时间点恢复
跳过某个发生错误的时间点实现数据恢复
九、备份与恢复实操
创建一个数据库表
create database kgc;
show databases;
use kgc;
create table if not exists info1 (
-> id int (4) not null auto_increment,
-> name varchar (10) not null,
-> sex char (10) not null,
-> hobby varchar(50),
-> primary key (id));
insert into info1 values(1,'user1','male','running');
insert into info1 values(2,'user2','female','swimming');
desc info1;
show tables;
select * from info1;
MySQL 完全备份
InnoDB存储引擎的数据库在磁盘上存储成三个文件:
db.opt(表属性文件)
表名.frm(表结构文件)
表名.ibd(表数据文件)
8.1 物理冷备份与恢复
systemctl stop mysqld
yum -y install xz
#压缩备份
tar jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
#解压恢复
tar jxvf /opt/mysql_all_2020-11-22.tar.xz -C /usr/local/mysql/data
8.2 mysqldump备份与恢复
(1)完全备份一个或多个完整的库(包括其中所有的表)
mysqldump -u root -p[密码] --databases 库名1 [库名2].〉/备份路径/备份文件名.sql #导出的就是数据库脚本文件
例:
mysqldump -u root -pabc123 --databases kgc > /opt/kgc.sql
mysqldump -u root -p --databases mysql kgc > /opt/mysql-kgc.sql
(2)完全备份MysQL服务器中所有的库
mysqldump -u root -p[密码] --all-databases >/备份路径/备份文件名.sql例:
mysqldump -u root -p --all-databases > /opt/all.sql
(3)完全备份指定库中的部分表
mysqldump -u root -p[密码]库名[表名1][表名2]...>/备份路径/备份文件名.sql
例:
mysqldump -u root -p [-d] kgc infol info2 > /opt/kgc_info1.sql
#使用“-d"选项,说明只保存数据库的表结构
#不使用"-d”选项,说明表数据也进行备份
(4)查看备份文件
grep -v "^--" /opt/kgc_info1.sql | grep -v "^/" | grep -v "^$"
1、物理冷备份与恢复
首先创建一个数据库
create database school;
use school;
create table if not exists info1 (
id int(4) not null auto_increment,
name varchar(10) not null,
age char(10) not null,
hobby varchar(50),
primary key (id));
insert into info1 values(1,'user1',20,'running');
insert into info1 values(2,'user2',30,'singing');
关闭数据库服务,压缩备份
systemctl stop mysqld
yum -y install xz
cd /usr/local/mysql
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz data/
删除掉刚刚创建的school
drop databases school;
解压恢复
tar Jxvf /opt/mysql_all_$(date +%F).tar.xz data/ -C ./ data/
systemctl restart mysqld.service
mysql -uroot -p123456
2、MySQLdump备份与恢复(温备份)
完全备份所有库
[root@localhost opt]# mysqldump -uroot -p123456 --all-databases > /opt/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# ls
all.sql mysql-5.7.17 mysql_all_2023-07-18.tar.xz
boost_1_59_0.tar.gz mysql-5.7.17.tar.gz rh
完全备份一个或多个库包括其中的表
[root@localhost opt]# mysqldump -uroot -p123456 --databases three > /opt/three.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# ls
all.sql mysql-5.7.17 mysql_all_2023-07-18.tar.xz three.sql
boost_1_59_0.tar.gz mysql-5.7.17.tar.gz rh
完全备份指定库中的指定表
[root@localhost opt]# mysqldump -uroot -p123456 --databases four info4 > /opt/four_info4.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# ls
all.sql four_info4.sql mysql-5.7.17.tar.gz rh
boost_1_59_0.tar.gz mysql-5.7.17 mysql_all_2023-07-18.tar.xz three.sql
[root@localhost opt]# grep -v "^--" /opt/four_info4.sql | grep -v "^/" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `four` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `four`;
DROP TABLE IF EXISTS `info4`;
CREATE TABLE `info4` (
`id` int(11) DEFAULT NULL,
`name` char(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` char(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `info4` WRITE;
INSERT INTO `info4` VALUES (1,'user',11,'性别'),(2,'user',11,'性别');
UNLOCK TABLES;
查询备份文件
grep -v "^--" /opt/four_info4.sql | grep -v "^/" | grep -v "^$"
3、完全恢复
免交互查看数据库
[root@localhost opt]# mysql -uroot -p -e 'show databases;'
Enter password:
免交互删除数据库
mysql -uroot -p -e 'drop database three;'
免交互恢复数据库
[root@localhost opt]# mysql -uroot -p < /opt/three.sql
Enter password:
[root@localhost opt]# mysql -uroot -p -e 'show databases;'
Enter password:
4、增量备份
开启二进制日志
[root@localhost ~]# vim /etc/my.cnf