MySQL备份与恢复

一、MySQL的数据库备份概述

1.数据库备份类型

(1)从物理与逻辑角度分类

物理备份可以分为冷备份，热备份和温备份。

冷备份：在数据库关闭状态下进行备份。

热备份：数据库处于运行状态下进行备份，备份方法依赖数据库的日志文件。

温备份：数据库锁定表格的状态进行备份。

（2）从数据库备份策略角度分类

从数据库备份策略角度，可以分为完全备份，差异备份，增量备份。

完全备份：对数据库进行完整备份，及对整个数据库，数据结构进行备份和文件结构进行备份，保存的是备份完成时的数据库。

差异备份：备份完全备份之后被修改的文件，备份的时间节点从完全备份起。

增量备份：以上次时间为节点，备份之间的数据变化，因而备份数据量小，占用空间小，备份速度快。

2.常见的备份方法

物理备份
备份数据库处于关闭状态，直接打包数据
备份速度快，恢复简单

常用的备份工具
mysqldump

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

第三方工具备份
MySQL热备份软件Percona XtraBackup

二、物理备份与恢复

1.物理冷备份与恢复

(数据备份)
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /bak
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
[root@localhost local]# tar zcvf /bak/mysql_$(date +%F).tar.gz mysql/

备注：
z 压缩
c 创建
v 详细信息
f 生成文件
F：格式化，单独提取年月日

（模拟故障）
[root@localhost backup]# systemctl start mysqld
[root@localhost backup]# mysql -uroot -ppwd123 -e 'drop database auth;'
[root@localhost backup]# mysql -u root -ppwd123 -e 'select * from auth.users;'
备注：
-e 直接在系统命令行进行，无需进入MySQL命令行

（数据恢复）

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# cd /bak/
[root@localhost bak]# tar zxvf mysql_2024-06-26.tar.gz
[root@localhost backup]# cd /usr/local/
[root@localhost local]# mv mysql/ mysql.bak （把系统文件进行重命名）

[root@localhost ~]# mv /bak/mysql /usr/local/

[root@localhost local]# systemctl restart mysqld
[root@localhost local]# mysql -uroot -ppwd123 -e 'select * from auth.users;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
+------+------+

2.MySQLdump备份与恢复

（导出数据）
[root@localhost local]# mysqldump -uroot -ppwd123 auth users>/opt/auth-users.sql

语法解读：
将MySQL库中auth库中users表导出到opt目录下，重命名为auth-users.sql

[root@localhost opt]# mysqldump -uroot -ppwd123 auth>/opt/auth.sql (导出数据库)
[root@localhost opt]# mysqldump -uroot -ppwd123 --opt--all-databases>/opt/all-data.sql （备份所有数据库）
备注：--opt：表示优化执行速度

（导入表）
已存在现有的数据库，可以将表导入任何现有的数据库
[root@localhost opt]# mysql -uroot -ppwd123 -e 'create database test'; （新建库）
root@localhost ~]# mysql -uroot -ppwd123 test</opt/auth-users.sql （导入到其他库[root@localhost ~]# mysql -uroot -ppwd123 auth < /opt/auth-users.sql ##导入到原始所在的库[root@localhost ~]# mysql -uroot -ppwd123 -e 'show tables from test;'

（导入库）
[root@localhost ~]# mysql -uroot -ppwd123 auth</opt/auth.sql

（全库还原）
[root@localhost ~]# mysql -uroot -ppwd123 </opt/all-data.sql

3.增量备份与恢复

增量备份（incremental backup）是备份的一个类型，是指在一次完全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。

（启动二进制日志功能）
[root@localhost ~]# vim /etc/my.cnf
在[mysqld]模块下添加
log-bin=mysql-bin (二进制log)
server-id=1 （id标识）
binlog_format=MIXED
[root@localhost ~]# systemctl restart mysqld

（生成新的日志文件）
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# mysqladmin -uroot -ppwd123 flush-logs （更新日志文件）

（查看日志内容）
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000001

<在表中创建若干数据>

<模拟数据故障>
[root@localhost data]# mysql -uroot -ppwd123 -e 'drop table auth.users';
[root@localhost data]# mysql -uroot -ppwd123 -e 'select * from auth.users';

<恢复数据>
（恢复完全备份）
[root@localhost opt]# mysql -uroot -ppwd123 auth</opt/auth-users.sql
[root@localhost opt]# mysql -uroot -ppwd123 -e 'select * from auth.users';
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
+------+----------+

<恢复增量备份>
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002 （查看日志内容）

备注：
-no-defaults：不读取任何选项文件，其后需要直接跟日志文件或开始停止位置
--base64-output=DECODE-ROWS 文件加密，(仅查看文件可无)

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -uroot -ppwd123
[root@localhost data]# mysql -uroot -ppwd123 -e 'select * from auth.users' （查看文件是否完整）
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 333 | aaa |
| 333 | bbb |
| 444 | ccc |
| 555 | ddd |
+------+----------+

<基于位置恢复>
<删除表中某一行>
[root@localhost data]# mysqladmin -u root -p flush-logs

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000002
方法一：指定位置恢复
[root@localhost data]# mysqlbinlog --no-defaults --start-position='870' --stop-position='1012' mysql-bin.000002 | mysql -uroot -ppwd123
备注：
--start-position='870' 开始位置
--stop-position='1012' 结束位置

方法二：指定时间恢复
[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2024-06-27 14:54:06' --stop-datetime='2024-06-27 14:54:25' mysql-bin.000002 | mysql -uroot -ppwd123