Linux shell script for database backup

Create a script similar to this:

#!/bin/sh -e location=~/`date +%Y%m%d_%H%M%S`.db mysqldump -u root --password=<your password> database_name > $location gzip $location 

Then you can edit the crontab of the user that the script is going to run as:

$> crontab -e 

And append the entry

01 * * * * ~/script_path.sh 

This will make it run on the first minute of every hour every day.

Then you just have to add in your rolls and other functionality and you are good to go.

I got the same issue. But I manage to write a script. Hope this would help.

#!/bin/bash # Database credentials user="username" password="password" host="localhost" db_name="dbname" # Other options backup_path="/DB/DB_Backup" date=$(date +"%d-%b-%Y") # Set default file permissions umask 177 # Dump database into SQL file mysqldump --user=$user --password=$password --host=$host $db_name >$backup_path/$db_name-$date.sql # Delete files older than 30 days find $backup_path/* -mtime +30 -exec rm {} \; #DB backup log echo -e "$(date +'%d-%b-%y %r '):ALERT:Database has been Backuped" >>/var/log/DB_Backup.log 

#!/bin/sh #Procedures = For DB Backup #Scheduled at : Every Day 22:00 v_path=/etc/database_jobs/db_backup logfile_path=/etc/database_jobs v_file_name=DB_Production v_cnt=0 MAILTO="[email protected]" touch "$logfile_path/kaka_db_log.log" #DB Backup mysqldump -uusername -ppassword -h111.111.111.111 ddbname > $v_path/$v_file_name`date +%Y-%m-%d`.sql if [ "$?" -eq 0 ] then v_cnt=`expr $v_cnt + 1` mail -s "DB Backup has been done successfully" $MAILTO < $logfile_path/db_log.log else mail -s "Alert : kaka DB Backup has been failed" $MAILTO < $logfile_path/db_log.log exit fi 

Here is my mysql backup script for ubuntu in case it helps someone.

#Mysql back up script start_time="$(date -u +%s)" now(){ date +%d-%B-%Y_%H-%M-%S } ip(){ /sbin/ifconfig eth0 2>/dev/null|awk '/inet addr:/ {print $2}'|sed 's/addr://' } filename="`now`".zip backupfolder=/path/to/any/folder fullpathbackupfile=$backupfolder/$filename db_user=xxx db_password=xxx db_name=xxx printf "\n\n" printf "******************************\n" printf "Started Automatic Mysql Backup\n" printf "******************************\n" printf "TIME: `now`\n" printf "IP_ADDRESS: `ip` \n" printf "DB_SERVER_NAME: DB-SERVER-1\n" printf "%sBACKUP_FILE_PATH $fullpathbackupfile\n" printf "Starting Mysql Dump \n" mysqldump -u $db_user -p$db_password $db_name| pv | zip > $fullpathbackupfile end_time="$(date -u +%s)" elapsed=$(($end_time-$start_time)) printf "%sMysql Dump Completed In $elapsed seconds\n" printf "******************************\n" 

PS: Rememember to install pv and zip in your ubuntu

sudo apt install pv sudo apt install zip 

Here is how I set crontab by using crontab -e in ubuntu to run every 6 hours

0 */6 * * * sh /path/to/shfile/backup-mysql.sh >> /path/to/logs/backup-mysql.log 2>&1 

Cool thing is it will create a zip file which is easier to unzip from anywhere

Now, copy the following content in a script file (like: /backup/mysql-backup.sh) and save on your Linux system.

 #!/bin/bash export PATH=/bin:/usr/bin:/usr/local/bin TODAY=`date +"%d%b%Y"` DB_BACKUP_PATH='/backup/dbbackup' MYSQL_HOST='localhost' MYSQL_PORT='3306' MYSQL_USER='root' MYSQL_PASSWORD='mysecret' DATABASE_NAME='mydb' BACKUP_RETAIN_DAYS=30 mkdir -p ${DB_BACKUP_PATH}/${TODAY} echo "Backup started for database - ${DATABASE_NAME}" mysqldump -h ${MYSQL_HOST} \ -P ${MYSQL_PORT} \ -u ${MYSQL_USER} \ -p${MYSQL_PASSWORD} \ ${DATABASE_NAME} | gzip > ${DB_BACKUP_PATH}/${TODAY}/${DATABASE_NAME}-${TODAY}.sql.gz if [ $? -eq 0 ]; then echo "Database backup successfully completed" else echo "Error found during backup" exit 1 fi ##### Remove backups older than {BACKUP_RETAIN_DAYS} days ##### DBDELDATE=`date +"%d%b%Y" --date="${BACKUP_RETAIN_DAYS} days ago"` if [ ! -z ${DB_BACKUP_PATH} ]; then cd ${DB_BACKUP_PATH} if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then rm -rf ${DBDELDATE} fi fi 

After creating or downloading script make sure to set execute permission to run properly.

$ chmod +x /backup/mysql-backup.sh 

Edit crontab on your system with crontab -e command. Add following settings to enable backup at 3 in the morning.

0 3 * * * root /backup/mysql-backup.sh 

Add the following code to your shell script file. Replace dbname, dbuser and dbpass with your database name, username and password respectively.

#!/bin/sh echo "starting db backup" day="$(date +"%m-%d-%y")" db_backup="mydb_${day}.sql" sudo mysqldump -udbuser -pdbpass --no-tablespaces dbname >/home/${db_backup} echo " backup complete" 

If you want to compress the above backup data, just Replace with the following code.

db_backup="mydb_${day}.gz" sudo mysqldump -udbuser -pdbpass --no-tablespaces dbname | gzip -c >/home/${db_backup} 

