Mysql备份与恢复

稗子酒

于 2024-03-01 20:12:55 发布

阅读量390

点赞数

分类专栏： Mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_64252848/article/details/136404984

版权

Mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

数据备份介绍

1.为何要备份
在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种.
硬件故障
软件故障
自然灾害
黑客攻击
误操作 (占比最大)
须知在生产环境中，服务器的硬件坏了可以维修或者换新，软件崩溃可以修复或重新安装, 但是如果数据
没了那可就毁了，生产环境中最重要的应该就是数据了。所以, 为了在数据丢失之后能够恢复数据, 我们
就需要定期的备份数据。

2.备份什么
我们要备份什么?
一般情况下, 我们需要备份的数据分为以下几种
数据
二进制日志, InnoDB事务日志
代码(存储过程、存储函数、触发器、事件调度器)
服务器配置文件

3.备份的类型

1）冷备、温备、热备
按照备份时数据库的运行状态，可以分为三种
MySQL 中进行不同类型的备份还要考虑存储引擎是否支持
MyISAM
热备 ×
温备 √
冷备 √
InnoDB
热备 √
温备 √
冷备 √

2）物理与逻辑
按照备份的内容分，可以分为两种
1、物理备份：直接将底层物理文件备份
2、逻辑备份：通过特定的工具从数据库中导出sql语句或者数据，可能会丢失数据精度

3）全量、差异、增量
按照每次备份的数据量，可以分为
全量备份/完全备份（Full Backup）：备份整个数据集( 即整个数据库 )
部分备份：备份部分数据集(例如: 只备份一个表的变化)

备份策略

直接cp,tar复制数据库文件
mysqldump+复制BIN LOGS
lvm2快照+复制BIN LOGS
xtrabackup+复制BIN LOGS

以上的几种解决方案分别针对于不同的场景
1. 如果数据量较小, 可以使用第一种方式, 直接复制数据库文件
2. 如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份
BINARY LOG达到增量备份的效果
3. 如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用 lvm2 的快照对数据文件进行
备份, 而后定期备份BINARY LOG达到增量备份的效果
4. 如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用 xtrabackup 进行完全备份
后, 定期使用 xtrabackup 进行增量备份或差异备份

备份实战

1.使用cp备份

环境：
db01  10.0.0.51   mysql5.7    生产库
db02  10.0.0.52   mysql5.7    迁移备份库

配置要一样
scp /etc/my.cnf 10.0.0.52:/etc/

案例1.使用cp做物理全备
1.在db01备份数据
#1.停库
systemctl stop mysqld

#2.将mysql的数据目录备份
mkdir -p /backup
cp -r /data/mysql/ /backup/

2.将备份的目录传到db02的数据目录下
scp -r /backup/mysql 10.0.0.52:/data/

3.db02上恢复数据
#1.修改db02的数据库目录归属
chown -R mysql.mysql /data/mysql

#2.重启db02的mysqld服务，同步数据
systemctl restart mysqld


扩展需求1：
db01的world库city表损坏，请问能否从备份的文件中拷贝回去来解决
可以恢复：
步骤：
1.将故障表删除
drop table world.city;

2.从备份表中找到创表语句，并执行
show create table world.city;

3.删除新建表表空间
alter table world.city discard tablespace;

4.将备份的表文件拷贝原路径
cp /backup/mysql/data/world/city.* /data/mysql/data/world
chown -R mysql.mysql /data/mysql

5.导入备份表的表空间
alter table world.city import tablespace;

扩展需求2，如果是world库呢，能否使用concat函数生成批量处理的sql语句或shell脚本
1.将故障库删除
drop database world;

2.从备份库中找到创库语句执行
show create database world;
CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET latin1 */;(执行)

3.从备份库中找到world库的创表语句执行
use world;
show create table country;
CREATE TABLE `country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint(6) DEFAULT NULL,
  `Population` int(11) NOT NULL DEFAULT '0',
  `LifeExpectancy` float(3,1) DEFAULT NULL,
  `GNP` float(10,2) DEFAULT NULL,
  `GNPOld` float(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

show create table city;
CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;


show create table countrylanguage;
CREATE TABLE `countrylanguage` (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
  `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

