Linux|系统管理|WEB开发

关注Linux,系统管理,WEB开发以及开源世界

使用Amanda ZRM备份远程MySQL数据库

| Comments

本文写道最后的时候，我才发现ZRM for MySQL的一个致命问题，就我目前的理解和测试来看，它恢复数据的时候是采取覆盖的方式，举个例子，假定某台数据库服务器上有两个数据库test1,test2，你备份了这两个数据库，某一天，test2数据库里的某一个表坏了，那么它无法做到仅仅只是恢复损坏的表，甚至无法做到仅仅恢复test2，而是直接把test1,test2都恢复。那这绝对是一个噩梦。 但愿我目前的理解是错误的，只是我实在找不到控制恢复粒度的地方。下面的文字就当闲着无聊看看吧.

Update：
原谅我的无知，ZRM备份是可以指定恢复特定的数据库的，刚才今天mwf的站点数据库需要恢复，测试了一下，完全没有问题。
具体的恢复办法，请看文末。 Zmanda Recovery Manager(ZRM)提供了备份MySQL数据库的能力，并且支持两种备份模式：逻辑备份和裸备份，有关详细的介绍，可以看官方的介绍。下面给出一个备份的实例。

先介绍备份的环境，如果大家看过之前的那篇Amanda集中备份实例，那么大概就知道是什么样的一个网络架构了，承接上文，这里备份服务器还是cp1.domain.com，MySQL数据库分别运行在cp2.domain.com和cp3.domain.com上，需要备份其所有的数据库。

一、服务端的安装和配置

首先从官方站点下载对应系统的服务端程序，然后安装。注意：服务端与perl-DBI,MySQL-client程序库有依赖关系，安装之前先安装这个两个包。 安装完后,/etc/mysql-zrm/是其配置文件的所在地。/etc/mysql-zrm/目录下有一个mysql-zrm.conf文件，这是最重要的配置文件。在/etc/mysql-zrm/目录下，你可以创建一些目录，每一个目录就是一个备份集的名称，我这里为了直观，采取了主机名的方式来创建备份集

mkdir /etc/mysql-zrm/{cp2.domain.com,cp3.domain.com}
cp  /etc/mysql-zrm/{mysql-zrm.conf,cp2.domain.com/,cp3.domain.com/}

/etc/mysql-zrm/mysql-zrm.conf 可以做为全局配置文件，针对每一台需要备份的数据库的不同，可以在备份集目录里的mysql-zrm.conf再定义，这里的定义可以覆盖全局定义。 首先，我们把一些全局定义信息配置在/etc/mysql-zrm/mysql-zrm.conf文件里

#cat /etc/mysql-zrm/mysql-zrm.conf
backup-level=0
backup-mode=logical
backup-type=regular
destination=/yourpath/db
compress=1
compress-plugin=/bin/gzip
encrypt=1
encrypt-plugin="/usr/share/mysql-zrm/plugins/encrypt.pl"
decrypt-option="-d"
all-databases=1
user="backup-user"
password="securepassword"
mailto="your＠domain.com"

上面的配置信息应该比较直观。 然后配置每一台MySQL服务器的一些特有参数

#cat /etc/mysql-zrm/cp2.domain.com/mysql-zrm.conf
host="cp2.domain.com"
destination=/yourpath/db

#cat /etc/mysql-zrm/cp3.domain.com/mysql-zrm.conf
host="cp3.domain.com"
destination=/yourpath/db

创建加密的密码文件

[root@cp1:~# touch /etc/mysql-zrm/.passphrase
[root@cp1:~# echo encryptpass > /etc/mysql-zrm/.passphrase
[root@cp1:~# chmod 700 /etc/mysql-zrm/.passphrase

二、配置客户端

这里有两台机器，cp2和cp3，因为我们备份的类型一样，所以cp2的配置过程和cp3的配置过程一样，这里只描述cp2的配置过程

  1. 登录cp2服务器

  2. 用MySQL的管理员帐号（一般是root）登录MySQL数据库

  3. 创建一个用户备份的帐号并授予一定的权限,帐号的定义在备份服务器的/etc/mysql-zrm/mysql-zrm.conf文件里有定义，这里帐号是backup-user，密码是securepassword

1
2
3

mysql> grant select, insert, update, create, drop, reload, shutdown,
    -> alter, super, lock tables, replication client on *.* to
    -> 'backup-user'@'cp1.domain.com' identified by 'securepassword';

提示：如果你需要备份的数据库不是全部，那么在授权的时候，最好是指定，而不是上面的*.*方式，安全为主！

三、测试备份

配置还是比较简单的，现在我们开始测试。 首先的用root帐号登录cp1机器，然后执行下面的指令：

root@cp1:/etc/mysql-zrm# mysql-zrm --action backup --backup-level 0 --backup-set cp3.domain.com
backup:INFO: ZRM for MySQL Community Edition - version 2.1
cp3.domain.com:backup:INFO: START OF BACKUP
cp3.domain.com:backup:INFO: PHASE START: Initialization
cp3.domain.com:backup:INFO: The quick backup-type is supported only for snapshot backups. Setting backup-type to 'regular'
cp3.domain.com:backup:WARNING: Binary logging is off.
cp3.domain.com:backup:INFO: backup-set=cp3.domain.com
cp3.domain.com:backup:INFO: backup-date=20090415180321
cp3.domain.com:backup:INFO: mysql-server-os=Linux/Unix
cp3.domain.com:backup:INFO: backup-type=regular
cp3.domain.com:backup:INFO: host=cp3.3yidc.cn
cp3.domain.com:backup:INFO: backup-date-epoch=1239789801
cp3.domain.com:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.1
cp3.domain.com:backup:INFO: mysql-version=5.0.45
cp3.domain.com:backup:INFO: backup-directory=/yourpath/db/cp3.domain.com/20090415180321
cp3.domain.com:backup:INFO: backup-level=0
cp3.domain.com:backup:INFO: backup-mode=logical
cp3.domain.com:backup:INFO: PHASE END: Initialization
cp3.domain.com:backup:INFO: PHASE START: Running pre backup plugin
cp3.domain.com:backup:INFO: PHASE END: Running pre backup plugin
cp3.domain.com:backup:INFO: PHASE START: Flushing logs
cp3.domain.com:backup:INFO: PHASE END: Flushing logs
cp3.domain.com:backup:INFO: PHASE START: Creating logical backup
cp3.domain.com:backup:INFO: logical-databases=apachelogs dtc fireyang hnnoritz mysql s567 wordpress
cp3.domain.com:backup:INFO: PHASE END: Creating logical backup
cp3.domain.com:backup:INFO: PHASE START: Calculating backup size & checksums
cp3.domain.com:backup:INFO: last-backup=/csdata/backup/db/cp3.domain.com/20090415180226
cp3.domain.com:backup:INFO: backup-size=4.04 MB
cp3.domain.com:backup:INFO: PHASE END: Calculating backup size & checksums
cp3.domain.com:backup:INFO: PHASE START: Compression/Encryption
cp3.domain.com:backup:INFO: compress=/bin/gzip
cp3.domain.com:backup:INFO: encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
cp3.domain.com:backup:INFO: decrypt-option=-d
cp3.domain.com:backup:INFO: backup-size-compressed=0.46 MB
cp3.domain.com:backup:INFO: PHASE END: Compression/Encryption
cp3.domain.com:backup:INFO: read-locks-time=00:00:02
cp3.domain.com:backup:INFO: flush-logs-time=00:00:00
cp3.domain.com:backup:INFO: compress-encrypt-time=00:00:02
cp3.domain.com:backup:INFO: backup-time=00:00:02
cp3.domain.com:backup:INFO: backup-status=Backup succeeded
cp3.domain.com:backup:INFO: Backup succeeded
cp3.domain.com:backup:INFO: PHASE START: Running post backup plugin
cp3.domain.com:backup:INFO: PHASE END: Running post backup plugin
cp3.domain.com:backup:INFO: PHASE START: Mailing backup report
cp3.domain.com:backup:INFO: PHASE END: Mailing backup report
cp3.domain.com:backup:INFO: PHASE START: Cleanup
cp3.domain.com:backup:INFO: PHASE END: Cleanup
cp3.domain.com:backup:INFO: END OF BACKUP

以上是备份cp3.domain.com的数据库，用同样的指令可以备份cp2.domain.com数据库。

四、检验

如果上述备份过程没有报错，那么接下来就要看看备份的数据是不是有效了。在cp1机器上执行下面的指令

root@cp1:~# mysql-zrm --action  verify-backup --backup-set cp3.domain.com
verify-backup:INFO: ZRM for MySQL Community Edition - version 2.1
cp3.domain.com:verify-backup:INFO: Verification successful

root@cp1:~# mysql-zrm --action  verify-backup --backup-set cp2.domain.com
verify-backup:INFO: ZRM for MySQL Community Edition - version 2.1
cp2.domain.com:verify-backup:INFO: Verification successful

校验都没有报错，我们还可以看看备份的性能，备份的详细信息等，更多的使用方式，可以查看mysql-zrm(1)

root@cp1:~# mysql-zrm --action report --show backup-performance-info 

REPORT TYPE : backup-performance-info 

backup_set  backup_date                  backup_level     backup_size  backup_size_compressed     backup_time
----------------------------------------------------------------------------------------------------------------------------------------------------------------
cp3.domain.com  Wed 15 Apr 2009 06:03:21                0         4.04 MB  0.46 MB                    00:00:02
cp3.domain.com  Wed 15 Apr 2009 06:02:26                0         4.04 MB  0.46 MB                    00:00:02
cp3.domain.com  Wed 15 Apr 2009 06:01:38                0         4.04 MB  0.46 MB                    00:00:03
cp2.domain.com  Wed 15 Apr 2009 05:19:57                0       315.05 MB  121.38 MB                  00:02:36
cp3.domain.com  Wed 15 Apr 2009 05:19:33                0         4.03 MB  0.46 MB                    00:00:02      

root@cp1:~# mysql-zrm --action list --backup-set cp3.domain.com
list:INFO: ZRM for MySQL Community Edition - version 2.1
0 /csdata/backup/db/cp3.domain.com/20090415180321
Index File
{   decrypt-option=-d
    backup-status=Backup succeeded
    mysql-version=5.0.45
    backup-date=20090415180321
    mysql-zrm-version=ZRM for MySQL Community Edition - version 2.1
    compress-encrypt-time=00:00:02
    backup-type=regular
    logical-databases=apachelogs dtc fireyang hnnoritz mysql s567 wordpress
    mysql-server-os=Linux/Unix
    backup-level=0
    last-backup=/csdata/backup/db/cp3.domain.com/20090415180226
    backup-size-compressed=0.46 MB
    backup-size=4.04 MB
    encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
    backup-set=cp3.domain.com
    backup-time=00:00:02
    host=cp3.3yidc.cn
    backup-directory=/csdata/backup/db/cp3.domain.com/20090415180321
    flush-logs-time=00:00:00
    read-locks-time=00:00:02
    compress=/bin/gzip
    backup-date-epoch=1239789801
}

五、恢复

假定cp2服务器上的数据库ehcpbackup20090403170240丢失了（这里是我人工删除了这个库），我们看看如何从备份里恢复。

  1. 登录cp1机器

  2. 执行下面的命令，看看cp2机器上的数据库现在备份在哪里(为了显示直观，输出结果有处理）

     root@cp1:~# mysql-zrm-reporter --show restore-info --where backup-set=cp2.domain.com

 REPORT TYPE : restore-info 

 backup_set  backup_date      backup_level  backup_directory
 ------------------------------------------------------------------------------
 cp3.domain.com  15/4 2009    0  /yourpath/db/cp2.domain.com/20090415171957

  3. 然后执行真正的备份指令：

     root@cp1:~#mysql-zrm --action restore --backup-set cp2.domain.com --source-directory \
  /yourpath/db/cp2.domain.com/20090415171957
 INFO: ZRM for MySQL Enteprise Edition - version 1.1
 WARNING: Binary logging is off. Incremental backups cannot be done.
 INFO: Restore done in 169 seconds.
 MySQL server has been shutdown. Please restart after verification.

  4. 重启cp2机器上的MySQL服务。

到此恢复完成。

Update:2009-04-29

恢复特定的数据库

以上恢复方式是恢复全部的数据库，如果仅仅是一个数据库坏了，那么我可以采取 --database "dbname1 dbname2"的方式恢复。比如：

root@cp1:~# mysql-zrm --action restore --backup-set cp2.3yidc.cn --source-directory /csdata/backup/db/cp2.3yidc.cn/20090419030002/ --database "ewmwiki ewmuc" --verbose
restore:INFO: ZRM for MySQL Community Edition - version 2.1
restore:INFO: Reading options from file /etc/mysql-zrm/mysql-zrm.conf
cp2.3yidc.cn:restore:INFO: Reading options from file /etc/mysql-zrm/cp2.3yidc.cn/mysql-zrm.conf
cp2.3yidc.cn:restore:INFO: Using /usr/share/mysql-zrm/plugins/ssh-copy.pl as the default plugin since copy-plugin has not been specified
cp2.3yidc.cn:restore:INFO: The quick backup-type is supported only for snapshot backups. Setting backup-type to 'regular'
cp2.3yidc.cn:restore:INFO: Mail address: mlsx.xplore@gmail.com is ok
cp2.3yidc.cn:restore:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/cp2.3yidc.cn/tmpbRcFA.conf
cp2.3yidc.cn:restore:INFO: {
cp2.3yidc.cn:restore:INFO:     encrypt-plugin=/usr/share/mysql-zrm/plugins/encrypt.pl
cp2.3yidc.cn:restore:INFO:     backup-level=0
cp2.3yidc.cn:restore:INFO:     encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
cp2.3yidc.cn:restore:INFO:     mailto=mlsx.xplore@gmail.com
cp2.3yidc.cn:restore:INFO:     all-databases=1
cp2.3yidc.cn:restore:INFO:     destination=/csdata/backup/db
cp2.3yidc.cn:restore:INFO:     databases=ewmwiki ewmuc
cp2.3yidc.cn:restore:INFO:     decrypt-option=-d
cp2.3yidc.cn:restore:INFO:     source-directory=/csdata/backup/db/cp2.3yidc.cn/20090419030002/
cp2.3yidc.cn:restore:INFO:     host=cp2.3yidc.cn
cp2.3yidc.cn:restore:INFO:     password=******
cp2.3yidc.cn:restore:INFO:     backup-mode=logical
cp2.3yidc.cn:restore:INFO:     backup-type=regular
cp2.3yidc.cn:restore:INFO:     compress-plugin=/bin/gzip
cp2.3yidc.cn:restore:INFO:     user=backup-user
cp2.3yidc.cn:restore:INFO:     compress=/bin/gzip
cp2.3yidc.cn:restore:INFO:     copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
cp2.3yidc.cn:restore:INFO: }
cp2.3yidc.cn:restore:INFO: Getting mysql variables
cp2.3yidc.cn:restore:INFO: mysqladmin --user="backup-user" --password="*****" --host="cp2.3yidc.cn" variables
cp2.3yidc.cn:restore:INFO: datadir is /var/lib/mysql/
cp2.3yidc.cn:restore:INFO: mysql_version is 5.0.45
cp2.3yidc.cn:restore:WARNING: Binary logging is off.
cp2.3yidc.cn:restore:INFO: InnoDB data file are /var/lib/mysql/ibdata1
cp2.3yidc.cn:restore:INFO: InnoDB log dir is /var/lib/mysql/.
cp2.3yidc.cn:restore:INFO: Command used is 'cat "/csdata/backup/db/cp2.3yidc.cn/20090419030002/backup-data"  | "/usr/share/mysql-zrm/plugins/encrypt.pl" -d |  "/bin/gzip" -d  | tar  --same-owner -xpsC  "/csdata/backup/db/cp2.3yidc.cn/20090419030002/"  2>/tmp/aAi1Fwp4DT'
cp2.3yidc.cn:restore:INFO: restoring using command mysql --user="backup-user" --password="*****" --host="cp2.3yidc.cn" -e "set character_set_client=utf8;set character_set_connection=utf8;set character_set_database=utf8;set character_set_results=utf8;set character_set_server=utf8;source /tmp/hGKhPYbpB8;"
cp2.3yidc.cn:restore:INFO: Restored database(s) from logical backup:  ewmwiki ewmuc
cp2.3yidc.cn:restore:INFO: Removing all of the uncompressed/unencrypted data
cp2.3yidc.cn:restore:INFO: Restore done in 30 seconds

Comments