数据库相关

ZRM for MySQL备份远程mysql数据库[技术]

书接上回,上次蚊子就介绍了ZRM备份本地数据库的方法了,这次来介绍一下用ZRM备份远程的mysql数据库的方法,按照ZRM的官方介绍,ZRM远程备份数据库有两种方法
1,使用远程socket连接
2,使用SSH连接
由于蚊子这边实际环境中mysql没有编译成支持ssh,所以这次我主要来讲解使用socket的方式,先来看下远程备份的结构

远程mysql服务器上需要安装的软件:MySQL-zrm-client-2.1-1.noarch.rpm,下载地址:http://www.zmanda.com/downloads/community/ZRM-MySQL/2.1/RPM/MySQL-zrm-client-2.1-1.noarch.rpm
备份服务器上安装软件: MySQL-zrm-2.1-1.noarch.rpm,下载地址:http://www.zmanda.com/downloads/community/ZRM-MySQL/2.1/RPM/MySQL-zrm-2.1-1.noarch.rpm
同时,备份服务器上需要两个perl程序:perl-XML-Parser和perl-DBI,可以使用yum安装

yum -y install perl-XML-Parser perl-DBI

备份工作时,需要使用到msyqladmin等程序,所以mysql-devel套件需要,这个也可以使用yum进行安装

yum -y install mysql-devel

在安装MySQL-zrm-2.1-1.noarch.rpm这个包的时候,需要备份机上有mysql的用户

groupadd mysql
useradd mysql -g mysql -s /nonexistent

远程mysql服务器的配置:

安装MySQL-zrm-client-2.1-1.noarch.rpm

# rpm -ivh MySQL-zrm-client-2.1-1.noarch.rpm
warning: MySQL-zrm-client-2.1-1.noarch.rpm: Header V3 DSA signature: NOKEY, key ID 3c5d1c92
Preparing…                ########################################### [100%]
   1:MySQL-zrm-client       ########################################### [100%]
Aug  6 2009 14:55:17: Reloading xinetd configuration…Reloading configuration: [  OK  ]

然后看下端口是否启动了,默认的zrm-client端口是25300

netstat –ln

tcp        0      0 0.0.0.0:25300               0.0.0.0:*                   LISTEN  

接下来是在mysql中添加备份用户

mysql> grant select,insert,update,create,drop,reload,shutdown,alter,super,lock tables,replication client on *.* to ‘backup_user’@’192.168.211.129’ identified by ‘backup_password’;

这里有一点需要注意,用户密码最好别用特殊字符比如‘$’,不然在做远程备份的时候会报错,具体原因我也不清楚为啥,另外就是如果你的数据库中有show view的操作,那还需要给这个用户赋予show view的权限,不然备份也会报错。

备份服务器:

安装MySQL-zrm-2.1-1.noarch.rpm的方法和上次蚊子那篇文章里一样,同样很多参数的配置,这里就不在详细叙述,需要的请看《ZRM for MySQL学习笔记01》,这里只说配置远程备份需要编辑的部分

1,先在/etc/mysql-zrm/目录下创建远程主机或者ip的目录,比如

mkdir –p /etc/mysql-zrm/211.128

2,编辑/etc/mysql-zrm目录下的mysql-zrm.conf文件,这个文件我们把他当成一个全局文件,一些全局信息主要由这个文件提供,看一下我的配置

backup-mode=logical                        //备份模式

destination=/data/dbbackup             //备份目录

retention-policy=8W                           //备份保存时间,这里设置的是8周

compress=1                                       //表示备份完成后压缩

compress-plugin=/usr/bin/gzip

user="backup_user"                         //远程备份mysql需要的用户名

password="backup_password"     //远程备份mysql用户的密码

mysql-binpath="/usr/bin"                 //备份机上需要执行的程序的路径

mysql-binlog-path="/data/db/mysql"       //远程mysql服务器上binlog的路径

copy-plugin=/usr/share/mysql-zrm/plugins/socket-copy.pl            //执行远程拷贝的脚本

remote-mysql-binpath="/usr/local/mysql/bin"

socket-remote-port="25300"         //远程client的端口,跟远程数据库上启动的端口一致

