MySQL简易备份方法

适用对象: MySQL DB管理员.
适用条件: 对 Linux 环境有基础的管理及操作能力.
文章附注: 本篇文章为自由文件,欢迎非商业性质转载,并请注明出处!
商业性质转载请来信告知!
特别注意: 1.本篇文章提供之程式及设定*不一定*符合您的环境,
请依您的系统环境适当地修改程式及设定.
2.在执行任何具破坏性的动作及行为前,
请确认您已作好完整可用的资料备份工作.

目录内容
一.前言
二.错误修正
三.开始备份
四.如何回存
五.MySQL线上备份
六.MySQL线上回存
七.开始排定备份
八.结语
九.参考资料
十.附录-备份Script原始档



一.前言

        前几周,正当我在网上快乐的冲浪,在酷!学园快乐的灌口水时, 竟蹦出一页 "phpBB critical error",查询 Database 出现错误的讯息,中断了各学员在各系版唇枪舌战,热烈的讨论,虽然过了不久,系统管理工友把网页修好,但开始连续几个星期,phpBB 讨论区有时还是很容易挂掉,频率大约是 一至二周一次,通常都是在周末大家最闲的时侯发生的,幸好此时刚好世界杯足球赛正好开打,不能到学园灌水,最少还有足球赛可以看. 当时修护的方式,是将 MySQL 服务停止,再把前一次可用的DB档案盖回去, 然後再重新启动 MySQL 服务来测试 phpBB 是否正常,这样子的作法通常都有效, 但总是会遗失某一段时间内的文章,这不是大家所希望的... 为了保存许多珍贵的资料和学园们恶心的口水,我开始著手准备 DB 的备份 工作,准备重建这个有问题 DB ,更为将来校园的迁址作 DB 移转准备.

(DB: Database的简称,以下都以 DB 取代 Database,
phpBB: 酷!学园讨论区系统的软体名称)

二.错误修正

花了一些时间熟悉主机的环境後,我开始寻找错误在那里... 检测 MySQL 内 phpBB 的 DB 後发现,有个 users 的 table 是有问题的,
使用 myisamchk 尝试去修护,发现还不行用预设方式修护,还要多加个 "-o" 的参数才行,在使用myisamchk 时,为避免还有用户来存取 DB ,最好是能够将 MySQL 服务停止,不然最少也要下个 "mysqladmin flush-tables" 後, 再作 myisamchk 指令,像:

myisamchk -o phpbb2_users.MYI

这个动作,可能要作个2~3次,直到没有错误的讯息出现! 修护完,重新启动 MySQL 服务後,就可以用 mysql 这个 client 的指令, 去 Query 一下 DB 内容,测试看是否正常.很幸运的,DB的部份在此时, 运作是正常的. 当然,在你要备份之前,假如能先检测资料是否正确,那是最好不过了, 假如有需要,可以把检测的工作,排定在备份工作之前,但是记得,这个检测DB 的动作不要排定在 DB 高用量的那段时间,深夜无人上线的时段是个不错的选择!


三.开始备份

phpBB 讨论区的资料档,主要有两个部份,就是 php 主程式和 DB 内容,
php 主程式的备份就比较简单,只要把全部档案 tar 起来就行了,就像:

tar cvfz phpbb2_20020601.tgz phpbb

(上面的 phpbb 是指 phpBB 的 php 网页程式存放目录.)

以後有改到 phpBB 网页程式部份再重新备份一次就行,它的内容资料都写在DB 内,所以 php 程式档部份异动性应该不大.

再来就是 MySQL DB 部份了,预设 MySQL 的 DB 档案是存在 /var/lib/mysql 内, 以 DB 名称为目录,目录内就是该 DB 的所有资料,像 phpbb2 这个 DB, 就是存在 /var/lib/mysql/phpbb2 内,在备份前,因为怕资料尚未完全写入磁碟, 而且 MySQL 会 Lock 在使用的 DB 档案,所以应该是要先把 MySQL 先 Shutdown 一下, 整个备份的程序可以下像下面的指令去完成:

/etc/rc.d/init.d/mysqld stop
tar cvfz phpbb2_db_20020601.tgz phpbb2
/etc/rc.d/init.d/mysqld start

(上面的 phpbb2 是指存放 phpbb2 这个 DB 的目录.) Ok!这样就完成了! (什麽?就这样! 3行就结束了?!)

对!这样就可以了! 不过要注意的是,怕 DB 内每个 Tables 间的资料有关关联性, 所以最好是把整个 DB 一次备起来,单独只备哪个 Tables 的档案,以後回存时, 怕会有资料关联不一致的问题! 以酷!学园的口水讨论区为例,有21000笔左右的文章加上1200名注册会员,资料库
整个 tar 起来大约30几 MB 左右,每天备份,以一周为周期来计算,备份大约只需要
(35*7=245) 200 多 MB 左右的空间,一星期的备份烧在一张光碟还够!

