mysql数据库备份的三种方式

章工运维

发布于 2023-05-19 17:43:25

6090

发布于 2023-05-19 17:43:25

mysqldump备份

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备份

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备份

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

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2023-01-04，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库