MySQL数据库的备份与恢复-超详细

一、备份概述

1.1 MySQL备份介绍

备份数据的重要性：能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方；数据有多份冗余，但不等备份，只能防止机械故障带来的数据丢失，例如主从模式、数据库集群。

1.2 MySQL备份类型

物理备份：直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

逻辑备份：备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。

二、物理备份实例

2.1完整备份

2.1.1创建备份目录

# mkdir -p /xtrabackup/full

备份之前，进入数据库，存储数据

# mysql -uroot -p'Cloud@2404'

mysql> create database backupdb;

mysql> use backupdb;

mysql> create table t1(id int,name char(10));

mysql> insert into t3 values(1 ,'user001');

2.1.2完整备份数据

# innobackupex --user=root --password='Cloud@2404' /xtrabackup/full

2.1.3 完全备份后恢复数据

（1）停止数据库：# systemctl stop mysqld

（2）清理环境：

# rm -rf /var/lib/mysql/*

# rm -rf /var/log/mysqld.log

# rm -rf /var/log/mysql-bin/*

（3）重演回滚：

# innobackupex --apply-log /xtrabackup/full/2024-08-15_16-54-32

（4）恢复数据：

恢复之前需要确认配置文件内有数据库目录指定，不然xtrabackup不知道恢复到哪里

# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

# innobackupex --copy-back /xtrabackup/full/2024-08-15_16-54-32/

2.1.4 修改权限

# chown -R mysql.mysql /var/lib/mysql

2.1.5启动数据库，查看数据是否恢复

# systemctl start mysqld

# mysql -uroot -p'Cloud@2404'

# show databases;

# select * from t1;

2.2增量备份

2.2.1 备份前新建1个数据库

# create database backupdb.t1

2.2.2 完整备份

# innobackupex --user=root --password='Cloud@2404' /xtrabackup/full

2.2.3 增量备份

（1）增量备份1：（--incremental-basedir:基于哪个增量）

mysql> insert into backupdb.t1 values(2,'user002');

# innobackupex -uroot -p"Cloud@2404" --incremental /xtrabackup/full/ --incremental-basedir=/xtrabackup/full/2024-08-15_17-56-11/

（2）增量备份2

mysql> insert into backupdb.t1 values(3,'user003');

# innobackupex -uroot -p"Cloud@2404" --incremental /xtrabackup/full/ --incremental-basedir=/xtrabackup/full/2024-08-15_18-01-05/

2.2.4 增量备份恢复

（1）停止数据库

# systemctl stop mysqld

（2）清理环境

# rm -rf /var/lib/mysql/*

# rm -rf /var/log/mysqld.log

# rm -rf /var/log/mysql-bin/*

（3）依次重演回滚redo log－－> 恢复数据

第一次全量备份回滚：

# innobackupex --apply-log --redo-only /xtrabackup/full/2024-08-15_17-56-11/

第一次增量回滚

# innobackupex --apply-log --redo-only /xtrabackup/full/2024-08-15_17-56-11/ --incremental-dir=/xtrabackup/full/2024-08-15_18-01-05/

第二次增量回滚

# innobackupex --apply-log --redo-only /xtrabackup/full/2024-08-15_17-56-11/ --incremental-dir=/xtrabackup/full/2024-08-15_18-05-06/

完全备份回滚

# innobackupex --copy-back /xtrabackup/full/2024-08-15_17-56-11/

（4）修改权限

chown -R mysql.mysql /var/lib/mysql

（5）启动数据库

systemctl start mysqld

2.3差异备份

2.3.1清理备份环境

# rm -rf /xtrabackup/full/*

2.3.2 登录数据库，准备环境

# mysql -uroot -p'Cloud@2404'

mysql> delete from backupdb.t1;

mysql> insert into backupdb.t1 values(11,'jack');

2.3.3 完整备份

# innobackupex --user=root --password='Cloud@2404' /xtrabackup/full

2.3.4 差异备份

（1）差异备份1

mysql> insert into backupdb.t1 values(22,'tom');

# innobackupex --user=root --password='Cloud@2404' --incremental /xtrabackup/full --incremental-basedir=/xtrabackup/full/2024-08-15_19-13-03/

（2）差异备份2

mysql> insert into backupdb.t1 values(33,'alice');

# innobackupex --user=root --password='Cloud@2404' --incremental /xtrabackup/full/ --incremental-basedir=/xtrabackup/full/2024-08-15_19-13-03/

2.3.5 差异备份恢复

（1）停止数据库

# systemctl stop mysqld

（2）清理环境

# rm -rf /var/lib/mysql/*

（3）重演回滚redo log（周一，某次差异）－－> 恢复数据

备份全量数据回滚：

# innobackupex --apply-log --redo-only /xtrabackup/full/2024-08-15_19-13-03/

备份某一天增量数据回滚

# innobackupex --apply-log --redo-only /xtrabackup/full/2024-08-15_19-13-03/ --incremental-dir=/xtrabackup/full/2024-08-15_19-18-08/

恢复数据：

# innobackupex --copy-back /xtrabackup/full/2024-08-15_19-13-03/

（4）修改权限

# chown -R mysql.mysql /var/lib/mysql

（5）启动数据库

# systemctl start mysqld

# mysql -uroot -p'Cloud@2404'

mysql> select * from backupdb.t1;

三、逻辑备份实例

3.1 备份表和恢复表

备份和恢复db1库里的table_1表

mysqldump -uroot -p'Cloud@2404' db1 table_1> /backup/table1.sql

mysql -uroot -p'Cloud@2404' db1 < /backup/table1.sql

3.2 备份数据库和恢复数据库

mysqldump -uroot -p'Cloud@2404' db2 > /backup/backupdb.sql

恢复库 ：mysql -uroot -p'Cloud@2404' db3 < /backup/backupdb.sql