1.创建备份用用户CREATE USER backuplocalhost IDENTIFIED BY backuptest; GRANT ALL PRIVILEGES ON database.* TO backuplocalhost; FLUSH PRIVILEGES;2.配置用户名密码sudo tee /root/.my.cnf /dev/null EOF [client] userbackup password强密码 EOF sudo chmod 600 /root/.my.cnf3.配置定时任务sudo tee /etc/cron.d/mariadb-backup /dev/null EOF SHELL/bin/bash PATH/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin 0 2 * * * root /usr/local/bin/mariadb-backup.sh EOF sudo systemctl restart cron4.mariadb-backup.sh文件#!/bin/bash BACKUP_DIR/var/backups/mariadb KEEP_DAYS7 # Retain the last 7 days; adjust as needed. STAMP$(date %Y%m%d_%H%M%S) LOG/var/log/mariadb-backup.log FILE$BACKUP_DIR/mariadb_$STAMP.sql.gz mkdir -p $BACKUP_DIR # --- Online Full Backup (No table locking, no blocking of writes) --- # --single-transaction : InnoDB consistent snapshot; application writes remain unaffected # --quick : Output row-by-row without buffering the entire table; reduces memory usage # --skip-lock-tables : Skip the table-locking step (used in conjunction with --single-transaction) # --routines/--events : Export stored procedures, functions, and events # --triggers : Explicitly export triggers # --flush-logs : Rotate binary logs before backup; facilitates incremental recovery mariadb-dump \ --single-transaction \ --quick \ --skip-lock-tables \ --routines \ --triggers \ --events \ database \ | gzip $FILE # --- Integrity Check --- if [ -s $FILE ] gzip -t $FILE 2/dev/null; then SIZE$(du -h $FILE | cut -f1) echo $(date %F %T) Backup successful: $(basename $FILE) ($SIZE) $LOG else echo $(date %F %T) Backup failed! $FILE is missing or corrupted. $LOG exit 1 fi # --- Clean up old backups exceeding the retention period --- find $BACKUP_DIR -name mariadb_*.sql.gz -mtime $KEEP_DAYS -delete echo $(date %F %T) Cleaned up backups older than ${KEEP_DAYS} days $LOG exit 0
mariadb 定时备份
1.创建备份用用户CREATE USER backuplocalhost IDENTIFIED BY backuptest; GRANT ALL PRIVILEGES ON database.* TO backuplocalhost; FLUSH PRIVILEGES;2.配置用户名密码sudo tee /root/.my.cnf /dev/null EOF [client] userbackup password强密码 EOF sudo chmod 600 /root/.my.cnf3.配置定时任务sudo tee /etc/cron.d/mariadb-backup /dev/null EOF SHELL/bin/bash PATH/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin 0 2 * * * root /usr/local/bin/mariadb-backup.sh EOF sudo systemctl restart cron4.mariadb-backup.sh文件#!/bin/bash BACKUP_DIR/var/backups/mariadb KEEP_DAYS7 # Retain the last 7 days; adjust as needed. STAMP$(date %Y%m%d_%H%M%S) LOG/var/log/mariadb-backup.log FILE$BACKUP_DIR/mariadb_$STAMP.sql.gz mkdir -p $BACKUP_DIR # --- Online Full Backup (No table locking, no blocking of writes) --- # --single-transaction : InnoDB consistent snapshot; application writes remain unaffected # --quick : Output row-by-row without buffering the entire table; reduces memory usage # --skip-lock-tables : Skip the table-locking step (used in conjunction with --single-transaction) # --routines/--events : Export stored procedures, functions, and events # --triggers : Explicitly export triggers # --flush-logs : Rotate binary logs before backup; facilitates incremental recovery mariadb-dump \ --single-transaction \ --quick \ --skip-lock-tables \ --routines \ --triggers \ --events \ database \ | gzip $FILE # --- Integrity Check --- if [ -s $FILE ] gzip -t $FILE 2/dev/null; then SIZE$(du -h $FILE | cut -f1) echo $(date %F %T) Backup successful: $(basename $FILE) ($SIZE) $LOG else echo $(date %F %T) Backup failed! $FILE is missing or corrupted. $LOG exit 1 fi # --- Clean up old backups exceeding the retention period --- find $BACKUP_DIR -name mariadb_*.sql.gz -mtime $KEEP_DAYS -delete echo $(date %F %T) Cleaned up backups older than ${KEEP_DAYS} days $LOG exit 0
