MySQL 定时备份

众所周知MySQL数据库在互联网服务中提供的重要的角色，所以他的稳定性至关重要所以对于数据的备份也是非常重要的，下面介绍一下：

• mysqldump
• xtrabackup之innobackupex
• shell脚本
• crontab之自动执行备份

mysqldump

bin/mysqldump 是MySQL提供的内置命令，方便使用者导出mysql数据表以及数据，具体的使用方式查看 bin/mysqldump --help, 这里我们简单介绍几个命令

1. bin/mysqldump --opt -uroot -p --databases db1 db2 db3 > /tmp/db.sql

opt Same as
–add-drop-table,
–add-locks,
–create-options,
–quick,
–extended-insert,
–lock-tables,
–set-charset,
and –disable-keys.
Enabled by default, disable with –skip-opt.

2. 还原mysql备份内容

2.1 在系统命令行中，输入如下实现还原：

1

bin/mysql -uroot -p123456 < /tmp/db.sql

2.2 在登录进入mysql系统中,通过source指令找到对应系统中的文件进行还原：

1

mysql> source /tmp/db.sql;

Xtrabackup

Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品。它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）。XtraBackup支持所有的Percona Server、MySQL、MariaDB和Drizzle。

1. Xtrabackup 特点

• 备份过程快速、可靠
• 备份过程不会打断正在执行的事务
• 能够基于压缩等功能节约磁盘空间和流量
• 自动实现备份检验
• 还原速度快

2. Xtrabackup 安装

这里就不说了，网上很多教程，最简单 yum install percona-xtrabackup

备份shell脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

#!/bin/bash

name=root
host=192.168.0.1
DATE=`date +"%Y%m%d"`
allbackup="/home/dbback/hotbackup/"

###所有集合顺序上下一一对应吗，即vpwd[0]、vchk[0]、vsocket[0]、vport[0]代表一个数据库实例
#root用户密码集
vpwd=("")

#配置文件集
vchk=("/data/mysql-5.5.43/etc/my.cnf")

#socket集
vsocket=("/tmp/mysql.sock")

#设置端口集
vport=("3306")
 
###获取端口个数
k=${#vport[*]} 

if [ -d $allbackup ];
then
        echo "完全备份目录存在"
else
    echo "完全备份目录不存在，开始创建......."
    /bin/mkdir -p $allbackup
fi

###for循环，循环备份不同端口数据库
for ((l=0;l<$k;++l))

do
  
	if [ -d $allbackup/$DATE/$host/${vport[$l]} ];
	then
        	echo "完全备份目录存在"
	else
    		echo "完全备份目录不存在，开始创建......."
		/bin/mkdir -p $allbackup/$DATE/$host/${vport[$l]}
#fi
 
	find $allbackup -type d  -mtime +1 -exec rm -rf {} \;

	cd $allbackup/$DATE/$host/${vport[$l]}


	innobackupex --defaults-file=${vchk[$l]} --ibbackup=xtrabackup --socket=${vsocket[$l]}  --slave-info --user=root  --password=${vpwd[$l]} --no-timestamp --parallel=4 --throttle=300 --compress --compress-threads=8  ./ 2>bak.$DATE.log

	result=`cat bak.$DATE.log | grep "completed OK\!" | grep -v prints`

	coun=1

		if [[ -z $result ]];
		then
			if [[ $coun -lt 2 ]];
			then
				rm -rf $allbackup/$DATE/$host/${vport[$l]}

				/bin/mkdir -p $allbackup/$DATE/$host/${vport[$l]}

				cd $allbackup/$DATE/$host/${vport[$l]}

				innobackupex --defaults-file=${vchk[$l]} --ibbackup=xtrabackup --socket=${vsocket[$l]}  --slave-info --user=root  --password=${vpwd[$l]} --no-timestamp --parallel=4 --throttle=300 --compress --compress-threads=8  ./ 2>bak.$DATE.log

       				coun=`expr $coun + 1`;

				echo $coun
			fi
		else 
			rsync -zavP --password-file=/etc/rsync.pass --bwlimit=30000 $allbackup/$DATE dbback@192.168.1.1::KAPROBAK/DBBAK
			echo "备份文件推送成功！"
		fi

	fi
done

–defaults-file：指定my.cnf参数文件的位置[此配置文件里必须指定datadir]
–apply-log：同xtrabackup的–prepare参数,一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据 文件仍处理不一致状态。–apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
–copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
–remote-host=HOSTNAME： 通过ssh将备份数据存储到进程服务器上
–stream=[tar]：备份文件输出格式, 该文件可在XtarBackup binary文件中获得. 在使用参数stream=tar备份的时候,你的xtrabackup_logfile可能会临时放在/tmp目录下,如果你备份的时候并发写入较大的话,xtrabackup_logfile可能会很大(5G+),很可能会撑满你的/tmp目录,可以通过参数–tmpdir指定目录来解决这个问题.
–tmpdir=DIRECTORY：当有指定–remote-host or –stream时, 事务日志临时存储的目录, 默认采用MySQL配置文件中所指定的临时目录tmpdir
–redo-only –apply-log：强制备份日志时只redo,跳过rollback,这在做增量备份时非常必要
–use-memory=*：该参数在prepare的时候使用,控制prepare时innodb实例使用的内存
–databases=LIST：列出需要备份的databases,如果没有指定该参数,所有包含MyISAM和InnoDB表的database都会被备份
–slave-info：备份从库, 加上–slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于:CHANGE MASTER TO MASTER_LOG_FILE=’’, MASTER_LOG_POS=0
–socket=SOCKET：指定mysql.sock所在位置，以便备份进程登录mysql.

Linux 自动执行crontab

crontab 就不用多说了，如果有不明白的自行搜索

1
2

###数据库备份及备份文件推送至备份服务器
30 2 * * * /bin/bash /home/op/shell/mysqlhotbackup5.5.sh 2 > /home/op/log/mysqlhotbackup5.5.log