介绍

数据库备份有逻辑备份和物理备份
- 逻辑备份的结果为sql语句,适合所有的存储引擎
- 物理备份是对数据库的拷贝,对于内存表只备份结构

逻辑备份

mysqldump

常用语法

# 备份指定表
mysqldump [OPTIONS] database [table1 table2]
# 备份指定的数据库
mysqldump [OPTIONS]  --database [OPTIONS] [DB1 DB2]
# 备份全部的数据库
mysqldump [OPTIONS]  --all-database [OPTIONS]

mysqldump的常用命令

命令	作用
-u,–user=name	数据库用户名
-p,–password	数据库密码
–single-transaction	使用事务的方式备份,使得备份时数据库的一致性,只能应用于InnoDB存储引擎
-x,–lock-all-tables	使用锁表的方式确保数据库的一致性,应用于没有事务的存储引擎
-R,–routines	备份的时候包括存储过程
–triggers	备份的时候包括触发器
-E,–events	备份的时候包括调度事件
–hex-blob	解决部分数据在文本格式下不可见的问题
–tab	指定备份文件存储的位置
-w,–where=[‘过滤条件’]	导出指定条件的数据 ==只支持单表导出==
–master-data=[1/2]	选项赋值为2，那么CHANGE MASTER TO 语句会被写成一个SQL comment（注释），从而只提供信息; 选项赋值为1，那么语句不会被写成注释并且在dump被载入时生效。(==默认值为1==)

使用例子

1	mysqldump --master-data=2 --single-transaction --routines --triggers --events -u${USER} -p${PASSWD} -P${HostPort} ${database} > ${DATADIR}/${Environment}-${database}.sql

备份脚本

 #!/bin/bash
###############Basic parameters##########################
DAY=`date +%Y%m%d`
Environment=$(/sbin/ifconfig | grep "inet addr" | head -1 |grep -v "127.0.0.1" | awk '{print $2;}' | awk -F':' '{print $2;}')
USER="backup"
PASSWD="123456"
HostPort="3306"
# 数据库数据存储位置
MYSQLBASE="/home/mysql/"
# 备份的位置
DATADIR="/home/db_backup/${DAY}"
# 数据库程序位置
MYSQL=`/usr/bin/which mysql`
# 备份程序的位置
MYSQLDUMP=`/usr/bin/which mysqldump`
mkdir -p ${DATADIR}

Dump(){
 ${MYSQLDUMP} --master-data=2 --single-transaction  --routines --triggers --events -u${USER} -p${PASSWD} -P${HostPort} ${database}  > ${DATADIR}/${Environment}-${database}.sql
 cd ${DATADIR}
 gzip ${Environment}-${database}.sql
}
# not in则为排除不需要备份的库
for db in `echo "SELECT schema_name FROM information_schema.schemata where schema_name not in ('information_schema','sys','performance_schema')" | ${MYSQL} -u${USER} -p${PASSWD} --skip-column-names`
do
   database=${db}
   Dump
done

恢复数据到数据库

# 方法1
mysql -u -p [dbname] < backup.sql
# 方法2
mysql> source /tmp/backup.sql

实时备份binlog日志

mkdir /backup/binlog

cat backup_binlog.sh

#!/bin/sh
#指定mysqlbinlog的位置
BACKUP_BIN=/usr/local/mysql/bin/mysqlbinlog
#指定将远程binlog备份到本地的位置
LOCAL_BACKUP_DIR=/backup/binlog/
#指定备份binlog时的日志
BACKUP_LOG=/backup/binlog/backup.log
#指定要备份哪台binlog的ip 
REMOTE_HOST=192.168.1.248
REMOTE_PORT=3306
REMOTE_USER=repl
REMOTE_PASS=repl
FIRST_BINLOG=mysql-bin.000001


#time to wait before reconnecting after failure
SLEEP_SECONDS=10


##create local_backup_dir if necessary
mkdir -p ${LOCAL_BACKUP_DIR}
cd ${LOCAL_BACKUP_DIR}


## 运行while循环，连接断开后等待指定时间，重新连接
while :
do
if [ `ls -A "${LOCAL_BACKUP_DIR}" |wc -l` -eq 0 ];then
    LAST_FILE=${FIRST_BINLOG}
else
LAST_FILE=`ls -l ${LOCAL_BACKUP_DIR} | grep -v backup.log |tail -n 1 |awk '{print $9}'`
 fi
#${BACKUP_BIN} --raw --read-from-remote-server --stop-never --host=${REMOTE_HOST} --port=${REMOTE_PORT} --user=${REMOTE_USER} --password=${REMOTE_PASS} ${LAST_FILE}


/usr/local/mysql/bin/mysqlbinlog  --raw --read-from-remote-server --stop-never --host=${REMOTE_HOST} --port=${REMOTE_PORT} --user=${REMOTE_USER} --password=${REMOTE_PASS} ${LAST_FILE}
echo "`date +"%Y/%m/%d %H:%M:%S"` mysqlbinlog停止，返回代码：$?" | tee -a ${BACKUP_LOG} 
echo "${SLEEP_SECONDS}秒后再次连接并继续备份" | tee -a ${BACKUP_LOG}  
sleep ${SLEEP_SECONDS}
done

后台运行该备份脚本
nohup sh  backup_binlog.sh &

物理备份

xtrabackup

介绍
- 用于在线备份innodb存储引擎表
- 备份过程中,不会影响表的读写操作
- 只会备份表数据,不会备份表结构
- innobackupex是对xtrabackup的封装并提供MyISAM表的备份功能(但是会锁表)

安装xtrabackup

1	https://hub.docker.com/r/perconalab/percona-xtrabackup

利用innobackupex进行全备

1	innobackupex --user=root --password=pwd --paraller=2 /home/db_backup/

利用innobackupex进行全备恢复

1	innobackupex --apply-log /path/to/BACKUP-DIR

利用innobackupex进行增量备份

innobackupex --user=root --password=pwd --paraller=2 /backups

innobackupex --user=root --password=pwd \
--incremental /home/db_backup/ \
--incremental-basedir=/home/db_backup/back-dir

innobackupex --user=root --password=pwd \
--incremental /home/db_backup/ \
--incremental-basedir=[上一次增量备份的目录]

利用innobackupex进行增量恢复

innobackupex --apply-log --redo-only 全备目录

innobackupex --apply-log --redo-only 全备目录 \
--incremental-dir=第一次增量备份目录