1 | 全库备份 | mysqlbackup -uroot -p --protocol=TCP --port=3322 --datadir=/test/mymon/data/mysqldata/mymon/ --backup-dir=/test/mymon/rdbms/mpmymon/tmp/backup0 backup-and-apply-log |
2 | 增量备份 | mysqlbackup -uroot -p --protocol=TCP --port=3322 --datadir=/test/mymon/data/mysqldata/mymon/ --incremental --incremental-backup-dir=/test/mymon/rdbms/mpmymon/tmp/backup1 --incremental-base=dir:/test/mymon/rdbms/mpmymon/tmp/backup0 backup |
9 | 应用增备到全备 | mysqlbackup -uroot -p --protocol=TCP --port=3322 --backup-dir=/test/mymon/rdbms/mpmymon/tmp/backup0 --incremental-backup-dir=/test/mymon/rdbms/mpmymon/tmp/backup1 apply-incremental-backup |
10 | 全库恢复 | mysqlbackup --defaults-file=/data01/myhome/mysql/my.cnf --backup-dir=/test/mymon/rdbms/mpmymon/tmp/backup0 |
#backup_mysql.sh
#!/bin/sh
export PATH=$PATH:/test/mysql/base/bin
hostname=`hostname -s | tr -s 'A-Z' 'a-z'`
full_backup ()
{
if [ -d full ]
then
mv full full_bk
fi
mysqlbackup --login-path=root \
--socket=/test/my${PORT}/var/mysql.sock \
--backup-dir=/test/myback/$hostname/${PORT}/full \
--backup-image=/test/myback/$hostname/${PORT}/full/mybackup.mbi \
--compress backup-to-image >full_backup_last.log 2>&1
cat full_backup_last.log >> full_backup.log
FULL_OK=`tail -1 full_backup_last.log |grep "mysqlbackup completed OK" |wc -l`
if [ $FULL_OK -eq 1 ]
then
rm -rf full_bk
echo `date` >> full_backup.log >> full_backup.log
echo " delete old_full_backup !" >> full_backup.log
else
rm -rf full
mv full_bk full
echo `date` >> full_backup.log >> full_backup.log
echo " backup error delete this full_backup,please check the full backup !!!" >> full_backup.log
fi
}
incr_backup ()
{
if [ -d incr ]
then
mv incr incr_bk
fi
if [ -d full ]
then
mysqlbackup --login-path=root --socket=/test/my${PORT}/var/mysql.sock --incremental --incremental-backup-dir=/test/myback/$hostname/${PORT}/incr --incremental-base=dir:full backup > incr_backup_last.log 2>&1
cat incr_backup_last.log >> incr_backup.log
INCR_OK=`tail -1 incr_backup_last.log |grep "mysqlbackup completed OK" |wc -l`
if [ $INCR_OK -eq 1 ]
then
rm -rf incr_bk
echo `date` >> incr_backup.log >> incr_backup.log
echo " delete old_incr_backup !" >> incr_backup.log
else
rm -rf incr
echo `date` >> incr_backup.log
echo " backup error delete this incr_backup,please check the incremental backup !!!" >> incr_backup.log
fi
else
echo "There was no full backup found, incr_backup fail,please check!" >> incr_backup.log
fi
}
binlog_backup()
{
BINLOG_DIR=/test/my$PORT/log
mkdir -p binlog
mkdir -p .conf
echo `date` >> binlog_backup.log
echo "Begin backup binlog " >> binlog_backup.log
find binlog/* -name "mysql-bin.*" |grep -vw "mysql-bin.index" |awk -F"/" '{print $NF}'> .conf/old
find $BINLOG_DIR/* -name "mysql-bin.*" |grep -vw "mysql-bin.index" |sed '$d'|awk -F"/" '{print $NF}' > .conf/new
FILETODO=`diff .conf/old .conf/new|grep ">"|awk '{print $2}'`
for loop in `echo $FILETODO`
do
name=$loop
cat $BINLOG_DIR/$name|gzip > /test/myback/$hostname/${PORT}/binlog/$name.gz
if [ $? -eq 0 ]
then
echo "backup $name sucessful... " >> binlog_backup.log
else
echo "backup $name fail... " >> binlog_backup.log
fi
done
echo `date` >> binlog_backup.log
echo "End backup binlog " >> binlog_backup.log
}
binlog_purge()
{
date >> purge_files.log
find /test/myback/$hostname/${PORT}/binlog/* -name "mysql-bin.*" -mtime +6 | xargs rm
}
PORT_SET=`ps -ef|grep -w mysqld|grep -vw grep|awk '{print $NF}'|awk -F= '{print $NF}'`
MYSQL=/test/mysql/base/bin/mysql
WEEK_DAY=`date +%w`
for PORT in $PORT_SET
do
mkdir -p /test/myback/$hostname/${PORT}/binlog
cd /test/myback/$hostname/${PORT}
READ_ONLY=`$MYSQL --login-path=root --socket=/test/my${PORT}/var/mysql.sock -Ns -e "select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES where VARIABLE_NAME='READ_ONLY';"`
if [ $READ_ONLY = 'OFF' ]
then
continue
fi
if [ $WEEK_DAY = 6 ] || [ ! -d full ]
then
echo "need full bk"
full_backup
binlog_backup
binlog_purge
else
echo "need incr bk"
incr_backup
binlog_backup
binlog_purge
fi
done
exit 0