目录
3.1.2 专用备份工具mysqldump和mysqlhotcopy
前言
数据库的备份主要的目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等。
一、MySQL日志管理
MySQL的日志默认保存位置为/usr/local/mysql/data，MySQL的日志配置文件为/etc/my.cnf，里面有个mysqld项。
1.1 日志的分类
1.1.1 错误日志
用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
vim /etc/my.cnf
log-error=/usr/local/mysql/data/mysql_error.log #指定日志的保存位置和文件名
1.1.2 通用查询日志
用来记录MySQL的所有连接和语句，默认是关闭的
vim /etc/my.cnf
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
1.1.3 二进制日志
用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
vim /etc/my.cnf
log_bin=mysql-bin 或 log-bin=mysql-bin
1.1.4 慢查询日志
用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的
vim /etc/my.cnf
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5 #设置超过5秒执行的语句被记录，缺省时为10秒
1.1.5 配置日志
1. #修改my.cnf配置文件
#错误日志
log-error=/usr/local/mysql/data/mysql_error.log
#通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#二进制日志
log-bin=mysql-bin
#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
2. #重新mysql服务
systemctl restart mysqld.service
修改my.cnf配置文件
重启mysql服务
1.2 日志的查询
#查看通用查询日志是否开启
show variables like 'general%';
#查看二进制日志是否开启
show variables like 'log_bin%';
#查看慢查询日功能是否开启
show variables like '%slow%';
#查看慢查询时间设置
show variables like 'long_query_time';
#在数据库中设置开启慢查询的方法
set global slow_query_log=ON;
variables 表示变量；like 表示模糊查询
二、MySQL完全备份和恢复
2.1 数据备份的重要性
- 备份的主要目的是灾难恢复
- 在生产环境中，数据的安全性至关重要
- 任何数据的丢失都可能产生严重的后果
2.2 造成数据丢失的原因
- 程序错误
- 认为操作错误
- 运算错误
- 硬盘故障
- 灾难（如火灾、地震）和盗窃
2.3 数据库备份的类型
Ⅰ 物理备份
物理备份是对数据库操作系统的物理文件（如数据文件、日志文件）的备份。这种类型的备份适用于在出现问题的时候需要快速恢复的大型重要数据库。
物理备份又可以分为：冷备份（脱机备份）、热备份（连接备份）和温备份
① 冷备份（脱机备份）：是在关闭数据库的时候进行的（tar）
② 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件（mysqlhotcopy mysqlbackup）
③ 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作（mysqldump）
Ⅱ 逻辑备份
逻辑备份是对数据库逻辑组件的备份，表示为逻辑数据库结构，这种类型的备份适用于可以编辑数据值或表结构。
2.4 数据库的备份策略
从数据库的备份策略角度，备份可分为：完全备份、差异备份和增量备份
- 完全备份：每一次都进行完全备份，会导致文件占用空间巨大，并且有大量的重复数据；恢复时，直接使用完全备份的文件即可。
-
差异备份：每次差异备份，都会备份上一次完全备份后的数据，可能会出现备份重复数据，导致占用额外的磁盘空间；恢复时，需要先恢复完全备份的数据，再恢复差异备份的数据。
-
增量备份：每一次增量备份都是备份在上一次完全备份或者增量备份之后的数据，不会出现备份重复数据的情况，也不会占用额外的磁盘空间；恢复时，需要按照次序恢复完全备份和增量备份的数据。
| 备份方式比较 | |||
| 备份方式 | 完全备份 | 差异备份 | 增量备份 |
| 完全备份时的状态 | 表1、表2 | 表1、表2 | 表1、表2 |
| 第一次添加内容 | 创建表3 | 创建表3 | 创建表3 |
| 备份内容 | 表1、表2、表3 | 表3 | 表3 |
| 第二次添加内容 | 创建表4 | 创建表4 | 创建表4 |
| 备份内容 | 表1、表2、表3、表4 | 表3、表4 | 表4 |
2.5 常见的备份方法
① 物理冷备
备份时数据库处于关闭状态，直接打包数据库文件（tar）
备份速度快，恢复时也是最简单的
② 专用备份工具 mysqldump 或 mysqlhotcopy
mysqldump 常用的逻辑备份工具
mysqlhotcopy 仅拥有备份MyISAM 和 ARCHIVE表
③ 启用二进制日志进行增量备份
进行增量备份，需要刷新二进制日志
MySQL支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份，需要刷新二进制日志。
④ 第三方工具备份
免费的MySQL热备份软件
三、实验部分
环境准备
create table if not exists k1(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 k1 values(1,'yr',22,'dancing');
insert into k1 values(2,'ys',23,'basketball');
insert into k1 values(3,'ln',23,'reading');
insert into k1 values(4,'gc',23,'football');
3.1 MySQL完全备份和恢复
InnoDB存储引擎的数据库在磁盘上存储成三个文件：
-
db.opt(表属性文件)
-
表名.frm(表结构文件)
-
表名.ibd(表数据文件)
3.1.1 物理冷备份与恢复
模拟环境：DL库中有k1表
- 备份时数据库处于关闭状态，直接打包数据库文件
- 备份速度快，恢复时也是最简单的
1. #关闭mysql，备份data目录
systemctl stop mysqld
yum -y install xz
cd /usr/local/mysql
#压缩备份data目录
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz data/
2. #登录mysql，删除school库
systemctl start mysqld.service
mysql -u root -p123456
3. #解压之前备份的数据库data目录，不用删除原目录，会自动替换
cd /opt
ls
cd /usr/local/mysql
tar Jxvf /opt/mysql_all_2022-06-21.tar.xz -C ./
4. #重启服务查看被删除的库
① 关闭mysql，备份data目录
② 登录mysql，删除DL库
③ 解压之前备份的数据库data目录，不用删除源目录，会自动替换
④ 重启服务，查看库
3.1.2 专用备份工具mysqldump和mysqlhotcopy
- mysqldump常用的逻辑备份工具
- mysqlhotcopy仅拥有备份MySIAM和ARCHIVE表
① 完全备份一个或多个完整的库（包括其中所有的表）
导出的就是数据库脚本文件
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql
示例：备份单个和多个库
mysqldump -uroot -p --databases DL > /opt/DL.sql #备份一个kgc库
mysqldump -uroot -p --databases mysql DL > /opt/mysql-DL.sql #备份mysql和DL两个库
② 完全备份MySQL服务器中所有的库
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
示例：备份所有库
mysqldump -uroot -p --all-databases > /opt/all.sql
③ 完全备份指定库中的部分表
mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
示例1：备份DL库中的一个表
mysqldump -uroot -p DL [-d] k1 > /opt/DL_k1.sql
-
使用“-d”选项，说明只保存数据库的表结构
-
不使用“-d”选项，说明表数据也进行备份
示例2：备份DL库中多个表
mysqldump -uroot -p DL [-d] k1 k2 > /opt/DL_k1_k2.sql
④ 查看备份文件
cat DL.sql |grep -v '^/'|grep -v '^$'|grep -v '^-'
或
grep -v "^--" /opt/DL.sql | grep -v "^/" | grep -v "^$"
3.1.3 Mysql完全恢复
恢复数据库
使用source命令恢复数据
模拟数据库出现的问题
mysql -uroot -p123456 #登录数据库
show databases; #查看数据库信息
drop database DL; #删除数据库DL
show databases;
应用示例
mysql -uroot -p123456 -e 'drop table DL.K1;' #删除数据库的表
用source恢复数据表
source /opt/DL_k1.sql;
select * from K1;
show tables;或免交互mysql -uroot -p123456 -e 'show tables from DL;'
方法二：mysql -uroot -p123456 DL < /opt/DL_k1.sql
mysql -uroot -p123456 -e 'show tables from DL;'
3.1.4 MySQL增量备份与恢复
① 开启二进制日志功能
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
#指定二进制日志(binlog)的记录格式为 MIXED
binlog_format = MIXED
server-id = 1
二进制日志（binlog）有三种不同的记录格式
- STATEMENT（基于SQL语句）
- ROW（基于行）
- MIXED（混合模式），默认格式是STATEMENT
#只要重启就会自动生成二进制文件
systemctl restart mysqld
② 可每周对数据库或表进行完全备份
对指定的表做备份
mysqldump -u root -p DL k1 > /opt/DL_k1_$(date +%F).sql
mysqldump -u root -p --all-databases DL > /opt/DL_$(date +%F).sql
③ 可每天进行增量备份操作，生成新的二进制日志文件
mysqladmin -u root -p flush-logs
④ 插入新的数据，以模拟数据的变更
⑤ 再次生成新的二进制日志文件
mysqladmin -u root -p flush-logs
之前的步骤4的数据库操作会保存到mysql-bin.000004文件中，之后数据库数据再发生变化则保存在mysql-bin.000005文件中
⑥ 查看二进制日志文件的内容
cp /usr/local/mysql/data/mysql-bin.000005 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000005
#--base64-output=decode-rows：使用64位编码机制去解码并按行读取
#-v：显示详细内容
3.1.5 NySQL增量回复
① 一般回复
将所有备份的二进制日志内容全部恢复
模拟丢失更改的数据的恢复步骤
use DL;
delete from k1 where id=5;
delete from k1 where id=6;
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000004 | mysql -uroot -p123456
systemctl restart mysqld.service
通过二进制日志进行恢复
再次查看表中数据
模拟丢失所有数据的恢复步骤
use DL;
drop table k1;
mysql -u root -p DL < /opt/DL_k1_k2.sqlmysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000004 | mysql -uroot -p123456
删除DL表中的k1表
先恢复整表，然后再恢复二进制日志文件
② 基于位置恢复
- 数据库在某一时间点可能既有错误的操作也有正确的操作
- 可以基于精确的位置跳过错误的操作
- 发生错误节点之前的一个节点，上一次正确操作的位置点停止
# 刷新生成新的二进制日志文件
mysqladmin -u root -p flush-logs
# 进入到data目录
cd /usr/local/mysql/data
#查看二进制日志文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000013
在数据库中插入数据并查看
use DL;
insert into k1 values(7,'yw',25,'computer');
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000013
at 294 #断点
220622 13：59：05 #时间
insert into k1 values(7,'yw',25,'computer') #插入数据
再插入一条数据
use DL;
insert into k1 values(8,'yq',25,'swimming');
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000013
at 578 #断点
220622 14：06：12 #时间
insert into k1 values(8,'yq',25,'swimming') #插入数据
以上插入的数据都存放在mysql-bin.000013
测试恢复到578之前的数据，不恢复“后面一条”的数据
删除数据前必须先刷新一下日志文件
mysqladmin -u root -p flush-logs
先删除表中数据
对数据进行恢复
仅恢复最后一条数据，跳过前面多条的数据恢复
删除表中所有数据
只恢复578后执行的
③ 基于时间恢复
测试恢复到2022-06-22 14:06:12之前的数据，不恢复后面一条的数据
仅恢复最后一条数据，跳过前面多条数据的恢复
四、总结
完全备份
①物理冷备份——》关闭mysqld服务——》tar命令打包data目录（恢复就是解压tar包即可）
②逻辑备份——》mysqldump -u（用户） -p（密码）--databases 库1 库2 > xxx.sql
mysqldump -u -p --all-databases > xxx.sql
mysqldump -u -p 库1 表1 表2 > xxx.sql
③完全恢复
mysql -u -p < xxx.sql(恢复库)
mysql -u -p 库名 < xxx.sql(恢复表)
④增量备份
开启二进制日志，再设置二进制格式MIXED（混合）
进行一次完全备份，可每周备份一次，通过crontab -e
使用mysqladmin -uroot -p flush-logs刷新分割出二进制日志，由于刷新之前的数据操作都记录在老的二进制日志里，所 以需要把老的二进制日志复制到一个安全的目录中保存管理
可以通过mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制日志文件名查看日志内容
可以通过mysqlbinlog --no-defaults 二进制日志文件名称 | mysql -uroot -p 恢复丢置的数据
位置恢复
时间恢复
4220
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
