Created
September 2, 2016 19:00
-
-
Save shakhmehedi/d9d966dba399451d1a32b8be1c183980 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 |
nice work !
Great script, Works nice, except for 2 of my servers request the root password, how can i avoid that?
Another questiion, how can i do to put the date on the filenames in spanish?
@samuelmf The easiest way to do this is to use a client section of the ~/.my.cnf
file, and add the credentials inside that file.
[client]
user=root
password=somepassword
how can i do to put the date on the filenames in spanish?
Try:
SET lc_time_names = 'es_ES';
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thanks! Great script!