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)