有了这份全局配置后,之后再对不同的远程mysql做备份时有些信息就不需要配置了。

3,拷贝一份默认的mysql-zrm.conf文件到我们刚建的211.128目录下,并修改如下

destination=/data/dbbackup       //设置备份路径,如果跟全局中的一样这里也同样需要设置,不然之后执行mysql-zrm-reporter的时候会报错

retention-policy=8W                    //针对这个远程服务器设置一个保存策略

databases=Account SNS mysql              //远程mysql数据库需要被备份的库

host="192.168.211.128"          //远程mysql的ip地址,当然也可以使用域名或主机名

4,现在就可以进行测试了

# mysql-zrm-scheduler –now –backup-level 0 –backup-set 211.128
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
211.128:backup:INFO: START OF BACKUP
211.128:backup:INFO: PHASE START: Initialization
211.128:backup:INFO: The quick backup-type is supported only for snapshot backups. Setting backup-type to ‘regular’
211.128:backup:INFO: backup-set=211.128
211.128:backup:INFO: backup-date=20090806152712
211.128:backup:INFO: mysql-server-os=Linux/Unix
211.128:backup:INFO: backup-type=regular
211.128:backup:INFO: host=192.168.211.128
211.128:backup:INFO: backup-date-epoch=1249543632
211.128:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition – version 2.1
211.128:backup:INFO: mysql-version=5.1.34-log
211.128:backup:INFO: backup-directory=/data/dbbackup/211.128/20090806152712
211.128:backup:INFO: backup-level=0
211.128:backup:INFO: backup-mode=logical
211.128:backup:INFO: PHASE END: Initialization
211.128:backup:INFO: PHASE START: Running pre backup plugin
211.128:backup:INFO: PHASE END: Running pre backup plugin
211.128:backup:INFO: PHASE START: Flushing logs
211.128:backup:INFO: PHASE END: Flushing logs
211.128:backup:INFO: PHASE START: Creating logical backup
211.128:backup:INFO: logical-databases=Account SNS mysql
211.128:backup:INFO: PHASE END: Creating logical backup
211.128:backup:INFO: PHASE START: Calculating backup size & checksums
211.128:backup:INFO: next-binlog=mysql-bin.000101
211.128:backup:INFO: backup-size=354.56 MB
211.128:backup:INFO: PHASE END: Calculating backup size & checksums
211.128:backup:INFO: PHASE START: Compression/Encryption
211.128:backup:INFO: compress=/usr/bin/gzip
211.128:backup:INFO: backup-size-compressed=11.83 MB
211.128:backup:INFO: PHASE END: Compression/Encryption
211.128:backup:INFO: read-locks-time=00:00:07
211.128:backup:INFO: flush-logs-time=00:00:01
211.128:backup:INFO: compress-encrypt-time=00:00:57
211.128:backup:INFO: backup-time=00:00:13
211.128:backup:INFO: backup-status=Backup succeeded
211.128:backup:INFO: Backup succeeded
211.128:backup:INFO: PHASE START: Running post backup plugin
211.128:backup:INFO: PHASE END: Running post backup plugin
211.128:backup:INFO: PHASE START: Cleanup
211.128:backup:INFO: PHASE END: Cleanup
211.128:backup:INFO: END OF BACKUP
/usr/bin/mysql-zrm started successfully

到此可以看到远程备份已经成功了,关于reporter等工具的查看可以参考我上一篇的文章,这里就不在赘述了

对于远程备份的几点想法:

1,关于ssh和使用socket方式,我认为,如果纯内网环境的话,使用socket是没有任何问题的,使用ssh反而会增加机器的负担
2,对于数据库的备份策略一定要想好,尽量选择在不繁忙的时候,因为raw方式再回复的时候需要保证mysql的版本一致,所以在选择备份策略上也需要考虑
3,关于备份文件的保存策略也需要考虑,如果需求很细致的话,可以考虑建立不同的目录,放置不同的mysql-zrm.conf文件

Related Post

2 Comments

  1. ZRM远程备份数据在freebsd下的备份服务器及远程mysql服务器怎么配置,分别用哪个安装包?谢谢

Leave a Reply

Your email address will not be published. Required fields are marked *