当前位置： 首页 > news >正文

石家庄网站关键词,牡丹江网站建设公司,电子产品外贸交易平台,WordPress朗读概述 整理Mysql数据库备份脚本#xff0c;用在生产环境数据库定时备份。 参考 链接: 安全管理MySQL凭证#xff1a;使用mysql_config_editor设置login-path 创建MySQL凭证 创建凭证 mysql_config_editor设置凭证 ./mysql_config_editor set –login-pathlocal –hostl…概述 整理Mysql数据库备份脚本用在生产环境数据库定时备份。 参考 链接: 安全管理MySQL凭证使用mysql_config_editor设置login-path 创建MySQL凭证 创建凭证 mysql_config_editor设置凭证 ./mysql_config_editor set –login-pathlocal –hostlocalhost –socket/data/mysqldb/tmp/mysql.sock –userroot –password输入mysql 用户密码 Enter password: 查看创建的凭证 ./mysql_config_editor print –all[local] user root password ***** host localhost socket /public/mysqldb/tmp/mysql.sock验证凭证 ./mysql –login-pathlocalWelcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.4.1 MySQL Community Server - GPLCopyright © 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.Type help; or \h for help. Type \c to clear the current input statement.mysql 创建备份脚本 脚本工作流程 初始化环境变量定义脚本参数根据参数备份每个数据库并打包压缩备份文件根据参数删除超出保存期限的数据库备份文件根据参数删除超出保存期限的脚本执行日志 脚本内容 创建目录和脚本文件 创建目录 mkdir -p /public/script/logs cd /public/script创建脚本 vim mysql_backup.sh 输入以下内容 #!/bin/bash# 需要调试时取消注释前台运行脚本 #set -x ################################################################################################################## #

备份mysql数据库脚本

1. 使用 mysql_config_editor定义mysql的user和password

2. 定义需要备份的数据库和保留时间

3. 备份定义的数据库并打包压缩

4. 备份成功后删除旧备份文件和脚本日志

5. crontab -e 定时样例

10 0 * * * /public/script/mysqlbackup.sh /public/script/logs/mysql$(date \%Y-\%m-\%d).log 21

