MySQL数据备份与恢复

mysql 评论

物理备份

对数据库的二进制文件（如数据文件、日志文件等）进行备份，适用于故障快速恢复，因为它直接备份数据库的原始文件。

适用场景 描述
整个数据库恢复 当需要完全恢复数据库时，包括数据和数据库的状态，物理备份是最好的选择。
数据库规模较大 对于大型数据库，物理备份通常比逻辑备份更快速和高效。
数据库备份和恢复频繁 如果需要频繁备份和恢复数据库，物理备份可能是更有效的选择，因为它通常比逻辑备份更快。

大型数据库通常是指数据量巨大、访问频繁、复杂度高、需要高可用性和扩展性的数据库系统。虽然没有一个固定的定义来界定何为大型数据库，但通常可以通过以下几个指标来判断一个数据库是否为大型数据库：

  1. 数据量：大型数据库通常包含数十亿到数百亿条记录甚至更多，数据量通常在几十 TB 或几百 TB 以上。

  2. 并发访问量：大型数据库通常需要支持大量并发用户的高频访问，要求数据库系统能够提供高性能和高吞吐量。

  3. 复杂性：大型数据库往往包含多个表、复杂的关联和存储过程，需要复杂的查询和数据处理。

  4. 高可用性和可靠性需求：大型数据库通常需要实现高可用性和故障容忍，并且要求数据库系统能够快速恢复，以确保服务不中断。

  5. 扩展性需求：大型数据库通常需要支持水平和垂直扩展，以应对数据量和并发访问量的增长。

备份方式 描述
冷备份（脱机备份） 关闭数据库的时候进行
热备份（联机备份） 数据库处于运行状态，依赖于数据库的二进制日志文件
温备份 数据库锁定表格（不可写入但可读）的状态下进行备份操作

逻辑备份

对数据库逻辑组件（如：表数据、结构等数据库对象）的备份，适用于可以编辑数据值或表结构，备份文件大小通常比物理备份小。

适用场景 描述
数据库迁移 当需要将数据从 A 数据库迁移到 B 数据库时，逻辑备份是一个理想的选择，因为它可以在不同的数据库之间方便地导入和恢复数据。
数据库结构更改 如果需要备份和恢复数据库的结构，如表结构、存储过程、触发器等，逻辑备份是更合适的选择。
部分数据恢复 逻辑备份允许选择性地恢复数据库中的特定数据，因为备份文件中的数据以文本形式保存。

备份策略

备份方式 描述 占用空间 备份速度 恢复速度 操作复杂度
完全备份（Full Backup） 每次对整个数据库的所有数据和文件进行备份，备份文件占用大量的空间，并且存在大量的重复数据；恢复时，将文件导入即可，刚开始速度很快，随着数据增大，速度会越来越慢。 很多 刚开始快，后面会变慢 最快 简单
差异备份（Differential Backup） 备份自从上次完全备份之后被修改过的文件，可能存在重复数据，也会占用额外的空间；恢复时，先恢复完全备份的数据，再导入差异备份的数据。 中等 比完全备份快 比完全备份慢 一般
增量备份（Incremental Backup） 备份上次完整或增量之后的数据，不会出现重复的数据，不会占用额外的磁盘空间；恢复时，先要完全恢复，再依次做增量恢复。 很少 最快 最慢 复杂

操作

完全备份

物理冷备份与恢复

InnoDB 存储引擎的数据库在磁盘上存储成 3 个文件：db.opt（表属性文件）、表名.from（表结构文件）、表名.ibd（表数据文件）。

MySQL 5.7 版本数据存放路径

1

备份步骤
  1. 关闭 mysqld 服务
1
systemctl stop mysqld
  1. 压缩迁移
1
2
3
tar zcvf data_$(data '+%Y%m%d').tar.gz ./*

mv data_20240426.tar.gz /home
恢复步骤
  1. 关闭 mysqld 服务
1
systemctl stop mysqld
  1. 解压还原
1
2
3
4
5
cd /home

tar zxvf data_20240426.tar.gz -C /var/lib/mysql

chown -R mysql:mysql /var/lib/mysql # 修改权限，否则无法启动
  1. 开启 mysqld 服务
1
systemctl start mysqld

mysqldump 备份（温备份）

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

备份数据库
1
2
3
4
5
6
7
8
# 备份数据库中的表
mysqldump -h hostname -u username -p password dbname tablename > backupfile.sql

# 备份整个数据库
mysqldump -h hostname -u username -p password --databases dbname1 dbname2 > backupfile.sql

# 备份整个数据库服务器
mysqldump -h hostname -u username -p password --all-databases > backupfile.sql
恢复数据库
1
2
3
4
5
6
# 命令行
mysql -h hostname -u username -p password dbname < backupfile.sql

# source语句
USE dbname;
SOURCE backupfile.sql;

增量备份

增量备份与恢复

MySQL的增量备份通常使用二进制日志（binary log）来实现。二进制日志是MySQL的一种日志，它记录了所有对数据库的更改操作，包括插入、更新、删除等操作。

开启二进制日志功能
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[root@localhost ~]# vi /etc/my.cnf
[mysqld]
log-error=/usr/local/mysql/data/mysql_error.log
# 指定日志的保存位置和文件名
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
# 通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
log-bin=mysql-bin 或 log_bin=mysql-bin
# 二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
# 慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便提醒优化，默认是关闭的
long_query_time=5
# 设置超过5秒执行的语句被记录，缺省时为10秒
[root@localhost ~]# systemctl restart mysqld.service
 
查看日志方式：
mysql> show variables like 'general%';        #查看通用查询日志是否开启
mysql> show variables like 'log_bin%';        #查看二进制日志是否开启
mysql> show variables like '%slow%';          #查看慢查询日功能是否开启
mysql> show variables like 'long_query_time'; #查看慢查询时间设置
mysql> set global slow_query_log=ON;          #在数据库中设置开启慢查询的方法
[root@localhost ~]# ls /usr/local/mysql/data
auto.cnf        ib_logfile0  mysql             mysql_error.log       performance_schema
ib_buffer_pool  ib_logfile1  mysql-bin.000001  mysql_general.log     sys
ibdata1         ibtmp1       mysql-bin.index   mysql_slow_query.log
二进制日志（binlog）记录格式
格式 描述
STATEMENT（基于 SQL 语句） 记录的是 SQL 语句，日志量过大
ROW（基于行） 记录的是每一行数据的变化，如果遇到 update、delete 数据量会越来越大
MIXED（推荐使用） 会根据 SQL 语句的类型来选择使用 statement 格式还是 row 格式，它的缺点是可能会因为 SQL 语句的类型而导致日志文件的大小和恢复的准确性有所不同
查看二进制日志文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240325 18:01:18 server id 1  end_log_pos 123 CRC32 0x8f464a57 	Start: binlog v 4, server v 5.7.17-log created 240325 18:01:18 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#240325 18:01:18 server id 1  end_log_pos 154 CRC32 0xbef4ecb6 	Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
# --base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
# -v: 显示详细内容
# --no-defaults : 默认字符集（不加会报UTF-8的错误）
 
二进制日志中需要关注的部分
at ：开始的位置点 
end_log_pos：结束的位置
时间戳： 240325 18:01:18
SQL语句
备份步骤
  1. 完全备份
1
mysqldump -uroot -p123456 dbname tablename > /target_file/data_$(data + '%Y%m%d').sql
  1. 生成新的二进制日志文件
1
2
3
mysqldump -uroot -p123456 flush-logs

cp mysql-bin.000001 /target_file/
恢复步骤

  1. 一般恢复

    1. 重定向恢复
    1
    mysql -uroot -p123456 dbname < /target_file.sql
    1. 二进制日志恢复
    1
    mysqlbinlog --no-defaults /mysql-bin.000001 | mysql -uroot -p123456

  2. 断点恢复

实现目标：class 表中5条数据，1、2条记录在mysql-bin.000001日志，3-5条记录在mysql-bin.000001日志，模拟第4条数据操作故障，实现跳过第4条断点恢复。

  1. 插入3-5条数据
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> insert into class values (3,'wang');
mysql> insert into class values (4,'wu');
mysql> insert into class values (5,'zhen');
mysql> select * from class;
+------+----------+
| id   | name     |
+------+----------+
| 1    | zhangsan |
| 2    | lisi     |
| 3    | wang     |
| 4    | wu       |
| 5    | zhen     |
+------+----------+
  1. 备份 mysql-bin.000002 日志
1
2
3
4
5
[root@localhost data]# ls
auto.cnf        ib_logfile0  mysql             mysql-bin.index    mysql_slow_query.log  sys
ib_buffer_pool  ib_logfile1  mysql-bin.000001  mysql_error.log    performance_schema
ibdata1         ibtmp1       mysql-bin.000002  mysql_general.log  school
[root@localhost data]# cp mysql-bin.000002 /opt/
  1. 生成新的二进制日志，并删除 class 表
1
2
3
4
5
mysqladmin -u root -p123456 flush-logs
[root@localhost data]# ls
auto.cnf        ib_logfile0  mysql             mysql-bin.000003  mysql_general.log     school
ib_buffer_pool  ib_logfile1  mysql-bin.000001  mysql-bin.index   mysql_slow_query.log  sys
ibdata1         ibtmp1       mysql-bin.000002  mysql_error.log   performance_schema
  1. 基于位置点恢复
1
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001

2

这里注意，school 库未删除，恢复从（BEGIN）操作 ID 为“384”到（end_log_pos）操作 ID 为“1097”之前的数据 

1
2
3
4
5
6
7
8
9
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='384' --stop-position='1097' /opt/mysql-bin.000001 | mysql -uroot -p123456
 
mysql> select * from class;
+------+----------+
| id   | name     |
+------+----------+
| 1    | zhangsan |
| 2    | lisi     |
+------+----------+

查看mysql-bin.000002日志

1
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

3

这里注意，由于mysql-bin.000002存放着3-5条数据的举例，需要先恢复从（BEGIN）操作 ID 为“2832”到（end_log_pos）操作 ID 为“3100”之前的数据

1
2
3
4
5
6
7
8
9
10
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='2832' --stop-position='3100' /opt/mysql-bin.000002 | mysql -uroot -p123456
 
mysql> select * from class;
+------+----------+
| id   | name     |
+------+----------+
| 1    | zhangsan |
| 2    | lisi     |
| 3    | wang     |
+------+----------+

查看mysql-bin.000002日志

1
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

4

这里注意，由于最后一条插入记录后没有操作，直接恢复到（BEGIN）操作 ID 为“3366”之后的数据

1
2
3
4
5
6
7
8
9
10
11
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='3366' /opt/mysql-bin.000002 | mysql -uroot -p123456
 
mysql> select * from class;
+------+----------+
| id   | name     |
+------+----------+
| 1    | zhangsan |
| 2    | lisi     |
| 3    | wang     |
| 5    | zhen     |
+------+----------+
  1. 基于时间点恢复

时间点恢复与断点恢复同理，只需要将 at 值改为对应的时间即可。

1
2
3
4
5
6
7
8
9
10
11
# 格式
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码

# 示例
mysqlbinlog --no-defaults --stop-datetime='2024-03-25 20:00:00' /opt/mysql-bin.000001 | mysql -uroot -p 
# 仅恢复到20:00:00之前的数据
mysqlbinlog --no-defaults --start-datetime='2024-03-25 20:00:00' /opt/mysql-bin.000001 | mysql -uroot -p
#仅恢复到20:00:00之后的数据
 
# 如果恢复某条SQL语之前的所有数据，就stop在这个语句的位置节点或者时间点
# 如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start

誰先生全栈工程师

个人简介。