Mysql-Backup-script

Backing up your MySQL databases is crucial for disaster recovery and peace of mind. Below, you’ll find two practical MySQL backup scripts, each with its own approach. Both are ready to use or adapt for your own server environment.

Script 1: Full Backup with Slave Control and Cleanup

This script is ideal for MySQL replication setups. It stops the slave, performs a full backup, restarts the slave, and cleans up old backups.

bash
#!/bin/sh

datum=$(/bin/date +%Y%m%d-%H)

# Stop MySQL slave (for replication setups)
mysqladmin --user=root --password=yourrootsqlpassword stop-slave

# Full backup with table lock
mysqldump --user=root --password=yourrootsqlpassword --lock-all-tables --all-databases > /home/sqlbackup/backup-${datum}.sql

# Restart MySQL slave
mysqladmin --user=root --password=yourrootsqlpassword start-slave

# Delete backups older than 2 days
find /home/sqlbackup -type f -mtime +2 -exec rm -f {} \;

exit 0

Key Features:

• Stops and restarts the MySQL slave for consistent backups in replication environments.

• Dumps all databases into a dated SQL file.

• Cleans up backups older than 2 days.

Script 2: Per-Database Compressed Backups with Retention

This script backs up each database individually, compresses the dumps, and keeps them organized with a hostname prefix. It’s suitable for most standalone MySQL servers.

bash
#!/bin/bash

MHOST=localhost
MUSER=backup
MPASS=OwBPimRc
BACKUPDIR="/mnt/backup"

MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

DBPREFIX="$(hostname -s).mysqldb"

echo "Run MySQL backup"

# Get list of databases
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"

for db in $DBS
do
  FILE=${BACKUPDIR}/${DBPREFIX}.${db}.$(date +%Y%m%d).gz
  $MYSQLDUMP --no-tablespaces --skip-lock-tables -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done

# Delete backups older than 10 days
find ${BACKUPDIR} -name "${DBPREFIX}*" -type f -mtime +10 -exec rm -f '{}' ';'

Key Features:

• Backs up each database separately for easier restoration.

• Compresses each SQL dump with gzip for space savings.

• Adds the server’s hostname as a prefix for easy identification.

• Cleans up backups older than 10 days.

Tips for Using These Scripts

• Permissions: Run as a user with sufficient privileges to access MySQL and write to the backup directory.

• Automation: Schedule with cron for regular backups.

• Security: Store your scripts securely and restrict permissions, as they contain database passwords.

• Testing: Regularly test your backups by restoring them to a test server.

Example Cron Job

To run the second script every night at 2:30 AM, add this to your crontab (crontab -e):

text
30 2 * * * /path/to/your/backup-script.sh