备份的目的只要是为了灾难恢复,备份还可以测试应用,回滚数据,修改和查询历史数据,审计等
日志在备份、恢复中起着重要作用
一、数据库备份的重要性
在生产环境中,数据的安全性至关重要
任何数据丢失都可能产生严重的后果
造成数据丢失的原因有:
程序错误
人为操作错误
运算错误
磁盘错误
灾难(如火灾、地震)和盗窃
容灾
二、数据库备份的分类
数据库备份可以分为物理备份和逻辑备份
1.物理备份:
对数据库操作系统的物理文件(如:数据文件、日志文件等)进行备份;适用于在出现问题的时候需要快速恢复的大型重要数据库
物理备份又分为:
冷备份(脱机备份):需要关闭数据库才能进行tar打包备份
热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件(mysqlhotcopy mysqlbackup)
温备份:数据库锁定表结构(不可写入但可读)的状态下进行备份操作(mysqldump)
2.逻辑备份:
逻辑备份是对数据库逻辑组件的备份,表示为逻辑数据库结构;这种类型的备份适用于可以编辑数据值或表结构
从数据库的备份策略来看,逻辑备份又分为完全备份、差异备份和增量备份
①完全备份
每次都对整个数据库进行完整的备份(包括数据库结构和文件结构);
缺点:有大量重复数据,占用磁盘空间最大;
优点:结构完整,恢复时速度快,直接把文件导入即可;
②差异备份
备份自从上次完全备份之后修改过的文件;后续的差异备份都会备份前面差异备份的内容,导致出现重复的数据,占用额外的磁盘空间;恢复时会先恢复完全备份,再导入最后一次差异备份的数据即可;
③增量备份
只有在上次完全备份或者增量备份后 被修改的数据(或文件)才会被备份;不会出现重复数据(导致额外占用磁盘空间);恢复时要先恢复完全备份,再按次序恢复增量备份
备份方式的比较
备份方式 完全备份 差异备份 增量备份
完全备份时的状态 表1、表2 表1、表2 表1、表2
第1次添加内容 创建表3 创建表3 创建表3
备份内容 表1、表2、表3 表3 表3
第2次添加内容 创建表4 创建表4 创建表4
备份内容 表1、表2、表3、表4 表3、表4 表4
逻辑备份的策略
逻辑备份策略的选择(频率)
一周一次进行完全备份,全备的时间需要再不提供业务或访问量少的时间区间进行,例:PM 10:00 到AM 5:00(看你公司情况)
增量备份:3天/2天/1天 一次增量备份
差异备份:选择特定的场景进行备份(比较少用到)
通过配置NFS可以提供额外的空间给mysql服务器使用;NFS严格上说不算是共享存储,知识共享了文件;
三、常见的备份方法
1.物理冷备
备份时,数据库要处于关闭状态,直接打包数据库文件;备份速度快,恢复也简单
2.专用备份工具mysqldump和mysqlhotcopy
mysqldump常用的逻辑备份工具;mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
3.启用二进制日志进行增量备份
进行增量备份时,需要刷新二进制文件
4.第三方工具备份
Navicat、免费的MySQL热备份软件Percona XtraBackup
四、数据库完全备份分类
是对整个数据库、数据库结构和文件结构的备份;保存的是备份完成时刻的数据库;是差异备份和增量备份的基础
优点:备份和恢复操作简单方便
缺点:数据大量重复,占用额外的磁盘空间,备份与恢复时等待时间长
物理冷备与恢复
关闭MySQL数据库
使用tar命令打包数据库文件夹
恢复:直接替换现有MySQL目录即可
mysqldump备份与回复
MySQL自带的备份工具,可以方便实现对MySQL的备份
可以见指定的库、表导出为SQL脚本文件
恢复:使用命令导入备份的数据
实验
环境准备
建库、建表,插入数据;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
| test1 |
+----------------+
2 rows in set (0.00 sec)mysql> select * from test;
+------+----------+-------+--------------+
| id | name | score | address |
+------+----------+-------+--------------+
| 0001 | zhangsan | 100 | 地址不详 |
| 0002 | lisi | 85 | bijing |
| 0003 | wangwu | 91 | 地球村 |
| 0004 | laoliu | 75 | nanjing |
+------+----------+-------+--------------+
4 rows in set (0.00 sec)mysql> select * from test1;
+------+----------+-------+-------+
| id | name | score | phone |
+------+----------+-------+-------+
| 0001 | zhangsan | 82.50 | 111 |
| 0002 | lisi | 85.00 | 222 |
| 0003 | wangwu | 91.00 | 333 |
+------+----------+-------+-------+
3 rows in set (0.00 sec)mysql>
mysql完全备份与恢复
1.物理冷备与恢复
第一步关闭mysqld
tar zxvf 直接备份指定路径的文件,不包括路径tar jxvf
按路径备份,会将整个路劲备份下来,但不会备份路径中其他的数据
第一种 zxvf
冷备
#关闭mysqld
systemctl stop mysqld
#
[root@test2 mysql]# cd /usr/local/mysql/
[root@test2 mysql]# tar zcvf data.tar.gz data/ -C /opt/mysql.bak/
恢复
第二种 jxvf
关闭mysql,安装 xz 压缩
systemctl stop mysqld
yum -y install xz
压缩备份
tar jcvf /opt/mysql_data_$(date +%F).tar.xz /usr/local/mysql/data/#j 表示按路径备份(打包)数据
解压恢复
[root@test2 ~]# tar jxvf /opt/mysql_data_.tar.xz -C /usr/local/mysql/[root@test2 ~]# cd /usr/local/mysql/[root@test2 mysql]# mv usr/local/mysql/data /usr/local/mysql/
2.mysqldump备份与恢复
完全备份
建库、建表,插入数据
create database school;
use school;
create table class (id int(4) zerofill not null,name char(10),score decimal(5,2),address varchar(80));
insert into class values (1,'yiyi',78,'shanghai');
insert into class values (2,'lizi',92,'henan');
insert into class values (3,'monor',88,'nanjing');
select * from class;完成后,quit退出MySQL
完全备份一个或多个完整的库(包括其中所有的表)
备份多个表
mysqldump -u用户名 -p密码 --databases 库1 库2 > 备份路径/备份文件名.sql
例:
[root@test2 ~]# mysqldump -uroot -p123456 --databases school test > /opt/mysql.bak/school.test_$(date +%F).sql备份成功后可以通过 vim 来查看备份表的内容
[root@test2 ~]# vim /opt/mysql.bak/school.test_2024-03-25.sql
删除数据库
备份恢复
[root@test2 ~]# mysql -uroot -p123456 < /opt/mysql.bak/school.test_2024-03-25.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
查看数据库和表
使用 -e 可以在bash环境中使用sql语句;
ps:报错
[root@test2 data]# mysql -uroot -p123456 < /opt/mysql.bak/school.test_2024-03-25.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1030 (HY000) at line 66: Got error 168 from storage engine1.检查mysql用户是否有数据目录的读写权限
2.或导入的数据库引擎不匹配(把报错行下的引擎改为InnoDB或MyISAM试试)另一种情况
df -h看一下磁盘空间是否有余,
若是不足的话 show variables like '%log'; 查看general_log状态
关闭日志记录模式 set global general_log = OFF
清理日志 echo "" > mysql.log
再开启日志 set global general_log = OFF
完全备份MySQL服务器中所有库
mysqldump -u用户名 -p密码 --all-databases > /备份路径/备份文件名.sql
例
mysqldump -uroot -p123456 --all-databases > /opt/mysql.bak/all.$(date +%F).sql
完全备份指定库中的部份表
mysqldump -u用户名 -p密码 库名 表名1 表名2 > 备份路径/备份文件名.sql
例:
mysqldump -uroot -p123456 test test class > /opt/mysql.bak/table.test.class.$(date +%F).sql
保存数据库的表结构
#使用"-d"或"-t"选项,表示只保存数据库的表结构
#不使用"-d"或"-t"选项,说明表数据也要进行备份
#可以用来当作一个表结构模板
查看备份文件
grep -v "^--" 备份文件名| grep -v "^/" | grep -v "^$"#-v "^$" 表示非空行
例:
grep -v "^--" /opt/mysql.bak/school.test_2024-03-25.sql| grep -v "^/" | grep -v "^$"
mysql完全恢复(有问题,搞不懂,重做)
恢复数据库
1.使用mysqldump导出的文件,可导入的方法有:
source命令
mysql命令
2.使用source 恢复数据库的步骤
登录mysql数据库
执行source备份sql脚本的路径
3.source 恢复的示例
MySQL [(none)]> source /backup/all-data.sql
使用 source 命令恢复数据
1.模拟数据库出现问题
[root@server1 backup]# mysql -uroot -pabc123 登录数据库
mysql> show databases; 查看数据库信息
mysql> drop database school; 删除数据库school
mysql> show databases;
2.应用实例
创建备份(对表进行备份)
备份表
mysqldump -uroot -p12356 school class > /opt/mysql.bak/class.sql删除表(模拟故障)
mysql -uroot -p123456恢复表
mysq
mysql> select * from info; 查询所有字段
mysql> show tables; 查看表信息
或免交互l> source /opt/info.sql
mysql -uroot -p123123 -e 'show tables from school;'
3.方式二:
#恢复class表
mysql -uroot -p123456 school < /opt/mysql.bak/school.class.sql
#查看class表
mysql -uroot -p123456 school -e 'show tables from school;'
PS:mysqldump 严格来说属于温备份,会需要对表进行写入的锁定
在全量备份与恢复实验中,假设现有school库,school库中有一个class表,需要注意的一点为:
① 当备份时加 --databases ,表示针对于school库
#备份命令
mysqldump -uroot -p123456 --databases school > /opt/mysql.bak/school.01.sql 备份库后
#恢复命令过程为:
mysql -uroot -p123456
drop database school;
exit
mysql -uroot -p12356 < /opt/mysql.bak/school.01.sql
② 当备份时不加 --databases,表示针对test库下的所有表
#备份命令
mysqldump -uroot -p123456 test > /opt/mysql.bak/test.02.sql
#恢复过程:
mysql -uroot -p123456
drop database test;
create database test;
exit
mysql -uroot -p123456 test < /opt/mysql.bak/test.02.sql
#查看school.01.sql 和test.02.sql
主要原因在于两种方式的备份(前者会从"create databases"开始,而后者则全是针对表格进行操作)
4.自动化备份
0 1 * * 6 /usr/local/mysql/data/ mysqldump -uroot -p123456 库1 库2 > ./备份命$(date +%Y%m%d).sql ;/usr/local/mysql/bin/mysqladmin -u root -p flush-logs
MySQL 增量备份与恢复
一、增备实验
1.开启二进制日志功能
二进制日志(binlog)有3种不同的记录格式:
STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式),默认格式是STATEMENT
[root@test2 ~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
#可选,指定二进制日志(binlog)的记录格式为MIXED(混合输入)
binlog_format = MIXED
#可加可不加该命令
server-id = 1
①statement(基于sql语句):
每一条涉及到被修改的sql都会记录在binlog中;
缺点:日志量大;如:sleep()函数,主从复制等架构记录日志时会出现问题
总结:增删改查通过sql语句来实现记录,高并发时会出错(有时间差异和延迟),记录的数据不一定会按顺序记录;准确率低
②row(基于行)
只记录变动的记录,不记录sql的上下文环境
缺点:如遇到update、set、wehere true那么binlog的数据量会越来越大
总结:update、delete等以多行数据起作用,被记录下来时;只会记录变动的记录,不记录sql的上下文环境;比如sql语句记录一行,但是row就很可能记录10行,准确性高;但高并发时由于操作量大,所有记录都被记录下来,性能会变低
③mixde 混合;推荐使用
一般的语句使用statement ,函数使用row方式存储
systemctl restart mysqld
2.查看二进制文件
cp /usr/local/mysql/data/mysql-bin.000002 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
#--base64-output=decode-rows:使用64位编码机制去解码(decode)并按行读取(rows)
#-v: 显示详细内容
#--no-defaults : 默认字符集(不加会报UTF-8的错误)
PS: 可以将解码后的文件导出为txt格式,方便查阅
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 > /opt/mysql-bin.000002
二进制日志中需要关注的部分
开始的位置点 at
结束的位置点 end_log_pos
时间戳 210712 11:50:30
SQL语句
2.进行完全备份
增量备份是基于完全备份的,所以需要先完全备份一次数据库
mysqldump -uroot -p school class > /opt/school.class.$(date +%F).sql
mysqldump -uroot -p123456 school > /opt/school.all.$(date +%F).sql
3.可每天进行增量备份操作,生成新的二进制日志文件002
mysqladmin -uroot -p123456 flush-logs
4.插入新数据,模拟数据的增加或变更
ps:在第一次完全备份之后刷新二进制文件,在第二个二进制文件中记载着“增量备份的数据”
建库、建表、添加数据
5.再次生成新的二进制日志文件003
mysqladmin -uroot -p123456 flush-logs
#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中,之后我们测试删除ky13库的操作会保存在mysql-bin.000003文件中 (以免当我们基于mysql-bin.000002日志进行恢复时,依然会删除库)
MySQL增量恢复
一、一般恢复
1、模拟丢失更改的数据库的就恢复步骤(直接使用恢复即可)