mysql数据库备份

mysql数据库备份脚本

全备份，一天备份一次，备份三个包，每天覆盖一个包
0 1 2三个包
0 0->1 1->2 2-> null

-set-gtid-purged=OFF 

mysqldump加了 -set-gtid-purged=OFF 这个参数，会记录在binlog日志，如果不加，就不会记录在binlog日志，有主从数据库的备份，必须加这个参数，否则在主数据库上导入恢复了数据，主数据库上没有写入到binlog日志，从数据库就不会被同步。就会造成数据丢失

--single_transaction

–single-transaction参数的作用，设置事务的隔离级别为可重复读，即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响，在这期间不会锁表。

#!/bin/bash
#
# Last updated: pzs
# ----------------------------------------------------------------------
# mysql backup scripts
# make by sunner 
# ----------------------------------------------------------------------
# your mysql login information
# db_user is mysql username
# db_passwd is mysql password
# db_host is mysql host
#
# keep times  is 3 [0 1 2]
# -----------------------------

db_user=user
db_passwd=password
db_host=localhost

# the directory for story your backup file.
backup_dir=/data/backup

# date format for backup file (yyyymmdd hhmiss)
time=`date +%Y-%m-%d%H%M%S`

# mysql, mysqldump and some other bin's path
MYSQL=/usr/local/mysql/bin/mysql
MYSQLDUMP=/usr/local/mysql/bin/mysqldump


# check the directory for store backup is writeable
test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0

# the directory for story the newest backup

for jnt in 0 1 2 
do
test ! -d  $backup_dir/backup.$jnt && mkdir -p  $backup_dir/backup.$jnt
test ! -z  $backup_dir/backup.$jnt/flag && touch $backup_dir/backup.$jnt/flag
done




# delete the oldest backup
rm -rf "$backup_dir/backup.2"
mkdir -p "$backup_dir/backup.2"

# rotate backup directory
for int in  1 0
do
if(test -d "$backup_dir"/backup."$int")
then
next_int=`expr $int + 1`
mv   /$backup_dir/backup.$int/*  /$backup_dir/backup.$next_int/
fi
done




# get all databases
#all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"
#for db in $all_db
#do
#$MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db | gzip -6 > "$backup_dir/backup.0/$db.$time.sql.gz"
#done

#specfy the database backup
base_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -S /data/mysql/mysql.sock -Bse 'show databases' | grep -v "information_schema" | grep -v "performance_schema")"
for db in $base_db
do
$MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db --set-gtid-purged=OFF --single_transaction -S /data/mysql/mysql.sock | gzip -8 > "$backup_dir/backup.0/$db.$time.sql.gz"
done


exit 0;