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`
--