# ################################################################################################################### 定义环境变量请确认使用路径正确 PATH/usr/java/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/mysql/lib export PATH# 定义备份数据库名字的列表多个数据库用空格分开。 DBSdb1 db2 db3# 定义脚本路径和备份存储路径 SCRIPT_HOME/public/script BAK_HOME/public/mysqlbackup # 检查是否存在日志目录 if [ ! -d \(SCRIPT_HOME/logs ]; thenmkdir -p \)SCRIPT_HOME/logs fi# 定义脚本变量 CURRENT_DATE\((date %Y-%m-%d) RETAIN_DAY3 #保留最近几天的备份最长不超过7天 LOG_RETAIN_DAY14 # 脚本执行日志保留天数 LOG_FILE\)SCRIPTHOME/logs/mysql\({CURRENT_DATE}.log# 开始执行 DATE_TIME\)(date %Y-%m-%d %H:%M:%S) echo -e \n \(DATE_TIME: #### Begin backup \)DBS ### \(LOG_FILE## 判断备份目录是否存在不存时新建目录 [ ! -d \)BAK_HOME ] mkdir -p \(BAK_HOME cd \)BAK_HOME #转到备份目录 ## 循环备份DBS中定义的数据库 for DBNAME in \(DBS; doDATE_TIME\)(date %Y-%m-%d %H:%M:%S)echo -e \n \(DATE_TIME: #### Start backup \)DBNAME ### \(LOG_FILEBACKUP_NAMEmysql_\){DBNAME}_\({CURRENT_DATE}## 备份单个数据库使用--login-path 方式认证mysqldump --login-pathlocal --single-transaction \)DBNAME \(BAK_HOME/\){BACKUP_NAME}.sqlif [ \(? -ne 0 ];then #\)? 代表上一个shell命令的执行的返回值。0表示执行成功。其他表示错误。并将结果写入到日志中。 DATE_TIME\((date %Y-%m-%d %H:%M:%S)echo -e \n \)DATE_TIME: \({CURRENT_DATE} \)DBNAME mysqldump failed\(LOG_FILEbreakelseDATE_TIME\)(date %Y-%m-%d %H:%M:%S)echo -e \n \(DATE_TIME: \){CURRENT_DATE} \(DBNAME mysqldump sucess\)LOG_FILE fi ## 打包压缩备份文件如果库大压缩需要谨慎处理tar -zcf \({BACKUP_NAME}.tar.gz \){BACKUP_NAME}.sqlif [ \(? -eq 0 ];thenrm -f \){BACKUP_NAME}.sqlDATE_TIME\((date %Y-%m-%d %H:%M:%S)echo \)DATE_TIME: Compress \({BACKUP_NAME}.tar.gz sucess\)LOG_FILEelseDATE_TIME\((date %Y-%m-%d %H:%M:%S)echo \)DATE_TIME: Compress \({BACKUP_NAME}.tar.gz failed\)LOG_FILEbreakfi## 如果存在上一步打包的压缩文件。查找保留天数-7天之间的旧备份压缩包并删除if [ -f \({BACKUP_NAME}.tar.gz ];thenfor ((i\){RETAIN_DAY}; i7; i)); doRETAIN_DATE\((date -d -\)i day %Y-%m-%d)DELETE_BACKUPNAMEmysql\({DBNAME}_\){RETAIN_DATE}if [ -f \({DELETE_BACKUP_NAME}.tar.gz ]; thenrm -f \){DELETE_BACKUP_NAME}.tar.gzif [ \(? -eq 0 ];thenDATE_TIME\)(date %Y-%m-%d %H:%M:%S)echo \(DATE_TIME: Delete \){DELETE_BACKUP_NAME}.tar.gz sucess\(LOG_FILEelseDATE_TIME\)(date %Y-%m-%d %H:%M:%S)echo \(DATE_TIME: Delete \){DELETE_BACKUP_NAME}.tar.gz failed\(LOG_FILEfifidone;fiDATE_TIME\)(date %Y-%m-%d %H:%M:%S)echo -e \n \(DATE_TIME: #### Finish backup \)DBNAME ### \(LOG_FILEdone;## 删除脚本执行历史日志 find \)SCRIPT_HOME/logs -maxdepth 1 -mtime \(LOG_RETAIN_DAY -name mysql_*.log -execdir rm -rf {} \;DATE_TIME\)(date %Y-%m-%d %H:%M:%S) echo -e \n \(DATE_TIME: #### End backup ### \)LOG_FILE脚本文件添加执行权限 chmod x mysql_backup.sh 添加定时任务 编辑crontab定时任务 crontab -e添加以下内容 10 0 * * * /public/script/mysqlbackup.sh /public/script/logs/mysql\((date \%Y-\%m-\%d).log 21 手动执行验证 注意手动执行时不需要 “\” 禁止转义 /public/script/mysql_backup.sh /public/script/logs/mysql_\)(date %Y-%m-%d).log 21 如果脚本执行失败可以取消脚本中 #set -x 的注释在前台执行脚本进行调试 查看执行结果 [rootlocalhost logs]# pwd /public/script/logs [rootlocalhost logs]# tail -n200 mysql_2024-08-01.log 2024-08-01 11:12:26: #### Begin backup db1 db2 db3 ###2024-08-01 11:12:26: #### Start backup db1 ###2024-08-01 11:12:26: 2024-08-01 db1 mysqldump sucess2024-08-01 11:12:26: Compress mysql_db1_2024-08-01.tar.gz sucess2024-08-01 11:12:26: #### Finish backup db1 ###2024-08-01 11:12:26: #### Start backup db2 ###2024-08-01 11:13:25: 2024-08-01 db2 mysqldump sucess2024-08-01 11:14:11: Compress mysql_db2_2024-08-01.tar.gz sucess2024-08-01 11:14:11: #### Finish backup db2 ###2024-08-01 11:22:28: #### Start backup db3 ###2024-08-01 11:22:29: 2024-08-01 db3 mysqldump sucess2024-08-01 11:22:29: Compress mysql_db3_2024-08-01.tar.gz sucess2024-08-01 11:22:29: Delete mysql_db3_2024-07-27.tar.gz sucess2024-08-01 11:22:29: Delete mysql_db3_2024-07-26.tar.gz sucess2024-08-01 11:22:29: #### Finish backup db3 ###2024-08-01 11:22:29: #### End backup ###