MySQL备份和恢复数据库

Backup Script

This script will create backups of the specified databases.

File: backup_databases.sh

#!/bin/bash

# Database credentials
USER="your_username"
PASSWORD="your_password"
HOST="localhost"

# Backup storage directory
BACKUP_DIR="/path/to/your/backup/directory"

# List of databases to backup
DATABASES=("ccnet_db" "seafile_db" "seahub_db")

# Date format for backup filename
DATE=$(date +"%Y%m%d%H%M")

# Loop through databases and create a backup of each
for DB in "${DATABASES[@]}"; do
    BACKUP_FILE="$BACKUP_DIR/$DB-$DATE.sql"
    echo "Backing up $DB to $BACKUP_FILE"
    mysqldump -u $USER -p$PASSWORD -h $HOST $DB > $BACKUP_FILE
    
    # Check if mysqldump was successful
    if [ $? -eq 0 ]; then
        echo "Backup of $DB completed successfully."
    else
        echo "Error during backup of $DB" >&2
        exit 1
    fi
done

echo "Backup process completed!"

Recovery Script

This script will restore databases from the specified backup files.

File: restore_databases.sh

#!/bin/bash

# Database credentials
USER="your_username"
PASSWORD="your_password"
HOST="localhost"

# Backup directory
BACKUP_DIR="/path/to/your/backup/directory"

# List of databases and their backup files
# Format: "database_name:path_to_backup_file"
DATABASES_BACKUPS=("ccnet_db:/path/to/ccnet_db_backup.sql" 
                   "seafile_db:/path/to/seafile_db_backup.sql"
                   "seahub_db:/path/to/seahub_db_backup.sql")

# Loop through databases and restore each from its backup
for ENTRY in "${DATABASES_BACKUPS[@]}"; do
    IFS=":" read -r DB BACKUP_FILE <<< "$ENTRY"
    echo "Restoring $DB from $BACKUP_FILE"
    mysql -u $USER -p$PASSWORD -h $HOST $DB < $BACKUP_FILE

    # Check if mysql command was successful
    if [ $? -eq 0 ]; then
        echo "Restoration of $DB completed successfully."
    else
        echo "Error during restoration of $DB" >&2
        exit 1
    fi
done

echo "Restore process completed!"

Scheduling Backups

To schedule the backup script to run automatically, you can use cron. Edit the crontab file with crontab -e and add a line like this for daily backups at 2 AM:

0 2 * * * /path/to/backup_databases.sh

Create with GPT-4