Q. How to schedule MySQL database backup? A bash script for MySQL database backup. In this tutorial, you will find a simple bash script, which takes the backup of MySQL database, archive the backup and store on the local system. This script will also to remove older backups from disk to free space. You can specify the number of days to keep the backup on local disk. You can also use this tutorial to backup MySQL database and store a copy on the remote FTP server.
Create MySQL Backup Script
Now, copy the following content in a script file (like: /backup/mysql-backup.sh) and save on your Linux system. Use this link to download script. After than change some configuration values in section “Update below values” in the script as per your environment.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | #!/bin/bash ################################################################ ## ## MySQL Database Backup Script ## Written By: Rahul Kumar ## URL: https://tecadmin.net/bash-script-mysql-database-backup/ ## Last Update: Jan 05, 2019 ## ################################################################ export PATH=/bin:/usr/bin:/usr/local/bin TODAY=`date +"%d%b%Y"` ################################################################ ################## Update below values ######################## DB_BACKUP_PATH='/backup/dbbackup' MYSQL_HOST='localhost' MYSQL_PORT='3306' MYSQL_USER='root' MYSQL_PASSWORD='mysecret' DATABASE_NAME='mydb' BACKUP_RETAIN_DAYS=30 ## Number of days to keep local backup copy ################################################################# 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 ### End of script #### |
After creating or downloading script make sure to set execute permission to run properly.
chmod +x /backup/mysql-backup.sh
Schedule Script in Crontab
Now schedule the script in crontab to run on a daily basis and complete backup on regular basis. Edit crontab on your system with crontab -e command. Add following settings to enable backup at 2 in the morning.
0 2 * * * root /backup/mysql-backup.sh
Save your crontab file. After enabling cron, the script will take backup automatically, But keep check backups on a weekly or monthly basis to make sure.
You need to add a “exit 1” after check for failed backup. If something is wrong in your database, preventng the script from running, you delete your old backups preventing you from restoring…
thanks. very good
Hi Rahul,
and if I want to copy just one table into another one, what have I to change?
Thank you
Hello Rahul, Thanks for your answer, but deleted older folders is still not working. Have you got any idea what can I do? May I send you an email with this script, you can check in your computer ?
Hello Rahul, I’ve a problem with this script. Everythink (connections with databse and dump database to folder) works great, but this script doesn’t delete folders older than example 5 days and I don’t know way. Only what I have changed is line : TODAY=`date “+%Y%m%d_%H:%M:%S” and DBDELDATE=`date “+%Y%m%d_%H:%M:%S” –date=”${BACKUP_RETAIN_DAYS} days ago”` , because I want to have folder like date_time. Could you help me and write where is my mistake. Thank for help.
Try syntax like belwo:
TODAY=`date +"%Y%m%d_%H:%M:%S"` DBDELDATE=`date +"%d%b%Y_%H:%M:%S" --date="${BACKUP_RETAIN_DAYS} days ago"`in /backup , it say that you have new mail in /var/spool/mail/root
This is very common. Many of services send emails to root account for various activities.
thank you
how if the database is more than one?
MYSQL_USER=’root’
MYSQL_PASSWORD=’mysecret’
DATABASE_NAME=’mydb’ (many databases)
You can try this script: https://github.com/tecrahul/mydumpadmin
thank you
terima kasih 🙂
rm -rf ${BACKUPPATH}/${DBDELDATE}
That’s a dangerous idea – you should consider adding tests BEFORE executing the rm -rf to ensure both variables are not null. If they are null (for whatever reason), and you run this as a privileged user, you could delete everything and royally ruin your system.
Thanks John, This was really a major issue. I have updated the script to verify directories before deleting them.
Hope this is fine now.
Thanks for sharing this post. Very informative. Sir I also had a doubt related to Finance study that is doing MBA in finance is a suitable degree or are there any rules to do so? Also if you can give me some advice for better career.
Hi
Hi
Remove backup older than x days doesnt work. it deletes the whole backup if enabled.
i think the parameter is wrong in the script
DBDELDATE=`date +”%d%b%Y” –date=”${DBRETAINDAYS} days ago”`
should be
DBDELDATE=`date +”%d%b%Y” –date=”${BACKUPRETAINDAYS} days ago”`
Thanks Roshan,
The issue has been corrected.
Great post !! Thanks for such a clear and step by step MySQL Backup Script.
thank you so much, bro!!!
Is their will be any data integrity issue with this solution or downtime ?
No downtime or any integrity issue.
Thanks!