Automating MySql Backups to SMB/CIFS Shares
I have recently had to automate the backup of all databases upon a MySql server, compress, and save to a SMB/CIFS share.
Although it's not the nicest example, the following script will mount a network share, iterate through the databases on the MySql Server, compress each, and save to the share, inside a folder created in the format YYYY-MM-DD.
I thought I'd post it in-case anyone else needed a similar solution.
#!/bin/bash
TIMESTAMP=$(date +"%F")
MYSQL="/usr/bin/mysql"
MYSQL_USER="{mysql_user_with_DBAdmin_permits}"
MYSQL_PASSWORD="{mysql_user_password}"
MYSQLDUMP="/usr/bin/mysqldump"
SMB_BACKUP_DIR="/mnt/backup"
SMB_BACKUP_SOURCE="//{path_to_network_share}"
SMB_BACKUP_USER="{network_user_with_permits_to_share}"
SMB_BACKUP_PASSWORD="{network_user_password}"
BACKUP_DIR="$SMB_BACKUP_DIR/$TIMESTAMP"
mkdir -p $SMB_BACKUP_DIR
mount -t cifs "$SMB_BACKUP_SOURCE" "$SMB_BACKUP_DIR" -o username="$SMB_BACKUP_USER",password="$SMB_BACKUP_PASSWORD"
mkdir -p $BACKUP_DIR
databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`
for db in $databases; do
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$db.gz"
done
umount $SMB_BACKUP_DIR