目录
八、MySQL 完全恢复(使用 mysqldump 备份的 sql 文件)
MySQL备份与恢复
一、数据备份目的
- 备份的主要目的是灾难恢复,在生产环境中,数据的安全性至关重要,任何数据的丢失都可能产生严重的后果。
- 造成数据丢失的原因:程序错误、人为操作错误、运算错误、磁盘故障、灾难(如火灾、地震)和盗窃等
二、数据库备份分类
(1)物理备份
对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
物理备份的方法:
- 冷备份(脱机备份):是在关闭数据库的时候进行的
- 热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件
- 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
(2)逻辑备份
对数据库逻辑组件(如:表等数据库对象)的备份
三、从数据库的备份策略角度分类
(1)完全备份
- 每次对数据库进行完整的备份;
(2)差异备份
- 备份自从上次完全备份之后,被修改过的文件;
(3)增量备份
- 只有在上次完全备份或者增量备份后被修改的文件才会被备份。
工作中通常使用 一次完全备份(如每月一次) 和 多次增量备份 结合使用。
四、常见的备份方法
物理备份
- 备份时数据库处于关闭状态,直接打包数据库文件;
- 备份速度快,恢复时也是最简单的;
专用备份工具 mydump 或 mysqlhotcopy
- mysqldump常用的逻辑备份工具;
- mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表;
启用二进制日志进行增量备份
- 进行增量备份,需要刷新二进制日志;
第三方工具备份
- 如:免费的 MySQL 热备份软件 Percona XtraBackup;
常用备份方式有:物理打包备份,还有 mysqldump 工具备份。
五、MySQL完全备份
1、定义
完全备份是对整个数据库、数据库结构和文件结构的备份;保存的是备份完成时刻的数据库;是差异备份与增量备份的基础;
-
优点:备份与恢复操作简单方便(就是压缩、解压缩、替换的过程)
-
缺点:数据存在大量的重复、占用大量的备份空间、备份与恢复时间长;
2、完全备份分类:
(1)物理冷备份与恢复
- 关闭MySQL数据库;
- 使用tar命令直接打包数据库文件夹;
- 直接替换现有MySQL目录即可;
(2)mysqldump备份与恢复
- MySQL自带的备份工具,可方便实现对MySQL的备份;
- 可以将指定的库、表导出为 .SQL脚本文件;
- 使用mysql命令 导入备份的数据;
六、物理备份之:冷备份、与恢复
冷备份在做备份之前需要先停止mysql服务,再进行备份
热备份则不需要停止mysql服务运行,可直接备份
物理冷备份与恢复步骤
systemctl stop mysqld
#xz 也是一个压缩工具,J:压缩,压缩文件结尾用 xz
yum -y install xz
#压缩备份
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
#解压恢复
tar Jxvf /opt/mysql_all_2022-11-08.tar.xz -C /usr/local/mysql/data
systemctl start mysqld
数据库数据位置一般是在:/usr/lcoal/mysql/data 目录中
.ibd 是数据文件
.frm 是表结构文件
例:
压缩过后重启mysql服务,进入数据库查看时,数据都是还存在
此时我将库中表Fmoney删除,由于删表之前我已将整个数据库备份压缩到/opt目录下,只需将压缩包解压到/usr/lcoal/mysql/data覆盖原有文件,即可以恢复删除的表Fmoney
例:
/opt目录下备份文件
此时进入库中查看会发现Fmoney表已经恢复
Tip:无论是备份压缩,还是恢复解压覆盖都是在关闭mysql服务的状态下进行
七、mysqldump 工具 备份与恢复
备份通过保存 SQL 语句,恢复时再执行 保存的 SQL 语句覆盖恢复;
mysqldump 是数据库的备份工具
1、完全备份一个、多个或完整的库
#导出的备份文件就是数据库脚本 SQL 文件
语法:
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql
例:
#完全备份一个库
mysqldump -u root -p --databases STARBUCKS > /opt/STARBUCKS.sql
#完全备份多个库,空格隔开
mysqldump -u root -p --databases STARBUCKS LUCKY > /opt/STARBUCKS-LUCKY.sql
#完全备份 MySQL 服务器中所有的库
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
例:
mysqldump -u root -p --all-databases > /opt/ALL_DATABASES.sql
例:
2、完全备份指定库中的部分表
格式:
mysqldump -u root -p[密码] [-d] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
#使用“ -d ”选项,只保存数据库的表结构
#不使用“ -d ”选项,表结构和表数据都进行备份
例:
mysqldump -u root -p school fmoney > /opt/school_fmoney.sql
例:
3、定时任务备份
#使用crontab 计划性任务来执行;每周6凌晨1点进行完整备份
crontab -e
0 1 * * 6 mysqldump -u root -pabc123 --all-databases > /opt/databases_$(date +%F).sql
八、MySQL 完全恢复(使用 mysqldump 备份的 sql 文件)
1、恢复数据库
#“-e”选项,用于指定连接 MySQL 后执行的命令,命令执行完后自动退出,就不用登录Mysql界面敲命令了
#我们用删掉库 school , 用上面保存的 school_bak.sql 进行恢复(及时执行sql语句)
mysql -u root -p -e 'DROP DATABASE school;'
mysql -u root -p -e 'show databases;'
#恢复(重新执行这个库的sql脚本)
mysql -u root -p < /opt/school_bak.sql
mysql -u root -p -e 'show databases;
例:
2、恢复数据表
数据库存在,其中的表被删除了,可以通过表的备份 sql 文件进行恢复(就是执行表的sql语句)
mysql -u root -p -e 'drop table school.test7;'
mysql -u root -p -e 'use school;show tables;'
mysql -u root -p school < /opt/school_fmoney.sql
mysql -u root -p -e 'show tables from school;'
例:
九、MySQL 增量备份与恢复
1、MySQL 增量备份
(1)修改配置文件,开启二进制日志功能
vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin=mysql-bin
binlog_format = MIXED #指定二进制日志(binlog)的记录格式为 MIXED
systemctl restart mysqld.service
#查看生成的二进制日志文件
ls -l /usr/local/mysql/data/mysql-bin.*
(2)执行 flush-logs 生成新的、空的 二进制日志文件
mysqladmin -u root -p flush-logs
生成的文件是空的,执行语句后才会记录在这个新日志文件中
(3)查看二进制日志文件的内容
由于 mysql-bin 文件中都是通过 base64 编码过的内容,不能直接阅读,所以需要进行转码。
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:使用base64编码机制去解码并按行读取
#-v:显示详细内容
Tip:flush-logs 执行后,会生成一个新的空的 二进制日志文件,之后再执行 INSERT 等语句才会记录在其中。
2、增量恢复
(1)一般增量恢复
二进制日志文件中记录的是增量的SQL语句,恢复的时候也是执行的增量SQL。
mysqlbinlog --no-defaults 备份的二进制日志文件名 | mysql -u root -p
例:
(2)断点增量恢复
如图,二进制日志文件中,有 at 后面跟着日志记录的位置,也有时间。
a.基于位置恢复
如:仅恢复到位置点302之前的数据
恢复到哪一行停止格式为
mysqlbinlog --no-defaults --stop-position=位置点数字 二进制日志文件 | mysql -u root -pabc123
#模拟数据丢失
mysql -uroot -pabc123 -e 'delete from school.fmoney where id=1;'
mysql -uroot -pabc123 -e 'select * from school.fmoney;'
#到位置点302停止恢复数据
mysqlbinlog --no-defaults --stop-position='302' /opt/mysql-bin.000004 | mysql -u root -pabc123
mysql -uroot -pabc123 -e 'select * from school.fmoney;'
从哪一行开始恢复格式为
mysqlbinlog --no-defaults --start-position=位置点数字 二进制日志文件 | mysql -u root -pabc123
b.基于时间点恢复
仅恢复到某个时间点的数据格式为
mysqlbinlog --no-defaults --stop-datetime=二进制日志中的时间点 二进制日志文件 | mysql -uroot -pabc123
MySQL日志管理
MySQL的日志保存位置在/etc/my.cnf
一、MySQL的日志分类
- 错误日志:error-log
- 通用查询日志:general_log
- 二进制日志:log-bin
- 慢查询日志:slow_query_log
二、MySQL的日志开启与配置
修改MySQL配置文件/etc/my.cnf进行永久性修改,修改配置文件后需要重启mysql服务
vim /etc/my.cnf
[mysqld]
#指定【错误日志】的 保存位置和文件名
log-error=/usr/local/mysql/data/mysql_error.log
#【通用查询日志】,用来记录MySQL的所有连接和语句
#【通用查询日志】默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#【二进制日志】默认已开启
log-bin=mysql-bin #也可以 log_bin=mysql-bin
#【慢查询日志】默认是关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
#设置超过5秒执行的语句被记录,缺省时为10秒
修改配置文件前
修改配置文件后
配置文件修改内容
#查看慢查询时间设置
show variables like 'long_query_time';
#在数据库中设置开启慢查询的方法
set global slow_query_log=ON;