Shell Scripts for MySQL Database Backup and Nginx Daily Log Rotation

This article provides two practical Bash scripts for automating routine server maintenance tasks.

The first script performs a multi‑cycle backup of all non‑system MySQL databases, creating timestamped directories for each database and dumping each table to an individual .sql file, with error handling for failed dumps.

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
HOST=localhost
USER=backup
PASS=123.com
BACKUP_DIR=/data/db_backup
DB_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "show databases;" 2>/dev/null | egrep -v "Database|information_schema|mysql|performance_schema|sys")
for DB in $DB_LIST; do
  BACKUP_DB_DIR=$BACKUP_DIR/${DB}_${DATE}
  [ ! -d $BACKUP_DB_DIR ] && mkdir -p $BACKUP_DB_DIR >/dev/null
  TABLE_LIST=$(mysql -h$HOST -u$USER -p$PASS -s -e "use $DB;show tables;" 2>/dev/null)
  for TABLE in $TABLE_LIST; do
    BACKUP_NAME=$BACKUP_DB_DIR/${TABLE}.sql
    if ! mysqldump -h$HOST -u$USER -p$PASS $DB $TABLE > $BACKUP_NAME 2>/dev/null; then
      echo "$BACKUP_NAME 备份失败!"
    fi
  done
done

The second script rotates Nginx access logs by moving the previous day's log file into a month‑based directory and signals Nginx to reopen its logs.

#!/bin/bash
LOG_DIR=/usr/local/nginx/logs
YESTERDAY_TIME=$(date -d "yesterday" +%F)
LOG_MONTH_DIR=$LOG_DIR/$(date +"%Y-%m")
LOG_FILE_LIST="default.access.log"
for LOG_FILE in $LOG_FILE_LIST; do
  [ ! -d $LOG_MONTH_DIR ] && mkdir -p $LOG_MONTH_DIR
  mv $LOG_DIR/$LOG_FILE $LOG_MONTH_DIR/${LOG_FILE}_${YESTERDAY_TIME}
done
kill -USR1 $(cat /var/run/nginx.pid)