Centos下 mysql 定时备份设置

Centos下 mysql 定时备份设置

一、使用配置好的bak.sh脚本配合cron定时任务实现每日定时备份到指定文件夹

将mysqlbak文件夹放到home/data 目录下

文件夹包含以下内容

 bak文件夹、backup.log日志输出文件、bak.sh 备份脚本文件

bak.sh内容如下

#!/bin/bash    
    
# MySQL配置    
MYSQL_USER='root'    
MYSQL_PASS='mysql@2024'    
BACKUP_DIR='/home/data/mysqlbak/bak'    
DATE=$(date +"%Y%m%d")    
    
# 确保备份目录存在    
mkdir -p "$BACKUP_DIR"    
    
# 定义备份和压缩文件的函数    
function backup_and_compress {    
    local DB_NAME=$1    
    local BACKUP_FILE="$BACKUP_DIR/mysql_backup_${DB_NAME}_$DATE.sql"    
    local COMPRESSED_FILE="$BACKUP_FILE.gz"    
    local CURRENT_TIME=$(date +"%Y-%m-%d %H:%M:%S")    
    
    # 使用mysqldump备份数据库    
    mysqldump -u$MYSQL_USER -p$MYSQL_PASS $DB_NAME --socket=/var/lib/mysql/mysql.sock > $BACKUP_FILE    
    
    # 检查备份是否成功    
    if [ $? -eq 0 ]; then    
        # 压缩备份文件    
        gzip $BACKUP_FILE    
        echo "Backup and compression for $DB_NAME successful at $CURRENT_TIME: $COMPRESSED_FILE" >> /home/data/mysqlbak/backup.log    
    else    
        echo "Backup failed for $DB_NAME at $CURRENT_TIME" >> /home/data/mysqlbak/backup.log    
    fi    
}    
    
# 调用函数备份每个数据库    
backup_and_compress 'ku1'    
backup_and_compress 'ku2'    
backup_and_compress 'ku3'    
    
echo "All backups completed."    
  
# 删除旧的备份文件（可选）    
# find $BACKUP_DIR -name "mysql_backup_*.gz" -type f -mtime +180 -delete

确保 /home/data/mysqlbak/bak.sh 脚本具有可执行权限。

你可以使用以下命令来设置执行权限

chmod +x /home/data/mysqlbak/bak.sh

--测试

sh  /home/data/mysqlbak/bak.sh

--1、创建定时任务

[root@localhost ~]# crontab -e

no crontab for root - using an empty one

crontab: installing new crontab

--2、设置任务时间及脚本位置  00代表00分钟，22代表小时  说明，

以下命令

00 22 * * * /home/data/mysqlbak/bak.sh

代表在每日的22点00分执行mysqlbak目录下的bak.sh脚本

--3、查看当前计划任务

[root@localhost ~]# crontab -l

00 22 * * * /home/data/mysqlbak/bak.sh

--4、重启定时任务

[root@localhost ~]# service crond restart

Redirecting to /bin/systemctl restart crond.service

--5、查看定时任务的服务状态

[root@localhost ~]# service crond status

--6、查看当前计划任务

[root@localhost ~]# crontab -l

00 22 * * * /home/data/mysqlbak/bak.sh

--执行结果全过程

Connecting to 172.20.50.23:22...

Connection established.

To escape to local shell, press 'Ctrl+Alt+]'.

Last login: Mon Sep  9 12:24:18 2024 from 10.0.255.101

[root@localhost ~]# chmod +x /home/data/mysqlbak/bak.sh

[root@localhost ~]# crontab -l

no crontab for root

[root@localhost ~]# crontab -e

no crontab for root - using an empty one

crontab: installing new crontab

[root@localhost ~]# crontab -l

10 22 * * * /home/data/mysqlbak/bak.sh

[root@localhost ~]# service crond restart

Redirecting to /bin/systemctl restart crond.service

[root@localhost ~]# service crond status

Redirecting to /bin/systemctl status crond.service

● crond.service - Command Scheduler

   Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled; vendor preset: enabled)

   Active: active (running) since 三 2024-09-18 10:27:28 CST; 9s ago

 Main PID: 5623 (crond)

    Tasks: 1

   CGroup: /system.slice/crond.service

           └─5623 /usr/sbin/crond -n