If you want to delete files older than 14 days in a folders, use following code.

#!/bin/bash fpath1=/home/ubuntu/mysql/* fpath2=/home/ubuntu/postgsql/* file_path=($fpath1 $fpath2) for i in ${file_path[@]}; do find $i -type d -mtime +13 -exec rm -Rf {} + done 

#!/bin/bash # Add your backup dir location, password, mysql location and mysqldump location DATE=$(date +%d-%m-%Y) BACKUP_DIR="/var/www/back" MYSQL_USER="root" MYSQL_PASSWORD="" MYSQL='/usr/bin/mysql' MYSQLDUMP='/usr/bin/mysqldump' DB='demo' #to empty the backup directory and delete all previous backups rm -r $BACKUP_DIR/* mysqldump -u root -p'' demo | gzip -9 > $BACKUP_DIR/demo$date_format.sql.$DATE.gz #changing permissions of directory chmod -R 777 $BACKUP_DIR 

You might consider this Open Source tool, matiri, https://github.com/AAFC-MBB/matiri which is a concurrent mysql backup script with metadata in Sqlite3. Features:

• Multi-Server: Multiple MySQL servers are supported whether they are co-located on the same or separate physical servers.
• Parallel: Each database on the server to be backed up is done separately, in parallel (concurrency settable: default: 3)
• Compressed: Each database backup compressed
• Checksummed: SHA256 of each compressed backup file stored and the archive of all files
• Archived: All database backups tar'ed together into single file
• Recorded: Backup information stored in Sqlite3 database

Full disclosure: original matiri author.

As a DBA, You must schedule the backup of MySQL Database in case of any issues so that you can recover your databases from the current backup.

Here, we are using mysqldump to take the backup of mysql databases and the same you can put into the script.

[orahow@oradbdb DB_Backup]$ cat .backup_script.sh

#!/bin/bash # Database credentials user="root" password="1Loginxx" db_name="orahowdb" v_cnt=0 logfile_path=/DB_Backup touch "$logfile_path/orahowdb_backup.log" # Other options backup_path="/DB_Backup" date=$(date +"%d-%b-%Y-%H-%M-%p") # Set default file permissions 

Continue Reading .... MySQL Backup

I have prepared a Shell Script to create a Backup of MYSQL database. You can use it so that we have backup of our database(s).

 #!/bin/bash export PATH=/bin:/usr/bin:/usr/local/bin TODAY=`date +"%d%b%Y_%I:%M:%S%p"` ################################################################ ################## Update below values ######################## DB_BACKUP_PATH='/backup/dbbackup' MYSQL_HOST='localhost' MYSQL_PORT='3306' MYSQL_USER='auriga' MYSQL_PASSWORD='auriga@123' DATABASE_NAME=( Project_O2 o2) BACKUP_RETAIN_DAYS=30 ## Number of days to keep local backup copy; Enable script code in end of th script ################################################################# { mkdir -p ${DB_BACKUP_PATH}/${TODAY} echo " ${TODAY}" >> ${DB_BACKUP_PATH}/Backup-Report.txt } || { echo "Can not make Directry" echo "Possibly Path is wrong" } { if ! mysql -u ${MYSQL_USER} -p${MYSQL_PASSWORD} -e 'exit'; then echo 'Failed! You may have Incorrect PASSWORD/USER ' >> ${DB_BACKUP_PATH}/Backup-Report.txt exit 1 fi for DB in "${DATABASE_NAME[@]}"; do if ! mysql -u ${MYSQL_USER} -p${MYSQL_PASSWORD} -e "use "${DB}; then echo "Failed! Database ${DB} Not Found on ${TODAY}" >> ${DB_BACKUP_PATH}/Backup-Report.txt else # echo "Backup started for database - ${DB}" # mysqldump -h localhost -P 3306 -u auriga -pauriga@123 Project_O2 # use gzip.. mysqldump -h ${MYSQL_HOST} -P ${MYSQL_PORT} -u ${MYSQL_USER} -p${MYSQL_PASSWORD} \ --databases ${DB} | gzip > ${DB_BACKUP_PATH}/${TODAY}/${DB}-${TODAY}.sql.gz if [ $? -eq 0 ]; then touch ${DB_BACKUP_PATH}/Backup-Report.txt echo "successfully backed-up of ${DB} on ${TODAY}" >> ${DB_BACKUP_PATH}/Backup-Report.txt # echo "Database backup successfully completed" else touch ${DB_BACKUP_PATH}/Backup-Report.txt echo "Failed to backup of ${DB} on ${TODAY}" >> ${DB_BACKUP_PATH}/Backup-Report.txt # echo "Error found during backup" exit 1 fi fi done } || { echo "Failed during backup" echo "Failed to backup on ${TODAY}" >> ${DB_BACKUP_PATH}/Backup-Report.txt # ./myshellsc.sh 2> ${DB_BACKUP_PATH}/Backup-Report.txt } ##### Remove backups older than {BACKUP_RETAIN_DAYS} days ##### # DBDELDATE=`date +"%d%b%Y" --date="${BACKUP_RETAIN_DAYS} days ago"` # if [ ! -z ${DB_BACKUP_PATH} ]; then # cd ${DB_BACKUP_PATH} # if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then # rm -rf ${DBDELDATE} # fi # fi ### End of script #### 

In the script we just need to give our Username, Password, Name of Database(or Databases if more than one) also Port number if Different.

To Run the script use Command as:

sudo ./script.sc 

I also Suggest that if You want to see the Result in a file Like: Failure Occurs or Successful in backing-up, then Use the Command as Below:

sudo ./myshellsc.sh 2>> Backup-Report.log 

Thank You.