MySQL数据库的备份

MySQL数据库的备份

MySQL备份方法

1. 物理备份：
   物理备份涉及直接复制MySQL的数据文件和日志文件。这种方法通常更快，但需要数据库服务器处于关闭状态或使用特殊的复制工具（如Percona XtraBackup）来进行热备份。物理备份适用于大型数据库，因为它可以快速恢复数据，但可能不适用于跨平台恢复。
2. 逻辑备份：
   逻辑备份是通过导出数据库的结构和数据来完成的，通常使用mysqldump工具。这种备份方法生成的是SQL语句，可以用于重建数据库。逻辑备份适用于小型数据库，恢复时需要执行SQL语句来重建数据，可能需要更长的时间。
   备份策略：
3. 完全备份：
   完全备份是备份整个数据库，包括所有数据和结构。这是最基本的备份类型，但备份和恢复过程可能需要较长的时间。完全备份是差异备份和增量备份的基础。
4. 差异备份：
   差异备份只备份自上次完全备份以来发生变化的数据。这种备份方法比完全备份快，因为只备份了部分数据。恢复时，需要先恢复最近的一次完全备份，然后应用所有差异备份。
5. 增量备份：
   增量备份只备份自上次完全备份或增量备份以来发生变化的数据。这种方法备份和恢复速度最快，但恢复过程最为复杂，因为需要按顺序应用所有增量备份。

此文主要讲解逻辑备份

完全备份

物理备份

备份

1.关闭MySQL服务器

systemctl stop mysqld.service

2.将MySQL数据库目录下/data/文件夹打包备份至其他磁盘

cd /opt	
tar cvf mysql_all_bak.tar /usr/local/mysql/data

逻辑热备完全备份

1使用mysqldump命令进行备份

所有库备份

mysqldump -u "你的账户" -p"你的密码" --all-databases > "/备份路径/文件名"

mysqldump -uroot -p123123 --all-databases > /opt/mysql_all_bak.sql

部分库备份

mysqldump -uroot -p123123 --databases "库名1" "库名2" "库名3" ...... >"/备份路径/文件名"

部分表的备份

mysqldump -uroot -p123123 "库名" "表名" >"/备份路径/文件名"

备份所有的库

备份单独的表

mysqldump -uroot -p123123 glry cd > /opt/cd_bak.sql

逻辑热备恢复

恢复库

目前数据库表与库如下图所示

现在删除库glry

drop database glry;

恢复

mysql -uroot -p123123 </opt/mysql_all_bak.sql

恢复表

删除表cd

drop table glry.cd

mysql -uroot -p123123 "库名" < "/备份路径/文件名"

mysql -uroot -p123123 glry < /opt/cd__bak.sql

cd恢复了

增量备份

备份

首先开启mysql数据库的二进制日志
在mysql配置文件 my.cof里配置
在[mysqld]配置块下添加

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

重启mysql服务
登录数据库，并创建库和表，然后刷新二进制日志文件

create database glcs;
use glcs;
create table 1d(id int,name char(5));
insert into 1d values(1,'tg1');
insert into 1d values(2,'tg2');
insert into 1d values(3,'tg3');
insert into 1d values(4,'tg4');
insert into 1d values(5,'tg5');
insert into 1d values(6,'tg6');
insert into 1d values(7,'tg7');
exit

刷新二进制日志

mysqladmin -uroot -p123123 flush-logs  

cd /usr/local/mysql/data/
cp -a  mysql-bin.000001  /opt/mysql-bin-$(date -d "-1 day" +%Y%m%d).sql
cd /opt
ls

增量备份恢复

基于位置进行恢复

首先查看二进制日志

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin-20240324.sql

删除glcs数据库模拟数据丢失

恢复到创建glcs表但没有创建cd表时
查看二进制日志 可以看到创建glcs的位置在at219至at313

mysqlbinlog --no-defaults --stop-position= /opt/mysql-bin-20240324.sql|mysql -uroot -p123123

目的达成

基于时间

为了恢复到tg4
查看二进制日志
起始时间 240325 17:14:03
创建完tg4是在240325 17:16:34

mysqlbinlog --no-defaults --start-datetime="2024-03-25 17:14:03" --stop-datetime="2024-03-25 17:16:34" /opt/mysql-bin-20240324.sql | mysql -uroot -p123123

恢复成功