- mysqldump备份
代码语言：javascript复制Mysqldump是mysql自带工具。备份出来的文件是一个可以直接倒入的sql脚本。该sql文件中实际上包含了多个CREATE 和INSERT语句，使用这些语句可以重新创建表和插入数据。
#!/bin/bash
aiserver_bak()
{
user="root"
passwd="xxxx"
db_name="ai_server"
backup_path="/data/mysql-backup/ai_server"
date=$(date "%Y%m%d%H%M%S")
umask 177
/usr/bin/mysqldump -u${user} -p${passwd} ${db_name} |gzip > ${backup_path}/${db_name}_mysql_${date}.sql.gz
find ${backup_path} -name "mysql_*.sql.gz" -type f -mtime 10 -exec rm -rf {} ;
}
aiserver_bak- mysqlbackup备份
代码语言：javascript复制Mysqlbackup是物理备份的方式。对innodb的表空间进行物理复制，但是，它是记录LSN点的。在备份过程中，新增加的输入直接写入备份文件的ibbackup_logfile中。同时记录最后的LSN点。还原的时候，检测对比ibbackup_logfile文件里面与表空间里面的差值，使ibbackup_logfile里面的数据进入事务日志或表空间。
#!/bin/bash
#热备，备份数据库目录
BACKUP_DIR=/var/backups/mysql/backups
BACKUP_PASS=xxx
BACKUP_USER=root
DATE_DAY=$(date "%Y-%m-%d")
DATE_HOUR=$(date "%H")
#EMAIL_RECIPIENT=dba_zhang@163.com
#/usr/local/mysql/bin/mysqlbackup --port=3306 --protocol=tcp --user=$BACKUP_USER --password=$BACKUP_PASS --with-timestamp --backup-dir=$BACKUP_DIR backup-and-apply-log
/var/lib/mysql/meb/meb-4.1.3-linux-glibc2.12-x86-64bit/bin/mysqlbackup --port=3306 --protocol=tcp --user=$BACKUP_USER --password=$BACKUP_PASS --with-timestamp --backup-dir=$BACKUP_DIR --compress --compress-level=9 backup-and-apply-log
sleep 100s
tar -zcvf ${BACKUP_DIR}/meb-bk${DATE_DAY}${DATE_HOUR}.tar.gz ${BACKUP_DIR}/${DATE_DAY}*
sleep 100s
rm -rf ${BACKUP_DIR}/${DATE_DAY}*
NO_OF_COMPLETE_OK_MESSAGES=$(cat $BACKUP_DIR/${DATE_DAY}_${DATE_HOUR}*/meta/MEB_${DATE_DAY}.${DATE_HOUR}*.log | grep "mysqlbackup completed OK" | wc -l)
# Note that the string "mysqlbackup completed OK" must occur 2 times in the log in order for the backup to be OK
if [ $NO_OF_COMPLETE_OK_MESSAGES -eq 2 ]; then
# Backup successful, find backup directory
echo "Backup succeeded"
exit 0
#else
# echo "MySQL backup failed, please check logfile" | mail -s "ERROR: MySQL Backup Failed!" ${EMAIL_RECIPIENT}
# exit 1
fi
find $BACKUP_DIR -mtime 17 -exec rm -f {} ;- innobackupex备份
代码语言：javascript复制Percona XtraBackup是一款基于MySQL的热备份的开源实用程序，它可以备份5.1到5.7版本上InnoDB,XtraDB,MyISAM存储引擎的表， Xtrabackup有两个主要的工具：xtrabackup、innobackupex 。 （1）xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表 （2）innobackupex则封装了xtrabackup，是一个脚本封装，所以能同时备份处理innodb和myisam，但在处理myisam时需要加一个读锁。
#!/bin/bash
#### -- default value -- ####
# mysql参数
MYSQL_CNF=/etc/my.cnf
MYSQL_USER="mysqlbackup"
MYSQL_PASS="xxx"
MYSQL_HOST='localhost'
# 备份参数
logtime=`date %x' '%T`
fullbase=/data/mysql_master_backup/full
logbase=/data/mysql_master_backup/logs
#DBlist=`mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "show databases" | egrep -v $exclude`
innobackupex=/usr/bin/innobackupex
#### -- 目录创建 -- ####
[ -d ${fullbase} ] || mkdir -p ${fullbase}
[ -d ${logbase} ] || mkdir -p ${logbase}
####备份时间、大小记录
log_file="/data/mysql_master_backup/logs/backup_record_"$(date %Y%m%d%H%M%S)".log"
#### -- main funcation -- ####
# 全备
_FULLBackup()
{
dtime=`date %Y-%m-%d`
$innobackupex --defaults-file=$MYSQL_CNF --user=$MYSQL_USER --password=$MYSQL_PASS --host=$MYSQL_HOST $fullbase > $logbase/${dtime}.log 2>&1
result=$?
if [ $result -eq 0 ];then
bkbase=`ls -d ${fullbase}/${dtime}* | sort -r | head -n 1`
bksize=`du -sh ${bkbase} | awk '{print $1}'`
echo -e "[$logtime]tStatus: successful, Backdir: $bkbase, size: $bksize" | tee -a ${logbase}/full.log
else
echo -e "[$logtime]tStatus: failed, Reason: check you log $logbase/${dtime}.log" | tee -a ${logbase}/backup.log
fi
}
# run
backup_timer_start=`date " %Y-%m-%d %H:%M:%S"`
echo "backup_timer_start : $backup_timer_start" >>$log_file
_FULLBackup
backup_timer_end=`date " %Y-%m-%d %H:%M:%S"`
echo "backup_timer_end : $backup_timer_end" >>$log_file
backup_duration=`echo $(($(date %s -d "${backup_timer_end}") - $(date %s -d "${backup_timer_start}"))) | awk '{t=split("60 s 60 m 24 h 999 d",a);for(n=1;n<t;n =2){if($1==0)break;s=$1%a[n]a[n 1] s;$1=int($1/a[n])}print s}'`
echo "total backup time : $backup_duration" >>$log_file
