MySQL管理之二进制日志文件的管理

Mysql的二进制日志记录的是所有mysql数据库发生变更的信息，因此对数据库非常重要，mysql的二进制日志还是非常重要的，在日常工作

mysql的二进制日志记录的是所有mysql数据库发生变更的信息，因此对数据库非常重要，二进制日志的作用我概括了一下主要包含如下几个方面：

1、可以用于数据库基于时间点的还原。

2、可以用来分析数据库发生更改的情况，比如程序的bug导致数据库数据更改等等。

3、二进制日志还可以用来重建数据库。

从上面的几点可以看出mysql的二进制日志还是非常重要的，，在日常工作中，二进制日志又往往非常占空间，尤其是写入比较频繁的数据库，二进制日志更是增长迅猛。那么在DBA的日常工作中如何来管理二进制日志呢？接下来就来详细叙述一下我在日常工作中是如何管理二进制日志的。

我是通过如下方式来管理二进制日志的：

1.  通过shell脚本每小时做一次二进制日志flush操作

2.  通过shell脚本每小时进行一次二进制日志的备份工作，将一小时新产生的二进制日志迁移到nfs备份服务器，并压缩存放，脚本最后附上！

3.  在my.cnf配置文件中加上expire_logs_days = 5，这样mysql会自动删除5天前的日志。

设置步骤如下：

1、搭建NFS备份服务器。

搭建NFS非常简单，如下几步就可以完成：

yum -y install setup-*  initscripts-*  nfs-utils-*  portmap-*  quota-*

编辑/etc/exports，添加一行二进制日志的备份条目：

vim /etc/exports

/data/binlog_backup 192.168.3.135(rw,sync,no_root_squash)  *(ro)

让192.168.3.135这个IP可以读写,其他的只读。

配置完成之后重新启动portmap和nfs就可以了：

/etc/rc.d/init.d/portmap restart

/etc/rc.d/init.d/nfs restart

至此服务器端的NFS就设置完成了。接下来设置客户端就是我们的mysql服务器如何挂载：

/etc/rc.d/init.d/portmap start

科汛网上商城管理系统

一个经过完善设计有着及其强大的会员互动和独特创新的内容管理系统。主要功能模块包括：文章频道、图片频道、下载频道、动漫频道、音乐频道、影视频道、商城频道、供求频道、采集管理 、专题频道等等。系统通用模块：用户管理、博客日志管理、相册管理、音乐盒管理、朋友圈管理、广告管理、公告管理、模板管理、网站信息配置、高级自定义SQL扩展标签，RSS在线订阅功能、网站统计、邮件列表、邮件群发、数据库管理、站内短消

0

查看详情

mkdir /data/binlog_backup

mount -t nfs 192.168.3.92:/data/binlog_backup /data/binlog_backup

如果不启动客户端的portmap的话挂载的时候会报：mount.nfs: Input/output error，记得将mount的命令添加到/etc/rc.local中，不然下次重启服务器后会导致备份二进制日志失败。

2、将脚本放到mysql数据库上，并设置计划任务为每小时运行一次。

脚本内容如下：

cat bin_log_magment.sh

#!/bin/bash
#Purpose:管理mysql二进制日志，每小时刷新二进制日志，并将日志复制到nfs服务器上，方便以后恢复和问题分析！
#Author:carl_zhang
#Date:2012-5-15

#some variables 一些参数
#定义数据库的相关信息
USERNAME=root
PASSWORD=zhang@123
HOSTIP=localhost
MYSQL=/usr/local/mysql/bin/mysql

#设置二进制日志的路径
BIN_LOG_DIR=/data/dbdata
#设置二进制日志备份的路径
BIN_LOG_BACKUP_DIR=/data/binlog_backup
#定义一个常量来跳过最后一个日志文件
COUNT=0
#定义一个日志文件
LOGFILE=$BIN_LOG_BACKUP_DIR/binlog_backup.log
#找出二进制日志的名称前缀
BINLOG_PREFIX=`grep "log-bin=" /etc/my.cnf | awk -F'=' '{print $2}'`
#检查backup.file文件是否存在，第一次跑的时候如果没有这个会报错

if [ ! -f "$BIN_LOG_BACKUP_DIR/backup.file" ];then
touch $BIN_LOG_BACKUP_DIR/backup.file
fi
#运行文件比较之前先执行一下flush logs
$MYSQL -u$USERNAME -p$PASSWORD -h$HOSTIP -e "flush logs"
#比较二进制日志文件列表和已备份的二进制文件列表
FILE_LIST=`comm -23 $BIN_LOG_DIR/$BINLOG_PREFIX.index $BIN_LOG_BACKUP_DIR/backup.file`
#统计日志文件的个数
FILE_COUNT=`comm -23 $BIN_LOG_DIR/$BINLOG_PREFIX.index $BIN_LOG_BACKUP_DIR/backup.file|wc -l`

#程序正文
#开始备份二进制日志文件
for file in $FILE_LIST
do
BINLOG_NAME=`basename $file`
let COUNT=$COUNT+1
if [ $COUNT -eq $FILE_COUNT ];then
#跳过最新的那个二进制日志文件
echo "skip the lastest binlog file" >> $LOGFILE
else
cp $BIN_LOG_DIR/$BINLOG_NAME $BIN_LOG_BACKUP_DIR/
if [ $? -eq 0 ];then
echo "`date -d "today" +%Y-%m-%d-%H-%M-%S` $BINLOG_NAME backup success" >> $LOGFILE
else
echo "`date -d "today" +%Y-%m-%d-%H-%M-%S` $BINLOG_NAME backup faild, Please check it out" >> $LOGFILE
exit 5
fi
#对二进制日志进行压缩存放
gzip $BIN_LOG_BACKUP_DIR/$BINLOG_NAME
if [ $? -eq 0 ];then
echo "`date -d "today" +%Y-%m-%d-%H-%M-%S` $BINLOG_NAME gzip success" >> $LOGFILE
else
echo "`date -d "today" +%Y-%m-%d-%H-%M-%S` $BINLOG_NAME gzip faild, Please check it out" >> $LOGFILE
exit 5
fi
echo ./$BINLOG_NAME >> $BIN_LOG_BACKUP_DIR/backup.file
fi
done

如果大家需要使用这个脚本，请自行更改对应的配置信息，比如IP地址，用户名密码、以及目录等等。

注意因为这里有mysql的用户名和密码，需要将脚本的权限设置成700，使用命令为：

chmod 700 bin_log_magment.sh

手工运行没有问题以后，添加到计划任务中去：

00 */1 * * *  /var/script/bin_log_magment/bin_log_magment.sh >/dev/null 2>&1

3、设置my.cnf配置文件，加上expire_logs_days = 5，并重启mysql。

这个就不详细叙述了，是个人都会了！