Skip to content

Instantly share code, notes, and snippets.

@tony4d

tony4d/mysqldump-backup.sh

Last active June 11, 2019 03:28
Download ZIP

Revisions

  1. tony4d revised this gist Aug 23, 2016. No changes.
  2. tony4d revised this gist Aug 23, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion mysqldump-backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -20,5 +20,5 @@ find $DB_BACKUP_DIR_ROOT/ -maxdepth 1 -type d -mtime +1 -exec rm -rf {} \;

    # Backup each db in the server, skip schema dbs though
    for db in $(mysql --login-path=local-backup -Bse 'show databases'|egrep -vi 'information_schema|performance_schema');
    do mysqldump --login-path=local-backup -xQce --master-data=2 --max-allowed-packet=1024M -B $db | gzip > "$DB_BACKUP_DIR_TODAY/$HN-$db-$(date +%Y-%m-%dT%H:%M:%S).sql.gz";
    do mysqldump --login-path=local-backup -xQce -R --master-data=2 --max-allowed-packet=1024M -B $db | gzip > "$DB_BACKUP_DIR_TODAY/$HN-$db-$(date +%Y-%m-%dT%H:%M:%S).sql.gz";
    done
  3. tony4d revised this gist Feb 26, 2014. 1 changed file with 8 additions and 3 deletions.
    11 changes: 8 additions & 3 deletions mysqldump-backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,13 @@
    #!/bin/bash

    # No username or passwords in this script, you should use mysql_config_editor
    # to store it securely. The login-path in this script is set to "local-backup" so when you create
    # your .mylogin.cnf with the mysql-config-editor make sure it is set the same
    # See http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html
    # An example to create your config for a mysql user "backup":
    # shell> sudo mysql_config_editor set --login-path=local-backup --host=localhost --user=backup --password
    # The backup user in the mysql server needs these privileges: SELECT, RELOAD, SHOW DATABASES, REPLICATION CLIENT

    DB_BACKUP_DIR_ROOT="/opt/mysql-backups"
    DB_BACKUP_DIR_TODAY="$DB_BACKUP_DIR_ROOT/`date +%Y-%m-%d`"
    HN=`hostname | awk -F. '{print $1}'`
    @@ -11,9 +19,6 @@ mkdir -p $DB_BACKUP_DIR_TODAY
    find $DB_BACKUP_DIR_ROOT/ -maxdepth 1 -type d -mtime +1 -exec rm -rf {} \;

    # Backup each db in the server, skip schema dbs though
    # Note, no username or password in this script, you should use mysql_config_editor to store it securely
    # The login-path is set as "local-backup" so when you create your .mylogin.cnf with the mysql-config-editor make sure it is set the same
    # See http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html
    for db in $(mysql --login-path=local-backup -Bse 'show databases'|egrep -vi 'information_schema|performance_schema');
    do mysqldump --login-path=local-backup -xQce --master-data=2 --max-allowed-packet=1024M -B $db | gzip > "$DB_BACKUP_DIR_TODAY/$HN-$db-$(date +%Y-%m-%dT%H:%M:%S).sql.gz";
    done
  4. tony4d revised this gist Feb 26, 2014. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion mysqldump-backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -11,7 +11,8 @@ mkdir -p $DB_BACKUP_DIR_TODAY
    find $DB_BACKUP_DIR_ROOT/ -maxdepth 1 -type d -mtime +1 -exec rm -rf {} \;

    # Backup each db in the server, skip schema dbs though
    # Note, no username or password in this script, you should use mysql_config_editor to store is securely
    # Note, no username or password in this script, you should use mysql_config_editor to store it securely
    # The login-path is set as "local-backup" so when you create your .mylogin.cnf with the mysql-config-editor make sure it is set the same
    # See http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html
    for db in $(mysql --login-path=local-backup -Bse 'show databases'|egrep -vi 'information_schema|performance_schema');
    do mysqldump --login-path=local-backup -xQce --master-data=2 --max-allowed-packet=1024M -B $db | gzip > "$DB_BACKUP_DIR_TODAY/$HN-$db-$(date +%Y-%m-%dT%H:%M:%S).sql.gz";
  5. tony4d created this gist Jul 20, 2013.
    18 changes: 18 additions & 0 deletions mysqldump-backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,18 @@
    #!/bin/bash

    DB_BACKUP_DIR_ROOT="/opt/mysql-backups"
    DB_BACKUP_DIR_TODAY="$DB_BACKUP_DIR_ROOT/`date +%Y-%m-%d`"
    HN=`hostname | awk -F. '{print $1}'`

    # Create the backup directory
    mkdir -p $DB_BACKUP_DIR_TODAY

    # Remove backups older than 1 day
    find $DB_BACKUP_DIR_ROOT/ -maxdepth 1 -type d -mtime +1 -exec rm -rf {} \;

    # Backup each db in the server, skip schema dbs though
    # Note, no username or password in this script, you should use mysql_config_editor to store is securely
    # See http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html
    for db in $(mysql --login-path=local-backup -Bse 'show databases'|egrep -vi 'information_schema|performance_schema');
    do mysqldump --login-path=local-backup -xQce --master-data=2 --max-allowed-packet=1024M -B $db | gzip > "$DB_BACKUP_DIR_TODAY/$HN-$db-$(date +%Y-%m-%dT%H:%M:%S).sql.gz";
    done