MySQL备份使用ZRM对于MySQL 2.0

MySQL备份使用ZRM对于MySQL 2.0 MySQL的Zmanda恢复管理器(ZRM) 简化了需要易于使用的数据库管理员的生命周期...

MySQL备份使用ZRM for MySQL 2.0

用于MySQL的Zmanda Recovery Manager(ZRM)简化了数据库管理员的生命周期,需要一个易于使用但灵活而强大的MySQL服务器的备份和恢复解决方案。 重要功能有:

*安排MySQL数据库的完整和增量逻辑或原始备份
*集中备份管理
*执行与存储引擎和MySQL配置最匹配的备份
*获取关于备份状态的电子邮件通知
*监控并获取有关备份的报告(包括RSS Feed)
*验证您的备份图像
*压缩并加密备份图像
*实施站点或应用程序特定的备份策略
*轻松恢复数据库到任何时间点或任何特定的数据库事件
*定制插件,以适应您的环境的MySQL备份
*使用Linux LVM和Solaris ZFS快照的MySQL备份

社区项目2.0版上周发布。 它可以从Zmanda下载页面下载。 它支持所有Linux和Solaris发行版。 该文档可在ZRM维基上获得ZRM论坛可用于获取有关该项目的问题。

这个例子假设ZRM服务器和MySQL服务器是相同的机器。 我们正在备份MySQL数据库

myisamnetflix

到同一台运行Ubuntu 7.04的机器。

ZRM用于MySQL安装

*必须以超级用户身份进行安装。

* ZRM for MySQL需要perl 5.8.7或更高版本。 Ubuntu 7.04已经安装了perl 5.8.8。

*安装perl-DBD和perl-XML-parser模块

# apt-get install libxml-parser-perl libdbd-mysql-perl

Zmanda下载页面下载ZRM for MySQL debian软件包。

*安装ZRM for MySQL(ZRM服务器包是足够的,因为MySQL服务器和ZRM服务器是相同的机器)。

# dpkg -i mysql-zrm_2.0_all.deb

选择以前取消选择的包mysql-zrm。
(读取数据库... 108342当前安装的文件和目录。)
开箱mysql-zrm(从mysql-zrm_2.0_all.deb)...
设置mysql-zrm(2.0)...
更新以前备份的数据集的所有权

MySQL服务器配置

*检查MySQL服务器是否正在运行。 如果没有安装MySQL服务器,请使用“apt-get”命令安装“mysql-server”。 使用mysqladmin命令(mysqladmin --user root password boot12)使用密码更新“root”MySQL服务器。 我们使用“boot12”作为root密码。 该用户将用于进行MySQL备份和还原。 最好用特定的用户使用最小的权限进行MySQL备份,而不是使用“root”MySQL用户。

* MySQL服务器必须以“mysql”用户身份运行,“mysql”操作系统用户应属于“mysql”组。 默认安装的ZRM for MySQL需要MySQL服务器作为“mysql”用户运行。

*“ps”输出显示mysql服务器正在运行使用默认的MySQL端口

 mysql    22034 21995  0 14:38 pts/2    00:00:09 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock

*在MySQL服务器上启用二进制日志记录。 必须启用二进制日志记录才能对MySQL服务器进行增量备份。

*编辑/etc/mysql/my.cnf配置文件。 在mysqld部分添加“log-bin”。

 [mysqld]
 log-bin

*我们有mysql数据库“myisamnetflix”,其中包含两个表。 我们将支持这个数据库。 该数据库使用MyISAM存储引擎:

 mysql> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | myisamnetflix      |
 | mysql              |
 +--------------------+
 3 rows in set (0.00 sec)

 mysql> use myisamnetflix;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A

 Database changed
 mysql> show tables;
 +-------------------------+
 | Tables_in_myisamnetflix |
 +-------------------------+
 | MovieID                 |
 | MovieRatings            |
 +-------------------------+
 2 rows in set (0.00 sec)

 mysql> select count(*) from MovieID;
 +----------+
 | count(*) |
 +----------+
 |    17770 |
 +----------+

* MySQL客户端命令安装在/ usr / bin /目录下。 如果没有,请在mysql-zrm.conf中配置客户端命令位置和二进制日志位置。

ZRM配置

*这应该是mysql用户完成的:

$ id

 uid=1002(mysql) gid=1001(mysql) groups=1001(mysql)

*创建备份集目录。 备份集称为“netflix”。

$ mkdir /etc/mysql-zrm/netflix

*创建mysql-zrm.conf配置文件。 启用备份压缩,并备份“myisamnetflix”数据库。 还指定了MySQL二进制日志的位置(“mysql-binlog-path”)。

$ cat /etc/mysql-zrm/netflix/mysql-zrm.conf

 host="localhost"
 databases="myisamnetflix"
 password="boot12"
 user="root"
 compress=1
 mysql-binlog-path="/var/log/mysql"

执行ZRM备份

*这个应该做为“mysql”用户。

