MySQL数据库备份

MySQL数据库备份

MySQL数据库备份是数据管理策略中不可或缺的一部分，它能够降低风险、提高数据安全性和恢复能力,能够在以下几方面提供强有力的保障。

• 数据安全：备份能够有效防止因意外删除、硬件故障或自然灾害等引起的数据丢失，保护关键数据的安全性。

• 快速恢复：在系统崩溃或数据丢失后，备份能帮助快速恢复数据库，减少业务中断时间，确保业务的持续性。

• 版本控制：通过定期备份，能够保留数据库的多个版本，便于在数据损坏或错误更改后恢复到某个特定时间点。

• 业务合规性：许多行业对数据备份有法规要求，定期备份能够帮助企业遵循法律法规，避免潜在的法律风险。

1. 逻辑备份 mysqldump

mysqldump 是 MySQL 的一个命令行工具，主要用于创建数据库的逻辑备份，适合用于多种备份和数据管理场景，尤其是在逻辑备份和数据迁移方面。

但是对于大型数据库，mysqldump 可能会比较慢，尤其是在恢复时，所有数据都需要重新插入。

1.1 全库备份

mysqldump -u'username' -p'password' -A > file.sql   #备份全库
            -h'ip地址' -P'端口号'                    #远程备份添加目标数据库的IP和端口
1、登录数据库后source /file.sql;                              #导入数据
2、cat file.sql | mysql -u'username' -p'password'            #导入数据
3、mysql -u'username' -p'password' < file.sql                #导入数据

1.2 库级备份

mysqldump -u'username' -p'password' -B database_name > file.sql     #备份某个库
1、登录数据库后source /file.sql;                                  #导入数据
2、cat file.sql | mysql -u'username' -p'password'                #导入数据
3、mysql -u'username' -p'password' < file.sql                    #导入数据 

1.3 表级备份

mysqldump -u'username' -p'password' database_name table_name > file.sql    #备份某个表
1、登录数据库后进入对应的库source /file.sql;                       #导入数据
2、cat file.sql | mysql -u'username' -p'password' database_name  #导入数据
3、mysql -u'username' -p'password' database_name < file.sql      #导入数据 

• 备份表结构

  mysqldump -u'username' -p'password' -d database_name table_name > file.sql
  #不导出任何数据，只导出数据库表结构
  mysql -u'username' -p'password' -D  < file.sql
  #导入表结构

• 备份表数据

  登录MySQL数据库
  show variables like "secure%";      #查看当前数据库存放文件的默认路径
  select * from table_name into outfile "默认路径/data.xlsx";     #备份表数据

  登录MySQL数据库
  create table table_name select * from database_name.table_name where 1=2;
  #建表并复制之前的表结构
  load data infile "默认路径/data.xlsx" into table table_name;
  #导入数据

2.bin-log日志恢复数据

二进制日志binlog是 MySQL 中用于记录所有更改数据库状态的事件的日志文件。

使用 binlog 进行备份和恢复可以提供灵活性和高效性，尤其适合需要频繁更新和恢复的场景。然而，它的管理和恢复过程相对复杂，因此在选择使用binlog 时，需要考虑具体的业务需求和环境。

step1 开启binlog日志

在MySQL的配置文件中添加以下内容

log_bin=/var/lib/mysql/mysql-binlog
server_id=1

添加完成后重启数据库systemctl restart mysqld生效

step2查找定位丢失数据位置

mysqlbinlog /var/lib/mysql/mysql-binlog.000001 --base64-output=decode-rows -vv
#根据时间大致定位数据位置

找到数据前边关键字BEGIN对应的at位置号num1和数据后边关键字COMMIT对应的at位置号num2

step3找回数据

mysqlbinlog /var/lib/mysql/mysql-binlog.000001 --start-position num1 --stop-position num2 | mysql -u'username' -p'password' 

3.物理备份

全量备份、增量备份和差异备份是物理备份中的三种主要方式，它们各有特点和适用场景。

• 全量备份适合需要快速恢复且数据量不大的情况。

• 增量备份适合存储空间有限且备份频率高的场景。

• 差异备份在速度和资源利用之间寻求平衡，适合大多数情况。

物理备份要借助第三方工具，在网站https://www.percona.com/downloads的Percona XtraBackup专区匹配适合的版本下载。

3.1 全量备份

• 定义：对所有数据进行一次完整的备份。

• 优点：

  • 恢复速度快，因为所有数据都在一个备份中。

  • 操作简单，一次备份后所有数据可随时恢复。

• 缺点：

  • 需要大量存储空间。

  • 备份时间较长，特别是数据量大时。

innobackupex --user=用户 --password=密码 /opt/full/     #全量备份，存储路径可自行定义

3.2 增量备份

• 定义：只备份自上次备份以来发生变化的数据。

• 优点：

  • 节省存储空间，因为只备份新变化的数据。

  • 备份速度相对较快。

• 缺点：

  • 恢复时需先恢复最后一次全量备份，再依次恢复所有的增量备份，恢复过程较慢。

  • 管理复杂度增加，需要跟踪多次备份。

innobackupex --user=用户 --password=密码 --incremental /opt/increment --incremental-basedir=/opt/full/2024-08-15_15-42-20
#基于上一次全量备份进行增量备份
innobackupex --user=用户 --password=密码 --incremental /opt/increment --incremental-basedir=/opt/incr/2024-08-15_15-44-40
#基于上一次增量备份进行增量备份

3.2 差异备份

• 定义：备份自上次全量备份以来发生变化的数据。

• 优点：

  • 恢复速度快于增量备份，只需最后一次全量备份和最后一次差异备份。

  • 一定程度上节省存储空间，相对于全量备份而言。

• 缺点：

  • 随着时间的推移，差异备份的大小会不断增加，最后的差异备份可能会变得相当大。

  • 仍需管理多个备份文件。

innobackupex --user=用户 --password=密码 --incremental /opt/diffrent --incremental-basedir=/opt/full/2024-08-15_15-42-20
#基于上一次全量备份进行差异备份

3.4 备份恢复

step1停库并清空数据目录

systemctl stop mysqld && rm -rf /var/lib/mysql/*

step2重演备份

innobackupex --user=用户 --password=密码 --apply-log --redo-only /opt/full/2024-08-15_15-42-20
#全量备份重演
innobackupex --user=用户 --password=密码 --apply-log --redo-only /opt/full/2024-08-15_15-42-20 --incremental-dir=/opt/increment/2024-08-15_15-54-52
#增量备份重演
innobackupex --user=用户 --password=密码 --apply-log --redo-only /opt/full/2024-08-15_15-42-20 --incremental-dir=/opt/diffrent/2024-08-15_15-54-52
#差异备份重演
#主要是理解备份的机制，差异+全量，增量+全量组合之后会有哪部分的数据
#重演会将差异化内容补全到全量备份内，所以重演之后全量备份文件内容会补全

step3拷贝数据

innobackupex --user=用户 --password=密码 --copy-back /opt/full/2024-08-15_15-42-20
#将数据拷贝到MySQL的数据目录

step4修改权限并启动

chown -R mysql.mysql /var/lib/mysql/*
#因为拷贝过来的数据属主和属组是当前用户，需要变更为MySQL，才能被MySQL正常调用
systemctl start mysqld
#启动MySQL