MySQL备份与恢复（一）

cy_88

　　一、mysqldump备份结合binlog日志恢复
　　MySQL备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在MySQL故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。
　　1.binlog日志恢复介绍
　　·首先要开启binary log功能
　　通过编辑my.cnf中的log-bin选项可以开启二进制日志；形式如下：
　　log-bin [=DIR/[filename]]  （配置文件中只写log_bin不写后面的文件名和路径时，默认存放在/usr/local/mysql/data目录下，文件名为主机名-bin.000001…命名）
　　·下面用一个例子来说明binlog日志恢复数据的方法

　　先对测试数据库的表进行一下增删改的操作，否则log里边数据有点空。

　　重新开始一个新的日志文件，并删除测试表中的一行数据，模拟错误操作。

　　查看MySQL Server上的二进制日志

　　查看二进制日志中的事件，默认显示可找到的第一个二进制日志文件中的事件，包含了日志文件名、事件的开始位置、事件类型、结束位置、信息等内容。
　　语法格式：SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
　　选项解析：
　　IN 'log_name'      指定要查询的binlog文件名(不指定就是第一个binlog文件)
　　FROM pos               指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
　　offset           偏移量(不指定就是0),例如为3就会从第3行开始
　　row_count          查询总条数(不指定就是所有行)
　　显示信息中的参数介绍：
　　Format_desc  此事件为格式描述事件
　　Query  为查询事件
　　BEGIN  为事务开始
　　Table_map  为表映射事件
　　Write_rows    为我们执行的insert事件
　　XidXid    时间是自动提交事务的动作
　　Rotate    为日志轮换事件，是我们执行flush logs开启新日志文件引起的。
　　COMMIT    为事务的提交

　　查看指定的二进制日志中的事件

　　为了便于查看记录了行变化信息的事件在当时具体执行了什么样的SQL语句可以使用mysqlbinlog工具的-v（--verbose）选项，该选项会将行事件重构成被注释掉的伪SQL语句，如果想看到更详细的信息可以将该选项给两次如-vv，这样可以包含一些数据类型和元信息的注释内容。
　　执行以下命令，我们可以看到上图中显示的信息：
　　mysqlbinlog  -v  /usr/local/mysql/data/mysql-bin.000002
　　另外mysqlbinlog和可以通过--read-from-remote-server选项从远程服务器读取二进制日志文件，这时需要一些而外的连接参数，如-h，-P，-p，-u等，这些参数仅在指定了--read-from-remote-server后有效。
　　从上图中可以看出delete事件发生position是291，事件结束position是420
　　恢复流程：直接用bin-log日志将数据库恢复到删除位置291前,然后跳过故障点,再进行恢复。下面所有的操作,命令如下：

　　常见的选项有以下几个：
　　--start-datetime
　　从二进制日志中读取指定时间戳或者本地计算机时间之后的日志事件。
　　--stop-datetime
　　从二进制日志中读取指定时间戳或者本地计算机时间之前的日志事件。
　　--start-position
　　从二进制日志中读取指定position 事件位置作为开始。
　　--stop-position
　　从二进制日志中读取指定position 事件位置作为事件截至。

　　删除test数据库，利用binlog恢复数据，完成后发现数据都恢复过来了。
　　注意：
　　（1）在实际生产环境中，如果遇到需要恢复数据库的情况，不要让用户能访问到数据库，以避免新的数据插入进来，以及在主从的环境下，关闭主从。
　　（2）以上方法中仅仅利用了binlog来恢复数据，并没有使用到mysqldump全库备份来恢复数据库，所以在恢复数据前要删除数据库。
　　2.利用脚本实现mysql的备份与恢复
　　（1）mysqldump介绍
　　mysqldump是mysql用于备份和数据转移的一个工具。它主要产生一系列的SQL语句，可以封装到文件，该文件包含有所有重建你的数据库所需要的 SQL命令如CREATE DATABASE，CREATE TABLE，INSERT等等。可以用来实现轻量级的快速迁移或恢复数据库。
　　mysqldump 是将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。
　　mysqldump一般在数据量很小的时候（几个G）可以用于备份。当数据量比较大的情况下，就不建议用mysqldump工具进行备份了。
　　·数据库的导出
　　导出对象说明：
　　mysqldump可以针对单个表、多个表、单个数据库、多个数据库、所有数据库进行导出的操作
　　# mysqldump [options] db_name [tbl_name ...]   //导出指定数据库或单个表
　　# mysqldump [options] --databases db_name ...   //导出多个数据库
　　#mysqldump [options] --all-databases           //导出所有
　　·数据库的导入
　　mysql  -uroot  -p  [options]  [db_name]  [tbl_name]  <  /路径/备份文件
　　生产环境中Mysql数据库的备份是周期性重复的操作，所以通常是要编写脚本实现，通过crond计划任务周期性执行备份脚本。
　　mysqldump备份方案：
　　·周日凌晨1点全库备份
　　·周一到周六凌晨每隔4个小时增量备份一次
　　设置crontab任务，每天执行备份脚本
　　# crontab –e
　　#每个星期日凌晨1:00执行完全备份脚本
　　0 1 * * 0 /root/mysqlfullbackup.sh >/dev/null 2>&1
　　#周一到周六每隔4个小时增量备份一次
　　0 */4 * * 1-6 /root/mysqldailybackup.sh >/dev/null 2>&1
　　mysqlfullbackup.sh脚本内容：
　　#!/bin/sh
　　#name:mysqlfullbackup.sh
　　mysqldir=/usr/local/mysql
　　user=root
　　userpwd=123456
　　dbname=test_db
　　#定义备份目录
　　databackupdir=/opt/mysqlbackup
　　[ ! -d $databackupdir ] && mkdir $databackdir
　　#定义邮件正文文件
　　emailfile=$databackupdir/email.txt
　　#定义邮件地址
　　email=root@localhost.localdomain
　　#定义备份日志文件
　　logfile=$databackupdir/mysqlbackup.log
　　DATE=`data -I`
　　echo "" > $emailfile
　　echo $(date +"%y-%m-%d%H:%M:%S") >> $emailfile
　　cd $databackupdir
　　#定义备份文件名
　　dumpfile=mysql_$DATE.sql
　　gzdumpfile=mysql_$DATE.sql.tar.gz
　　#使用mysqldump备份数据库
　　$mysqldir/bin/mysqldump -u$user -p$userpwd --flush-logs -x $dbname > $dumpfile
　　#压缩备份文件
　　if [ $? -eq 0 ]; then
　　tar zcf $gzdumpfile $dumpfile >> $emailfile 2>&1
　　echo "backupfilename:$gzdumpfile" >> $emailfile
　　echo "database backup success!" >> $emailfile
　　rm -f $dumpfile
　　else
　　echo "database backup fail!" >> $emailfile
　　fi
　　#写日志文件
　　echo "------------------------------------------" >> $logfile
　　cat $emailfile >> $logfile
　　#发送邮件通知
　　cat $emailfile | mail -s "mysql backup" $email
　　mysqldailybackup.sh脚本内容：
　　#!/bin/sh
　　# Name:mysqlDailyBackup.sh
　　# 定义数据库目录和数据目录
　　mysqldir=/usr/local/mysql
　　datadir=$mysqldir/data
　　# 定义用于备份数据库的用户名和密码
　　user=root
　　userpwd=123456
　　# 定义备份目录，每日备份文件备份到$dataBackupDir/daily
　　databackupdir=/opt/mysqlbackup
　　dailybackupdir=$databackupdir/daily
　　[ ! -d $dailybackupdir ] && mkdir -p $dailybackupdir/daily
　　# 定义邮件正文文件
　　emailfile=$databackupdir/email.txt
　　# 定义邮件地址
　　email=root@localhost.localdomain
　　# 定义日志文件
　　logfile=$databackupdir/mysqlbackup.log
　　echo "" > $emailfile
　　echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile
　　# 刷新日志，使数据库使用新的二进制日志文件
　　$mysqldir/bin/mysqladmin -u$user -p$userpwd flush-logs
　　cd $datadir
　　# 得到二进制日志列表
　　filelist=`cat mysql-bin.index`
　　icounter=0
　　for file in $filelist
　　do
　　icounter=`expr $icounter+1`
　　done
　　nextnum=0
　　ifile=0
　　for file in $filelist
　　do
　　binlogname=`basename $file`
　　nextnum=`expr $nextnum+1`
　　if [ $nextnum -eq $icounter ];then
　　echo "Skip laster!" > /dev/null
　　else
　　dest=$dailybackupdir/$binlogname
　　if [ -e $dest ];then
　　echo "Skip exist $binlogname" > /dev/null
　　else
　　cp $binlogname $dailybackupdir
　　if [$? -eq 0 ];then
　　ifile=`expr $ifile+1`
　　echo "$binlogname backup success!" >> $emailfile
　　fi
　　fi
　　fi
　　done
　　if [ $ifile -eq 0 ];then
　　echo "no binlog backup!" >> $emailfile
　　else
　　echo "backup $ifile files" >> $emailfile
　　fi
　　# 发送邮件通知
　　cat $emailfile | mail -s "MySQL Backup" $email
　　# 写日志文件
　　echo "--------------------------------------------------------" >> $logfile
　　cat $emailfile >> $logfile