4.在备份库中使用concat函数拼接删除新建表表空间的sql文件
select concat("alter table ",table_schema,".",table_name," discard tablespace;") from  information_schema.tables where table_schema='world' into outfile '/tmp/discard.sql';

5.将sql文件从52主机scp到51主机上
scp /tmp.discrd.sql 10.0.0.51:/tmp

6.临时取消外键约束
set FOREIGN_KEY_CHECKS=0;

7.导入discard.sql文件
use world
source /tmp/discard.sql

8.将备份的world库文件拷到原路径
# cp -r /backup/mysql/data/world/* /data/mysql/data/world/
# chown -R mysql.mysql /data/mysql

9.在备份库中使用concat函数拼接导入新建表表空间的sql文件
select concat("alter table ",table_schema,".",table_name," import tablespace;") from  information_schema.tables where table_schema='world' into outfile '/tmp/import.sql';

10.将sql文件从52主机scp到51主机上
scp /tmp.import.sql 10.0.0.51:/tmp

11.导入import.sql文件
use world
source /tmp/import.sql

扩展需求3，将world库的所有表，迁到新库softeem里，不重启mysqld，如何操作
1.创建新库softeem
CREATE DATABASE `softeem` /*!40100 DEFAULT CHARACTER SET latin1 */;

2.使用concat函数拼接导入新建表表空间的sql文件
select concat("alter table ","softeem",".",table_name," import tablespace;") from  information_schema.tables where table_schema='world' into outfile '/tmp/importsof.sql';

3.使用concat函数拼接导入新建表表空间的sql文件
select concat("alter table ","softeem",".",table_name," discard tablespace;") from  information_schema.tables where table_schema='world' into outfile '/tmp/discardsof.sql';

4.在softeem库创建world库的表
use softeem;

show create table world.country;
CREATE TABLE `country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint(6) DEFAULT NULL,
  `Population` int(11) NOT NULL DEFAULT '0',
  `LifeExpectancy` float(3,1) DEFAULT NULL,
  `GNP` float(10,2) DEFAULT NULL,
  `GNPOld` float(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

show create table world.city;
CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;


show create table world.countrylanguage;
CREATE TABLE `countrylanguage` (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
  `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

5.临时取消外键约束
set FOREIGN_KEY_CHECKS=0;

6.导入删除表空间的sql文件
source /tmp/discardsof.sql

7.将world库的表文件放到softeem库中
# cp -r /data/mysql/data/world/* /data/mysql/data/softeem/
# chown -R mysql.mysql /data/mysql

8.导入创建表空间的sql文件
use softeem
source /tmp/importsof.sql

2.使用mysqldump+binlogs备份

mysqldump命令
===选项与参数
1、-A/--all-databases 所有库
2、-B/--databases bbs db1 db2 多个数据库
3、db1 数据库名
4、db1 t1 t2 db1数据库的表t1、t2
5、-F 备份的同时刷新binlog
6、-R 备份存储过程和函数数据（如果开发写了函数和存储过程，就备，没写就不备）
7、--triggers 备份触发器数据（现在都是开发写触发器）
8、-E/--events 备份事件调度器
9、-d 仅表结构
10、-t 仅数据
11、--master-data=1 备份文件中 change master语句是没有注释的，默认为1
用于已经制作好了主从，现在想扩展一个从库的时候使用
如此备份，扩展添加从库时导入备份文件后
便不需要再加mater_pos了
change matser to
master_host='10.0.0.111'
master_user='rep'
master_password=123
master_log_pos=120
master_log_file='master-bin.000001'
12、--master-data=2 备份文件中 change master语句是被注释的
13、--lock-all-tables 备份过程中所有表从头锁到尾，简单粗暴
14、--single-transaction： 快照备份 （搭配--master-data可以做到热备）
15、--lock-tables：如果是备份所有库，那么备份到某个库时只锁某个库，其他库可写，而--lock-alltables是
#==========完整语句
mysqldump -uroot -p123 -A -E -R --triggers --master-data=2 --single-transaction
> /backup/full.sql
#====文件太大时可以压缩 gzip ，但是gzip不属于mysql独有的命令，可以利用管道
mysqldump -uroot -p123 -A -E -R --triggers --master-data=2 --single-transaction
| gzip > /tmp/full$(date +%F).sql.gz
#====导出时压缩了，导入时需要解压，可以使用zcat命令，很方便
zcat /backup/full$(date +%F).sql.gz | mysql -uroot -p123

