MySQL Backup

Полный дамп MySQL — mysql_full_backup.

#!/bin/bash

sqldump_path=`which mysqldump`
backup_dir='/backup/mysql/full'
mkdir -p $backup_dir
mysql_user="root"
mysql_password="password"

backupfile='mysql_full_backup'`date +%Y%m%d`'.sql'

rm -f $backup_dir/*
$sqldump_path -u $mysql_user --password=$mysql_password --events --flush-logs --single-transaction --master-data=2 --all-databases | gzip > $backup_dir/$backupfile.gz

Инкрементальный бекап bin-log’s — mysql_incr_backup.

#!/bin/bash

backupdir="/backup/mysql/full"
binlogdir="/var/log/mysql"
binlogindex="mysql-bin.index"

mysql_user="root"
mysql_password="password"

sql_adm_path=`which mysqladmin`

#flush logs

$sql_adm_path -u $mysql_user --password=$mysql_password flush-logs

#Determine current log file

cur_log_file=`tail -n 1 $binlogdir\/$binlogindex | sed 's/^.*\///g'`
cp_file_list=`egrep -v $cur_log_file $binlogdir\/$binlogindex | sed 's/^.*\///g'`
cur_dir=`pwd`

cd $binlogdir
cp $cp_file_list $backupdir

echo "PURGE BINARY LOGS TO '$cur_log_file';" | mysql -u $mysql_user --password=$mysql_password
cd $cur_dir

Дамп всех БД по отдельности — mysql_db_backup.

#!/bin/bash
## Main Environment
date=`date +%F_%R:%S`
host=`hostname -f`

## MySQL Environment
mysqldump=`which mysqldump`
mysql=`which mysql`
mysql_user="root"
mysql_password="password"

## DB backup Environment
databases=`$mysql --user=$mysql_user --password=$mysql_password -e 'show databases' -s --skip-column-names`

for database in $databases; do
backup_dir='/backup/mysql/'$host'/'$database''
backupfile=''$database'_'$date'.sql'
mkdir -p $backup_dir
$mysqldump -u $mysql_user --password=$mysql_password --events --flush-logs --single-transaction --master-data=2 --databases $database | gzip > $backup_dir/$backupfile.gz
find $backup_dir -type f -mtime +14 -exec rm {} \;
done

Crontab

# MySQL backup
0 20 * * 5 /usr/local/scripts/mysql_full_backup >/dev/zero
0 20 * * 1-4,6,7 /usr/local/scripts/mysql_incr_backup >/dev/zero
30 20 * * 3 /usr/local/scripts/mysql_db_backup >/dev/zero