*使用“mysql-zrm-scheduler”立即对数据库进行完全备份。

$ mysql-zrm-scheduler --now --backup-set netflix --backup-level 0

 schedule:INFO: ZRM for MySQL Community Edition - version 2.0
 Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
 backup:INFO: ZRM for MySQL Community Edition - version 2.0
 netflix:backup:INFO: START OF BACKUP
 netflix:backup:INFO: PHASE START: Initialization
 netflix:backup:INFO: backup-set=netflix
 netflix:backup:INFO: backup-date=20080326161652
 netflix:backup:INFO: mysql-server-os=Linux/Unix
 netflix:backup:INFO: host=localhost
 netflix:backup:INFO: backup-date-epoch=1206573412
 netflix:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
 netflix:backup:INFO: mysql-version=5.0.38-Ubuntu_0ubuntu1.4-log
 netflix:backup:INFO: backup-directory=/var/lib/mysql-zrm/netflix/20080326161652
 netflix:backup:INFO: backup-level=0
 netflix:backup:INFO: backup-mode=raw
 netflix:backup:INFO: PHASE END: Initialization
 netflix:backup:INFO: PHASE START: Running pre backup plugin
 netflix:backup:INFO: PHASE END: Running pre backup plugin
 netflix:backup:INFO: PHASE START: Flushing logs
 netflix:backup:INFO: PHASE END: Flushing logs
 netflix:backup:INFO: PHASE START: Find table type
 netflix:backup:INFO: PHASE END: Find table type
 netflix:backup:INFO: PHASE START: Creating raw backup
 netflix:backup:INFO: raw-databases=myisamnetflix
 netflix:backup:INFO: PHASE END: Creating raw backup
 netflix:backup:INFO: PHASE START: Calculating backup size & checksums
 netflix:backup:INFO: next-binlog=mysql-bin.000009
 netflix:backup:INFO: backup-size=122.27 MB
 netflix:backup:INFO: PHASE END: Calculating backup size & checksums
 netflix:backup:INFO: PHASE START: Compression/Encryption
 netflix:backup:INFO: compress=
 netflix:backup:INFO: backup-size-compressed=37.65 MB
 netflix:backup:INFO: PHASE END: Compression/Encryption
 netflix:backup:INFO: read-locks-time=00:00:01
 netflix:backup:INFO: flush-logs-time=00:00:00
 netflix:backup:INFO: compress-encrypt-time=00:02:20
 netflix:backup:INFO: backup-time=00:00:15
 netflix:backup:INFO: backup-status=Backup succeeded
 netflix:backup:INFO: Backup succeeded
 netflix:backup:INFO: PHASE START: Running post backup plugin
 netflix:backup:INFO: PHASE END: Running post backup plugin
 netflix:backup:INFO: PHASE START: Mailing backup report
 netflix:backup:INFO: PHASE END: Mailing backup report
 netflix:backup:INFO: PHASE START: Cleanup
 netflix:backup:INFO: PHASE END: Cleanup
 netflix:backup:INFO: END OF BACKUP
 /usr/bin/mysql-zrm started successfully

* Delete some entries from the "myisamnetflix" database (so that we can do incremental backup of the database)
 mysql> use myisamnetflix;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A

 Database changed

 mysql> delete from MovieID where MovieTitle = "Alien Hunter";
 Query OK, 1 rows affected (0.01 sec)

*执行备份集的增量备份。

$ mysql-zrm-scheduler --now --backup-set netflix --backup-level 1

 schedule:INFO: ZRM for MySQL Community Edition - version 2.0
 Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
 backup:INFO: ZRM for MySQL Community Edition - version 2.0
 netflix:backup:INFO: START OF BACKUP
 netflix:backup:INFO: PHASE START: Initialization
 netflix:backup:INFO: backup-set=netflix
 netflix:backup:INFO: backup-date=20080326164433
 netflix:backup:INFO: mysql-server-os=Linux/Unix
 netflix:backup:INFO: host=localhost
 netflix:backup:INFO: backup-date-epoch=1206575073
 netflix:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
 netflix:backup:INFO: mysql-version=5.0.38-Ubuntu_0ubuntu1.4-log
 netflix:backup:INFO: backup-directory=/var/lib/mysql-zrm/netflix/20080326164433
 netflix:backup:INFO: backup-level=1
 netflix:backup:INFO: PHASE END: Initialization
 netflix:backup:INFO: PHASE START: Running pre backup plugin
 netflix:backup:INFO: PHASE END: Running pre backup plugin
 netflix:backup:INFO: PHASE START: Flushing logs
 netflix:backup:INFO: PHASE END: Flushing logs
 netflix:backup:INFO: PHASE START: Creating incremental backup
 netflix:backup:INFO: incremental=mysql-bin.[0-9]*
 netflix:backup:INFO: PHASE END: Creating incremental backup
 netflix:backup:INFO: PHASE START: Calculating backup size & checksums
 netflix:backup:INFO: next-binlog=mysql-bin.000013
 netflix:backup:INFO: last-backup=/var/lib/mysql-zrm/netflix/20080326162210
 netflix:backup:INFO: backup-size=0.03 MB
 netflix:backup:INFO: PHASE END: Calculating backup size & checksums
 netflix:backup:INFO: PHASE START: Compression/Encryption
 netflix:backup:INFO: compress=
 netflix:backup:INFO: backup-size-compressed=0.00 MB
 netflix:backup:INFO: PHASE END: Compression/Encryption
 netflix:backup:INFO: read-locks-time=00:00:00
 netflix:backup:INFO: flush-logs-time=00:00:00
 netflix:backup:INFO: compress-encrypt-time=00:00:00
 netflix:backup:INFO: backup-time=00:00:00
 netflix:backup:INFO: backup-status=Backup succeeded
 netflix:backup:INFO: Backup succeeded
 netflix:backup:INFO: PHASE START: Running post backup plugin
 netflix:backup:INFO: PHASE END: Running post backup plugin
 netflix:backup:INFO: PHASE START: Mailing backup report
 netflix:backup:INFO: PHASE END: Mailing backup report
 netflix:backup:INFO: PHASE START: Cleanup
 netflix:backup:INFO: PHASE END: Cleanup
 netflix:backup:INFO: END OF BACKUP
 /usr/bin/mysql-zrm started successfully

