MySQL数据库日志管理+备份

一、MySQL日志管理

1.1 日志分类

• 事务日志：transaction log

  事务日志的写入类型为"追加"，因此其操作为"顺序IO"；通常也被称为：预写式日志 write ahead logging

  事务日志文件： ib_logfile0， ib_logfile1

• 错误日志 error log

• 通用日志 general log

• 慢查询日志 slow query log

• 二进制日志 binary log （备份时使用的）

• 中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

1.2 日志管理

MySQL 的日志默认保存位置为 /usr/local/mysql/data

vim /etc/my.cnf
[mysqld]
##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
log-error=/usr/local/mysql/data/mysql_error.log					#指定日志的保存位置和文件名

##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

##二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
log-bin=mysql-bin				#也可以 log_bin=mysql-bin

##慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5												#设置超过5秒执行的语句被记录，缺省时为10秒


systemctl restart mysqld

mysql -u root -p
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;				#在数据库中设置开启慢查询的方法

二、备份

2.1 数据备份的必要性

备份的主要目的是灾难恢复

在生产环境中，数据的安全性至关重要

任何数据的丢失都可能产生严重的后果

造成数据丢失的原因
程序错误
人为操作错误
运算错误
磁盘故障
灾难(如火灾、地震)和盗窃

2.2 数据备份分类

2.2.1物理备份

对数据库操作系统的物理文件(如数据文件、日志文件等)的备份。

物理备份的方法 ：

• 冷备份(脱机备份):是在关闭数据库的时候进行的；
• 热备份(联机备份):数据库处于运行状态，依赖于数据库的日志文件；
• 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作。

2.2.2 逻辑备份

对数据库的表或者对象进行备份。

2.3 数据库备份策略

完全备份：整个数据集，每次都备份完整的数据库。

差异备份：仅备份最近一次完全备份后变化的数据，备份较慢，还原简单

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂.

2.4 常用的备份方法和工具

物理冷备
备份时数据库处于关闭状态，直接打包数据库文件

备份速度快，恢复时也是最简单的。

专用备份工具mysqldump或mysqlhotcopy

mysqldump常用的逻辑备份工具（逻辑热备）

备份速度较慢（如果数据库超过100G，一般不建议使用mysqldump)

mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

archive

启用二进制日志进行增量备份
进行增量备份，需要刷新二进制日志

第三方工具备份
免费的MySQL热备份软件Percona;XtraBackup

XtraBackup(物理热备)

2.5 数据库上云迁移

冷迁移

热迁移

三、MySQL完全备份

3.1 简介

优点
备份与恢复操作简单方便
缺点
数据存在大量的重复占用大量的备份空间
备份与恢复时间长

• 完全备份是对整个数据库、数据库结构和文件结构的备份
• 保存的是备份完成时刻的数据库
• 是差异备份与增量备份的基础

3.2 物理冷备份与恢复

• 关闭MySQL数据库
• 使用tar命令直接打包数据库文件夹
• 直接替换现有MySQL目录即可

3.2.1 物理冷备份

#先关闭数据库
systemctl stop mysqld
#建立备份文件存放目录
mkdir /opt/bak

