MySQL备份与恢复

目录

备份方法

物理备份方法

备份策略

常见的备份方法

物理冷备

专用备份工具mysqldump或mysqlhotcopy

启用二进制日志进行增量备份

第三方工具备份

物理冷备份与恢复

mysqldump备份与恢复

MySQL完全备份

优点

缺点

数据库上云迁移

完全备份

mysqldump逻辑热备

----MySQL 增量备份----

---

备份方法

物理备份:对数据库操作系统的物理文件(如数据文件，日志文件等)的备份

逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份

物理备份方法

冷备份 (脱机备份) :是在关闭数据库的时候进行的

热备份 (联机备份) :数据库处于运行状态，依赖于数据库的日志文件

温备份: 数据库锁定表格 (不可写入但可读) 的状态下进行备份操作O

逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份

备份策略

完全备份:每次备份都备份完整的库或者表数据

差异备份:只备份上一次完全备份后的更新数据

增量备份: 每次备份只备份上一次完全备份或增量备份后的更新数据

常见的备份方法

物理冷备

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

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

专用备份工具mysqldump或mysqlhotcopy

mysqldump常用的逻辑备份工具。

mysglhotcopy仅拥有备份MyISAM和ARCHIVE表

启用二进制日志进行增量备份

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

第三方工具备份

免费的MySQL热备份软件Percona XtraBackup

物理冷备份与恢复

关闭MySQL数据库

使用tar命令直接打包数据库文件夹

直接替换现有MySQL目录即可

mysqldump备份与恢复

·MySQL自带的备份工具，可方便实现对MySQL的备份

可以将指定的库、表导出为SQL脚本

使用命令mysql导入备份的数据

MySQL完全备份

优点

备份与恢复操作简单方便

缺点

数据存在大量的重复

占用大量的备份空间

备份与恢复时间长

数据库上云迁移

冷迁移 物理冷备 先关闭数据库，再打包备份+恢复

热迁移 阿里云 DTS

完全备份

物理冷备:先关闭数据库，使用tar命令打包备份数据库的数据目录和文件  mysql/data/

mysqldump逻辑热备

完全恢复

先登陆到数据库，再执行source, XXX.sql（如果sql文件是只备份了表数据的，需要先创建库并 use 切换库后再执行）

mysql -uXXX -pXXX <XXX.sql cat XXX.sql | mysql -uXXX -pXXX 恢复库

mysql -uXXX -pXXX 库名<XXX.sql cat XXX.sql | mysql -uXXX -pXXX 库名 恢复表

增量备份

通过刷新二进制日志间接实现增量备份

mysqladmin -u XXX -p XXX flush-logs

----MySQL 增量备份----

1．开启二进制日志功能
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED				#可选，指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1

#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

systemctl start mysqld
ls -l /usr/local/mysql/data/mysql-bin.*

2．可每周对数据库或表进行完全备份
mysqldump -u root -p kgc info1 > /opt/kgc_info1_$(date +%F).sql
mysqldump -u root -p --databases kgc > /opt/kgc_$(date +%F).sql

3．可每天进行增量备份操作，生成新的二进制日志文件(例如 mysql-bin.000002)
mysqladmin -u root -p flush-logs

4．插入新数据，以模拟数据的增加或变更
use kgc;
insert into info1 values(3,'user3','male','game');
insert into info1 values(4,'user4','female','reading');

5．再次生成新的二进制日志文件(例如 mysql-bin.000003)
mysqladmin -u root -p flush-logs
#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.000003文件中

6．查看二进制日志文件的内容
cp /usr/local/mysql/data/mysql-bin.000002 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
#--base64-output=decode-rows：使用64位编码机制去解码并按行读取
#-v：显示详细内容

断点恢复

基于位置点恢复

mysqlbinlog --no-defaults --start-position='开始位置点'--stop-position='结束位置点'<BINLOG> | mysql -uXXX -pXXX

基于时间点恢复

mysqlbinlog --no-defaults --start-datetime='开始时间点' --stp-position='结束时间点' <BINLOG> | mysql -uxxX-pxxX

如果需要恢复到某条sgl语句之前的所有数据，就stop在这个语句的位置点或时间点之前

如果需要恢复某条sql语句及其之后的所有数据，就从这个语句的位置点或时间点开婚start