案例练习
环境准备
周一做全备 000009
mysqldump -AER --triggers --master-data=2 --single-transaction | gzip > /tmp/full$(date +%F).sql.gz
create database inc1;
use inc1
create table t1(id int);
insert into t1 values(1);
commit;

周二增备 000010
flush logs;
create database inc2;
use inc2
create table t1(id int);
insert into t1 values(1),(2);
commit;

周三增备 000011
flush logs;
create database inc3;
use inc3
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;

周四增备 000012
flush logs;
create database inc4;
use inc4
create table t1(id int);
insert into t1 values(1),(2),(3),(4);
#误操作
delete from inc1.t1 where id=1;
commit;

周五增备 000013
flush logs;
create database inc5;
use inc5
create table t1(id int);
insert into t1 values(1),(2),(3),(4),(5);
commit;

周五晚上做一个误操作，删了很多数据，要求通过mysqldump的全备+binlog的增备恢复故障
delete from inc3.t1 where id=3;
commit;
drop table inc4.t1;
drop database inc2;


恢复思路：
周一的全备+周一增量+周二增量+周三的增量+周四的增量（误操作前的binlog）+周五增量+到故障前的binlog

1.获取所有的sql文件
周一的全备文件：
/tmp/full2024-02-29.sql.gz

周一新增：
mysqlbinlog /data/mysql/binlog/mysql_bin.000009 > /tmp/inc1.sql

周二新增：
mysqlbinlog /data/mysql/binlog/mysql_bin.000010 > /tmp/inc2.sql

周三新增：
mysqlbinlog /data/mysql/binlog/mysql_bin.000011 > /tmp/inc3.sql

周四新增：
mysqlbinlog --stop-position=808 /data/mysql/binlog/mysql_bin.000012 > /tmp/inc4.sql

周五新增：
mysqlbinlog --stop-position=813 /data/mysql/binlog/mysql_bin.000013 > /tmp/inc5.sql
	
# 周五新增需要需要解码周五的binlog，判断误操作的position
mysqlbinlog --base64-output=decode-rows -v /data/mysql/binlog/mysql_bin.000012
# 周五新增需要需要解码周五的binlog，判断误操作的position
mysqlbinlog --base64-output=decode-rows -v /data/mysql/binlog/mysql_bin.000013

2.将这些文件传到测试库，尝试恢复
scp /tmp/full2024-03-01.sql.gz /tmp/inc* 10.0.0.52:/tmp/

3.在测试库db02上尝试恢复
zcat /tmp/full2024-02-29.sql.gz | mysql
mysql < /tmp/inc1.sql 
mysql < /tmp/inc2.sql 
mysql < /tmp/inc3.sql 
mysql < /tmp/inc4.sql 
mysql < /tmp/inc5.sql

需求1：将db01的数据库全量备份，导入到db02
#1.db01上全备
mysqldump -A >/tmp/all.sql

#2.将全备文件拷贝到db02
scp /tmp/all.sql 10.0.0.52:/tmp

#3.db02导入全备数据
mysql < /tmp/all.sql

需求2：将db01的world库备份，并导入到db02
备份指定库的方法一：（不建议使用）
#1.db01备份world库
mysqldump world > /tmp/world.sql

#2.将备份文件拷贝到db02
scp /tmp/world.sql 10.0.0.52:/tmp

#3.db02导入
先创建world库，在使用命令导入
mysql world </tmp/world.sql

备份指定库的方法二：
#1.db01备份world库
mysqldump -B world > /tmp/world.sql

#2.将备份文件拷贝到db02
scp /tmp/world.sql 10.0.0.52:/tmp

#3.db02导入
mysql </tmp/world.sql

需求3:备份world库的city表
mysqldump world city > /tmp/world_city.sql

恢复时，要指定库名
mysql world < /tmp/world_city.sql