#压缩备份
tar zcvf /opt/bak/mysql_full_$(date +%F).tar.gz /usr/local/mysql/data/*

$(date -d "-1 day" +%Y%m%d)

主机1：192.168.10.20

主机2：192.168.10.30

备份到另一台主机

#先关闭数据库
systemctl stop mysqld

scp 192.168.10.20:/opt/bak/* /opt 
#将主机1的备份包远程传输到主机2

#先备份数据，防止恢复失败
mv /usr/local/mysql/data /usr/local/mysql/data.bak

cd /opt
tar xf mysql_full_2024-07-03.tar.gz


cp -a usr/local/mysql/data  /usr/local/mysql/data
#不用-a 权限不足，无法启动

systemctl start mysqld
mysql -u root -p 密码
#登录到数据库

3.3 mysqldump备份与恢复

1）完全备份一个或多个完整的库（包括其中所有的表）

mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql	
#导出的就是数据库脚本文件


如果不加 --databases ,只备份库中的表，不会备份库

#举个例子

#单个数据库
mysqldump -u root -p123123 -B hellodb > /opt/bak/hellodb.sql

#多个数据库
mysqldump -u root -p123123 -B hellodb  mysql > /opt/bak/hd.sql

2）完全备份 MySQL 服务器中所有的库

mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql

#举个例子
mysqldump -u root -p123123 --all-databases > /opt/bak/all.sql

cat /opt/all.sql | grep "^CREATE DATABASES"

3）完全备份指定库中的部分表

mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql

#备份hellodb的students表
mysqldump -u root -p [-d] hellodb students > /opt/bak/hellodb-students.sql

#使用“-d”选项，说明只保存数据库的表结构
#不使用“-d”选项，说明表数据也进行备份

4）查看备份文件

cat xx.sql|grep -v "^--"| grep -v "^/" | grep -v "^$"

grep -E -v "^--|^/\*|^$" #或运行符

#举个例子

cat hellodb-students.sql|grep -v "^--" | grep -v "^/" | grep -v "^$"

四、MySQL 完全恢复

4.1 恢复数据库

当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在。

在生产环境中，可以使用Shell脚本自动实现定时备份。

4.1.1 通过source命令恢复

先使用mysqldump工具，生成备份sql脚本；

然后登录到MySQL数据库；

再执行source 备份sql脚本的路径

MySQL[(none)]> source /backup/all-data.sql

举个例子

先备份目标数据库，以hellodb为例
mysqldump -uroot -p123123 --databases hellodb > /opt/bak/hellodb.sql

模拟数据库被删除
mysql -uroot -p123123 -e "drop database hellodb;"
mysql -uroot -p123123 -e "show databases;"

source命令执行sql脚本

#先登录到数据库
mysql -u root -p
#执行脚本
source /opt/bak/hellodb.sql;

4.1.2 通过mysql命令

输入重定向

#先模拟数据库删除，这里不再赘述

#恢复数据库
mysql -u root -p123123 < /opt/bak/hellodb.sql
mysql -u root -p123123 -e 'show databases;'

使用管道符

cat hellodb.sql | mysql -uroot -p123123

4.2 恢复数据表

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。

#备份byyb的student表
mysqldump -u root -p123123 [-d] hellodb students > /opt/bak/hellodb-students.sql

#模拟删除表
mysql -u root -p -e 'drop table hellodb.students;'
mysql -u root -p -e 'show tables from hellodb;'

#恢复表数据
mysql -uroot -p123123 hellodb < /opt/bak/hellodb-students.sql
#查看表数据，验证是否成功恢复
mysql -u root -p123123 -e 'show tables from hellodb;'

五、MySQL增量备份与恢复

5.1 MySQL增量备份

5.1.1 开启二进制日志功能

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED				#可选，指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1

#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

systemctl start mysqld
ls -l /usr/local/mysql/data/mysql-bin.*

5.1.2 通过刷新二进制日志文件间接实现增量备份

mysqladmin -uroot -p[密码] flush-logs

5.1.3 查看二进制日志文件的内容

mysqlbinlog --no-defaults --base-output=decode-rows -v 二进制文件（绝对路径）
#--base-output=decode-rows：使用位编码机制去解码并按行读取
#-v：显示详细内容

举个例子

#举个例子
cp /usr/local/mysql/data/mysql-bin.000001 /opt 
mysqlbinlog --no-defaults --base-output=decode-rows -v /opt/mysql-bin.000001

5.2 MySQL增量恢复

MySQL没有提供直接的增量备份方法，可通过MySQL提供的二进制日志间接实现增量备份。

5.2.1 一般恢复

1）模拟丢失更改的数据的恢复步骤

use hellodb;
delete from students where stuid=24;


mysqlbinlog --no-defaults /data/mysql-bin.000001 | mysql -u root -p123123

（2）模拟丢失所有数据的恢复步骤
 

use hellodb;
drop table students;
mysql -u root -p kgc < /opt/hello_students_2024-07-04.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

5.2.2 断点恢复

#查看二进制文件 
mysqlbinlog --no-defaults --base-output=decode-rows -v /data/mysql-bin.000002

1）基于位置恢复

mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 二进制日志文件（绝对路径） | mysql -uroot -p[密码]

#仅恢复到操作 ID 为“623”之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-position='623' /data/mysql-bin.000002 | mysql -uroot -p

#仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-position='623' /data/mysql-bin.000002 | mysql -uroot -p

2）基于时间点恢复

mysqlbinlog --no-defaults --start-datetime='时间点' --stop-datetime='时间点' 二进制日志文件（绝对路径） | mysql -uroot -p[密码]

#仅恢复到 16∶41∶24 之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /data/mysql-bin.000002 |mysql -uroot -p

#仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /data/mysql-bin.000002 |mysql -uroot -p

如果恢复某条SQL语句之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start

5.3 实战案例

每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的students表

#开启二进制日志
vim /etc/my.cnf
log_bin=/data/mysql-bin
server-id = 1
chown mysql.mysql /data/ -R
systemctl restart   mysqld


#######2:30  执行全备
mysqldump -uroot -pabc123 -A -F --single-transaction --master-data=2 > /opt/all.sql

#完全备份后数据更新
insert students (name,age,gender) values('rose',20,'f');
insert students (name,age,gender) values('jack',20,'f');

##############10:00  误删除了一个students的表
drop table students;



###########后续其余的表继续更新
 insert teachers (name,age,gender)values('test',30,'M');
 insert teachers (name,age,gender)values('test1',30,'M');
 
 
 
#####10点10分发现进行还原
#停止数据库访问



[root@localhost ~]#grep '\-\- CHANGE MASTER TO'  /opt/all.sql 
#从完全备份中，找到二进制位置
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;


 
#备份  完全备份后的二进制日志
mysqlbinlog --start-position=154 /data/mysql-bin.000002 > /opt/inc.sql



#找到  删除的语句
[root@localhost ~]#grep -i "^drop table"  /opt/inc.sql 
DROP TABLE `students` /* generated by server */

#删除 删表的那一行
sed -i.bak '/^DROP TABLE/d'  /opt/inc.sql 



#登录数据库还原
set sql_log_bin=0;     #先关闭二进制日志    临时关闭  
set sql_log_bin=1; 


source     /opt/inc.sql