数据库备份的分类
-
数据备份的重要性
- 备份的主要目的是灾难恢复
- 在生产环境中,数据的安全性至关重要
- 任何数据的丢失都可能产生严重的后果
- 任何数据丢失的原因
- 程序错误
- 人为操作错误
- 运算错误
- 磁盘故障
- 灾难(如火宅、地震)个盗窃
-
从物理与逻辑的角度,备份可分为
- 物理备份:对数据库操作系统的物理文件(如数据文件日志文件等)的备份
- 物理备份方法
- 冷备份 (脱机备份) :是在关闭数据库的时候进行的冷备份
- 热备份 (联机备份): 数据库处于运行状态,依赖于数据库的日志文件
- 温备份: 数据库锁定表格 (不可写入但可读)的状态下进行备份操作
- 逻辑备份:对数据库逻辑组件 (如:表等数据库对象)的备份
- 物理备份方法
- 物理备份:对数据库操作系统的物理文件(如数据文件日志文件等)的备份
-
从数据库的备份策略角度,备份可分为
- 完全备份:每次对数据库进行完整的备份
- 差异备份:备份自从上次完全备份之后被修改过的文件
- 增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份
常见的备份方法
- 物理冷备
- 备份时数据库处于关闭状态,直接打包数据库文件
- 备份速度快,恢复时也是最简单的
- 专用备份工具mysqldump或mysqlhotcopy
- ·mysqldump常用的逻辑备份工具
- mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
- 启用二进制日志进行增量备份
- 进行增量备份,需要刷新二进制日志
- 第三方工具备份
- 免费的MySQL热备份软件Percona XtraBackup
MySQL完全备份与恢复
-
是对整个数据库、数据库结构和文件结构的备份
-
保存的是备份完成时刻的数据库
-
是差异备份与增量备份的基础
-
优点
- 备份与恢复操作简单方便
-
缺点
- 数据存在大量的重复
- 占用大量的备份空间
- 备份与恢复时间长
数据库完全备份分类
- 物理冷备份与恢复
- 关闭MySQL数据库
- 使用tar命令直接打包数据库文件夹
- 直接替换现有MySQL目录即可
- mysqldump备份与恢复
- MySQL自带的备份工具,可方便实现对MySQL的备份
- 可以将指定的库、表导出为SQL脚本
- 使用命令mysql导入备份的数据
##########物理冷备##########
##########7-7##########
create database mt;
#创建数据库mt
use mt;
#切换到数据库mt
create table tt (id int, name char(4),age int,sex char(2),hobby varchar(20),primary key(id));
insert into tt(id,name,age,sex,hobby) values(1,'aa',18,'男','football');
insert into tt values(2,'bb',19,'男','basketball');
insert into tt values(3,'cc',20,'男','tennus');
insert into tt values(4,'dd',18,'女','singing');
insert into tt values(5,'ee',18,'女','dancing');
create table ttt like tt;
insert into ttt(select * from tt);
quit
systemctl stop mysqld
cd /usr/local/mysql/
ls
ls data/
mkdir /opt/backup
tar zcvf /opt/backup/mysql_full_$(date -d "-1 day" +%Y%m%d).tar.gz ./data/
cd /opt/backup/
tar tf mysql_full_20230917.tar.gz
###tar tf 不解压查看文件
scp mysql_full_20230917.tar.gz 192.168.174.105:/opt
###把该文件传给另外一个服务器数据库
##########7-6##########
systemctl stop firewalld
setenforce 0
systemctl stop mysqld
cd /opt
tar xf mysql_full_20230917.tar.gz
cd /usr/local/mysql/
mv data/ data.bak
cp -a /opt/data/ ./
systemctl start mysqld
mysql -u root -pabc123
show databases;
use mt;
show tables;
select * from tt;
select * from ttt;
[root@t6 ~]#date +%D
09/18/23
[root@t6 ~]#date +%F
2023-09-18
[root@t6 ~]#date +%Y%m%d
20230918
[root@t6 ~]#date +%Y/%m/%d
2023/09/18
[root@t6 ~]#date +%Y/%m/%d-%H:%M:%S
2023/09/18-18:54:39
[root@t6 ~]#date +%Y/%m/%d
2023/09/18
[root@t6 ~]#date -d "+1 day" +%Y/%m/%d
2023/09/19
[root@t6 ~]#date -d "-1 day" +%Y/%m/%d
2023/09/17
[root@t6 ~]#date -d "1 day ago" +%Y/%m/%d
2023/09/17
[root@t6 ~]#date -d "$(date +%Y%m01) -1 day" +%Y/%m/%d
2023/08/31
#####上个月的最后一天
[root@t6 ~]#date -d "$(date -d "1 month" +%Y%m01) -1 day" +%Y/%m/%d
2023/09/30
#####这个月的最后一天
[root@t6 ~]#date +%Y/%m/01
2023/09/01
#####这个月的第一天
[root@t6 ~]#date -d "1 month" +%Y/%m/01
2023/10/01
#####下个月的第一天
date +%Y%m01
#当月第一天
date -d "1 month" +%Y%m%d
#下个月的今天
date -d "1 month" +%Y%mO1
#下个月的第一天
date -d "$(date -d "1 month" +%Y%m01) -1 day" +%Y%m%d
#当月最后一天
date -d "$(date +%Y%m01) -1 day" +%Y%m%d
#上个月最后一天
##########逻辑热备##########
cd /opt/backup
mysqldump -uroot -pabc123 mt > /opt/backup/mt.sql
cat mt.sql |grep -v '^--' |grep -v '^/\*'
cat mt.sql |grep -v '^--' |grep -v '^/\*' |grep -v '^$'
cat mt.sql |egrep -v '^--|^/\*|^$'
mysqldump -uroot -pabc123 --databases mt > /opt/backup/mt_full.sql
###
cat mt_full.sql |egrep -v '^--|^/\*|^$'
mysqldump -uroot -pabc123 --all-databases > /opt/backup/all_data_full.sql
###所有数据库
cat all_data_full.sql |grep '^CREATE DATABASE'
mysqldump -uroot -pabc123 mt tt > /opt/backup/mt_tt.sql
#####xtrabackup物理热备#####
#####恢复
#####source命令恢复数据
mysql -uroot -pabc123
show databases
drop database mt
show databases
source /opt/backup/mt_full.sql
show databases
use mt;
show tables;
select * from tt
drop database mt;
show databases
create database mt
use mt
###可以切换其他数据库恢复到其他数据库中
source /opt/backup/mt.sql
show tables
select * from tt
#####mysql命令恢复数据
###在linux命令行输入sql语句执行
mysql -uroot -pabc123 -e "show databases;"
mysql -uroot -pabc123 -e "drop database mt;"
mysql -uroot -pabc123 < /opt/backup/mt_full.sql
mysql -uroot -pabc123 -e "show databases;"
mysql -uroot -pabc123 -e "show tables from mt;"
mysql -uroot -pabc123 -e "select * from tt;"
mysql -uroot -pabc123 -e "drop database mt;"
mysql -uroot -pabc123 -e "create database mt;"
mysql -uroot -pabc123 mt< /opt/backup/mt.sql
mysql -uroot -pabc123 -e "show databases;"
mysql -uroot -pabc123 -e "show tables from mt;"
cat mt_full.sql |mysql -uroot -pabc123
MySQL增量备份与恢复
-
使用mysqldump进行完全备份存在的问题
- 备份数据中有重复数据
- 备份时间与恢复时间过长
-
是自上一次备份后增加/变化的文件或者内容
-
特点
- 没有重复数据,备份量不大,时间短
- 恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复,而且要对所有增量备份进行逐个反推恢复
-
MySQL没有提供直接的增量备份方法
-
可通过MySQL提供的二进制日志间接实现增量备份
-
MySQL二进制日志对备份的意义
- 二进制日志保存了所有更新或者可能更新数据库的操作
- 二进制日志在启动MySQL服务器后开始记录,并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
- 只需定时执行flush logs方法重新创建新的日志,生成二进制文件序列,并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份
-
一般恢复
- 将所有备份的二进制日志内容全部恢复
-
基于位置恢复
- 数据库在某一时间点可能既有错误的操作也有正确的操作
- 可以基于精准的位置跳过错误的操作
-
基于时间点恢复
- 跳过某个发生错误的时间点实现数据恢复
vim /etc/my.cnf
#错误日志
log-error=/usr/local/mysql/data/mysql_error.log
#通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#二进制日志(binlog)
log-bin=mysql-bin
#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=1
systemctl restart mysqld
cd /usr/local/data
###查看添加的错误日志
mysql -uroot -pabc123
show [session/global]variables like 'general%'
show [session/global]variables like 'log_bin%'
show [session/global]variables like '%slow%'
show [session/global]variables like '%long%'
show variables like 'long_query_time'
set global slow_query_log=ON
###重启数据库后会还原
#####增量备份#####
mysql -uroot -pac123
use mt;
select * from tt;
update tt set age=20 where sex='男';
select * from tt;
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000001
###查看二进制日志文件的内容
###高并发的情况下,ROW(基于行)比STATEMET(基于SQL语句)好一些,更加精准
#STATEMET(基于SQL语句):速度块,占用空间小
#ROW(基于行):速度慢一点,占用空间大
#MIXED(混合模式)
vim /etc/my.cnf
binlog_format = MIXED
update tt set age=22 where sex='男';
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000002
mysqladmin -uroot -pabc123 flush-logs
#通过flush-logs命令刷新二进制日志文件
cat mysql-bin.index
#查看最新的二进制日志文件,最后一行就是最新的二进制文件
cat mysql-bin.index |tail -2 |head -1
#获取倒数第二行的日志
#昨天
mysqladmin -uroot -pabc123 flush-logs
cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
#今天
mysqladmin -uroot -pabc123 flush-logs
cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
###今天的日志文件就是04和05
#昨天
cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
mysqladmin -uroot -pabc123 flush-logs
#今天
cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
mysqladmin -uroot -pabc123 flush-logs
###今天的日志文件就是05和06
##########获取今日日志备份脚本##########
TODAY=$(date +%Y%m%d)
YESTERDAY=$(date -d "-1 day" +%Y%m%d)
cp /usr/local/mysql/data/mysql-bin.index /opt/backup/mysql-bin.index-$TODAY
COUNT=cat /opt/backup/mysql-bin.index-$TODAY |grep -v '/opt/backup/mysql-bin.index-$YESTERDAY' |awk -F '/' '{print $2}'
mysqladmin -uroot -pabc123 flush-logs
for i in $COUNT
do
mv -f /usr/local/mysql/data/$i /opt/backup/$i-$TODAY
done
#需求:把今天生成的所有二进制日志文件都备份移动到 /opt/backup/目录
cp /usr/local/mysql/data/mysql-bin.index /opt
cd /opt
cp mysql-bin.index mysql-bin.index-old
vim !$
cat mysql-bin.index-old
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
vim mysql-bin.index
cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
cat mysql-bin.index |grep -v "$(cat mysql-bin.index-old)"
MySQL备份恢复案例
###mt数据库下的tt数据表
###one day 2023-9-19
cd
mkdir mysql-backup
cd mysql-backup/
mkdir full bin
#full完全备份 bin增量备份
#crontab -e
#0 2 * * 2 full.sh
#0 2 * * * bin.sh
mysqldump -uroot -pabc123 mt tt > /root/mysql-backup/full/mt_tt-$(date +%Y%m%d).sql
#完全备份006
ls full/
mysqladmin -uroot -pabc123 flush-logs
#日志刷新
cd /usr/local/mysql/data/
###two day 2023-9-20
insert into tt values(6,'ff',19,'男','PS5');
insert into tt values(7,'gg',20,'男','PS4');
mysqladmin -uroot -pabc123 flush-logs
#刷新日志,增量备份 007
mv -f mysql-bin.000006 /root/mysql-backup/bin/mysql-bin.000006-$(date -d '1 day' +%Y%m%d)
#增量备份
ls /root/mysql-backup/bin/
###three day 2023-9-21
insert into tt values(8,'hh',21,'男','switch');
insert into tt values(9,'ii',22,'男','switch pro');
mysqladmin -uroot -pabc123 flush-logs
#刷新日志,增量备份 008
mv -f mysql-bin.000007 /root/mysql-backup/bin/mysql-bin.000007-$(date -d '2 day' +%Y%m%d)
#增量备份
ls /root/mysql-backup/bin/
###four day 2023-9-22
#有人删库跑路了
drop database mt;
show databases;
#恢复备份
create database mt;
quit
mysql -uroot -pabc123 mt < /root/mysql-backup/full/mt_tt-20230919.sql
#one day 完全备份
mysql -uroot -pabc123
show databases;
use mt;
show tables;
select * from tt;
quit
mysqlbinlog --no-defaults /root/mysql-backup/bin/mysql-bin.000006-20230920 |mysql -uroot -pabc123
#two day 增量备份
mysql -uroot -pabc123
show databases;
use mt;
show tables;
select * from tt;
quit
mysqlbinlog --no-defaults /root/mysql-backup/bin/mysql-bin.000007-20230921 |mysql -uroot -pabc123
#three day 增量备份
mysql -uroot -pabc123
show databases;
use mt;
show tables;
select * from tt;
quit
###选择性的增量恢复
cd /root/mysql-backup/bin/
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000006-20230920 > mysql-bin-20230920
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000007-20230921 > mysql-bin-20230921
vim mysql-bin-20230920
vim mysql-bin-20230921
#基于位置点和时间点恢复
delete from tt where id > 5;
vim mysql-bin-20230920
mysqlbinlog --no-defaults --start-position='294' --stop-position='504' /root/mysql-backup/bin/mysql-bin.000006-20230920 |mysql -uroot -pabc123
#基于位置点恢复
select * from tt;
delete from tt where id > 5;
vim mysql-bin-20230920
mysqlbinlog --no-defaults --start-position='579' /root/mysql-backup/bin/mysql-bin.000006-20230920 |mysql -uroot -pabc123
#基于位置点恢复
select * from tt;
delete from tt where id > 5;
mysqlbinlog --no-defaults --start-datetime='2023-09-19 17:10:54' --stop-datetime='2023-09-19 17:11:01' /root/mysql-backup/bin/mysql-bin.000006-20230920 |mysql -uroot -pabc123
#基于时间点恢复
select * from tt;
delete from tt where id > 5;
mysqlbinlog --no-defaults --start-datetime='2023-09-19 17:11:01' /root/mysql-backup/bin/mysql-bin.000006-20230920 |mysql -uroot -pabc123
select * from tt;
总结
数据库备份
备份方法
物理备份:直接对数据库的数据文件或日志文件进行备份
逻辑备份:对数据库的库或表对象进行备份
备份策略
完全备份:每预备份都备份完整的数据库
差异备份:只备份上一次完个备份后的更新数据
增量备份: 每次备份只备份上一次完全备份或增量备份后的更新数据
数据库上云迁移 冷迁移 物理冷备 打包备份+恢复
热迁移 阿里云的DTS服务
tar zcvf GZIP
jcvf BZIP2
Jcvf XZ
zip zip
完全备份
物理冷备:先关闭myaqld服务,使用tar命令打包备份数据的数据日录及文件 /usr/local/mysgl/data/
myaqldump逻辑热备
xtrabaekup物理热备
#在linux中面交互执行sql语句
mysql -u root -pabc123 -e "show tables;"
vim /etc/my.cnf
#错误日志
log-error=/usr/local/mysql/data/mysql_error.log
#通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#二进制日志(binlog)
log-bin=mysql-bin
#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=1
binlog_format = MIXED
systemctl stop firewalld
setenforce 0
systemctl start mysqld
systemctl status mysqld
mysql -uroot -pabc123
show databases;
create database grades;
show databases;
use grades;
show tables;
create table scores(name char(4), class char(4), sno int,chinese int, english int, maths int, science int);
show tables;
select * from scores;
insert into scores values ('张三','一班',20170822,110,105,92,235);
insert into scores values ('李四','一班',20170820,95,115,110,260);
insert into scores values ('王五','一班',20170818,95,103,108,270);
insert into scores values ('赵六','一班',20170816,100,109,112,265);
select * from scores;
quit
mkdir mysql-backup
cd mysql-backup/
mkdir full bin
mysqldump -uroot -pabc123 grades scores > /root/mysql-backup/full/grades_scores-$(date +%Y%m%d).sql
mysqladmin -uroot -pabc123 flush-logs
mysql -uroot -pabc123
show databases;
use grades;
show tables;
select * from scores;
insert into scores values ('李宁','二班',20170824,92,98,105,235);
insert into scores values ('陈铭','二班',20170826,111,107,96,204);
select * from scores;
quit
mysqladmin -uroot -pabc123 flush-logs
ls /usr/local/mysql/data/
/usr/local/mysql/data/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000002
mv -f mysql-bin.000002 /root/mysql-backup/bin/mysql-bin.000002-$(date -d '1 day' +%Y%m%d)
ls /root/mysql-backup/bin/
mysql -uroot -pabc123
show databases;
use grades;
show tables;
select * from scores;
insert into scores values ('付杰','二班',20170828,115,118,116,268);
insert into scores values ('郭尚','二班',20170830,111,99,80,259);
quit
mysqladmin -uroot -pabc123 flush-logs
ls /usr/local/mysql/data/
/usr/local/mysql/data/
mv -f mysql-bin.000003 /root/mysql-backup/bin/mysql-bin.000003-$(date -d '2 day' +%Y%m%d)
ls /root/mysql-backup/bin/
###模拟数据丢失
mysql -uroot -pabc123
show databases;
drop database grades;
show databases;
create database grades;
show databases;
quit
###
cd /root/mysql-backup/bin/
ls
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002-20230921 > mysql-bin-20230921
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000003-20230922 > mysql-bin-20230922
ls
vim mysql-bin-20230921
vim mysql-bin-20230922
###一班完全恢复
mysql -uroot -pabc123 grades < /root/mysql-backup/full/grades_scores-20230920.sql
mysql -uroot -pabc123
show databases;
use grades;
show tables;
select * from scores;
quit
###按位置增量恢复二班陈铭
cd /root/mysql-backup/bin/
vim mysql-bin-20230920
mysqlbinlog --no-defaults --start-position='627' --stop-position='805' /root/mysql-backup/bin/mysql-bin.000002-20230921 |mysql -uroot -pabc123
mysql -uroot -pabc123
show databases;
use grades;
show tables;
select * from scores;
quit
###按时间增量恢复二班付杰
cd /root/mysql-backup/bin/
vim mysql-bin-20230921
mysqlbinlog --no-defaults --start-datetime='2023-09-20 00:20:50' --stop-datetime='2023-09-20 00:20:58' /root/mysql-backup/bin/mysql-bin.000003-20230922 |mysql -uroot -pabc123
mysql -uroot -pabc123
show databases;
use grades;
show tables;
select * from scores;
quit
标签:bin,pabc123,恢复,备份,MySQL,uroot,mysql,backup
From: https://www.cnblogs.com/mtwm/p/17721624.html