An Advance Bash Script for MySQL Database Backup

    By 2 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

    Share.

    Related Posts

    View 18 Comments

    18 Comments

    1. Chandrashekhar Sapkal on

      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
    3. ROGER on

      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

      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

      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
        • Uzma on

          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
    7. James on

      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
    8. InnoMarozva on

      /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
    9. Avtar Singh on

      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
    11. David on

      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

      Thanks a lot.

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

      Reply

    Leave A Reply