Mysql 完 全 备 份 + 增 量 备 份 +binlog 作 者 : 佚 名 时 间 :2011/12/06 MSN:w7374520@hotmail.com 目 标 : 在 Centos 系 统 中, 对 Mysql 进 行 热 备 份, 备 份 完 成 发 送 邮 件 给 管 理 员 如 果 发 生 误 操 作 可 以 通 过 完 全 备 份 + 增 量 备 份 +binlog 时 间 点 进 行 恢 复 数 据 提 示 : 请 您 自 行 在 测 试 机 上 测 试 成 功 后, 再 到 生 产 系 统 部 署 应 用 本 文 仅 供 参 考, 谨 慎 使 用 备 份 策 略 :0 级 +1 级 一 源 码 : 1 下 载 msmtp 用 于 发 送 邮 件 wget http://cdnetworks-kr-2.dl.sourceforge.net/project/msmtp/msmtp/1.4.26/msmtp-1.4.26.tar.bz2 2 下 载 xtrabackup 用 于 mysql 热 备 我 下 载 的 是 64 位 的, 请 选 择 相 应 的 版 本 wget http://www.percona.com/downloads/xtrabackup/xtrabackup-1.6.3/linux/binary/x86_64/xtrabackup-1.6.3.tar.gz 二 安 装 配 置 发 送 邮 件 程 序 在 linux 系 统 下 如 果 您 可 以 正 常 发 送 邮 件, 请 直 接 看 第 四 步 骤 1 安 装 msmtp [root@test_b src]# cd /usr/local/src/ [root@test_b src]# tar jxvf msmtp-1.4.16.tar.bz2 截 图 1
2 配 置 msmtp 帐 号 [root@test_b msmtp-1.4.16]# cd /usr/local/msmtp/ [root@test_b msmtp]# mkdir etc [root@test_b msmtp]# cd etc/ # 新 建 etc 目 录 # 进 入 etc 目 录 [root@test_b etc]# pwd /usr/local/msmtp/etc 截 图 2 [root@test_b etc]# vim msmtprc # 手 动 编 辑 msmtprc 文 本, 下 面 内 容 手 写 如 下 : # Set default values for all following accounts. defaults logfile /usr/local/msmtp/msmtp.log # The SMTP server of the provider. account mysqlbackup # SMTP 邮 件 服 务 器 地 址 host smtp.live.com # 发 送 的 邮 件 Email from w7374520@hotmail.com auth login # 邮 件 服 务 器 登 录 账 号 user w7374520@hotmail.com # 邮 件 服 务 器 登 陆 密 码 password 1234567890 # Set a default account account default : mysqlbackup 截 图 三
3 测 试 是 否 可 以 使 用 msmtp 发 送 邮 件 [root@test_b etc]# /usr/local/msmtp/bin/msmtp w7374520@hotmail.com 你 好! 收 到 信 没 有 啊! 期 望 回 复 哦! You have new mail in /var/spool/mail/root [root@test_b etc]# 输 入 任 意 字 符, 然 后 按 Ctrl+D 退 出, 查 看 邮 件 是 否 收 到, 也 可 以 查 看 msmtp.log 日 志 截 图 4 三 安 装 配 置 mutt 邮 件 客 户 端 1 Centos 默 认 自 动 了 mutt 邮 件 客 户 端, 如 果 没 有 #yum install mutt 2 编 辑 mutt 配 置 文 件 [root@test_b etc]# vim /etc/muttrc # 在 最 后 添 加 如 下 内 容 ######################################################### set from="gangqi.wu@24life.com.cn" set sendmail="/usr/local/msmtp/bin/msmtp" set use_from=yes set realname="mysql"
set editor="vi 截 图 5 4 测 试 mutt 发 送 邮 件 [root@test_b etc]# echo " 内 容 " mutt -s " 主 题 " w7374520@hotmail.com 截 图 6 四 安 装 配 置 xtrabackup 1 解 压 缩, 安 装 [root@test_b src]# tar zxf xtrabackup-1.6.3.tar.gz [root@test_b src]# cd xtrabackup-1.6.3 [root@test_b xtrabackup-1.6.3]# ll # 二 进 制 文 件 只 有 两 个 目 录 bin 和 share total 8 drwxr-xr-x 2 root root 4096 Sep 24 04:50 bin drwxr-xr-x 3 root root 4096 Sep 24 04:50 share [root@test_b xtrabackup-1.6.3]# cd bin/ # 查 看 bin 下 的 内 容 [root@test_b bin]# ll total 27824 -rwxr-xr-x 1 root root lrwxrwxrwx 1 root root -rwxr-xr-x 1 root root 91664 Sep 24 04:50 innobackupex 12 Dec 6 13:55 innobackupex-1.5.1 -> innobackupex 145287 Sep 24 04:50 tar4ibd -rwxr-xr-x 1 root root 8886466 Sep 24 04:50 xtrabackup -rwxr-xr-x 1 root root 7746799 Sep 24 04:50 xtrabackup_51 -rwxr-xr-x 1 root root 11558030 Sep 24 04:50 xtrabackup_55 [root@test_b bin]# cp -ar * /usr/local/mysql/bin/ # 把 bin 下 的 文 件 拷 贝 到 mysql 的 bin 目 录 下
2 声 明 mysql 的 bin 的 环 境 变 量 [root@test_b ~]# vim /root/.bash_profile 添 加 一 行 export PATH=$PATH:/usr/local/mysql/bin 截 图 7 五 完 全 备 份 脚 本 ############################################################################### [root@test_b ~]# vim /root/scirpt/backupfull.sh #!/bin/bash # 声 明 变 量, 删 除 7 天 以 前 的 文 件 夹 source /root/.bash_profile DBUser=root DBPasswd=1234567890 DBHost=127.0.0.1 DBName=test /bin/mkdir -p /data/mysqlbackup/$(date +%Y%m%d)/log /bin/mkdir -p /data/mysqlbackup/$(date +%Y%m%d)/dump /usr/bin/find /data/mysqlbackup/ -type d -mtime +7 xargs rm -rf {} \; BackupPath=/data/mysqlbackup/`date +%Y%m%d` LogFile=/data/mysqlbackup/`date +%Y%m%d`/log/$(date +%Y%m%d%H%M)_full.log DUmpfile=$(date +%Y%m%d_%H-%M-%S)"_"$DBname".sql.gz"
# 使 用 innobackupex 对 mysql 做 完 全 备 份 echo "---------------backup start `/bin/date +%Y%m%d_%H-%M-%S`---------------------" >>$LogFile /usr/local/mysql/bin/innobackupex --user=$dbuser --password=$dbpasswd --host=$dbhost --database=$dbname --slave-info --defaults-file='/etc/my.cnf' $BackupPath 2>>$LogFile echo "---------------------backup end `/bin/date +%Y%m%d_%H-%M-%S`----------------------" >> $LogFile # 使 用 mysqldump 对 数 据 完 全 导 出, 同 时 对 非 常 重 要 的 数 据 变 化 频 繁 经 常 需 要 修 改 的 table1 table2 table3 进 行 单 独 备 份 echo "-------------------------------bakcup dump start `/bin/date +%Y%m%d_%H-%M-%S`----------------------------" >> $LogFile /usr/local/bin/mysqldump -u"$dbuser" -p"$dbpasswd" -h"$dbhost" --single-transaction --allow-keywords --add-locks --add-drop-table -F -q -B "$DBName" gzip 1> "$BackupPath/dump/$DUmpfile" /usr/local/bin/mysqldump -u"$dbuser" -p"$dbpasswd" -h"$dbhost" --single-transaction --allow-keywords --add-locks --add-drop-table -F -q "$DBName" table1 gzip 1> "$BackupPath/dump/$(date +%Y%m%d_%H-%M-%S)_table1.sql.gz" /usr/local/bin/mysqldump -u"$dbuser" -p"$dbpasswd" -h"$dbhost" --single-transaction --allow-keywords --add-locks --add-drop-table -F -q "$DBName" table2 gzip 1> "$BackupPath/dump/$(date +%Y%m%d_%H-%M-%S)_table2.sql.gz" /usr/local/bin/mysqldump -u"$dbuser" -p"$dbpasswd" -h"$dbhost" --single-transaction --allow-keywords --add-locks --add-drop-table -F -q "$DBName" table3 gzip 1> "$BackupPath/dump/$(date +%Y%m%d_%H-%M-%S)_table3.sql.gz" echo "--------------------------------backup dump end `/bin/date +%Y%m%d_%H-%M-%S`-----------------------------" >> $LogFile # 发 送 邮 件 通 知 管 理 员 /bin/cat $BackupPath/log/*_full.log /usr/bin/mutt -s "mysql backup full" w7374520@hotmail.com ########################################################################### 不 完 全 截 图 : 六 增 量 脚 本 说 明 : 增 量 脚 本 是 在 五 完 全 备 份 的 基 础 上, 进 行 增 量 备 份 ############################################################# [root@mysql_a ~]# vim /opt/scirpt/backup_incremental.sh #!/bin/bash
# 声 明 变 量 source /root/.bash_profile DBUser=root DBPasswd=1234567890 DBName=test DBHost=127.0.0.1 DUmpfile=$(date +%Y%m%d_%H-%M-%S)"_"$DBName".sql.gz" DBdir=/data/mysqlbackup/`date +%Y%m%d` /bin/ls -dhl /data/mysqlbackup/`date +%Y%m%d`/* head -n 1 >/data/mysqlbackup/`date +%Y%m%d`/aa fullbackdir=`awk -F '/' '{print $NF}' /data/mysqlbackup/$(date +%Y%m%d)/aa` datelog=$(date +%Y%m%d_%H-%M).incremental.log # 增 量 备 份, 需 要 --incremental-basedir 参 数 指 定 完 整 备 份 目 录 的 位 置 echo $fullbackdir > echo "-------------------------------bakcup start `/bin/date +%Y%m%d_%H-%M-%S`----------------------------" >> #/usr/local/mysql/bin/innobackupex --user=$dbuser --password=$dbpasswd --database=$dbname --host=$dbhost --safe-slave-backup --slave-info --incremental --incremental-basedir=$dbdir/$fullbackdir $DBdir 2> /usr/local/mysql/bin/innobackupex --user=$dbuser --password=$dbpasswd --database=$dbname --host=$dbhost --incremental --incremental-basedir=$dbdir/$fullbackdir $DBdir 2>> echo "--------------------------------backup end `/bin/date +%Y%m%d_%H-%M-%S`-----------------------------" >> #/bin/ls -thr $DBdir/log/* /usr/bin/tail -n 1 > $DBdir/log/a # 增 量 备 份 的 时 候 对 全 库 做 一 个 逻 辑 的 备 份, 重 要 的 表 需 要 单 独 备 份, 方 便 恢 复. echo "-------------------------------bakcup dump start `/bin/date +%Y%m%d_%H-%M-%S`----------------------------" >> /usr/local/mysql/bin/mysqldump -u"$dbuser" -p"$dbpasswd" -h"$dbhost" --single-transaction --allow-keywords --add-locks --add-drop-table -F -q -B "$DBName" gzip 1> "$DBdir/dump/$DUmpfile" /usr/local/mysql/bin/mysqldump -u"$dbuser" -p"$dbpasswd" -h"$dbhost" --single-transaction --allow-keywords --add-locks --add-drop-table -F -q "$DBName" table1 gzip 1> "$DBdir/dump/$(date +%Y%m%d_%H-%M-%S)_table1.sql.gz" /usr/local/mysql/bin/mysqldump -u"$dbuser" -p"$dbpasswd" -h"$dbhost" --single-transaction --allow-keywords --add-locks --add-drop-table -F -q "$DBName" table2 gzip 1> "$DBdir/dump/$(date +%Y%m%d_%H-%M-%S)_table2.sql.gz" /usr/local/mysql/bin/mysqldump -u"$dbuser" -p"$dbpasswd" -h"$dbhost" --single-transaction --allow-keywords --add-locks --add-drop-table -F -q "$DBName" table3 gzip 1> "$DBdir/dump/$(date +%Y%m%d_%H-%M-%S)_table3.sql.gz" echo "--------------------------------backup dump end `/bin/date +%Y%m%d_%H-%M-%S`-----------------------------" >> # 发 送 邮 件 echo "--------------------------------start sendmail `/bin/date +%Y%m%d_%H-%M-%S`-----------------------------" >> /bin/cat $DBdir/log/`/bin/ls -thlr $DBdir/log/* /usr/bin/tail -n 1 awk -F '/' '{ print $NF }'` > $DBdir/log/a /bin/cat $DBdir/log/a /usr/bin/mutt -s "mysql backup incremental" w7374520@hotmail.com echo "--------------------------------sendmail end `/bin/date +%Y%m%d_%H-%M-%S`-----------------------------" >> /bin/rm -rf $DBdir/log/a
七 加 入 crontab 中,0 点 5 分 做 完 全 备 份, 每 半 小 时 增 量 备 份 [root@test_b mail]# crontab -l 5 0 * * * /root/scirpt/backupfull.sh */30 * * * * /root/scirpt/backup_incremental.sh [root@test_b mail]# service crond restart # 重 启 服 务 使 之 生 效 八 数 据 库 恢 复 思 路 场 景 如 上 : Mysql 数 据 库 在 0 点 5 分 做 了 完 全 备 份, 然 后 0 点 30 分 增 量 备 份,1 点 钟 增 量 备 份 1 点 15 分 做 了 误 操 作 删 除 了 数 据 所 有 人 都 不 知 道, 继 续 写 输 入 正 确 的 数 据 知 道 当 前 时 间 才 发 现 数 据 出 现 错 误, 怎 么 恢 复? 1 增 量 恢 复 的 方 法 : 是 把 最 后 一 个 1 级 备 份 应 用 到 0 级 完 全 备 份 中, 然 后 恢 复 完 全 备 份 [root@test_b mail]#service mysqld stop # 停 止 数 据 库 恢 复 增 量 备 份 : [root@test_b mail]# /usr/local/mysql/bin/innobackupex --user=root --passwd=1234567890 --host=127.0.0.1 --defaults-file=/etc/my.cnf --app-log /data/mysqlbackup/20111206/2011-12-06_00-05-08/ --incremental-dir=/data/mysqlbackup/20111206/2011-12-06_01-00-07/
2 增 量 备 份 应 用 到 完 全 备 份 上 完 成 后, 把 完 全 备 份 拷 贝 到 数 据 中 [root@test_b mail]# /usr/local/mysql/bin/innobackupex --user=root --passwd=1234567890 --host=127.0.0.1 --defaults-file=/etc/my.cnf --copy-back /data/mysqlbackup/20111206/2011-12-06_00-05-08/ 3 修 改 数 据 库 权 限, 启 动 数 据 库 [root@test_b mail]# chown mysql:mysql /usr/local/mysql/var/ -R [root@test_b mail]# service mysqld restart 4 增 量 备 份 + 完 全 备 份 应 用 完 成 后, 我 们 恢 复 的 知 识 1 点 以 前 的 数 据 在 1 点 到 1 点 45 分 中 之 间, 如 果 这 段 时 间 生 成 了 4 个 日 志, 请 把 日 志 追 加 到 一 个 文 本 中 例 如 向 下 [root@test_b var]# mysqlbinlog /usr/local/mysql/var/mysql-bin.000023 >/root/log.txt [root@test_b var]# mysqlbinlog /usr/local/mysql/var/mysql-bin.000024 >>/root/log.txt [root@test_b var]# mysqlbinlog /usr/local/mysql/var/mysql-bin.000025 >>/root/log.txt [root@test_b var]# mysqlbinlog /usr/local/mysql/var/mysql-bin.000026 >>/root/log.txt [root@test_b var]# mysqlbinlog --stop-date="2011-12-06 01:14:59" /root/log.txt mysql -uroot -p123456 -h127.0.0.1 # 应 用 到 2011-12-06 01:14:59 这 一 刻 停 止 [root@test_b var]# mysqlbinlog --stop-date="2011-12-06 01:16:00" /root/log.txt mysql -uroot -p123456 -h127.0.0.1 # 跳 过 15 分 钟 的 操 作, 一 直 应 用 到 最 后