A Simple Bash Script for MySQL Database Backup

By 2 Mins Read

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.

Advertisement

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.

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.

Related Posts

39 Comments

  1. Chris on

    I’m trying to use this script, but without gziping the output. When I do that, it takes a long time on my server. I’d rather output just the .sql file. I can’t figure out the correct syntax.

    Does anyone know what to use instead of this?

    “${DATABASE_NAME} | gzip > ${DB_BACKUP_PATH}/${TODAY}/${DATABASE_NAME}-${TODAY}.sql.gz”

    I tried the following, but without any luck – ${DATABASE_NAME} > ${DB_BACKUP_PATH}/${TODAY}/${DATABASE_NAME}-${TODAY}.sql.gz

    Reply
  3. Sid on

    I don’t want to download the views from my db. I want to download everything but vies. What would be the change in this script? Can you please help?

    Reply
  7. Anil on

    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

    Reply
  9. Vivek Gupta on

    Hi,

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

    Reply
  12. Stefan on

    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?

    Reply
  13. Ronald on

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

    Reply
  14. khuyn on

    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?

    Reply
  15. Amin on

    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

    Reply
  16. René Olskjær on

    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…

    Reply
  19. Rico on

    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 ?

    Reply
  20. Rico on

    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.

    Reply
    • Rahul on

      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"`
      Reply
      • Sunny on

        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

        Reply
  22. siswanto on

    thank you

    how if the database is more than one?

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

    Reply
  23. john on

    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.

    Reply
    • Rahul on

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

      Hope this is fine now.

      Reply
  24. DBA on

    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.

    Reply
Leave A Reply

Exit mobile version