mysql自动备份脚本与恢复

mysql数据库备份脚本（全备）

#!/bin/bash

# 备份文件要保存的目录
basepath='/tsmbak/'

# 备份文件要保存的目录是否存在  mkdir -p 递归创建目录，即使上级目录不存在，会按目录层级自动创建目录
if [ ! -d "$basepath" ]; then
mkdir -p "$basepath"
fi

# mysql/bin路径   whereis mysql   
BIN_DIR="/usr/local/mysql/bin" 

# 数据库参数
DB_USER="root"
DB_PASS="123456"


# 备份数据库生成SQL文件   /bin/nice -n 19 优先级 19为最低    
/bin/nice -n 19 $BIN_DIR/mysqldump --opt --single-transaction --master-data=2 -u$DB_USER -p$DB_PASS --all-databases > $basepath$(date +%Y%m%d).sql

# 将生成的SQL文件压缩
/bin/nice -n 19 tar zPcf $basepath$(date +%Y%m%d).tar.gz $basepath$(date +%Y%m%d).sql

# 删除7天之前的备份数据
find $basepath -mtime +7 -name "*.tar.gz" -exec rm -rf {} ;

# 删除生成的SQL文件
rm -rf $basepath/*.sql

#备份成功以下操作  "$?" == 0 判断上一条语句是否成功执行
if [ "$?" == 0 ];then
echo “$(date +%Y%m%d%T)备份成功!” >> ${basepath}$(date +%Y%m%d).log
find $basepath -mtime +7 -name "*.log" -exec rm -rf {} ;
else
#备份失败则进行以下操作
echo “$(date +%Y%m%d%T)备份失败!” >> ${basepath}$(date +%Y%m%d).log
fi
done

参数说明：

-u  -p   -S   -h  -P     --set-gtid-purged=OFF -R   --triggers   -E 
 --max-allowed-packet=256M  -F 
本地备份:
mysqldump -uroot -p  -S /tmp/mysql.sock
远程备份:
mysqldump -uroot -p  -h 10.0.0.12 -P3306

# 补充:
# 1.常规备份是要加 --set-gtid-purged=OFF,解决备份时的警告  禁用了 session级别的 binlog

# 2.主备从用了gtid时,不需要加这个参数加了
#--set-gtid-purged=OFF时，<!--重点注意下面这两行-->
#SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
#SET @@SESSION.SQL_LOG_BIN= 0; 禁用了 session级别的 binlog 
#所以在我们做主备从用了gtid时，用mysqldump备份时不加--set-gtid-purged=OFF，否则恢复数据时
#主没有了binlog日志，同步则不会被同步。
#


-R            备份存储过程及函数
--triggers    备份触发器
-E            备份事件
-F             -F 在备份开始时,刷新一个新binlog日志

 show variables like '%gtid%';

binlog_gtid_simple_recovery	ON
enforce_gtid_consistency	OFF
gtid_executed_compression_period	1000
gtid_mode	OFF
gtid_next	AUTOMATIC
gtid_owned	
gtid_purged	
session_track_gtids	OFF

gtid_mode	OFF gtid默认未开启

--master-data[=#]

在备份导出的文件里追加二进制binlog文件的位置和名称
如果值等于1，就会添加一个CHANGE MASTER语句
如果值等于2，就会在CHAGE MASTER语句前添加注释（记录binlog位置）
这个参数会--lock-all-tables锁表，除非你指定了--single-transaction
这种情况下进行快照 备份

修改权限为用户可执行

#用户  属组 和其他  读、写、运行三项权限可以用数字表示，就是r=4，w=2，x=1   
chmod 744   /opt/backup/mysql_backup.sh


#添加定时任务 1小时执行一次
#分     小时     日      月      星期     命令
#0-59   0-23   1-31    1-12    0-6     command 
crontab -e   
0 */1 * * *  /opt/mysqlbackup/mysql-backup-all.sh 

#查看定时任务
crontab -l

数据恢复

#-z, gzip : 对归档文件使用 gzip 压缩
#-x, --extract, --get : 释放tar归档文件中文件及目录
#-p, --preserve-permissions : 保留原文件的访问权限
#-v, --verbose : 显示命令整个执行过程
#-x, --extract, --get : 释放tar归档文件中文件及目录

tar zxPvf xxx.tar.gz


 mysql -u root -p123456  < xxx.sql 


source命令也可导入

[root@instance-bg3s0btb tsmbak]# head -n 50  20221214.sql 
-- MySQL dump 10.13  Distrib 5.7.35, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version       5.7.35-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=866055;

--
-- Current Database: `mysql`
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=866055;全备结束位置

二进制日志截取日志恢复数据

https://blog.csdn.net/NBSPNBSP1/article/details/128237782