mysql的备份与恢复
一、备份
1.数据备份的必要性
备份的主要目的是灾难恢复
在生产环境中,数据的安全性至关重要
任何数据的丢失都可能产生严重的后果
造成数据丢失的原因
程序错误
人为操作错误
运算错误
磁盘故障
灾难(如火灾、地震)和盗窃
2.数据备份分类
物理备份
对数据库操作系统的物理文件(如数据文件、日志文件等)的备份。
- 冷备份(脱机备份):是在关闭数据库的时候进行的;
- 热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件;
- 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作。
逻辑备份
对数据库的表或者对象进行备份。
3.数据库备份策略
完全备份:整个数据集,每次都备份完整的数据库。
差异备份:仅备份最近一次完全备份后变化的数据,备份较慢,还原简单
增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快,还原复杂
4.常用的备份方法和工具
物理冷备备份时数据库处于关闭状态,直接打包数据库文件
备份速度快,恢复时也是最简单的。
专用备份工具mysqldump或mysqlhotcopymysqldump常用的逻辑备份工具(逻辑热备)
备份速度较慢(如果数据库超过100G,一般不建议使用mysqldump)
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
archive
启用二进制日志进行增量备份
进行增量备份,需要刷新二进制日志
第三方工具备份
免费的MySQL热备份软件Percona;XtraBackup
XtraBackup(物理热备)
https://mp.weixin.qq.com/s/d3spGhku154dOgWSct-3Ww
5.数据库上云迁移
冷迁移
热迁移
二、MySQL完全备份
1.简介
优点
备份与恢复操作简单方便
缺点
数据存在大量的重复占用大量的备份空间
备份与恢复时间长
- 完全备份是对整个数据库、数据库结构和文件结构的备份
- 保存的是备份完成时刻的数据库
- 是差异备份与增量备份的基础
2.物理冷备份与恢复
- 关闭MySQL数据库
- 使用tar命令直接打包数据库文件夹
- 直接替换现有MySQL目录即可
物理冷备份
#先关闭数据库 systemctl stop mariadb #建立备份文件存放目录 mkdir /opt/bak #压缩备份
cd /var/lib tar zcvf /opt/bak/mysql_full_$(date +%F).tar.gz ./mysql $(date -d "-1 day" +%Y%m%d)
解压恢复
主机1:192.168.100.146
主机2:192.168.100.150
备份到另一台主机
#先关闭数据库 systemctl stop mariadb scp 192.168.100.146:/opt/bak/* /opt #将主机1的备份包远程传输到主机2
#先备份数据,防止恢复失败 mv /var/lib/mysql/ /var/lib/mysql.bak
tar xf mysql_full_2024-07-03.tar.gzcp -a /../data /usr/local/mysql/data #不用-a 权限不足,无法启动
#如果是从主机2传输回主机1,需要将属主和属组改为mysql
systemctl start mysqld mysql -u root -p #登录到数据库
3.mysqldump备份与恢复
含义 | |
---|---|
-A, --all-databases | #备份所有数据库,含create database |
-B, --databases db_name… | #指定备份的数据库,包括create database语句 |
-E, --events: | #备份相关的所有event scheduler |
-R, --routines: | #备份所有存储过程和自定义函数 |
--triggers: | #备份表相关触发器,默认启用,用--skip-triggers,不备份触发器 |
--default-character-set=utf8 | #指定字符集 |
--master-data[=#]: | #此选项须启用二进制日志 #1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定#,默认为1,适合于主从复 制多机使用 #2:记录为被注释的#CHANGE MASTER TO语句,适合于单机使用,适用于备份还原 #此选项会自动关闭--lock-tables功能,自动打开-x | --lock-all-tables功能(除非开启-- single-transaction) |
-F, --flush-logs | #备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志文件, 配合-A 或 -B 选项时,会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新,可通过和--single transaction或-x,--master-data 一起使用实现,此时只刷新一次二进制日志 |
--compact | #去掉注释,适合调试,节约备份占用的空间,生产不使用 |
-d, --no-data | #只备份表结构,不备份数据,即只备份create table |
-t, --no-create-info | #只备份数据,不备份表结构,即不备份create table |
-n,--no-create-db | #不备份create database,可被-A或-B覆盖 |
--flush-privileges | #备份mysql或相关时需要使用 |
-f, --force | #忽略SQL错误,继续执行 |
--hex-blob | #使用十六进制符号转储二进制列,当有包括BINARY, VARBINARY, BLOB,BIT的数据类型的列时使用,避免乱码 |
-q, --quick |
1)完全备份一个或多个完整的库(包括其中所有的表)
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql
#导出的就是数据库脚本文件
如果不加 --databases ,只备份库中的表,不会备份库
#举个例子 #单个数据库 mysqldump -u root -p --databases hx > /opt/bak/hx.sql #多个数据库 mysqldump -u root -p --databases db1 db2 > /opt/bak/db12.sql
2)完全备份 MySQL 服务器中所有的库
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
#举个例子 mysqldump -u root -p --all-databases > /opt/bak/all.sql
cat /opt/bak/all.sql | grep "^CREATE DATABASE"
3)完全备份指定库中的部分表
mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
举个例子
#备份byyb的student表 mysqldump -u root -p [-d] db2 student > /opt/bak/db2-student.sql #使用“-d”选项,说明只保存数据库的表结构 #不使用“-d”选项,说明表数据也进行备份
4)查看备份文件
cat xx.sql|grep -v "^--"| grep -v "^/" | grep -v "^$" egrp -v "^--|^/\*|^$" #或运行符
#举个例子 cat db2-student.sql|grep -v "^--" | grep -v "^/" | grep -v "^$"
三、MySQL 完全恢复
1.恢复数据库
当备份文件中只包含表的备份,而不包括创建库的语句时,必须指定库名,且目标库必须存在。
在生产环境中,可以使用Shell脚本自动实现定时备份
通过source命令恢复
先使用mysqldump工具,生成备份sql脚本;
然后登录到MySQL数据库;
再执行source 备份sql脚本的路径
MySQL[(none)]> source /backup/all-data.sql
举个例子
#先备份目标数据库,以hx为例
mysqldump -uroot -p123 --databases hx > /opt/bak/hx.sql
#模拟数据库被删除 mysql -uroot -p123 -e "drop database hx;" mysql -uroot -p123 -e "show databases;"
source命令执行sql脚本 #先登录到数据库 mysql -u root -p #执行脚本 source /opt/bak/byyb.sql;
通过mysql命令
输入重定向
#先模拟数据库删除,这里不再赘述 #恢复数据库 mysql -u root -p123 < /opt/bak/hx.sql mysql -u root -p123 -e 'show databases;'
管道符
cat byyb.sql | mysql -uroot -p123123
2.恢复数据表
当备份文件中只包含表的备份,而不包含创建的库的语句时,执行导入操作时必须指定库名,且目标库必须存在。
#备份byyb的student表
mysqldump -u root -p123 [-d] db2 student > /opt/bak/db2-student.sql
#模拟删除表 mysql -u root -p -e 'drop table db2.student;' mysql -u root -p -e 'show tables from db2;'
#恢复表数据 mysql -uroot -p123 db2 < /opt/bak/db2-student.sql #查看表数据,验证是否成功恢复 mysql -uroot -p123 -e "select * from db2.student;"
四、MySQL增量备份与恢复
1.MySQL增量备份
开启二进制日志功能
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 /var/lib/mysql-bin.*
通过刷新二进制日志文件间接实现增量备份
mysqladmin -uroot -p[密码] flush-logs
#flush-logs可简写为-F
查看二进制日志文件的内容
mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制文件(绝对路径) #--base64-output=decode-rows:使用64位编码机制去解码并按行读取 #-v:显示详细内容
#举个例子 cp /usr/local/mysql/data/mysql-bin.000001 /opt mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001
2.MySQL增量恢复
MySQL没有提供直接的增量备份方法,可通过MySQL提供的二进制日志间接实现增量备份。
一般恢复
1)模拟丢失更改的数据的恢复步骤
use hx; delete from student where id=3; delete from student where id=4; mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
2)模拟丢失所有数据的恢复步骤
use hx; drop table info1; mysql -u root -p kgc < /opt/kgc_info1_2020-11-22.sql mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
断点恢复
#查看二进制文件 mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
1)基于位置恢复
mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 二进制日志文件(绝对路径) | mysql -uroot -p[密码]
#仅恢复到操作 ID 为“623”之前的数据,即不恢复“user4”的数据 mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p #仅恢复“user4”的数据,跳过“user3”的数据恢复 mysqlbinlog --no-defaults --start-position='623' /opt/mysql-bin.000002 | mysql -uroot -p
2)基于时间点恢复
mysqlbinlog --no-defaults --start-datetime='时间点' --stop-datetime='时间点' 二进制日志文件(绝对路径) | mysql -uroot -p[密码]
#仅恢复到 16∶41∶24 之前的数据,即不恢复“user4”的数据 mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p #仅恢复“user4”的数据,跳过“user3”的数据恢复 mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p
如果恢复某条SQL语句之前的所有数据,就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据,就从这个语句的位置节点或者时间点start
3.备份与恢复配置实例(完全+增量)
为了测试备份数据是否可用,模拟数据丢失故障,进行数据还原。
在数据库表中,分三次录入学生考试成绩。
首次录入成绩后,做该表的完全备份。
后两次成绩的录入之后,分别作增量备份。
模拟数据丢失,并使用增量备份分别基于位置和时间点恢复一班所有学生成绩与二班陈铭与付杰的成绩。
1)录入第一次数据,并进行完全备份
mysql -u root -p create database hx; use hx; create table student (name varchar(10),class varchar(10),id int,chinese int,math int,english int); insert into student values ('aa','1',20170822,110,105,92); insert into student values ('bb','1',20170820,95,115,110); insert into student values ('cc','1',20170818,95,103,108); #其余数据添加格式类似
通过mysqldump工具完全备份
mysqldump -uroot -p123 hx student > /opt/bak/hx-student.sql;
2)录入第二次数据,进行增量备份
vim /etc/my.cnf [mysqld] log-bin=/data/mysql-bin binlog_format = MIXED #可选,指定二进制日志(binlog)的记录格式为 MIXED server-id = 1
mkdir /data; #创建二进制日志目录
chown mysql.mysql /data #修改属主属组
systemctl restart mariadb #重启服务
insert into student values ('dd','2',20170824,92,98,105); insert into student values ('ee','2',20170826,111,107,96);
drop table student;
#删除该表
insert into test values (4,test4,ddd,24); #在test表中增加数据
mysqlbinlog /data/mysql-bin.000001 #查看二进制日志文件
#备份 完全备份后的二进制日志 mysqlbinlog --start-position=1218 /data/mysql-bin.000001 > /opt/inc.sql
#找到 删除的语句
grep -i "^delete" /opt/inc.sql
#删除 删表的那一行 sed -i.bak '/^delete/d' /opt/inc.sql
mysql -u root -p 123 set sql_log_bin=0; #关闭二进制,恢复完全备份和二进制日志备份 source /opt/bak/hx-student.sql; source /opt/inc.sql;