Facebook Twitter Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    You are at:Home»Databases»MySQL»A Simple Bash Script for MySQL Database Backup

    A Simple Bash Script for MySQL Database Backup

    By RahulMay 16, 20192 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.

    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.

    backup dump MySQL mysqldump script
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    Backing Up Your Linux System with Rsync: A Step-by-Step Guide

    (Resolved) MySQL connection error: certificate verify failed

    How to Connect Python with MySQL Database

    View 39 Comments

    39 Comments

    1. Chris on December 1, 2022 12:20 am

      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
    2. A on March 13, 2022 6:19 pm

      Great! Thank you!

      Reply
    3. Sid on July 20, 2021 8:58 pm

      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
    4. Nagapandi on June 19, 2020 6:49 am

      Thank you so much RAHUL

      Reply
    5. Musir on May 18, 2020 8:29 am

      Hello Rahul,

      Is it possible that I get mail after backup?

      Reply
    6. Joe on February 15, 2020 8:52 pm

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

      Reply
    7. Anil on December 31, 2019 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

      Reply
    8. Shivkant Baghel on November 14, 2019 3:12 pm

      Thank you so much brother.

      Reply
    9. Vivek Gupta on November 1, 2019 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.

      Reply
    10. SofyanSyuhri on October 25, 2019 3:19 am

      Thank you RAHUL

      Reply
    11. sonu kumar on September 6, 2019 7:08 am

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

      Reply
    12. Stefan on August 31, 2019 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?

      Reply
    13. Ronald on August 28, 2019 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.

      Reply
    14. khuyn on June 25, 2019 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?

      Reply
    15. Amin on May 30, 2019 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

      Reply
    16. René Olskjær on May 16, 2019 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…

      Reply
    17. Lê Văn Công on May 7, 2019 3:59 am

      thanks. very good

      Reply
    18. Teo on April 26, 2019 2:15 pm

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

      Thank you

      Reply
    19. Rico on March 27, 2019 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 ?

      Reply
    20. Rico on March 26, 2019 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.

      Reply
      • Rahul on March 27, 2019 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"`
        
        Reply
        • Sunny on August 24, 2019 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

          Reply
    21. Aabhusan on February 11, 2019 5:11 am

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

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

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

        Reply
    22. siswanto on January 27, 2019 10:02 am

      thank you

      how if the database is more than one?

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

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

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

        Reply
        • siswanto on January 30, 2019 3:06 pm

          thank you

          terima kasih 🙂

          Reply
    23. john on January 3, 2019 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.

      Reply
      • Rahul on January 5, 2019 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.

        Reply
    24. DBA on December 27, 2018 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.

      Reply
    25. Roshan on December 16, 2018 8:33 am

      Hi

      Reply
    26. Roshan on December 16, 2018 5:18 am

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

      Reply
      • Roshan on December 20, 2018 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”`

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

          Thanks Roshan,

          The issue has been corrected.

          Reply
    27. santoshsahgal on December 14, 2018 6:41 am

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

      Reply
    28. Srinivasa Reddy Kolagatla on August 23, 2018 1:26 pm

      thank you so much, bro!!!

      Reply
    29. vaibhav on May 10, 2018 11:46 am

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

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

        No downtime or any integrity issue.

        Reply
    30. M on April 25, 2018 9:29 am

      Thanks!

      Reply

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • How to Install PHP 8.2-7.4 on RHEL & CentOS Stream 9
    • How to Install MySQL 8.0 on RHEL & CentOS Stream 9
    • How to Split Large Archives in Linux using the Command Line
    • System.out.println() Method in Java: A Beginner’s Guide
    • Split Command in Linux With Examples (Split Large Files)
    Facebook Twitter Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.