最终的全备的命令如下：
mysqldump -AER --triggers --master-data=2 --single-transaction | gzip > /tmp/full$(date +%F).sql.gz

恢复命令如下：
zcat /tmp/full$(date +%F).sql.gz | mysql

3.xtrabackup工具的使用

安装

1.下载二进制包
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.24/binary/tarball/percona-xtrabackup-2.4.24-Linux-x86_64.glibc2.12.tar.gz

2.解压
mkdir -p /app 
tar xf percona-xtrabackup-2.4.24-Linux-x86_64.glibc2.12.tar.gz -C /app/

3.路径优化
echo "export PATH=$PATH:/app/percona-xtrabackup-2.4.24-Linux-x86_64.glibc2.12/bin/" >> /etc/profile

# 刷新环境变量（不用重启服务器）
source /etc/profile

常用选项

--no-timestamp  去掉时间戳
--incremental 开启增量备份功能
--incremental-basedir 上一次备份的路径
--apply-log 通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
--redo-only 通过重做日志使数据文件处于一致性状态
--incremental-dir 存增量文件的目录
--use-memory=3G 越大效率越高，但是不要超过内存大小，超过则报错

案例练习

环境准备
db01 主库 10.0.0.51
db04 备份库 10.0.0.54

周一做全备 
mkdir -p /backup 
innobackupex -S/tmp/mysql.sock --no-timestamp /backup/full

create database inc1;
use inc1
create table t1(id int);
insert into t1 values(1);
commit;

周一增备：
innobackupex -S/tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1

周二的操作：
create database inc2;
use inc2
create table t1(id int);
insert into t1 values(1),(2);
commit;

周二的增备：
innobackupex -S/tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2


周三的操作
create database inc3;
use inc3
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;

周三的增备：
innobackupex -S/tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/inc2 /backup/inc3

周四的操作
create database inc4;
use inc4
create table t1(id int);
insert into t1 values(1),(2),(3),(4);
commit;

周四的增备
innobackupex -S/tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/inc3 /backup/inc4

周五的操作
create database inc5;
use inc5
create table t1(id int);
insert into t1 values(1),(2),(3),(4),(5);
commit;

周五的增备
innobackupex -S/tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/inc4 /backup/inc5

周五晚上做一个误操作，删了很多数据，要求通过mysqldump的全备+binlog的增备恢复故障
delete from inc3.t1 where id=3;
commit;
drop table inc4.t1;
drop database inc2;

恢复思路
仅需要将周一全备+后续每天的增备组合即可
实现过程
1.校验全备
innobackupex --apply-log --redo-only  /backup/full

2.将inc1合并到full
innobackupex --apply-log --redo-only  --incremental-dir=/backup/inc1 /backup/full 

3.将inc2合并到full
innobackupex --apply-log --redo-only  --incremental-dir=/backup/inc2 /backup/full

4.将inc3合并到full
innobackupex --apply-log --redo-only  --incremental-dir=/backup/inc3 /backup/full

5.将inc4合并到full
innobackupex --apply-log --redo-only  --incremental-dir=/backup/inc4 /backup/full

6.将inc5合并到full
innobackupex --apply-log --incremental-dir=/backup/inc5 /backup/full

7.如果还有binlog记录一些事务，需要找到binlog，并导出
略

8.将合并后的目录拷贝到数据库服务器的数据目录中
# scp -r /backup/full/* 10.0.0.52:/data/mysql/data/

9.关闭54的mysqld服务
systemctl stop mysqld

9.恢复数据的数据库的操作
#1.修改归属
chown -R mysql.mysql /data/mysql

#2.重启mysqld，加载数据
systemctl restart mysqld

稗子酒

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
Mysql备份与恢复

1.为何要备份在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种.硬件故障软件故障自然灾害黑客攻击误操作 (占比最大)须知在生产环境中，服务器的硬件坏了可以维修或者换新，软件崩溃可以修复或重新安装, 但是如果数据没了那可就毁了，生产环境中最重要的应该就是数据了。所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据。2.备份什么我们要备份什么?一般情况下, 我们需要备份的数据分为以下几种数据。
复制链接

扫一扫