Skip to content

Instantly share code, notes, and snippets.

@chill117

chill117/mysql_backup.sh

Last active December 13, 2021 22:02
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save chill117/6243212 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save chill117/6243212 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. chill117 revised this gist Jul 6, 2014. 1 changed file with 13 additions and 20 deletions.
    33 changes: 13 additions & 20 deletions mysql_backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,47 +1,40 @@
    #!/bin/bash

    #
    # Use this script to perform backups on one or more MySQL databases.
    # Use this script to perform backups of one or more MySQL databases.
    #

    # Databases that you wish to be backed up by this script. You can have any number of databases specified; encapsilate each database name in single quotes and separate each database name by a space.
    #
    # Example:
    # databases=( '__DATABASE_1__' '__DATABASE_2__' )
    databases=( )
    databases=()

    # The host name of the MySQL database server; usually 'localhost'
    db_host="localhost"

    # The port number of the MySQL database server; usually '3306'
    db_port="3306"

    # The MySQL user to use when performing the database backup.
    db_user=""
    db_user="backups"

    # The password for the above MySQL user.
    db_pass=""

    # Host name of the remote server to which the database backup files will be uploaded.
    # To skip the upload step, leave this blank.
    remote_host=""
    # Directory to which backup files will be written. Should end with slash ("/").
    backups_dir="/home/backups/db/"

    # The user that will be used when connecting to the remote server.
    #
    # Note:
    # You will need to set up Passwordless SSH with the remote server.
    remote_user=""
    backups_user="backups"

    # Date/time included in the file names of the database backup files.
    datetime=$(date +'%Y-%m-%dT%H:%M:%S')

    for db_name in ${databases[@]}; do
    # Create database backup and compress using gzip.
    mysqldump -u $db_user -h $db_host --password=$db_pass $db_name | gzip -9 > /home/backups/db/$db_name--$datetime.sql.gz
    mysqldump -u $db_user -h $db_host -P $db_port --password=$db_pass $db_name | gzip -9 > $backups_dir$db_name--$datetime.sql.gz
    done

    # Set strictest possible file permissions.
    chown root:root /home/backups/db/*--$datetime.sql.gz
    chmod 000 /home/backups/db/*--$datetime.sql.gz

    if [ "$remote_host" != '' ]; then
    # Use secure copy to upload backup files to remote server.
    scp /home/backups/db/*--$datetime.sql.gz $remote_user@$remote_host:/home/backups/db/
    fi
    # Set appropriate file permissions/owner.
    chown $backups_user:$backups_user $backups_dir*--$datetime.sql.gz
    chmod 0400 $backups_dir*--$datetime.sql.gz
  2. Charles Hill renamed this gist Aug 15, 2013. 1 changed file with 0 additions and 0 deletions.
    0 backup.sh → mysql_backup.sh
    View file
    File renamed without changes.
  3. Charles Hill renamed this gist Aug 15, 2013. 1 changed file with 0 additions and 0 deletions.
    0 gistfile1.txt → backup.sh
    View file
    File renamed without changes.
  4. Charles Hill created this gist Aug 15, 2013.
    47 changes: 47 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,47 @@
    #!/bin/bash

    #
    # Use this script to perform backups on one or more MySQL databases.
    #

    # Databases that you wish to be backed up by this script. You can have any number of databases specified; encapsilate each database name in single quotes and separate each database name by a space.
    #
    # Example:
    # databases=( '__DATABASE_1__' '__DATABASE_2__' )
    databases=( )

    # The host name of the MySQL database server; usually 'localhost'
    db_host="localhost"

    # The MySQL user to use when performing the database backup.
    db_user=""

    # The password for the above MySQL user.
    db_pass=""

    # Host name of the remote server to which the database backup files will be uploaded.
    # To skip the upload step, leave this blank.
    remote_host=""

    # The user that will be used when connecting to the remote server.
    #
    # Note:
    # You will need to set up Passwordless SSH with the remote server.
    remote_user=""

    # Date/time included in the file names of the database backup files.
    datetime=$(date +'%Y-%m-%dT%H:%M:%S')

    for db_name in ${databases[@]}; do
    # Create database backup and compress using gzip.
    mysqldump -u $db_user -h $db_host --password=$db_pass $db_name | gzip -9 > /home/backups/db/$db_name--$datetime.sql.gz
    done

    # Set strictest possible file permissions.
    chown root:root /home/backups/db/*--$datetime.sql.gz
    chmod 000 /home/backups/db/*--$datetime.sql.gz

    if [ "$remote_host" != '' ]; then
    # Use secure copy to upload backup files to remote server.
    scp /home/backups/db/*--$datetime.sql.gz $remote_user@$remote_host:/home/backups/db/
    fi