tony4d/mysqldump-backup.sh

## mysqldump-backup.sh
#!/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}'`

# 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
for db in $(mysql --login-path=local-backup -Bse 'show databases'|egrep -vi 'information_schema|performance_schema');
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
	#!/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}'`

	# 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
	for db in $(mysql --login-path=local-backup -Bse 'show databases'\|egrep -vi 'information_schema\|performance_schema');
	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