MySQL数据库的备份与恢复-CSDN博客

本文链接：https://blog.csdn.net/m0_75135871/article/details/140761483

数据备份的重要性

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

物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

逻辑备份：对数据库逻辑文件（表等数据库对象）的备份

备份策略

常见的备份方法

物理冷备

MySQL没有办法直接备份，需要启用二进制日志进行增量备份

第三方工具备份

备份

1.准备两台部署MySQL数据库的虚拟机

2.第一台数据库密码123，第二台数据库密码123456

数据备份的重要性

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

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

造成数据丢失的原因

程序错误

人为操作错误

运算错误

磁盘故障

天灾和盗窃

数据库备份

从物理与逻辑角度，备份可分为

物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

冷备份：（脱机备份）：是在关闭数据库的时候进行

热备份：（联机备份）：数据库处于运行状态，依赖于数据库的日志文件

温备份：数据库锁定表格（不可写但可读）的状态下进行备份操作

逻辑备份：对数据库逻辑文件（表等数据库对象）的备份

备份策略

完全备份：每次对数据库进行完整的备份

差异备份：备份自从上次完全备份之后被修改过的文件

增量备份：只有在上次完全备份或增量备份后被修改的文件才会备份

常见的备份方法

物理冷备

备份时数据库处于关闭状态，直接打包数据库文件

备份速度快，恢复时也是最简单的

专用备份工具mydum或sqlhotcopy

mysqldump常用的逻辑备份工具

mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

MySQL没有办法直接备份，需要启用二进制日志进行增量备份

进行增量备份，需要刷新二进制日志

第三方工具备份

免费的MySQL热备份软件percona XtraBackup

备份

1.准备两台部署MySQL数据库的虚拟机

2.第一台数据库密码123，第二台数据库密码123456

3.查看存储路径

grep datadir /etc/my.cnf

4.冷备份

1.物理冷备

1.关闭服务（冷备份需要关闭服务）

systemctl stop mysqld

2. 创建备份文件存放目录

mkdir /backup
cd /usr/local/mysql/

3.打包文件

tar zcf /backup/mysql-all-$(date +%F).tar.gz data/
ls /backup/
#开启服务
systemctl start mysqld

4.传输文件到第二台机器，输入第二台机器密码

scp /backup/mysql-all-2024-07-29.tar.gz root@192.168.27.129:/root/
#：/root/ 传输到目标地址的文件

5.第二台机器验证传输

6.在第二台恢复备份

#1.停止数据库
systemctl stop mysqld

#2.查看存储路径 
ls /usr/local/mysql/     

#3.改原文件名称，防止冲突
mv /usr/local/mysql/data/ /usr/local/mysql/data.old

#4.解压备份
tar zxf mysql-all-2024-07-29.tar.gz

#5.移动备份文件
mv data/ /usr/local/mysql/

#6.开启服务
systemctl start mysqld

7.验证数据（查看数据库是否有第一台数据库的数据，登录数据库时登录的密码也是第一台的密码）

2.MySQL dump备份

1.备份表

# 备份指定库中的部分表
# mysqldump [选项] 库名 [表名 1] [表名 2] … > /备份路径/备份文件名
mysqldump -uroot -p mysql user > mysql_user.sql

2.备份库

# 备份一个或多个完整的库（包括其中所有的表）
# mysqldump [选项] --databases 库名 1 [库名 2] … > /备份路径/备份文件名
mysqldump -uroot -p --databases mysql > mysql.sql

3.备份所有数据

# 备份 MySQL 服务器中所有的库
# mysqldump [选项] --all-databases > /备份路径/备份文件名
# -A 是 --all-databases的简写
mysqldump -uroot -p --all-databases > all-data.sql
mysqldump -uroot -p -A > all-data.sql

4.恢复

mysql -uroot -p < all-data.sql 
mysql -uroot -p123 -e 'show databases;'

5.增量备份

1.开启二进制备份

vi /etc/my.cnf

log-bin=mysql-bin
binlog_format = MIXED
server-id=1

2.重启服务

3.开始备份

#进入数据库填充数据
CREATE DATABASE client;
USE client;

CREATE TABLE user_info(
IdentityCard CHAR(20) NOT NULL,
name CHAR(20) NOT NULL, 
gender CHAR(4),
UserID CHAR(10) NOT NULL,
expenses INT(10));

INSERT INTO user_info VALUES('000006','张三','男','016','10');
INSERT INTO user_info VALUES('000007','李四','女','017','91');
INSERT INTO user_info VALUES('000008','王五','女','018','23');

SELECT * FROM user_info;

1.先完全备份

mkdir /mysql_bak
mysqldump -uroot -p -A > /mysql_bak/mysql-all.sql
# 刷新日志
mysqladmin -uroot -p flush-logs

2.刷新后，是否出现新的日志

3.进入数据库，新增数据

USE client;

INSERT INTO user_info VALUES('000009','赵六','男','019','37');
INSERT INTO user_info VALUES('000010','孙七','男','020','36');

#停顿几秒，验证时间
INSERT INTO user_info VALUES('000011','张三','男','021','35');
INSERT INTO user_info VALUES('000012','李四','男','022','37');

4.开始增量备份

# 刷新日志
mysqladmin -uroot -p flush-logs
复制二进制文件
cp /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/

5.查看二进制文件

二进制备份恢复

先恢复完全备份

mysql -uroot -p123 < /mysql_bak/mysql-all.sql
mysql -uroot -p123 -e 'show databases;'

基于时间恢复

#--start-datetime  从什么时间开始恢复
mysqlbinlog --no-defaults --start-datetime='2024-07-28 22:13:33' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
#--stop-datetime 恢复到什么时间
mysqlbinlog --no-defaults --stop-datetime='2024-07-28 22:13:33' /mysql_bak/mysql-bin.000002 | mysql -uroot -p

基于偏移量恢复

#--stop-position 恢复到什么位置
mysqlbinlog --no-defaults --stop-position='623' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
#--start-position 从什么位置开始恢复，可以混用
mysqlbinlog --no-defaults --start-position='623' /mysql_bak/mysql-bin.000002 | mysql -uroot -p

全部恢复

把mysql-bin.000002的内容全部恢复
mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -uroot -p