• Home
  • Ubuntu 18.04
    • Whats New?
    • Upgrade Ubuntu
    • Install Java
    • Install Node.js
    • Install Docker
    • Install Git
    • Install LAMP Stack
  • Tutorials
    • AWS
    • Shell Scripting
    • Docker
    • Git
    • MongoDB
  • Funny Tools
  • FeedBack
  • Submit Article
  • About Us
TecAdmin
Menu
  • Home
  • Ubuntu 18.04
    • Whats New?
    • Upgrade Ubuntu
    • Install Java
    • Install Node.js
    • Install Docker
    • Install Git
    • Install LAMP Stack
  • Tutorials
    • AWS
    • Shell Scripting
    • Docker
    • Git
    • MongoDB
  • Funny Tools
  • FeedBack
  • Submit Article
  • About Us

A Simple Bash Script for MySQL Database Backup

Written by Rahul, Updated on May 16, 2019

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.

Share it!
Share on Facebook
Share on Twitter
Share on LinkedIn
Share on Reddit
Share on Tumblr
Share on Whatsapp
Rahul
Rahul
Connect on Facebook Connect on Twitter

I, Rahul Kumar am the founder and chief editor of TecAdmin.net. I am a Red Hat Certified Engineer (RHCE) and working as an IT professional since 2009..

36 Comments

  1. Avatar Nagapandi Reply
    June 19, 2020 at 6:49 am

    Thank you so much RAHUL

  2. Avatar Musir Reply
    May 18, 2020 at 8:29 am

    Hello Rahul,

    Is it possible that I get mail after backup?

  3. Avatar Joe Reply
    February 15, 2020 at 8:52 pm

    Thanks! Exactly what I was looking for, I appreciate you taking the time to create and post.

  4. Avatar Anil Reply
    December 31, 2019 at 5:38 am

    I need to aad –routines in the dumping command because i am using procedures also. can you please modify the script so the backup file include procedures.

    Thanks
    Anil

  5. Avatar Shivkant Baghel Reply
    November 14, 2019 at 3:12 pm

    Thank you so much brother.

  6. Avatar Vivek Gupta Reply
    November 1, 2019 at 5:20 am

    Hi,

    I have one doubt that if previous job does not complete then what happened cron create a new job or they will overlap.

  7. Avatar SofyanSyuhri Reply
    October 25, 2019 at 3:19 am

    Thank you RAHUL

  8. Avatar sonu kumar Reply
    September 6, 2019 at 7:08 am

    Thnaks Rahul…
    This is working fine and i got something first time that run in single shot

  9. Avatar Stefan Reply
    August 31, 2019 at 6:30 pm

    If the access data of the database is not correct, the query is still output as correct. Why? That can’t be. I think it is due to the simultaneous backup as a GZ file. If I change the path there, which does not exist, then the IF query works. But how does it work if there are problems with the database?

  10. Avatar Ronald Reply
    August 28, 2019 at 7:16 am

    Can this be done without exposing the credentials on the script itself? For example my credentials is stored in a .yaml file.

  11. Avatar khuyn Reply
    June 25, 2019 at 8:26 am

    hello guys!
    if [ ! -z ${DB_BACKUP_PATH} ]; then
    cd ${DB_BACKUP_PATH}
    if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then
    rm -rf ${DBDELDATE}
    fi
    fi

    -z What is the effect?

  12. Avatar Amin Reply
    May 30, 2019 at 9:24 pm

    I would add to the cron tab
    >> /backup/logs .txt

    so you know what’s happening if you check the logs

    and I would store file dates in numbers, easier to parse than names, so instead of
    30May2019

    I would do: 2019.05.30

  13. Avatar René Olskjær Reply
    May 16, 2019 at 7:18 am

    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…

  14. Avatar Lê Văn Công Reply
    May 7, 2019 at 3:59 am

    thanks. very good

  15. Avatar Teo Reply
    April 26, 2019 at 2:15 pm

    Hi Rahul,
    and if I want to copy just one table into another one, what have I to change?

    Thank you

  16. Avatar Rico Reply
    March 27, 2019 at 7:37 am

    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 ?

  17. Avatar Rico Reply
    March 26, 2019 at 9:37 am

    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.

    • Rahul Rahul Reply
      March 27, 2019 at 4:09 am

      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"`
      
      • Avatar Sunny Reply
        August 24, 2019 at 3:32 am

        Hello Rahul

        I am new to mysql i need MySQL Backup Script only to take backup of specific tables on daily basis & also it should delete backup of old days keeping 2 days latest backups.

        Thanks You
        Sunny

  18. Avatar Aabhusan Reply
    February 11, 2019 at 5:11 am

    in /backup , it say that you have new mail in /var/spool/mail/root

    • Rahul Rahul Reply
      February 11, 2019 at 9:16 am

      This is very common. Many of services send emails to root account for various activities.

  19. Avatar siswanto Reply
    January 27, 2019 at 10:02 am

    thank you

    how if the database is more than one?

    MYSQL_USER=’root’
    MYSQL_PASSWORD=’mysecret’
    DATABASE_NAME=’mydb’ (many databases)

    • Rahul Rahul Reply
      January 29, 2019 at 10:11 am

      You can try this script: https://github.com/tecrahul/mydumpadmin

      • Avatar siswanto Reply
        January 30, 2019 at 3:06 pm

        thank you

        terima kasih 🙂

  20. Avatar john Reply
    January 3, 2019 at 12:03 am

    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.

    • Rahul Rahul Reply
      January 5, 2019 at 5:08 am

      Thanks John, This was really a major issue. I have updated the script to verify directories before deleting them.

      Hope this is fine now.

  21. Avatar DBA Reply
    December 27, 2018 at 6:12 am

    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.

  22. Avatar Roshan Reply
    December 16, 2018 at 8:33 am

    Hi

  23. Avatar Roshan Reply
    December 16, 2018 at 5:18 am

    Hi
    Remove backup older than x days doesnt work. it deletes the whole backup if enabled.

    • Avatar Roshan Reply
      December 20, 2018 at 7:16 am

      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”`

      • Rahul Rahul Reply
        December 20, 2018 at 10:55 am

        Thanks Roshan,

        The issue has been corrected.

  24. Avatar santoshsahgal Reply
    December 14, 2018 at 6:41 am

    Great post !! Thanks for such a clear and step by step MySQL Backup Script.

  25. Avatar Srinivasa Reddy Kolagatla Reply
    August 23, 2018 at 1:26 pm

    thank you so much, bro!!!

  26. Avatar vaibhav Reply
    May 10, 2018 at 11:46 am

    Is their will be any data integrity issue with this solution or downtime ?

    • Rahul Rahul K. Reply
      July 17, 2018 at 9:27 am

      No downtime or any integrity issue.

  27. Avatar M Reply
    April 25, 2018 at 9:29 am

    Thanks!

Leave a Reply Cancel reply

Popular Posts

  • How To Install Python 3.9 on Ubuntu 20.04 5
  • How to Install Python 3.9 on CentOS/RHEL 7 & Fedora 32/31 0
  • How To Install VNC Server on Ubuntu 20.04 1
  • How To Install NVM on macOS with Homebrew 0
  • (Solved) apt-add-repository command not found – Ubuntu & Debian 0
© 2013-2020 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy