mysql备份数据(mysql备份数据库的sql语句)

由于mysql存在多种数据库备份方式，而且各有利弊,个人觉得，首先要基于公司的需求，考虑能够容忍丢失多少数据、花多少人力时间成本等，这是我们制定备份方案的依据，同时制定出来的方案要可执行，要执行，不能把方案当作纸上谈兵。下面我把我们实际的备份方案整理出来。

作为数据安全的一个重要内容——数据备份的重要性往往被人们所忽视。只要发生数据传输、数据存储和数据交换，就有可能产生数据故障。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，有时造成的损失是无法弥补与估量的。结合我们前公司线上业务的实际情况，我们之前的备份方案，主要采取全备+binlog备份方式。其中全备分为逻辑备份+物理备份，同时主从复制也作为一种备份的方式存在，从而最大程度降低数据故障带来的风险。

· 应用场景

逻辑备份，我们主要用在当数据量较小时，数据库出现数据故障，对于恢复时间要求不高；搭建主从环境，搭建测试环境及备用库等方面。

· 备份时间及地点

每日凌晨3:00在从库上备份，备份文件存放在从库上的/data/back/mysql/

，当然如果有充足的机器，更安全的方式是备份到远程服务器。

· 备份方式

采用mysqldump进行全库备份，通过定时任务，定时执行shell备份脚本。

1）全备脚本如下：

#!/bin/bash

bakdir=/data/back/mysql/full/

datetime=`date +%Y%m%d%H%M%S`

ur=root

password=123456789

mysqldump=/usr/bin/mysqldump

echo "`date` 开始全备" >>$bakdir/bak.log

echo "------show master status--------------" >>$bakdir/bak.log

echo "`mysql -u${ur} -p${password} -e 'show master status;'` " >>$bakdir/bak.log

$mysqldump -u${ur} -p${password} -B -A --single-transaction --flush-logs --master-data=2 --events |gzip > ${bakdir}/all-${datetime}.sql.gz

if [ $? -eq 0 ];then

echo "`date` 全备成功" >>$bakdir/bak.log

el

echo "`date` 全备失败" >>$bakdir/bak.log

# echo "`date` 全备失败" |mail -s "`date` 全备失败" 邮箱地址参数值

fi

2）增量备份全库脚本

#!/bin/bash

# u cp to backup mysql data everyday!

BakDir=/data/back/mysql/zl

BinDir=/var/lib/mysql/

LogFile=/data/back/mysql/zl/bak.log

BinFile=/var/lib/mysql/mysql-bin.index

mysqladmin -uroot -p123456789 flush-logs

#这个是用于产生新的mysql-bin.00000*文件

Counter=`wc -l $BinFile |awk '{print $1}'`

NextNum=0

#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的

for file in `cat $BinFile`

do

ba=`baname $file`

#baname用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./

NextNum=`expr $NextNum + 1`

if [ $NextNum -eq $Counter ]

then

echo $ba skip! >> $LogFile

el

dest=$BakDir/$ba

if(test -e $dest)

#test -e用于检测目标文件是否存在，存在就写exist!到$LogFile去

then

echo $ba exist! >> $LogFile

el

cp $BinDir/$ba $BakDir

echo $ba copying >> $LogFile

fi

fi

done

echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $LogFile

3）定时任务

每个星期日凌晨3:00执行完全备份脚本

0 3 * * 0 /bin/bash /data/script/msyql_bak/mysql_full_bak.sh >/dev/null 2>&1

#周一到周六凌晨3:00做增量备份

0 3 * * 1-6 /bin/bash /data/script/msyql_bak/mysql_zl_bak.sh >/dev/null 2>&1

· 应用场景

主要应对要求恢复时间较高；数据量比较大；

· 备份时间及地点

每周一凌晨3:00在主库上备份。备份文件存放远程服务器目录下

· 备份方式

采用percona的社区工具innobackupex，该工具可以在线热备，不影响线上的业务。

以上两种方式的备份只能恢复某段时间的数据，对于按照时间点的恢复是无能为力的，那怎么办呢？binlog日志，是的，我们采取的是实时同步binlog日志到远程服务器上，这样理论上是可以恢复到任意时间点的。

· 应用场景

对于一些由于错误操作等造成数据丢失错误的，需要按照时间点进行还原的情况下。

· 备份时间及地点

备份服务器实时将主库上binlog同步到远程服务器上。

· 备份方式

mysqlbinlog工具进行日志拉取，shell脚本如下：

mysqlbinlog --read-from-remote-rver --host=1.1.1.1 --port=3306 --ur="backup" --password="backup" --raw --stop-never mysql-bin.000840 --result-file=/data/backup/binlog/

经过以上三种结合的备份方式，基本上可以满足在数据异常丢失情况下，恢复到正常状态。

· 应用场景

主要应用于读写分离，故障转移的情况下

· 备份时间及地点

几乎可以认为是同步进行数据的复制

· 备份方式

采用mysql提供的复制技术

对于主从复制，如果用于备库的话，最好是让sql_thread执行慢一段时间，可以是1天。这个结合实际情况，自己选择。

备份文件有了之后还需要对其定期的进行恢复测试，不然可能是白忙一场。因为很多情况下，有些备份文件可能已经损坏。当我们遇到数据丢失故障时，在紧急关头，竟然发现备份的文件无法恢复或者数据一致性和完整性没有达到要求，如果我们定期的对备份文件进行恢复测试，这种悲剧可能就不会发生。

1 恢复时间及地点

每周进行一次恢复测试，主要在测试机上进行

2 恢复方式

模拟某个时间点主机数据全部丢失，要求恢复到丢失时间点的所有数据，先进行全备恢复，然后根据binlog恢复到最近时间点。