推荐

mongodb自动备份，并zabbix检测备份文件是否正常

CentOS 7.9服务器，一分钟部署完mysql8.0

Ubuntu22部署MySQL8.0二进制安装

快速部署mysql主从，zabbix监控检查主从是否正常

Mycat2版部署mysql读写分离

Mycat1.6版部署mysql读写分离

一/编写脚本

注：修改host，port，user_db，passwd_db，mysql_db1的内容就行

[root@aaa_192_168_1_10 mysql_backups]# pwd
/data/mysql_backups
[root@aa_192_168_1_10 mysql_backups]# chmod +x mysql_bachups.sh
[root@aa_192_168_1_10 mysql_backups]# crontab -e
17 4 * * * sh /data/mysql_backups/mysql_bachups.sh  > /dev/null 2
[root@HK_192_168_5_110 mysql_backups]# service crond restart
[root@aa_192_168_1_10 mysql_backups]#cat mysql_bachups.sh
#!/bin/bash
#############################################################################
#需要的权限GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, TRIGGER ,Event
host='localhost'
port='3306'
user_db='user'
passwd_db='passwd'
#mongodump文件路径
sourcepath='/usr/local/mysql/bin/'
#当前时间
nowtime=$(date "+%Y%m%d")
#前一天时间
yesterday=$(date -d "1 day ago" +%Y-%m-%d)
#前3day时间
backtime=$(date -d '-3 days' "+%Y%m%d")
#日志时间年月
nowtime_log=$(date "+%Y%m")
#库
#mysql_db1="请到start函数中的for 语句中写入库名"
#压缩包备份路径
backupdir=/data/mysql_backups
#备份的文件名
mydb_name=mydb


#### 自动检查/创建备份路径 ####
if [ ! -d /data/mysql_backups/mysql_bak ];then
    mkdir -p /data/mysql_backups/mysql_bak
fi

if [ ! -d /data/mysql_backups/log ];then
    mkdir -p /data/mysql_backups/log
fi

if [ ! -d /data/mysql_backups/mysql_bak/${nowtime} ];then
    mkdir -p /data/mysql_backups/mysql_bak/${nowtime}
fi
#备份路径
targetpath="/data/mysql_backups/mysql_bak"
#日志
logpath="/data/mysql_backups/log"

#### 备份函数 ####
start(){
#库,这里写库
for my_db in aaa bbb ccc  ddd
do
    $sourcepath/mysqldump -h $host -u $user_db -p"$passwd_db" -P $port -E -R --databases $my_db |gzip  > ${targetpath}/${nowtime}/$my_db.sql.gz
done
}
#解压gzip -dc file.gz > file.txt    tar -xzvf archive.tar.gz

#### 判断备份 ####
execute(){
echo "=========================$(date)  备份所有mysql启动  ${nowtime}========="  >> ${logpath}/${nowtime_log}.log
#为什么要这样做，zabbix监控检查文件是否存在vfs.file.size[/data/mysql_backups/test.tar.gz]
mv $backupdir/${mydb_name}.tar.gz  $backupdir/${mydb_name}_${yesterday}.tar.gz
#mkdir ${targetpath}/${nowtime}
start
if [ $? -eq 0 ];then
    cd ${targetpath}
    tar -czvf $backupdir/${mydb_name}.tar.gz ${nowtime}
    echo " mysql备份成功 !"  >> ${logpath}/${nowtime_log}.log
    find $backupdir -mtime +7 -name "*.tar.gz" -exec rm -rf {} \;
else
    echo " mysql备份失败失败失败失败 "   >> ${logpath}/${nowtime_log}.log
fi
}
execute


#### 删除3day前备份 ####
find $targetpath -mtime +3 -name "20*" -exec rm -rf {} \;
find $logpath -mtime +180 -name "*.log" -exec rm -rf {} \;

echo "========================= $(date)  备份mysql结束  ${nowtime}========="   >> ${logpath}/${nowtime_log}.log

二/添加mysql用户

CREATE USER `user`@`localhost` IDENTIFIED BY 'passwd';
GRANT Event, Lock Tables, Reload, Replication Client, Select, Show View, Trigger ON *.* TO `user`@`localhost`;

三/添加zabbix监控文件和文件大小，触发器

没了就这样了。