Forked from shakhmehedi/bash_backup_all_mysql_databases.sh
Created
June 2, 2023 18:22
-
-
Save zhangran/c95ad4c2114d4aee7cac2679ce5f8450 to your computer and use it in GitHub Desktop.
Bash scripts to backup all databases in a MySQL server with the option to exclude some databases.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env bash | |
#This script backups selected databases in local MySQL server | |
#REQUIREMENTS | |
##mysqldump gzip | |
##mysql database has no root password. This script uses 'root' MySQL user without password as no 'root' password is set. | |
##This is not good practice. User with more restrictive permission should be used. | |
#set database user | |
DBUSER=root | |
#set backup path | |
BACKUPPATH=/backups/magento/mysql/ | |
#set date string for file name | |
DATESTR=`date +%A_%d-%m-%Y_%H-%M-%S` | |
#set file extension | |
EXT=.sql.gz | |
#Get datanase list from MySQL server | |
echo "Geting list of databases from MySQL server" | |
databases=( `echo "SHOW DATABASES;" | mysql -u root` ) | |
#set databses to be excluded | |
excludedDatabases=( Database performance_schema information_schema ) | |
#set start date | |
STARTDATE=$(date) | |
echo "########################################### Start Database Backup ##############################################" | |
echo $STARTDATE": Starting database backups, trying to backup all databases" | |
#checks if the datavase is in excluded list. database name is passed as first param | |
#EXAMPLE | |
# isExCludedDb=$(isExDb <database_name>) | |
function isExDb() | |
{ | |
local isXdb="false" | |
for exdb in "${excludedDatabases[@]}" | |
do | |
if [ "$1" == "${exdb}" ]; then | |
isXdb="true" | |
break | |
fi | |
done | |
echo "$isXdb" | |
} | |
#Loop through all databases | |
for db in "${databases[@]}" | |
do | |
#check if the database in excluded database list | |
if [ $(isExDb "$db") == "true" ]; then | |
#do not backup if the database is in excluded list | |
echo $(date)': Excluded database: '$db | |
else | |
#back up database | |
echo $(date)': Backing up database: '$db | |
#generate backup file path | |
newbackuppath=$BACKUPPATH$db"_"$DATESTR$EXT | |
#backup database | |
mysqldump -u root --opt --routines --triggers --quick $db | gzip > $newbackuppath | |
#check if database backup succeeded or failed | |
if [ "${PIPESTATUS[0]}" -ne "0" ]; then | |
#if database backup failed, delete the file created during backup attempt | |
echo 'Failed to backup, database: '$db | |
#check and delete file if exist | |
if [ -f $newbackuppath ]; then | |
rm -f $newbackuppath | |
echo $(date)': Removed failed backup file'$newbackuppath | |
#create simple errorlog file to indicate database backup failed | |
errLogPath=$newbackuppath".error.log" | |
echo $(date)': Failed to create database backup, something went wrong in mysqldump' > $errLogPath | |
fi | |
else | |
#show success message, if backup succeeded. | |
echo $(date)': Backup successfull, database: '$db' path: '$newbackuppath | |
fi | |
fi | |
done | |
echo $(date)": Database backup finished, started at: "$STARTDATE | |
echo "-------------------------------------------------End-------------------------------------------------------------" | |
#TODO update database list before deploy |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment