ZRM for MySQL学习笔记01[技术] | 蚊子世界

ZRM for MySQL学习笔记01[技术]

七月 30th, 2009 发表在 数据库相关 本文作者：深夜的蚊子

mysql数据库现在应用越来越广了，所以mysql数据库的数据也是一个企业中相当重要的，对于mysql的备份与恢复就成为了一项很关键的任务。蚊子再一次mysql经销商过来做售前咨询的时候认识了Zmanda这款软件，这里要介绍的是ZRM for MySQL，这款软件业分为商业版和free版，free的版本是ZRM for MySQL Community Edition这个，下载地址http://www.zmanda.com/download-zrm.php。

这款软件的主要功能包括：

根据需要设定备份计划
从ZRM server集中的备份管理
获得mysql数据库备份的报告
在本地备份数据库同时也在远程服务器上备份
可以直接把备份存储在NAS/SAN的存储上

下面的图显示了在本机运行的ZRM for MySQL备份两台mysql-server上多个mysql数据库

接下来来介绍一下这款软件的功能。

备份的功能

ZRM for MySQL可以备份被mysql服务器管理的多个数据库
可以备份多台mysql服务器上的多个数据库
可以备份单个数据库中的表
可以对数据库进行热备
根据MySQL表使用的不同的存储引擎支持多种不同的备份方式
具有两种级别的备份：全备份和对数据库的增量备份
可以使用mysqldump,mysqlhotcopy,snapshots(Linux LVM/Solaris ZFS)和MySQL replication作为不同的备份方式
它可以创建保持一致的数据库备份而不管数据库表使用的哪种存储引擎
它支持本机ZRM for MySQL端和远端MySQL服务器之间的SSL认证连接，从而允许通过网络或穿过防火墙进行安全的备份。
它还能够验证备份数据镜像
使用标准的工具如gzip，GPG等可以对备份镜像进行压缩或者加密
系统管理员可以终止备份任务
把snapshots当做备份镜像来看待，并在之后把这种备份转化成真实的备份

恢复的功能

ZRM for MySQL使得恢复备份的数据变得很容易
它支持使用备份的检索，这个检索存储了每个备份运行的信息
它还有一个reporting工具用来浏览备份的索引
它还可以恢复全备份和增量备份
它选择做增量恢复是根据binary日志的位置或者时间点。这个允许回复数据库操作失误
这个点可以是一个时间点或者数据库binary日志中的一个点
ZRM for MySQL提供了一个很容易的方法来虑进/滤出binary日志中的数据库事件
这个可以帮助决定哪些被恢复，哪些不被恢复
根据你所做的备份的类型，可以使用备份在本机恢复或者在不同的机器上进行恢复

报告和计划任务能力

ZRM for MySQL可以立即执行备份计划或者按天/按周/按月做备份计划任务
它可以自动生成备份报告
它具有预先设定的备份报告也有用户自定义的备份报告
它可以报告任何备份的统计或者这些统计的合并
它支持HTML或者Text格式的报告
它可以发送email告知备份的运行状态
它还可以通过RSS feed的方式提供备份报告

插件

ZRM for MySQL提供了一套plugins扩展
Plugins允许管理员根据自己的环境调整备份进程
它提供了模板使得创建plugins更容易
预先日程plugin
预先备份plugin
在备份之后plugin
拷贝plugin
binary日志解析plugin
快照plugin

ZRM for MySQL套件是使用perl写的，所以使用环境必须支持perl，这个套件支持MySQL的版本有4.0.x（4.0.24或以上版本），4.1.x，5.0.x和5.1.x

在安装ZRM for MySQL时首先需要检查下列事项：
1，MySQL的版本是否是被ZRM for MySQL支持的
2，确认ZRM服务器和mysql服务器上存在mysql用户和组。同时MySQL服务进程是使用相同的用户和组启动的
3，在MySQL服务器和ZRM服务器上的mysql用户的uid和gid要相同
4，ZRM for MySQL需要用到MySql客户端的命令列在了下面，这些命令需要安装在ZRM for MySQL运行的服务器上
5，这些命令被ZRM服务器需要，尽管ZRM for MySQL是备份远程的mysql服务器
mysqladmin
mysqlhotcopy
mysqldump
mysqlbinlog
mysql
6，MySQL ZRM必须的perl模块
perl-DBI
MySQL-perl-DBD
perl-XML-Parser

以上基本准备完毕就可以安装ZRM for MySQL了，安装方法超级简单，下载完rpm包之后直接用

rpm -ivh MySQL-zrm-2.1-1.noarch.rpm

安装即可，如果过程中有报错，请查看相应提示。

包安装好后会在本机的/etc目录下生成mysql-zrm目录，这下面就保存着zrm的配置文件

我的环境vmware6.0，centos5.3，mysql5.1.24，因为是本机测试，我就直接使用mysql的root用户，备份需要的数据300MB左右。下面看我的配置

1，编辑/etc/mysql-zrm/mysql-zrm.conf，请确保这个文件的权限，因为这个文件保存了数据库用户名密码

