MySQL备份与恢复、日志管理_支持包括数据备份、数据恢复、数据更新、日志管理、权限配置等基础管理功能。-CSDN博客

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;

MySQL备份与恢复、日志管理