ZRM备份报告

*使用“mysql-zrm-reporter”查看备份的状态。

$ /usr/bin/mysql-zrm-reporter --where backup-set=netflix --show backup-status-info

 REPORT TYPE : backup-status-info

          backup_set  backup_date                  backup_level  backup_status         comment
 -----------------------------------------------------------------------------------------------------------
             netflix  Wed 26 Mar 2008 04:44:33                1  Backup succeeded      ----
                      PM PDT
             netflix  Wed 26 Mar 2008 04:16:52                0  Backup succeeded      ----
                      PM PDT

* ZRM报告还可以提供有关MySQL应用程序影响的信息。

$ /usr/bin/mysql-zrm-reporter --where backup-set=netflix --show  backup-app-performance-info

 REPORT TYPE : backup-app-performance-info

          backup_set  backup_date                  backup_level     backup_size  backup_time   read_locks_time     flush_logs_time
-------------------------------------------------------------------------------------------------------------------------------------
             netflix  Wed 26 Mar 2008 04:44:33                1         0.03 MB  00:00:00      00:00:00            00:00:00
                      PM PDT
             netflix  Wed 26 Mar 2008 04:16:52                0       122.27 MB  00:00:15      00:00:01            00:00:00
                      PM PDT

数据库恢复

*使用ZRM报告工具来确定MySQL备份映像的位置。

$ /usr/bin/mysql-zrm-reporter --where backup-set=netflix --show restore-info

 REPORT TYPE : restore-info

          backup_set  backup_date                  backup_level  backup_directory                           backup_status         comment
-----------------------------------------------------------------------------------------------------------------------------------------------------
             netflix  Wed 26 Mar 2008 04:44:33                1  /var/lib/mysql-zrm/netflix/20080326164433  Backup succeeded      ----
                      PM PDT
             netflix  Wed 26 Mar 2008 04:16:52                0  /var/lib/mysql-zrm/netflix/20080326161652  Backup succeeded      ----
                      PM PDT

*您可以解析增量备份以识别感兴趣的数据库事件。 在我们的示例中,我们将寻找“DELETE”事件。

$ /usr/bin/mysql-zrm-parse-binlogs --source-directory /var/lib/mysql-zrm/netflix/20080326164433 | grep delete

 parse-binlogs:INFO: ZRM for MySQL Community Edition - version 2.0
 /var/lib/mysql-zrm/netflix/20080326164433/mysql-bin.000011 | 13634 | 08-03-26 16:28:03 | Query | use myisamnetflix/*!*/; delete from MovieID where MovieTitle = "Alien Hunter"/*!*/;

*从16:16:52完成的完整备份恢复数据库。

$ /usr/bin/mysql-zrm-restore --user=root --password=boot12 --source-directory=/var/lib/mysql-zrm/netflix/20080326161652

 restore:INFO: ZRM for MySQL Community Edition - version 2.0
 BackupSet1:restore:INFO: Restored database from raw backup: myisamnetflix
 BackupSet1:restore:INFO: Restore done in 9 seconds.
 MySQL server has been shutdown. Please restart after verification.

* Restart the MySQL server
 # /etc/init.d/mysql restart
 * Stopping MySQL database server mysqld                                 [ OK ]
 * Starting MySQL database server mysqld                                 [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.

*检查数据库恢复。

 mysql> use myisamnetflix;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A

 Database changed
 mysql> select * from MovieID where MovieTitle = "Alien Hunter";
 +---------+------+--------------+
 | MovieID | Year | MovieTitle   |
 +---------+------+--------------+
 |   17770 | 2003 | Alien Hunter |
 +---------+------+--------------+
 1 row in set (0.02 sec)