# Backup level. It can be full or incremental
# Use 0 for full and 1 for incremental backups
# This parameter is optional and default value is full backup.
#
backup-level=0      //我们进行全备份# Backup method
# Values can be "raw" or "logical". Logical backup are backups using
# mysqldump(1) tool
# This parameter is optional and default value is "raw".
#
backup-mode=logical  //备份方式使用logical

 

# Specifies the type of backup
# Values can be "regular" or "quick".
# Quick backup type uses the snapshot itself as the backup
# without copying the data from the snapshot volume
backup-type=regular    //备份类型使用regular

# Directory to which backups are done. All backups are stored under this
# directory.  This parameter is optional and the default
# value is "/var/lib/mysql-zrm"
#
destination=/data/backup   //将备份放到的目录

# Specifies how long the backup should be retained. The value can be
# specified in days (suffix D), weeks (suffix: W), months (suffix: M) or
# years (suffix Y). 30 days in a month and 365 days in a year are assumed
# This parameter is optional and the default is the backups are retained
# forever.
#
retention-policy=10D     //备份文件保留时间，10天

# This parameter should be set to 1 if backups should be compressed. If this
# parameter is set, gzip(1) command is used by default. If different
# compression algorithm should be used, it must be set in "compress-plugin"
# parameter. Default: There is no data compression.
compress=1   //备份完毕是否压缩，这里选择压缩

# This specifies the program to be used for compression. The "compression"
# parameter must be set for this parameter to be used. The compression
# command should also support -d option for uncompress backup images. If
# value is not specified then gzip(1) is used for compression.
compress-plugin=/usr/bin/gzip   //压缩使用的插件

# Databases/Tables in the backup set
#
# One of the "all-databases" or "databases" or "tables"/"database" parameters
# should be specified. If none of the them are specified, "all-databases"
# is assumed.
#

# This parameter should be set to 1 if all databases are part of this backup set
#
#all-databases=1

# List of databases that are part of this backup set. Multiple database
# names are separated by space character. This parameter is ignored if
# "all-databases" is set 1.
#
databases=SNS     //这里选择要备份的数据库

# List of specific tables that are part of this backup set. This parameter
# should not be specified if all tables in the databases in "databases"
# parameter are part of the backup set. Multiple table names should be
# separated by space character. The database to which these tables belong
# to should be specified in "database" parameter.
#
#tables=text user page
#database="wikidb"

#
# MySQL server parameters
#

# MySQL database user used for backup and recovery of the backup set.
# This parameter is optional. If this parameter is not specified, values from
# my.cnf configuration file.
#
user="root"     //备份数据库使用的用户名

# MySQL database user password.
# This parameter is optional. If this parameter is not specified, values from
# my.cnf configuration file or no password is used.
#
password="12345678"       //该用户对应的密码

#Name of Socket file that can be used for connecting to MySQL
#
socket=/tmp/mysql.sock      //mysql的sock文件放置位置

# Directory where MySQL commands can be found. The parameter is optional.
#
mysql-binpath="/usr/local/mysql/bin"           //mysql可执行文件路径

# Directory where MySQL binary logs can be found. The parameter is optional.
#
mysql-binlog-path="/usr/local/mysql/var"       //mysql binlog存放路径

以上更改做完后保存退出，接下来就来进行备份等相应工作

现在来执行mysql备份操作

mysql-zrm-scheduler --now --backup-set dailyrun

可以看到如下返回结果

schedule:INFO: ZRM for MySQL Community Edition - version 2.1
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 2.1
dailyrun:backup:INFO: START OF BACKUP
dailyrun:backup:INFO: PHASE START: Initialization
dailyrun:backup:INFO: The quick backup-type is supported only for snapshot backups. Setting backup-type to 'regular'
dailyrun:backup:INFO: backup-set=dailyrun
dailyrun:backup:INFO: backup-date=20090728191102
dailyrun:backup:INFO: mysql-server-os=Linux/Unix
dailyrun:backup:INFO: backup-type=regular
dailyrun:backup:INFO: host=localhost
dailyrun:backup:INFO: backup-date-epoch=1248779462
dailyrun:backup:INFO: retention-policy=10D
dailyrun:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.1
dailyrun:backup:INFO: mysql-version=5.1.30-log
dailyrun:backup:INFO: backup-directory=/data/backup/daily/dailyrun/20090728191102
dailyrun:backup:INFO: backup-level=0
dailyrun:backup:INFO: backup-mode=logical
dailyrun:backup:INFO: PHASE END: Initialization
dailyrun:backup:INFO: PHASE START: Running pre backup plugin
dailyrun:backup:INFO: PHASE END: Running pre backup plugin
dailyrun:backup:INFO: PHASE START: Flushing logs
dailyrun:backup:INFO: PHASE END: Flushing logs
dailyrun:backup:INFO: PHASE START: Creating logical backup
dailyrun:backup:INFO: logical-databases=SNS
dailyrun:backup:INFO: PHASE END: Creating logical backup
dailyrun:backup:INFO: PHASE START: Calculating backup size & checksums
dailyrun:backup:INFO: next-binlog=mysql-bin.000041
dailyrun:backup:INFO: last-backup=/data/backup/daily/dailyrun/20090726230115
dailyrun:backup:INFO: backup-size=346.16 MB
dailyrun:backup:INFO: PHASE END: Calculating backup size & checksums
dailyrun:backup:INFO: PHASE START: Compression/Encryption
dailyrun:backup:INFO: compress=/usr/bin/gzip
dailyrun:backup:INFO: backup-size-compressed=11.49 MB
dailyrun:backup:INFO: PHASE END: Compression/Encryption
dailyrun:backup:INFO: read-locks-time=00:00:40
dailyrun:backup:INFO: flush-logs-time=00:00:00
dailyrun:backup:INFO: compress-encrypt-time=00:27:20
dailyrun:backup:INFO: backup-time=00:00:58
dailyrun:backup:INFO: backup-status=Backup succeeded
dailyrun:backup:INFO: Backup succeeded
dailyrun:backup:INFO: PHASE START: Running post backup plugin
dailyrun:backup:INFO: PHASE END: Running post backup plugin
dailyrun:backup:INFO: PHASE START: Cleanup
dailyrun:backup:INFO: PHASE END: Cleanup
dailyrun:backup:INFO: END OF BACKUP
/usr/bin/mysql-zrm started successfully

这里我们运行了一个即时的备份，使用这个命令还可以制作备份计划任务，如

# mysql-zrm-scheduler --add --interval weekly --start-time 00:00 --day-of-week 0
schedule:INFO: ZRM for MySQL Community Edition - version 2.1
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
DONE

通过这个命令我们建立了一个每周的周日的午夜0点执行的计划，使用下面的命令我们可以查询我们创建的备份计划

#mysql-zrm-scheduler --query
schedule:INFO: ZRM for MySQL Community Edition - version 2.1
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
0 0 * * 0 /usr/bin/zrm-pre-scheduler --action backup --backup-set BackupSet1 --backup-level 0 --interval weekly

接下来我们查看一下备份的报告

# mysql-zrm-reporter --where backup-set=dailyrun backup-status-info

 

REPORT TYPE : backup-status-info

backup_set  backup_date                  backup_level  backup_status         backup_type       comment
-----------------------------------------------------------------------------------------------------------------------------
dailyrun  Tue 28 Jul 2009 07:11:02                0  Backup succeeded      regular           ----
PM CST

从这个报告中我们可以看到备份的名字，备份时间，备份级别，备份的状态，再来看下面一个报告

# mysql-zrm-reporter --where backup-set=dailyrun --show backup-performance-info

 

REPORT TYPE : backup-performance-info

backup_set  backup_date                  backup_level     backup_size  backup_size_compressed     backup_time   backup_type       compress_encrypt_time
----------------------------------------------------------------------------------------------------------------------------------------------------------------
dailyrun  Tue 28 Jul 2009 07:11:02                0       346.16 MB  11.49 MB                   00:00:58      regular           00:27:20

在这份报告中我们能看到备份的大小，压缩后的大小，备份所用时常等等

下面我们来做一下恢复操作，首先进入到数据库，然后删除SNS这个库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| HAtest             |
| SNS                |
| ldirectordb        |
| mysql              |
| test               |
| wordpress          |
+--------------------+
7 rows in set (0.18 sec)

 

mysql> drop database SNS;
Query OK, 57 rows affected (2.13 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| HAtest             |
| ldirectordb        |
| mysql              |
| test               |
| wordpress          |
+--------------------+
6 rows in set (0.00 sec)

现在可以看到SNS库已经被蚊子我删掉了，下面我们就来恢复，首先来决定一下用哪个备份来恢复

# mysql-zrm-reporter --show restore-info --where backup-set=dailyrun

 

REPORT TYPE : restore-info

backup_set  backup_date                  backup_level  backup_directory                          backup_status         comment
-----------------------------------------------------------------------------------------------------------------------------------------------------
dailyrun  Tue 28 Jul 2009 07:11:02                0  /data/backup/daily/dailyrun/200907281911  Backup succeeded      ----
PM CST                                     02

这个就是我们刚才备份的，下面来从这个恢复我们刚刚删除的库

# mysql-zrm --action restore --backup-set dailyrun --source-directory /data/backup/daily/dailyrun/20090728191102/
restore:INFO: ZRM for MySQL Community Edition - version 2.1
dailyrun:restore:INFO: The quick backup-type is supported only for snapshot backups. Setting backup-type to 'regular'
dailyrun:restore:INFO: Restored database(s) from logical backup:  SNS
dailyrun:restore:INFO: Restore done in 217 seconds.

恢复完毕再来看下数据库中是否有了这个库了

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| HAtest             |
| SNS                |
| ldirectordb        |
| mysql              |
| test               |
| wordpress          |
+--------------------+
7 rows in set (0.08 sec)

 

mysql> use SNS
Database changed
mysql> show tabases;

+-------------------------------+
| Tables_in_SNS    |
+-------------------------------+
| MovieID                   |
| MovieRatings           |
+-------------------------------+
2 rows in set (0.00 sec)

到此，这一篇的ZRM for mysql蚊子就介绍到这里，这个软件功能还是很强大的，蚊子会在后续继续深入的研究