MySQL 备份和恢复
备份类型 (备份后要做还原测试,并写成规范的技术文档)
完全备份:整个数据集
部分备份:只备份数据子集,如部分库或表
完全备份,增量备份,差异备份
增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快, 还原复杂
差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单
增量和差异备份的基础和前提是完全备份
注意:二进制日志文件不应该与数据文件放在同一磁盘
冷,温,热备份 (MyISAM:温备,不支持热备 InnoDB:都支持)版本一致,建议不要跨平台
冷备:读,写操作均不可进行,数据库停止服务
温备:读操作可执行;但写操作不可执行
热备:读,写操作均可执行
物理和逻辑备份
物理备份:直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快
逻辑备份:从数据库中"导出"数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可 能丢失精度
备份工具 (主要使用mysqldump xtrabackup为增强版-了解即可)
cp, tar等复制归档工具:物理备份工具,适用所有存储引擎;只支持冷备;完全和部分备份
LVM的快照:先加读锁,做快照后解锁,几乎热备;借助文件系统工具进行备份
mysqldump:逻辑备份工具,适用所有存储引擎,对MyISAM存储引擎进行温备;支持完全或部分备份;对InnoDB存储引擎支持热备,结合binlog的增量备份
xtrabackup:由Percona提供支持对InnoDB做热备(物理备份)的工具,支持完全备份,增量备份
MariaDB Backup: 从MariaDB 10.1.26开始集成,基于Percona XtraBackup 2.3.8实现
mysqlbackup:热备份, MySQL Enterprise Edition 组件
mysqlhotcopy:PERL 语言实现,几乎冷备,仅适用于MyISAM存储引擎,使用LOCK TABLES, FLUSH TABLES和cp或scp来快速备份数据库
实战案例:数据库冷备份和还原(MySQL8.0)数据库需停止服务
备份过程(备份数据不要放在本机)
第一部主机
第一步:停止数据库服务 systemctl stop mysqld
第二步:备份文件加时间 tar zcf mysql-backup-`date +%F_%H-%M-%S`.tar.gz /data/mysql
第三步:备份到远程主机 scp mysql-backup-'date +%F_%H-%M-%S'.tar.gz 10.0.0.152: 输入对方密码
第二部主机
第一步:停止数据库服务 systemctl stop mysqld
第二步:清空现有的数据 rm /data/mysql*
第三步:解开收到的备份包 tar xf mysql-backup-2022-08-31_22-01-31.tar.gz -C /opt/
第四步:把文件移动到数据库该在的目录下 mv /opt/data/mysql* /data/mysql 确认文件属性
第五步:启动数据库服务 systemctl start mysqld
第六步:进入数据库查看还原的数据
确定拷贝过去的文件是否与本主机的文件是否一样可以对两边的文件做哈希运算,哈希运算的值一样确认文件一样
md5
数据库冷备份脚本(执行脚本前需要guk验证)
guk验证
ssh-keygen生成公钥
ssh-copy-id 10.0.0.152 输入密码
然后执行脚本
#!/bin/bash
DATAPATH=/var/lib/mysql #需要备份的数据库存放路径,这是默认路径,如有指定路径,请输入指定路径
TIME=`date +%F_%H-%M-%S` #打包文件加时间戳
BACKUP_SERVER=10.0.0.100 #备份远程服务器地址
LOCK_FIFE=/tmp/backup_mysql.lock #定义锁文件路径
lock() { #锁机制,判断文件是否存在
if [ -e ${LOCK_FIFE} ] ;then #判断文件是否存在
echo "另一个备份正在执行,退出" #有,退出
exit
else #没有,创建锁文件
touch ${LOCK_FIFE}
fi
}
unlock (){ #解锁
if [ -e ${LOCK_FIFE} ] ;then
rm -f ${LOCK_FIFE} #存在,删除
fi
}
backup_mysql(){
lock #加锁
systemctl stop mysqld &>/dev/null
tar zcf mysql-backup-${TIME}.tar.gz ${DATAPATH} &>/dev/null
scp mysql-backup-${TIME}.tar.gz ${BACKUP_SERVER}:/backup/ &>/dev/null
#远程/backup/目录下,没有要提前创建
unlock #解锁,防止同时执行多个备份
}
backup_mysql
计划任务执行脚本,可把显示的信息都丢到垃圾箱
计划任务
echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
创建计划任务
crontab -e
PATH=/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
* * * * * /root/backup_mysql.sh 一分钟执行一次脚本
crontab -l 查看计划任务列表
tail -f /var/log/cron 查看计划任务日志,观察计划任务是否执行
mysqldump 备份工具(都可以写脚本)
部分备份:只备份某个数据库
不加-B:不带着数据库本身信息备份
第一种:mysqldump -uroot -p123456 hellodb | gzip > /backup/mysql.sql.gz 部分备份 不常用
还原前先解包 gzip -d mysql.sql.gz
还原:需要进入mysql重新创建一个新的同名数据库再还原 mysql hellodb /backup/mysql-B.sql 这种部分是带着数据库本身信息
还原 mysql -uroot -p123456 /backup/all.sql 全备份 只备份mysql和用户数据库
停止数据库服务:systemctl stop mysqld
删除mysql数据库:rm -rf /data/mysql/* 进入看是否删除干净
初始化mysql: mysqld --initialize-insecure --user=mysql --datadir=/data/mysql
查看是否初始化:ll /data/mysql
启动mysql服务:systemctl start mysqld
还原:mysql -uroot -p123456
mysqldump 备份还原实战案例
分库备份并压缩(排除mysql数据库和用户数据库之外的数据库进行备份)
for db in `mysql -uroot -p123456 -e 'show databases' |grep -Ewv '^(Database|information_schema|performance_schema|sys)$'`;do mysqldump -B $db | gzip > /backup/$db.sql.gz;done
分库备份实战脚本,可用于计划任务
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
PASS=123456
[ -d "$DIR" ] || mkdir $DIR
for db in `mysql -uroot -p "$PASS" -e 'showw databases' |grep -Ewv "^Database|.*schema$"`;do
mysqldump -F --single-transaction --master-data=2 --default-charactre-set=utf8 -q -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz
done
实战案例:利用二进制日志,还原数据库最新状态
--master-data=1 #所备份的数据之前加一条记录为CHANGE MASTER TO语句,适用于主从复制多机使用
--master-data=2 #纪录被注释的#CHANGE MASTER TO语句,适用于单机使用,适用于备份还原
记录备份的二进制位置
-F #生成新的二进制文件
--single-transaction #以事务方式
--flush-privileges #刷新权限
--default-character-set=utf8md4 #指定字符集
--hex-blob #使用十六进制符号转储二进制列
恢复误删除的表(完全备份后,在students表中增加信息后删除表,利用二进制日志恢复)
二进制日志独立存放
[mysqld]
log-bin=/data/mysql/mysql-bin
1、完全备份,并记录备份的二进制位置
mysqldump -uroot -p123456 -A -F --single-transaction --master-data=2 --flush-privileges --default-character-set=utf8md4 --hex-blob > /backup/mysql_all-`date +%F`.sql
2、在学生表中添加信息
insert students (name,age,gender)value('mage',20,'M');
删除学生表
drop table students;
在老师表中添加信息
insert teachers (name,age)values('a',30);
3、还原数据库:还原数据库的过程也会被记录到二进制日志文件中,所以临时关闭二进制日志
在mysql中临时关闭二进制日志:set sql_log_bin=off;
进入mysql还原完全备份:
source /backup/mysql_all-`date +%F`.sql;
4、找到二进制日志位置,利用mysqlbinlog把二进制文件导出来,找到删表的操作,把它删除,进行还原
show master logs; 其最下面就是最新的二进制日志文件
less /backup/mysql_all-`date +%F`.sql也可找到最新的二进制日志文件
找到文件导出来
mysqlbinlog /data/mysql/binlog.000017 > /backup/inc.sql
打开二进制日志文件找到删除表的那个命令
vim /backup/inc.sql 找到删除的那条命令
sed -i '/^dorp table/d' /backup/inc.sql
删除后把文件导入mysql
source /backup/inc.sql
开启二进制日志
set sql_log_bin=1;
安装iotop 可查看各服务io情况。
reset master;清空所有二进制
服务器租用托管,机房租用托管,主机租用托管,https://www.e1idc.com