MySQL数据库备份

• 一、MySQL常用日志
• • 1.1 概述
  • 1.2 错误日志
  • 1.3 二进制文件
  • 1.4 中继日志
  • 1.5 慢查询日志
  • 1.6 数据库中的查询日志状态
  • • 1.6.1 查看二进制日志状态开启
    • 1.6.2 查看慢查询日志功能是否开启
    • 1.6.3 查询慢时间设置
    • 1.6.4 在数据库中设置开启慢查询的办法（临时）
• 二、备份
• • 2.1 概述
  • 2.2 备份的重要性
  • 2.3 备份类型
  • 2.4 备份的办法
  • • 2.4.1 冷备份
    • 2.4.2 通过启用二进制日志进行增量备份
    • 2.4.3 通过第三方工具备份
  • 2.5 备份命令
  • 2.6 增量备份与恢复
  • • 2.6.1 增量备份需要开启二进制日志功能
    • 2.6.2 可每天进行增量备份操作，生成新的二进制文件
    • 2.6.3 查看新生成的日志内容
  • 2.7 恢复的方法
  • • 2.7.1 按位置恢复
    • 2.7.2 按时间恢复
    • • 总结：

前言：
数据库备份谨防数据丢失

一、MySQL常用日志

1.1 概述

日志文件在数据库进行备份和恢复时起到了很重要的作用
常用的日志文件默认保存在 /usr/local/mysql/data 目录下
可在 /etc/my.cnf 配置文件中的 [mysqld] 中进行日志的路径修改、开启、关闭等操作

1.2 错误日志

用于记录 mysql 启动、停止或运行时产生的错误信息
可通过一下字段进行更新

log-error=/usr/local/mysql/data/mysql_error.log （指定日志的保存位置和文件名）
1

1.3 二进制文件

二进制日志，用来记录所有更新的数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复
开启方式：

log-bin=mysql-bin 或者 log_bin=mysql-bin
1

1.4 中继日志

一般情况下，它在 mysql 主从同步（复制）、读写分离集群的从节点上才开启。
主节点一般不需要这个日志。

1.5 慢查询日志

慢查询日志，用来记录所有执行时间超过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秒）
1
2
3

1.6 数据库中的查询日志状态

1.6.1 查看二进制日志状态开启

show variables like '%log_bin%';
1

1.6.2 查看慢查询日志功能是否开启

show variables like '%slow%';
1

1.6.3 查询慢时间设置

show variables like 'long_query_time';
1

1.6.4 在数据库中设置开启慢查询的办法（临时）

set global slow_query_log=ON;

查看
show variables like ‘long_query_time’;
1
2
3
4

日志超时时间

二、备份

2.1 概述

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

还可以用来测试应用、回滚数据修改、查询历史数据、审计等

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

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

2.2 备份的重要性

在企业中，数据的价值至关重要，数据保障了企业业务的正常运行。
因此，数据的安全性及数据的可靠性是运维的重中之重，任何数据的吊事都可能对企业产生严重的后果。

== 通常情况下，造成数据丢失的原因有一下几种：==

程序错误
人为操作错误
运算错误
磁盘故障
灾难（火灾、地震、盗窃等）

2.3 备份类型

从物理与逻辑的角度分类可分为：逻辑备份、物理备份

从数据库的备份策略角度分类可分为：完全备份、差异备份、增量备份

==完全备份：==每次对数据进行完整的备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复，并且会占用大量的磁盘空间，备份的时间也很长。

==差异备份：==备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。

==增量备份：==只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

2.4 备份的办法

数据库的备份可以采用很多种方式，如直接打包数据库文件（物理冷备份）、专用备份工具（mysqldump）、二进制日志增量备份、第三方工具备份等

2.4.1 冷备份

冷备份时需要在数据库处于关闭状态下，能够较好地保证数据库的完整性
冷备份的特点就是速度快，恢复时也是最为简单的。
通过直接打包数据库文件夹(/usr/loc.al/mysql/data)来实现备份

2.4.2 通过启用二进制日志进行增量备份

支持增量备份，进行增量备份时必须启用二进制日志。
二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复。
如果进行增量备份(包含自上次完全备份或增量备份以来发生的数据修改) ，需要刷新二进制日志

2.4.3 通过第三方工具备份

第三方工具Percona xtraBackup是一个免费的MysQL热备份软件，支持在线热备份Innodb和xtraDB，也可以支持MySQL表备份，不过MyISAM表的备份要在表锁的情况下进行。

2.5 备份命令

完全备份

InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。
1

物理冷备份与恢复

systemctl stop mysqld
yum -y install xz
 
#压缩备份
cd /usr/local/mysql/data
tar jcvf mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
systemctl start mysqld
 
#模拟故障，删除数据库
drop database HUISUO;
 
#解压恢复
tar jxvf /opt/mysql_all_2022-06-21.tar.xz -C /usr/local/mysql/data
cd /usr/local/mysql/data
mv usr/local/mysql/data/* ./
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql   
#导出的就是数据库脚本文件
 
例:
 
mysqldump -u root -p --databases liu > /opt/kgc.sql       #备份一个kgc库
mysqldump -u root -p --databases mysql li > /opt/mysql-kgc.sql    #备份mysql与 kgc两个库
1
2
3
4
5
6
7

备份所有的库

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

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

mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
 
如：
mysqldump -uroot -p[密码] [-d] HUISUO member1 > /opt/member1.sql
#使用“-d”选项，说明只保存数据库的表结构
#不使用“-d”选项，说明表数据也进行备份
1
2
3
4
5
6

查看备份文件

grep -v "^--" /opt/member1.sql | grep -v "^/" | grep -v "^$"
1

2.6 增量备份与恢复

2.6.1 增量备份需要开启二进制日志功能

vim /etc/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
#配置文件添加完后需要重启MySQL
systemctl restart mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.6.2 可每天进行增量备份操作，生成新的二进制文件

先完成完全备份（在创建好表和库的基础上）

systemctl restart mysqld.service
mysqldump -uroot -p meeting working > /mnt/meeting_working_$(date +%F).sql
mysqldump -uroot -p meeting > /mnt/meeting_$(date +%F).sql
生成新的二进制文件(可每天进行增量备份操作)
mysqladmin -uroot -p flush-logs
1
2
3
4
5

2.6.3 查看新生成的日志内容

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000002
1

2.7 恢复的方法

2.7.1 按位置恢复

先删除表
drop table working;
 
清空表内容
truncate table meeting.working;
 
恢复结束点为删除命令前和插入命令后
mysqlbinlog --no-defaults --stop-position='902' usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -p
1
2
3
4
5
6
7
8

2.7.2 按时间恢复

先清空表CLASS1,方便实验
mysql -uroot -p -e "truncate table meeting.working;"
mysql -uroot -p -e "select * from meeting.woring;"
 
mysqlbinlog --no-defaults --stop-datetime='2021-04-15 15:39:23' /opt/mysql-bin.000003 |mysql -uroot -p
mysql -uroot -p -e "select * from meeting.woring;"
1
2
3
4
5
6

总结：

mysql没有直接提供增量备份的工具，需要借助二进制日志文件进行操作

使用日志分隔日志的方式进行增量备份

增量恢复需要根据日志文件的时间先后逐个执行

使用基于时间和位置的方式进行恢复，可以更精准的恢复数据