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

    An Advance Bash Script for MySQL Database Backup

    By RahulMay 5, 20202 Mins Read

    After writing a simple shell script for MySQL database backup. This is our new advance bash script for MySQL database dump. This script will provide you to backup MySQL database and upload backups to various remote locations like FTP, SFTP and Amazon s3 bucket.

    Advertisement

    This script is available on our Github account with all supporting file. You can simply download the script and run it on your server. Use the following steps to use this script and configure backups within 5 min.

    Step 1 – Clone this Repository

    Download this repository put files under /etc/mydumpadmin directory. Alternatively, you may also clone this repository under /etc directory using the following commands.

    cd /etc/
    git clone https://github.com/tecrahul/mydumpadmin.git
    

    Step 2 – Configure Setup

  • Edit settings.conf file and update all required values as per your requirements. You can enable/disable FTP, SFTP and s3 backups as well
  • Now edit credentials.txt file and put your mysql server login details.
  • Step 3 – Execute Backup Script

    Set the execute permission on shell script and run this as following commands.

    chmod a+x mysql-dump.sh
    ./mysql-dump.sh
    

    Step 4 – Schedule Daily Cron

    You can also schedule this to run on daily basis using crontab. Add the following settings to crontab to run on 2:00 AM daily.

     0 2 * * * cd /etc/mydumpadmin && ./mysql-dump.sh
    

    backup MySQL mysql bash backup mysql s3 backup mysql shell script mysqldump
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    How to Prevent SQL-injection in PHP using Prepared Statements

    Preventing SQL injection attacks with prepared statements in MySQL

    Securing MySQL Database with Limited User Permissions

    View 18 Comments

    18 Comments

    1. Chandrashekhar Sapkal on June 4, 2022 12:35 pm

      Hi Rahul I’m using this script working fine but problem is when this script are run twice in days their are only one Latest backup generate on local and s3 we are having old bucket on the local and s3

      Reply
    2. siswanto on April 14, 2021 11:57 am

      how to backup to google drive? thank

      Reply
    3. ROGER on March 18, 2021 6:54 am

      Hi, Just tested your script, it is great.

      I modified the MYSQL DUMP command to avoid locking the db as the script was making my service unavailable during the backup.

      ${MYSQLDUMP} –single-transaction –quick –lock-tables=false –opt ${CREDENTIALS} -h ${MYSQL_HOST} -P $MYSQL_PORT $db | ${GZIP} -9 > $FILENAMEPATH

      Enjoy

      Reply
    4. Uzma on August 9, 2020 6:54 am

      Hello,

      To accomplish deletion of backup files x number of days I have added following rsync code. Its working fine just 1 issue that it is executing before everything even I have added this code to last in the sh file.
      Please guide whats worng or how to set it to run at the end.

      ###Rsync backup folder #######
      [ $VERBOSE -eq 1 ] && echo “Rsync backup folder”
      rsync -avuh -e “ssh -p ${SFTP_PORT}” –delete ${LOCAL_BACKUP_DIR}/ ${SFTP_USERNAME}@${SFTP_HOST}:${SFTP_UPLOAD_DIR}
      [ $VERBOSE -eq 1 ] && echo “***Rsync completed***”

      Reply
    5. Uzma on August 7, 2020 12:00 pm

      Hello Rahul,
      Thank you for great script. I have few questions.
      This script not saving in .sql.gz format. When I unzip compressed file it extract file without .sql extension. How I can change this to store files with .sql.gz extension and extrated files with .sql extension.

      Reply
      • Rahul on August 8, 2020 9:59 am

        Hi Uzma, I have updated the backup script to include “.sql” in backup file extension.

        Reply
        • Uzma on August 9, 2020 6:07 am

          Thanks again Rahul,

          I am using this script to transfer backups to backup server. Will this delete backups from remote server after x days? As if not it will full backup server with unnecessary data.

          2nd, is it possible that we can automate database import on remote server so backup server always remain updated.

          For example, In case main server is down and we want website to shift on backup server. Files are already rsynced and we have database dumps on backup server. So we have to manually import recent database on backup server.
          But if there is any script which can automate the import part ( use cron after x hours/days and import most recent dump) it would be great. This might need some additional script on backup server.

          Reply
    6. Pramod on November 20, 2019 7:10 am

      hi Rahul,

      Can you modify code to backup db to azure blob storage

      Reply
    7. James on October 23, 2019 8:05 pm

      Uploading backup file to S3 Bucket
      ./mysql-dump.sh: line 115: –access_key=***********************: command not found

      I am having this error. Not sure how to resolve. I am able to upload to s3 with s3cmd just fine.

      Reply
      • james on October 23, 2019 11:57 pm

        Figured it out. Had to do: export S3CMD=/usr/bin/s3cmd

        Thanks for the script. works great!

        Reply
    8. InnoMarozva on June 3, 2019 11:19 am

      /etc/mydumpadmin/settings.conf: line 99: $’\r’: command not found
      : No such file or directorybin/mkdir
      /mydumpadmin-03062019-1035.txt: No such file or directory
      /mydumpadmin-03062019-1035.txt: No such file or directory
      /mydumpadmin-03062019-1035.txt: No such file or directory
      /mydumpadmin-03062019-1035.txt: No such file or directory
      does not exists. Make sure correct path is set in /etc/mydumpadmin/settings.conf.

      Reply
      • Rahul on October 19, 2019 5:25 am

        Hi, This error has been resolved.

        Reply
    9. Avtar Singh on May 24, 2019 3:23 am

      Script is not working show the error. please let me what was going wrong if i run srcipt then showing error
      ./MySQLBackup.sh: line 31: syntax error near unexpected token `(‘
      ./MySQLBackup.sh: line 31: `PASSWORD= (‘password ‘)’

      Reply
    10. Rosel on December 14, 2018 6:40 am

      Great post !! Thanks for such a wonderful script.

      Reply
    11. David on October 30, 2017 11:12 pm

      Thanks for this. I tested it on ubuntu 17.10 local machine and I had to run dos2unix on the settings.conf file first and also had to run the script as root (sudo su) or gzip failed with OS error code 32: Broken pipe

      Reply
    12. Tom on July 31, 2017 4:02 pm

      Thanks a lot.

      This helped me to quickly setup mysql backups … But it showing s3cmd command is not available…

      Reply
      • Rahul K. on July 31, 2017 5:30 pm

        Hi Tom, You can use below tutorial to install s3cmd on Linux system.

        https://tecadmin.net/install-s3cmd-manage-amazon-s3-buckets/

        Reply
        • Avtar Singh on May 24, 2019 3:25 am

          Hello Rahul, Please check post of script

          Reply

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • Implementing a Linux Server Security Audit: Best Practices and Tools
    • cp Command in Linux (Copy Files Like a Pro)
    • 15 Practical Examples of dd Command in Linux
    • dd Command in Linux (Syntax, Options and Use Cases)
    • Iptables: Common Firewall Rules and Commands
    Facebook Twitter Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

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