9月 18 10:27:28 localhost.localdomain systemd[1]: Started Command Scheduler.

9月 18 10:27:28 localhost.localdomain crond[5623]: (CRON) INFO (Syslog will be used instead of sendmail.)

9月 18 10:27:28 localhost.localdomain crond[5623]: (CRON) INFO (RANDOM_DELAY will be scaled with factor 57% if used.)

9月 18 10:27:28 localhost.localdomain crond[5623]: (CRON) INFO (running with inotify support)

9月 18 10:27:28 localhost.localdomain crond[5623]: (CRON) INFO (@reboot jobs will be run at computer's startup.)

[root@localhost ~]# crontab -l

10 22 * * * /home/data/mysqlbak/bak.sh

[root@localhost ~]# sh  /home/data/mysqlbak/bak.sh

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

All backups completed.

[root@localhost ~]# cd /home/data/mysqlbak/bak

[root@localhost bak]# ll

总用量 15340

-rw-r--r--. 1 root root 14418218 9月  18 10:29 mysql_backup_ku1_20240918.sql.gz

-rw-r--r--. 1 root root    19247 9月  18 10:29 mysql_backup_ku2_20240918.sql.gz

-rw-r--r--. 1 root root  1262420 9月  18 10:29 mysql_backup_ku3_20240918.sql.gz

[root@localhost bak]#

二、创建rsa密钥实现异机备份—之前xx验证过，但是使用的rsa空密钥，使用真实密钥未经验证

[root@localhost ~]# ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/root/.ssh/id_rsa): RSA@2024

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in RSA@2024

Your public key has been saved in RSA@2024.pub

The key fingerprint is:

SHA256:no0pt/JyKXOKyBNv9vpBEPNqyvrFUZ5mbptl2rIGr5I root@localhost.localdomain

The key's randomart image is:

+---[RSA 3072]----+

|    o            |

|     +           |

|    . o          |

|     = .         |

|    + * S        |

| ..+.* . =       |

|  o+oo= O..      |

| oEo+oO@+.       |

|..+=+*O%+        |

+----[SHA256]-----+

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQDVeHvjbdXE5kCR2s72KHRAbngm6Bw2K4O6b4zCQWRsiYmUC8Ab4xVCCaGWjVZ0sp2DB35IcX21/KIuLkL+XuRRwL8WOz34c39/17wcud+4WN/RlKbg0h6Gf1VJShIwPhm+AWcNSX2sx+oiLbC5r2Yg+3yie3CPXLAcS9/cHOiMuJwZJZ6BQm9EgDFAtGpKQ+3e5lzjKfFV2ap7ZTdu67qY4zi1ZuSajhYuoj9XOuyvmW/9Y5XCR23D6+qpAfoHcPP7wzFnMdnhkOXIYhOX+ikgU4ag676LCC1F1XgJB8IO6HrFK8YadlaOx0HOpFlcgQ932L79D/RMj1dhKbiS1ugaZF3GP3TzotIoNrphhYwXbTi9CTjcds1RsxyVpcpZiBWmEh6Nt6q6f7BVVud2gf3VMrM/8YDW4ztyV+qOCMWRFBDWG/Lb+Y+2tLML3weQVx0L7JAeDtEQ6WlrtTHjEmWvuyK0fe5mPkZcUQHzFljoL0oHsP9iqoGIlnNG3GOe9us= root@localhost.localdomain

--将创建的rsa密钥上传到109应用服务器，可手动传输并更名

scp ~/.ssh/id_rsa.pub 18.18.18.109:/root/.ssh/authorized_keys

--设置定时任务，每日的23点00分执行同步程序，将数据服务器/data/mysqlbak下数据增量同步到109应用服务器/data/mysqlbak下

00 23 * * * /usr/bin/rsync -avz /data/mysqlbak/ root@18.18.18.109:/data/mysqlbak

--rsa密钥创建后，手动验证增量同步的可用性

rsync -avz /data/mysqlbak/ root@18.18.18.109:/data/mysqlbak