Skip to content

Instantly share code, notes, and snippets.

@dwinurhadia

dwinurhadia/dump.sh

Forked from andsens/dump.sh
Created October 24, 2023 07:04
Show Gist options
  • Save dwinurhadia/998a942b560bd2418f94aecba204d32a to your computer and use it in GitHub Desktop.
Save dwinurhadia/998a942b560bd2418f94aecba204d32a to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @andsens andsens revised this gist Mar 6, 2013. 1 changed file with 21 additions and 18 deletions.
    39 changes: 21 additions & 18 deletions dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,30 +1,33 @@
    #!/bin/sh
    # backup each mysql db into a different file, rather than one big file
    # as with --all-databases - will make restores easier

    # To backup a single database simply add the db name as the first parameter

    # Putting it in /var/backups/mysql seems sensible... on a debian machine that is

    # Create a user for the task or simply run it as root
    # crontab entry - backup every night at 02:00
    ## backup each mysql db into a different file, rather than one big file
    ## as with --all-databases. This will make restores easier.
    ## To backup a single database simply add the db name as a parameter (or multiple dbs)

    ## Putting the script in /var/backups/mysql seems sensible... on a debian machine that is
    ## Create the user and directories
    # mkdir -p /var/backups/mysql/databases
    # useradd --home-dir /var/backups/mysql --gid backup --no-create-home mysql-backup
    ## Remember to make the script executable, and unreadable by others
    # chown -R mysql-backup:backup /var/backups/mysql
    # chmod u=rwx,g=rx,o= /var/backups/mysql/dump.sh

    ## crontab entry - backup every night at 02:00
    # sudo -u mysql-backup crontab -e
    # 0 2 * * * /var/backups/mysql/dump.sh

    # Create 'backup' mysql user
    ## Create 'backup' mysql user
    # CREATE USER 'backup'@'localhost' IDENTIFIED BY 's3cr3t';
    # GRANT EVENT, LOCK TABLES, PROCESS, REFERENCES, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER ON *.* TO 'backup'@'localhost' ;

    # Remember to make the script executable, and unreadable by others
    # chmod u=rwx,go= /var/backups/mysql/dump.sh


    USER="backup"
    PASSWORD="s3cr3t"
    OUTPUTDIR=$(dirname $0)"/databases"
    MYSQLDUMP="/usr/bin/mysqldump"
    MYSQL="/usr/bin/mysql"



    if [ -z "$1" ]; then
    databases=`$MYSQL --user=$USER --password=$PASSWORD --batch --skip-column-names -e "SHOW DATABASES;" | grep -v 'mysql\|information_schema'`
    for database in $databases; do
    @@ -48,4 +51,4 @@ else
    --databases $database \
    --result-file="$OUTPUTDIR/$database.sql"
    done
    fi
    fi
  2. @andsens andsens revised this gist Nov 30, 2012. 1 changed file with 19 additions and 4 deletions.
    23 changes: 19 additions & 4 deletions dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -2,6 +2,8 @@
    # backup each mysql db into a different file, rather than one big file
    # as with --all-databases - will make restores easier

    # To backup a single database simply add the db name as the first parameter

    # Putting it in /var/backups/mysql seems sensible... on a debian machine that is

    # Create a user for the task or simply run it as root
    @@ -22,15 +24,28 @@ MYSQLDUMP="/usr/bin/mysqldump"
    MYSQL="/usr/bin/mysql"


    databases=`$MYSQL --user=$USER --password=$PASSWORD --batch --skip-column-names -e "SHOW DATABASES;" | grep -v 'mysql\|information_schema'`

    for database in $databases; do
    $MYSQLDUMP \
    if [ -z "$1" ]; then
    databases=`$MYSQL --user=$USER --password=$PASSWORD --batch --skip-column-names -e "SHOW DATABASES;" | grep -v 'mysql\|information_schema'`
    for database in $databases; do
    $MYSQLDUMP \
    --user=$USER --password=$PASSWORD \
    --force \
    --quote-names --dump-date \
    --opt --single-transaction \
    --events --routines --triggers \
    --databases $database \
    --result-file="$OUTPUTDIR/$database.sql"
    done
    else
    for database in ${@}; do
    $MYSQLDUMP \
    --user=$USER --password=$PASSWORD \
    --force \
    --quote-names --dump-date \
    --opt --single-transaction \
    --events --routines --triggers \
    --databases $database \
    --result-file="$OUTPUTDIR/$database.sql"
    done
    done
    fi
  3. @andsens andsens revised this gist Sep 17, 2012. 1 changed file with 2 additions and 3 deletions.
    5 changes: 2 additions & 3 deletions dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -12,9 +12,8 @@
    # CREATE USER 'backup'@'localhost' IDENTIFIED BY 's3cr3t';
    # GRANT EVENT, LOCK TABLES, PROCESS, REFERENCES, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER ON *.* TO 'backup'@'localhost' ;

    # Remember to make the script executable
    # chmod +x /var/backups/mysql/dump.sh

    # Remember to make the script executable, and unreadable by others
    # chmod u=rwx,go= /var/backups/mysql/dump.sh

    USER="backup"
    PASSWORD="s3cr3t"
  4. @andsens andsens revised this gist Sep 17, 2012. 1 changed file with 5 additions and 0 deletions.
    5 changes: 5 additions & 0 deletions dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -2,6 +2,8 @@
    # backup each mysql db into a different file, rather than one big file
    # as with --all-databases - will make restores easier

    # Putting it in /var/backups/mysql seems sensible... on a debian machine that is

    # Create a user for the task or simply run it as root
    # crontab entry - backup every night at 02:00
    # 0 2 * * * /var/backups/mysql/dump.sh
    @@ -10,6 +12,9 @@
    # CREATE USER 'backup'@'localhost' IDENTIFIED BY 's3cr3t';
    # GRANT EVENT, LOCK TABLES, PROCESS, REFERENCES, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER ON *.* TO 'backup'@'localhost' ;

    # Remember to make the script executable
    # chmod +x /var/backups/mysql/dump.sh


    USER="backup"
    PASSWORD="s3cr3t"
  5. @andsens andsens revised this gist Sep 17, 2012. 1 changed file with 10 additions and 0 deletions.
    10 changes: 10 additions & 0 deletions dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,16 @@
    #!/bin/sh
    # backup each mysql db into a different file, rather than one big file
    # as with --all-databases - will make restores easier

    # Create a user for the task or simply run it as root
    # crontab entry - backup every night at 02:00
    # 0 2 * * * /var/backups/mysql/dump.sh

    # Create 'backup' mysql user
    # CREATE USER 'backup'@'localhost' IDENTIFIED BY 's3cr3t';
    # GRANT EVENT, LOCK TABLES, PROCESS, REFERENCES, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER ON *.* TO 'backup'@'localhost' ;


    USER="backup"
    PASSWORD="s3cr3t"
    OUTPUTDIR=$(dirname $0)"/databases"
  6. @andsens andsens revised this gist Sep 17, 2012. 1 changed file with 7 additions and 7 deletions.
    14 changes: 7 additions & 7 deletions dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -12,11 +12,11 @@ databases=`$MYSQL --user=$USER --password=$PASSWORD --batch --skip-column-names

    for database in $databases; do
    $MYSQLDUMP \
    --user=$USER --password=$PASSWORD \
    --force \
    --quote-names --dump-date \
    --opt --single-transaction \
    --events --routines --triggers \
    --databases $database \
    --result-file="$OUTPUTDIR/$database.sql"
    --user=$USER --password=$PASSWORD \
    --force \
    --quote-names --dump-date \
    --opt --single-transaction \
    --events --routines --triggers \
    --databases $database \
    --result-file="$OUTPUTDIR/$database.sql"
    done
  7. @andsens andsens revised this gist Sep 17, 2012. 1 changed file with 0 additions and 1 deletion.
    1 change: 0 additions & 1 deletion dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,3 @@

    #!/bin/sh
    # backup each mysql db into a different file, rather than one big file
    # as with --all-databases - will make restores easier
  8. @andsens andsens created this gist Sep 17, 2012.
    23 changes: 23 additions & 0 deletions dump.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,23 @@

    #!/bin/sh
    # backup each mysql db into a different file, rather than one big file
    # as with --all-databases - will make restores easier
    USER="backup"
    PASSWORD="s3cr3t"
    OUTPUTDIR=$(dirname $0)"/databases"
    MYSQLDUMP="/usr/bin/mysqldump"
    MYSQL="/usr/bin/mysql"


    databases=`$MYSQL --user=$USER --password=$PASSWORD --batch --skip-column-names -e "SHOW DATABASES;" | grep -v 'mysql\|information_schema'`

    for database in $databases; do
    $MYSQLDUMP \
    --user=$USER --password=$PASSWORD \
    --force \
    --quote-names --dump-date \
    --opt --single-transaction \
    --events --routines --triggers \
    --databases $database \
    --result-file="$OUTPUTDIR/$database.sql"
    done