Skip to content

Instantly share code, notes, and snippets.

@jonasdk

jonasdk/README.md

Forked from oodavid/README.md
Created April 7, 2016 06:42
Show Gist options
  • Save jonasdk/a5f7d380d97f90aae9c9b9f76c9e40df to your computer and use it in GitHub Desktop.
Save jonasdk/a5f7d380d97f90aae9c9b9f76c9e40df to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @oodavid oodavid revised this gist May 19, 2012. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion s3mysqlbackup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -19,7 +19,7 @@ bucket="s3://bucketname"
    stamp=`date +"%s - %A %d %B %Y @ %H%M"`

    # List all the databases
    databases=`mysql -u root -p$mysqlpass -e "SHOW DATABASES;" | tr -d "| " | grep -v "\(Database\|information_schema\|mysql\)"`
    databases=`mysql -u root -p$mysqlpass -e "SHOW DATABASES;" | tr -d "| " | grep -v "\(Database\|information_schema\|performance_schema\|mysql\|test\)"`

    # Feedback
    echo -e "Dumping to \e[1;32m$bucket/$stamp/\e[00m"
  2. @oodavid oodavid revised this gist Apr 9, 2012. 1 changed file with 10 additions and 7 deletions.
    17 changes: 10 additions & 7 deletions s3mysqlbackup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -2,6 +2,15 @@

    # Based on https://gist.github.com/2206527

    # Be pretty
    echo -e " "
    echo -e " . ____ . ______________________________"
    echo -e " |/ \| | |"
    echo -e "[| \e[1;31m♥ ♥\e[00m |] | S3 MySQL Backup Script v.0.1 |"
    echo -e " |___==___| / © oodavid 2012 |"
    echo -e " |______________________________|"
    echo -e " "

    # Basic variables
    mysqlpass="ROOTPASSWORD"
    bucket="s3://bucketname"
    @@ -40,10 +49,4 @@ for db in $databases; do
    done;

    # Jobs a goodun
    echo -e " "
    echo -e " . ____ . ______________________________"
    echo -e " |/ \| | |"
    echo -e "[| \e[1;31m♥ ♥\e[00m |] | S3 MySQL Backup Script v.0.1 |"
    echo -e " |___==___| / © oodavid 2012 |"
    echo -e " |______________________________|"
    echo -e " "
    echo -e "\e[1;32mJobs a goodun\e[00m"
  3. @oodavid oodavid revised this gist Mar 26, 2012. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,7 +2,7 @@

    This is a simple way to backup your MySQL tables to Amazon S3 for a nightly backup - this is all to be done on your server :-)

    ***Sister Document - [Restore MySQL from Amazon S3](https://gist.github.com/2208520) - read that next***
    ***Sister Document - [Restore MySQL from Amazon S3](https://gist.github.com/2209819) - read that next***

    ## 1 - Install s3cmd

  4. @oodavid oodavid revised this gist Mar 26, 2012. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions s3mysqlbackup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -7,7 +7,7 @@ mysqlpass="ROOTPASSWORD"
    bucket="s3://bucketname"

    # Timestamp (sortable AND readable)
    stamp=`date +"%s - %A %d %B %Y %H%M"`
    stamp=`date +"%s - %A %d %B %Y @ %H%M"`

    # List all the databases
    databases=`mysql -u root -p$mysqlpass -e "SHOW DATABASES;" | tr -d "| " | grep -v "\(Database\|information_schema\|mysql\)"`
    @@ -19,7 +19,7 @@ echo -e "Dumping to \e[1;32m$bucket/$stamp/\e[00m"
    for db in $databases; do

    # Define our filenames
    filename="$stamp-$db.sql.gz"
    filename="$stamp - $db.sql.gz"
    tmpfile="/tmp/$filename"
    object="$bucket/$stamp/$filename"

  5. @oodavid oodavid revised this gist Mar 26, 2012. 1 changed file with 17 additions and 8 deletions.
    25 changes: 17 additions & 8 deletions s3mysqlbackup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,19 +1,20 @@
    #!/bin/bash

    # BASED ON - https://gist.github.com/2206527
    # Based on https://gist.github.com/2206527

    # Basic variables
    mysqlpass="ROOTPASSWORD"
    bucket="s3://bucketname"

    # Timestamp (sortable AND readable)
    stamp=`date +"%s - %A %d %B %Y %H-%M-%S"`
    datestamp=`date +"%Y-%m-%d"`
    timestamp=`date +"%H-%M-%S"`
    stamp=`date +"%s - %A %d %B %Y %H%M"`

    # List all the databases
    databases=`mysql -u root -p$mysqlpass -e "SHOW DATABASES;" | tr -d "| " | grep -v "\(Database\|information_schema\|mysql\)"`

    # Feedback
    echo -e "Dumping to \e[1;32m$bucket/$stamp/\e[00m"

    # Loop the databases
    for db in $databases; do

    @@ -22,19 +23,27 @@ for db in $databases; do
    tmpfile="/tmp/$filename"
    object="$bucket/$stamp/$filename"

    # Feedback
    echo -e "\e[1;34m$db\e[00m"

    # Dump and zip
    echo -e "Dumping \e[0;34m$db\e[0m to \e[0;35m$tmpfile\e[0m..."
    echo -e " creating \e[0;35m$tmpfile\e[00m"
    mysqldump -u root -p$mysqlpass --force --opt --databases "$db" | gzip -c > "$tmpfile"

    # Upload
    echo -e "Moving \e[0;34m$tmpfile\e[0m to \e[0;35m$object\e[0m..."
    echo -e " uploading..."
    s3cmd put "$tmpfile" "$object"

    # Delete
    echo -e "Removing \e[1;31m$tmpfile\e[0m"
    rm -f "$tmpfile"

    done;

    # Jobs a goodun
    echo -e "\e[1;32mAll done :-)\e[0m"
    echo -e " "
    echo -e " . ____ . ______________________________"
    echo -e " |/ \| | |"
    echo -e "[| \e[1;31m♥ ♥\e[00m |] | S3 MySQL Backup Script v.0.1 |"
    echo -e " |___==___| / © oodavid 2012 |"
    echo -e " |______________________________|"
    echo -e " "
  6. @oodavid oodavid revised this gist Mar 26, 2012. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -4,7 +4,7 @@ This is a simple way to backup your MySQL tables to Amazon S3 for a nightly back

    ***Sister Document - [Restore MySQL from Amazon S3](https://gist.github.com/2208520) - read that next***

    ## Install s3cmd
    ## 1 - Install s3cmd

    *this is for Centos 5.6, see http://s3tools.org/repositories for other systems like ubuntu etc*

    @@ -16,7 +16,7 @@ This is a simple way to backup your MySQL tables to Amazon S3 for a nightly back
    s3cmd --configure
    # You’ll need to enter your AWS access key and secret key here, everything is optional and can be ignored :-)

    ## Add your script
    ## 2 - Add your script

    Upload a copy of [s3mysqlbackup.sh](#file_s3mysqlbackup.sh) (it will need some tweaks for your setup), make it executable and test it

    @@ -25,7 +25,7 @@ Upload a copy of [s3mysqlbackup.sh](#file_s3mysqlbackup.sh) (it will need some t
    # Run the script to make sure it's all tickety boo
    ./s3mysqlbackup.sh

    ## Run it every night with CRON
    ## 3 - Run it every night with CRON

    Assuming the backup script is stored in /var/www/s3mysqlbackup.sh we need to add a crontask to run it automatically:

    @@ -35,7 +35,7 @@ Assuming the backup script is stored in /var/www/s3mysqlbackup.sh we need to add
    # Run the database backup script at 3am
    0 3 * * * bash /var/www/s3mysqlbackup.sh >/dev/null 2>&1

    ## Don't expose the script!
    ## 4 - Don't expose the script!

    If for some reason you put this script in a public folder (not sure why you would do this), you should add the following to your .htaccess or httpd.conf file to prevent public access to the files:

  7. @oodavid oodavid revised this gist Mar 26, 2012. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,7 +2,7 @@

    This is a simple way to backup your MySQL tables to Amazon S3 for a nightly backup - this is all to be done on your server :-)

    ***Sister Document - [Restore MySQL from Amazon S3](https://gist.github.com/2208520)***
    ***Sister Document - [Restore MySQL from Amazon S3](https://gist.github.com/2208520) - read that next***

    ## Install s3cmd

  8. @oodavid oodavid revised this gist Mar 26, 2012. 1 changed file with 3 additions and 6 deletions.
    9 changes: 3 additions & 6 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,6 +2,8 @@

    This is a simple way to backup your MySQL tables to Amazon S3 for a nightly backup - this is all to be done on your server :-)

    ***Sister Document - [Restore MySQL from Amazon S3](https://gist.github.com/2208520)***

    ## Install s3cmd

    *this is for Centos 5.6, see http://s3tools.org/repositories for other systems like ubuntu etc*
    @@ -41,9 +43,4 @@ If for some reason you put this script in a public folder (not sure why you woul
    <Files *.sh>
    Order allow,deny
    Deny from all
    </Files>

    ## How to restore...?

    It would be silly to do backups without planning the restore procedure!
    How to... [Restore MySQL from Amazon S3](https://gist.github.com/2208520)
    </Files>
  9. @oodavid oodavid revised this gist Mar 26, 2012. 1 changed file with 17 additions and 9 deletions.
    26 changes: 17 additions & 9 deletions s3mysqlbackup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,10 +1,13 @@
    #!/bin/bash

    # BASED ON - https://gist.github.com/2206527

    # Basic variables
    mysqlpass="ROOTPASSWORD"
    bucket="s3://bucketname"

    # Timestamps
    # Timestamp (sortable AND readable)
    stamp=`date +"%s - %A %d %B %Y %H-%M-%S"`
    datestamp=`date +"%Y-%m-%d"`
    timestamp=`date +"%H-%M-%S"`

    @@ -14,19 +17,24 @@ databases=`mysql -u root -p$mysqlpass -e "SHOW DATABASES;" | tr -d "| " | grep -
    # Loop the databases
    for db in $databases; do

    # Define our filenames - it makes sense to keep all the tables grouped together on S3 so we can see all the tables in a single folder :-)
    filename="$datestamp-$timestamp-$db.sql.gz"
    # Define our filenames
    filename="$stamp-$db.sql.gz"
    tmpfile="/tmp/$filename"
    object="$bucket/$datestamp/$filename"
    object="$bucket/$stamp/$filename"

    # Dump and zip
    echo -e "Dumping \e[0;34m$db\e[0m to \e[0;35m$tmpfile\e[0m..."
    mysqldump -u root -p$mysqlpass --force --opt --databases $db | gzip -c > $tmpfile
    mysqldump -u root -p$mysqlpass --force --opt --databases "$db" | gzip -c > "$tmpfile"

    # Upload
    echo -e "Moving \e[0;34m$tmpfile\e[0m to \e[0;35m$object\e[0m..."
    s3cmd put $tmpfile $object
    s3cmd put "$tmpfile" "$object"

    # Delete
    echo -e "Removing \e[1;31m$tmpfile\e[0m"
    rm -f $tmpfile
    rm -f "$tmpfile"

    done;

    echo -e "\e[1;32mDatabase: $db backed up successfully to S3\e[0m"
    done;
    # Jobs a goodun
    echo -e "\e[1;32mAll done :-)\e[0m"
  10. @oodavid oodavid revised this gist Mar 26, 2012. 1 changed file with 6 additions and 1 deletion.
    7 changes: 6 additions & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -41,4 +41,9 @@ If for some reason you put this script in a public folder (not sure why you woul
    <Files *.sh>
    Order allow,deny
    Deny from all
    </Files>
    </Files>

    ## How to restore...?

    It would be silly to do backups without planning the restore procedure!
    How to... [Restore MySQL from Amazon S3](https://gist.github.com/2208520)
  11. @oodavid oodavid created this gist Mar 26, 2012.
    44 changes: 44 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,44 @@
    # Backup MySQL to Amazon S3

    This is a simple way to backup your MySQL tables to Amazon S3 for a nightly backup - this is all to be done on your server :-)

    ## Install s3cmd

    *this is for Centos 5.6, see http://s3tools.org/repositories for other systems like ubuntu etc*

    # Install s3cmd
    cd /etc/yum.repos.d/
    wget http://s3tools.org/repo/CentOS_5/s3tools.repo
    yum install s3cmd
    # Setup s3cmd
    s3cmd --configure
    # You’ll need to enter your AWS access key and secret key here, everything is optional and can be ignored :-)

    ## Add your script

    Upload a copy of [s3mysqlbackup.sh](#file_s3mysqlbackup.sh) (it will need some tweaks for your setup), make it executable and test it

    # Add the executable bit
    chmod +x s3mysqlbackup.sh
    # Run the script to make sure it's all tickety boo
    ./s3mysqlbackup.sh

    ## Run it every night with CRON

    Assuming the backup script is stored in /var/www/s3mysqlbackup.sh we need to add a crontask to run it automatically:

    # Edit the crontab
    env EDITOR=nano crontab -e
    # Add the following lines:
    # Run the database backup script at 3am
    0 3 * * * bash /var/www/s3mysqlbackup.sh >/dev/null 2>&1

    ## Don't expose the script!

    If for some reason you put this script in a public folder (not sure why you would do this), you should add the following to your .htaccess or httpd.conf file to prevent public access to the files:

    ### Deny public access to shell files
    <Files *.sh>
    Order allow,deny
    Deny from all
    </Files>
    32 changes: 32 additions & 0 deletions s3mysqlbackup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,32 @@
    #!/bin/bash

    # Basic variables
    mysqlpass="ROOTPASSWORD"
    bucket="s3://bucketname"

    # Timestamps
    datestamp=`date +"%Y-%m-%d"`
    timestamp=`date +"%H-%M-%S"`

    # List all the databases
    databases=`mysql -u root -p$mysqlpass -e "SHOW DATABASES;" | tr -d "| " | grep -v "\(Database\|information_schema\|mysql\)"`

    # Loop the databases
    for db in $databases; do

    # Define our filenames - it makes sense to keep all the tables grouped together on S3 so we can see all the tables in a single folder :-)
    filename="$datestamp-$timestamp-$db.sql.gz"
    tmpfile="/tmp/$filename"
    object="$bucket/$datestamp/$filename"

    echo -e "Dumping \e[0;34m$db\e[0m to \e[0;35m$tmpfile\e[0m..."
    mysqldump -u root -p$mysqlpass --force --opt --databases $db | gzip -c > $tmpfile

    echo -e "Moving \e[0;34m$tmpfile\e[0m to \e[0;35m$object\e[0m..."
    s3cmd put $tmpfile $object

    echo -e "Removing \e[1;31m$tmpfile\e[0m"
    rm -f $tmpfile

    echo -e "\e[1;32mDatabase: $db backed up successfully to S3\e[0m"
    done;