四.如何回存

phpBB 讨论区的回存,只需把档案解回原来存放网页的路径就可以,用以下指令解开:

tar xvfz phpbb2_20020601.tgz

DB 发生错误而要回存时,其实也不难,先找出最近一次完整正常的备份,先把现在错误的网页或 DB 先更名或 tar 起来,再把好的备份给解开回原来目录位置就行了,需要注意的是, MySQL 服务最好也是要先停止,回存完成後再启动服务,回存 DB 的整个程序
可能像下面:

/etc/rc.d/init.d/mysqld.stop
mv phpbb2 phpbb2_error
tar xvfz phpbb2_db_20020601.tgz
/etc/rc.d/init.d/mysqld.start

然後再去测试一下网页及资料库! 看使用上是否正常就行了...


五.MySQL线上备份

使用像上面的"档案"方式备份是个不错的方法,它最少可以保持该主机某个时间点的完整档案备份,但还是有一些问题需要考虑到,有些主机就不只建立一个 DB 而已, 总不能为了备份某个 DB 而把整个 MySQL 服务停止,备份档案的方式,回存在原主机上一定适用,但假如MySQL 版本升级,或是在那天,该网页空间需迁机移机到别的主机时, 那就没人敢保证备出来的资料档可以用,所以我们可以考虑另一种备份的方式,是使用 MySQL 本身提供的功能: "MySQL Data Dump",指令是 "mysqldump".

使用 MySQL 的 Dump 功能可以把 DB 的"结构","资料"或"结构加资料" Dump 成 文字档, mysqldump 指令提供的弹性很大,你可以选择把整个 DB Dump 成一个档, 或是每个 Table 为一个档,甚至是把结构档和资料分开储存都可以. 检测酷!学园的 phpBB 资料库後发现,以结构加资料 Full Dump 成一个档案, 档案的大小大约也是30几MB左右. 在 Dump 之前最好多下个 Flush-Logs 更新 LOG, 所以整个 Dump 的指令如下:

mysqladmin -uroot -p flush-logs
mysqldump phpbb2 -uroot -p opt > phpbb2_20020601.sql

(phpbb2 是 DB 名称, opt 是一个使用完整 Dump 参数)

再使用 time 指令去测试执行时间,这个30几 MB 的 DB , Dump 出来竟只要15秒左右, Query 的速度还真是快,假如只是要单独 Dump 某个 Table 时,只要在上面的指令後, 转出符号 ">" 前加个 Table 名称就可以,如只要 phpbb2_users 这个 Table 的 Dump 时, 只要下:

mysqldump phpbb2 -uroot -p opt phpbb2_users > phpbb2_users_20020601.sql

Dump 出来的档案是个纯文字档,你可以用 tar 把它压起来,以上面30几 MB 的 DB 为例, 大约可以把档案大小压到1/3左右的大小,因为 Dump 出是文字档,所有的资料都是以明文 显示,所以必须注意一下备份档保存的安全性,而且建议备份档最好再另外储存於异地以及其他易於保存的媒体上,像光碟片或磁带,这样的备份才有意义.


六.MySQL线上回存

若要回存整个资料库,只需将压缩的备份档还原成 Dump 的档案,再用下面的指令回存:

mysql phpbb2 -uroot -p < phpbb2_20020601.sql

这边需注意的是,若建立备份时是以"opt"或"add-drop-table"为参数时,回存的动作是先将旧的 Table 先删除,重建 Table 的结构後再把 Data 汇入,所以回存後, 所有的资料会回到你当时备份那个时间点,因此在回存资料时,可以考虑把现有错误或不完整的 DB 先备份一份下来,以备不时之需,或是拿来比对错误的地方在那里,当然你也可以把资料回存到另一个测试用的 DB 内,只要把上面指令的 DB 名称改成你的测试用 DB 名称即可.

使用这种回存方式, MySQL 服务不需停止,也不会动到其他正在使用的 DB ,在一些提供MySQL 服务的虚拟主机,可用这种方式作你自己的 DB 备份及回存.

另外,假如你是系统重建或是移机时,切需在新的 MySQL 内,新建一个空白 DB 後,才行作回存的工作,你可以用下面的指令建立:

mysql -uroot -p -e "CREATE DATABASE phpbb2"

(上面的phpbb2是你要新建的 DB 名称)

另一种作法,先用 "mysql" 指令进入 "mysql client console", 然後再用:

CREATE DATABASE phpbb2;

这样就行了,记得尾端要加个 ";" 符号该行指令才会执行.
七.开始排定备份

知道备份的方法之後,就可以把整个备份的动作作成一个 Scirpt ,指定 DB 相关参数後,
便可利用 Crontab 排定备份的周期和时间,可以把下面的指令加入 Crontab 内:

00 04 * * * /home/dbadmin/backup

(backup 是 Script 的档名)
排定的时间当然最好是夜间离峰时. (酷!学园常24hr都有人在线上)
以本文附录提供的 Script 作备份规则说明,每日执行备份作业,以一周为一个周期,所以最近的资料是昨天,最久的资料是一星期前,每次备份是将指定的 DB Full Dump 成一个档, 用 tar 压缩後存入指定的路径. 备份档以 DB 的名称加上星期来命名,星期的表示为"0" 代表日,"1"代表星期一,以此类推. 目前该 Scirpt 一次只能备份一个 DB 而已,你可以加以修改以符合你的系统环境及需求.


八.结语

目前这个 Script 在测试机上测试的效果还不错,以酷!学园讨论区的2万多篇讨论文章, DB 总大小有30几 MB, 跑这个 Script 大约只需要30几秒就完成,下面是以 time 指令测试数次计算的结果:

[root@demo tmp]# time backup

real 0m32.788s
user 0m22.770s
sys 0m4.610s
[root@demo tmp]# time backup

real 0m33.898s
user 0m24.740s
sys 0m4.590s
[root@demo tmp]# time backup

real 0m32.808s
user 0m23.710s
sys 0m4.750s

测试机的硬体配备是Intel Celeron 600 + 256mb SDRAM, 作业系统是CLE Linux 1.0.

因为 Dump 备份的速度很快,大部份作 MySQL 的备份都是这样作的,假如是大型大量的DB 环境时,就需要考虑以 Incremental 的备份方式,而 MySQL 本身也提供这种备份方式, 这种备份方式,一周作一次完整的备份,每天只要作异动作业档(LOG)的备份就行,在备份的成本上,可以节省备份时间和储存空间. 完成了备份的工作安排,现在又可以和学园们一起快乐的上网冲浪去了...


九.参考资料

1.MySQL中文参考手册


2.MySQL man page

3.Study-area - Shell和Shell Script



十.附录-备份Script原始档

#!/bin/sh
#
# Title: MySQL Backup Script
# Created: 2002.06.01
# Current: 2002.06.23
# Contact: Duncan Lo < >
# Note: This script just only backup one database!
#

#
# Variables define for your server
#

# Database name
DBNAME="phpbb2"

# Database admins name
DBUSER="root"

# Database admins password
DBPASS="password"

# MySQL commands path
# default: /usr/bin
BINPATH="/usr/bin"

# Backup date format
BAKDATE=`date +%w`

# Path for backup files save to
BAKPATH="/usr/backup/phpbb"

# Temp directorys name
TMPDIR="tmp.db_bak".$BAKDATE

# Backup filess directory
BAKDIR="$DBNAME"_$BAKDATE

# Database tables list files name
TABLST="tables_list"

# Backup Type
# 0: All tables in one dump file
# 1: Pre table in one dump file
BAKTYPE="0"

#
# Script Start
#

#
# Create backup temp directory
#
cd /tmp
rm -rf $TMPDIR
mkdir $TMPDIR
cd $TMPDIR
mkdir $BAKDIR
cd $BAKDIR

#
# Create databases table list
#
$BINPATH/mysql $DBNAME -u$DBUSER -p$DBPASS -N -e "show tables" > $TABLST

#
# Flush databases LOG before dump
#
$BINPATH/mysqladmin -u$DBUSER -p$DBPASS flush-logs

#
# Choice one type to dump datebase
#
case $BAKTYPE in
0)
#
# Dump database all table in one file
#
$BINPATH/mysqldump $DBNAME -u$DBUSER -p$DBPASS opt > $DBNAME.sql
;;
1)
#
# Dump database pre table in one file
#
awk { print BINPATH"/mysqldump "DBNAME" -u"DBUSER" -p"DBPASS" \
opt " $1 " > " $1".sql" } \
BINPATH="$BINPATH" DBNAME="$DBNAME" DBUSER="$DBUSER" DBPASS="$DBPASS" \
$TABLST \
| /bin/sh
;;
*);;
esac

cd ..

#
# Compress backup files
#
tar cfz $BAKDIR.tgz $BAKDIR

#
# Move file to backup directory
#
mv $BAKDIR.tgz $BAKPATH

cd ..
#
# Clean temp files and directory
#
rm -rf $TMPDIR

#
# Script End
#