$ -weight: 600;">sudo nano /usr/local/bin/pg-backup.sh -weight: 600;">sudo nano /usr/local/bin/pg-backup.sh -weight: 600;">sudo nano /usr/local/bin/pg-backup.sh #!/bin/bash set -e BACKUP_DIR="/var/backups/postgresql" DATE=$(date +%Y%m%d_%H%M%S) KEEP_DAYS=7 DB_NAME="${1:-all}" # Pass db name or backs up all mkdir -p "$BACKUP_DIR" if [ "$DB_NAME" = "all" ]; then # Backup all databases pg_dumpall -U postgres | gzip > "$BACKUP_DIR/all_${DATE}.sql.gz" else # Backup specific database pg_dump -U postgres -Fc "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${DATE}.dump" fi # Remove backups older than KEEP_DAYS find "$BACKUP_DIR" -type f -mtime +$KEEP_DAYS -delete # Verify backup file exists and is not empty LATEST=$(ls -t "$BACKUP_DIR" | head -1) if [ ! -s "$BACKUP_DIR/$LATEST" ]; then echo "ERROR: Backup file is empty!" | mail -s "DB Backup FAILED" [email protected] exit 1 fi echo "Backup successful: $BACKUP_DIR/$LATEST ($(du -h "$BACKUP_DIR/$LATEST" | cut -f1))" #!/bin/bash set -e BACKUP_DIR="/var/backups/postgresql" DATE=$(date +%Y%m%d_%H%M%S) KEEP_DAYS=7 DB_NAME="${1:-all}" # Pass db name or backs up all mkdir -p "$BACKUP_DIR" if [ "$DB_NAME" = "all" ]; then # Backup all databases pg_dumpall -U postgres | gzip > "$BACKUP_DIR/all_${DATE}.sql.gz" else # Backup specific database pg_dump -U postgres -Fc "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${DATE}.dump" fi # Remove backups older than KEEP_DAYS find "$BACKUP_DIR" -type f -mtime +$KEEP_DAYS -delete # Verify backup file exists and is not empty LATEST=$(ls -t "$BACKUP_DIR" | head -1) if [ ! -s "$BACKUP_DIR/$LATEST" ]; then echo "ERROR: Backup file is empty!" | mail -s "DB Backup FAILED" [email protected] exit 1 fi echo "Backup successful: $BACKUP_DIR/$LATEST ($(du -h "$BACKUP_DIR/$LATEST" | cut -f1))" #!/bin/bash set -e BACKUP_DIR="/var/backups/postgresql" DATE=$(date +%Y%m%d_%H%M%S) KEEP_DAYS=7 DB_NAME="${1:-all}" # Pass db name or backs up all mkdir -p "$BACKUP_DIR" if [ "$DB_NAME" = "all" ]; then # Backup all databases pg_dumpall -U postgres | gzip > "$BACKUP_DIR/all_${DATE}.sql.gz" else # Backup specific database pg_dump -U postgres -Fc "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${DATE}.dump" fi # Remove backups older than KEEP_DAYS find "$BACKUP_DIR" -type f -mtime +$KEEP_DAYS -delete # Verify backup file exists and is not empty LATEST=$(ls -t "$BACKUP_DIR" | head -1) if [ ! -s "$BACKUP_DIR/$LATEST" ]; then echo "ERROR: Backup file is empty!" | mail -s "DB Backup FAILED" [email protected] exit 1 fi echo "Backup successful: $BACKUP_DIR/$LATEST ($(du -h "$BACKUP_DIR/$LATEST" | cut -f1))" chmod +x /usr/local/bin/pg-backup.sh chmod +x /usr/local/bin/pg-backup.sh chmod +x /usr/local/bin/pg-backup.sh -weight: 600;">sudo nano /etc/postgresql/*/main/pg_hba.conf -weight: 600;">sudo nano /etc/postgresql/*/main/pg_hba.conf -weight: 600;">sudo nano /etc/postgresql/*/main/pg_hba.conf local all postgres trust local all postgres trust local all postgres trust # ~/.pgpass # hostname:port:database:username:password localhost:5432:*:postgres:your_password chmod 600 ~/.pgpass # ~/.pgpass # hostname:port:database:username:password localhost:5432:*:postgres:your_password chmod 600 ~/.pgpass # ~/.pgpass # hostname:port:database:username:password localhost:5432:*:postgres:your_password chmod 600 ~/.pgpass -weight: 600;">sudo crontab -e -weight: 600;">sudo crontab -e -weight: 600;">sudo crontab -e # Daily backup at 2 AM 0 2 * * * /usr/local/bin/pg-backup.sh mydb >> /var/log/pg-backup.log 2>&1 # Weekly full backup on Sunday 0 1 * * 0 /usr/local/bin/pg-backup.sh all >> /var/log/pg-backup.log 2>&1 # Daily backup at 2 AM 0 2 * * * /usr/local/bin/pg-backup.sh mydb >> /var/log/pg-backup.log 2>&1 # Weekly full backup on Sunday 0 1 * * 0 /usr/local/bin/pg-backup.sh all >> /var/log/pg-backup.log 2>&1 # Daily backup at 2 AM 0 2 * * * /usr/local/bin/pg-backup.sh mydb >> /var/log/pg-backup.log 2>&1 # Weekly full backup on Sunday 0 1 * * 0 /usr/local/bin/pg-backup.sh all >> /var/log/pg-backup.log 2>&1 -weight: 600;">sudo nano /usr/local/bin/mysql-backup.sh -weight: 600;">sudo nano /usr/local/bin/mysql-backup.sh -weight: 600;">sudo nano /usr/local/bin/mysql-backup.sh #!/bin/bash set -e BACKUP_DIR="/var/backups/mysql" DATE=$(date +%Y%m%d_%H%M%S) KEEP_DAYS=7 MYSQL_USER="backup_user" MYSQL_PASS="${MYSQL_BACKUP_PASSWORD}" # Set as env var in cron mkdir -p "$BACKUP_DIR" # Get all databases DATABASES=$(mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)") for DB in $DATABASES; do mysqldump -u "$MYSQL_USER" -p"$MYSQL_PASS" --single-transaction --routines --triggers "$DB" | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz" echo "Backed up: $DB" done find "$BACKUP_DIR" -type f -mtime +$KEEP_DAYS -delete echo "Backup complete. Files in $BACKUP_DIR" #!/bin/bash set -e BACKUP_DIR="/var/backups/mysql" DATE=$(date +%Y%m%d_%H%M%S) KEEP_DAYS=7 MYSQL_USER="backup_user" MYSQL_PASS="${MYSQL_BACKUP_PASSWORD}" # Set as env var in cron mkdir -p "$BACKUP_DIR" # Get all databases DATABASES=$(mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)") for DB in $DATABASES; do mysqldump -u "$MYSQL_USER" -p"$MYSQL_PASS" --single-transaction --routines --triggers "$DB" | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz" echo "Backed up: $DB" done find "$BACKUP_DIR" -type f -mtime +$KEEP_DAYS -delete echo "Backup complete. Files in $BACKUP_DIR" #!/bin/bash set -e BACKUP_DIR="/var/backups/mysql" DATE=$(date +%Y%m%d_%H%M%S) KEEP_DAYS=7 MYSQL_USER="backup_user" MYSQL_PASS="${MYSQL_BACKUP_PASSWORD}" # Set as env var in cron mkdir -p "$BACKUP_DIR" # Get all databases DATABASES=$(mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)") for DB in $DATABASES; do mysqldump -u "$MYSQL_USER" -p"$MYSQL_PASS" --single-transaction --routines --triggers "$DB" | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz" echo "Backed up: $DB" done find "$BACKUP_DIR" -type f -mtime +$KEEP_DAYS -delete echo "Backup complete. Files in $BACKUP_DIR" CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_password'; GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO 'backup_user'@'localhost'; FLUSH PRIVILEGES; CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_password'; GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO 'backup_user'@'localhost'; FLUSH PRIVILEGES; CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_password'; GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO 'backup_user'@'localhost'; FLUSH PRIVILEGES; # Install AWS CLI -weight: 600;">sudo -weight: 500;">apt -weight: 500;">install awscli -y aws configure # Add your AWS credentials # Add to backup script aws s3 cp "$BACKUP_DIR/$LATEST" s3://your-backup-bucket/postgresql/ # Or sync entire directory aws s3 sync "$BACKUP_DIR" s3://your-backup-bucket/postgresql/ # Install AWS CLI -weight: 600;">sudo -weight: 500;">apt -weight: 500;">install awscli -y aws configure # Add your AWS credentials # Add to backup script aws s3 cp "$BACKUP_DIR/$LATEST" s3://your-backup-bucket/postgresql/ # Or sync entire directory aws s3 sync "$BACKUP_DIR" s3://your-backup-bucket/postgresql/ # Install AWS CLI -weight: 600;">sudo -weight: 500;">apt -weight: 500;">install awscli -y aws configure # Add your AWS credentials # Add to backup script aws s3 cp "$BACKUP_DIR/$LATEST" s3://your-backup-bucket/postgresql/ # Or sync entire directory aws s3 sync "$BACKUP_DIR" s3://your-backup-bucket/postgresql/ # PostgreSQL restore test pg_restore -U postgres -d test_restore_db /var/backups/postgresql/latest.dump # MySQL restore test gunzip -c /var/backups/mysql/mydb_latest.sql.gz | mysql -u root -p test_restore_db # PostgreSQL restore test pg_restore -U postgres -d test_restore_db /var/backups/postgresql/latest.dump # MySQL restore test gunzip -c /var/backups/mysql/mydb_latest.sql.gz | mysql -u root -p test_restore_db # PostgreSQL restore test pg_restore -U postgres -d test_restore_db /var/backups/postgresql/latest.dump # MySQL restore test gunzip -c /var/backups/mysql/mydb_latest.sql.gz | mysql -u root -p test_restore_db