Zmanda Recovery Manager for MySQL Man pages

Zmanda Recovery Manager for MySQL Man pages

    mysql-zrm - Backup and recovery utility for ZRM for MySQL
    mysql-zrm-reporter - MySQL backup reporting utility
    mysql-zrm-scheduler - backup scheduler utility for ZRM for MySQL
    mysql-zrm.conf - Configuration file for ZRM for MySQL
    mysql-zrm-reporter.conf - Configuration file for ZRM reporter for MySQL
    mysql-zrm-abort-backup - Utility to abort a backup in ZRM for MySQL
    mysql-zrm-backup - Backup utility for ZRM for MySQL
    mysql-zrm-check - Utility to check the given backup set configuration
    mysql-zrm-extract-backup - Utility to extract a compressed and/or encrypted backup in ZRM for MySQL
    mysql-zrm-getconf - Utility to get the options set in the mysql-zrm.conf file.
    mysql-zrm-list - Utility to list backups performed by ZRM for MySQL
    mysql-zrm-manage-backup - Utility to manage on-line (quick) snapshot backups.
    mysql-zrm-parse-binlogs - Utility to parse MySQL binary logs for ZRM for MySQL
    mysql-zrm-purge - Utility to purge backups that have gone past retention policy date in ZRM for MySQL.
    mysql-zrm-restore - Recovery utility for ZRM for MySQL
    mysql-zrm-verify-backup - Utility to verify backup in ZRM for MySQL 

1、MySQL数据库备份

To do a backup of all databases now, use the mysql-zrm-scheduler tool.

# mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0

A backup set dailyrun is created and backups are done to the /var/lib/mysql-zrm directory.

2、定时任务
To schedule weekly full backups on Sunday at 2am:

# mysql-zrm-scheduler --interval weekly --backup-set dailyrun --start 02:00

3、报告
For more information on backup scheduling, see MySQL ZRM backup section.
Backup reports

The mysql-zrm-reporter tool can obtain the status of a backup run as well as produce various reports on the backup runs.

  #  mysql-zrm-reporter --where backup-set=dailyrun --show backup-status-info
  backup_set  backup_date                             backup_level  backup_status         comment
  ----------------------------------------------------------------------------------------------------
  dailyrun    Sun 10 Sep 2006 02:15:23 AM PDT         0             Backup succeeded      ----
  dailyrun    Sun 17 Sep 2006 02:16:43 AM PDT         0             Backup succeeded      ----

For more information on MySQL ZRM backup reporting, please see Backup reports section.
MySQL database restoration

Restoration of backups requires two steps:

    Identify the backup image to be restored. Run a backup report (restore-full-info) to find out all backup images that are available: 

#  /usr/bin/mysql-zrm-reporter -show restore-info --where backup-set=dailyrun
    backup_set  backup_date             backup_level  backup_directory
   -------------------------------------------------------------------------------------------
    dailyrun    Sun 10 Sep 2006                      0  /var/lib/mysql-zrm/dailyrun/
                02:15:23 AM PDT                         20060910021523
    dailyrun    Sun 17 Aug 2006                      0  /var/lib/mysql-zrm/dailyrun/
                02:16:43 AM PDT                         20060917021643

5、恢复备份    
Use the mysql-zrm tool to restore the database from a full backup image. 

# mysql-zrm --action restore --backup-set dailyrun \
 --source-directory /var/lib/mysql-zrm/dailyrun/20060917021643
MySQL server has been shutdown. Please restart after verification.


6、赋权限
    mysql>  grant select, insert, update, create, drop, reload, shutdown, alter, super, lock tables, replication client on *.* to 'backup-user'@'localhost' identified by '';

    mysql> show grants for 'backup-user'@'localhost';
    Grants for backup-user@localhost
    | GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, ALTER, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup-user'@'localhost' IDENTIFIED BY PASSWORD '591051bb593d2f5b' WITH GRANT OPTION |

7、检查配置文件
Checking Backup Configuration

he action check of mysql-zrm command is used to verify that ZRM for MySQL is correctly configured for the backup set.

It performs following checks for a particular backup set:

    It is able to read and write to the destination directory.
    Whether the TMPDIR is writable.
    If /etc/mysql-zrm/<backup-set name> is writable.
    Whether mysql-zrm is able to connect to MySQL server. 

mysql-zrm  --action check

    Specify the backup set you to whose configuration you want to check. The command will be 

mysql-zrm --action check --backup-set Backupset1

    The action returns the following when the configuration is correct. 

INFO: ZRM for MySQL - version built from source
WARNING: Binary logging is off. Incremental backups cannot be done.
INFO: Configuration check successful

    If the configuration is incorrect it will exit with a set of error messages that reflect the problems found. The following is an example of the returned error 

INFO: ZRM for MySQL - version built from source
ERROR: Unable to open logfile /var/log/mysql-zrm/mysql-zrm.log.
ERROR: No write permission in /var/lib/mysql-zrm
ERROR: No write permission in /etc/mysql-zrm/BackupSet1
ERROR: No write permissions on /etc/mysql-zrm/BackupSet1/last_backup
ERROR: Errors found during configuration check


mysql-zrm --action [ backup |
                           restore |
                           schedule-backup |
                           report |
                           list |
                           purge  |
                           parse-binlogs  |
                           check   |
                           verify-backup  |
                           extract-backup  |
                           abort-backup ]
mysql-zrm --action backup --help

 mysql-zrm-reporter  [--fields  name1, name2, name3, name4, name5 ....]
                      [--destination dir ]
                      [--where  fieldname=value]
                      [--type  html]
                      [--output filename]
                      [--latest ]
                      [--noheader ]
                      [--show  backup-method-info |
                               backup-status-info |
                               backup-retention-info |
                               backup-performance-info |
                               restore-full-info |
                               restore-incr-info |
                               replication-info |
                               backup-app-performance-info ]
                      [--help]

 EXAMPLES

    To display backup date, location of backups, and backup set name use: 

 mysql-zrm-reporter --destination /var/lib/mysql-zrm/backup   --fields backup-date,backup-directory,backup-set

    To display backup date, backup set name, and size of backup use: 

 mysql-zrm-reporter --destination /var/lib/mysql-zrm/backup   --fields backup-date,backup-set,backup-size

    To display size of backup, backup set name, and date of backup for"config1" backup set use: 

 mysql-zrm-reporter --destination /var/lib/mysql-zrm/backup       --fields      backup-date, backup-set, backup-size --where backup-set=config1

    To display backup status report for "config1" backup set use: 

 mysql-zrm-reporter  --destination /var/lib/mysql-zrm/backup   --show backup-status-info --where backup-set=config1

    To display backup status report for "config1" backup set in HTML format and write a file use: 

 mysql-zrm-reporter --destination /var/lib/mysql-zrm/backup --show backup-status-info     --where backup-set=config1 --type html --output myreport.html


 
 mysql-zrm-scheduler --add|--query|--delete|--now
                    [--start-time start time]
                    [--day-of-week day of week]
                    [--day-of-month day of month]
                    [--interval interval]
                    [--backup-level level]
                    [--backup-set backupset name]
                    [--help]

 EXAMPLES

    To schedule a daily mysql-zrm run that starts at 1:35pm: 

$ mysql-zrm-scheduler --add -interval daily --start 02:02 --backup-set behe_ministite

    To schedule a weekly mysql-zrm run on Tuesday and Wednesday: 

$ mysql-zrm-scheduler --add -interval weekly --day-of-week 2-3

    To schedule a monthly mysql-zrm run on the tenth day of each month: 

$ mysql-zrm-scheduler    --add    --interval  monthly --day-of-month  10

    To query scheduled runs: 

$ mysql-zrm-scheduler --query
35  13  *  *  * /usr/bin/zrm-pre-scheduler --action backup --backup-set BackupSet1 --interval daily
0  2 * * 2-3  /usr/bin/zrm-pre-scheduler  --action  backup --backup-set BackupSet1  --interval weekly
0  0  10  *  *  /usr/bin/zrm-pre-scheduler --action backup --backup-set BackupSet1 --interval monthly

    To delete a daily mysql-zrm run with specific start time: 

$  mysql-zrm-scheduler  --delete  --interval daily --start 08:20


mysql-zrm-restore
                      [--backup-set name]
                      [--source-directory directory name]
                      [--bin-logs "/fullpath/name1 /fullpath/name2 ..."]
                      [--mysql-shutdown | --no-mysql-shutdown]
                      [--retry-count count]
                      [--retry-delay seconds]
                      [--replication|--noreplication]
                      [--all-databases]
                      [--databases "name1 name2 ..."]
                      [--start-position #]
                      [--stop-position #]
                      [--offset #]
                      [--start-datetime name]
                      [--stop-datetime name]
                      [--copy-plugin plugin]
                      [--ssh-user user]
                      [--socket-remote-port port]
                      [--passfile filename]
                      [--user user]
                      [--password password]
                      [--host hostname]
                      [--port portnumber]
                      [--socket name]
                      [--ssl-options "MySQL ssl options"]
                      [--mysql-binpath location of MySQL commands]
                      [--tmpdir temp_directory]
                      [--options-file filename]
                      [--quiet|--no-quiet]
                      [--verbose]
                      [--help]
 EXAMPLES

    For restoring backup image dated 18 Aug 2006 (backup image can be either full or incremental backup): 

mysql-zrm  --action restore --source-directory /var/lib/mysql-zrm/backupset1/20060818121532

    The above command will restore all databases (full or incremental as the case may be) that were backed up. 

mysql-zrm  --action restore --source-directory /var/lib/mysql-zrm/backupset1/20060818121532 --databases db1 db2 db3

    The above command does a selective restore of the specified databases. 

mysql-zrm    --action   restore   --bin-logs   /var/lib/mysql-zrm/backupset1/20060818121532/mysql-bin.[0-9]* /var/lib/mysql-zrm/backupset1/20060819121532/mysql-bin.[0-9]*

    The above command does a selective restore of all specified binary log files using a single MySQL server connection. 

20 个数据库设计最佳实践

原谅我吧,我喜欢保存文字,不喜欢保存链接,所以我不知道从哪弄来的了。

20 个数据库设计最佳实践
1. 使用明确、统一的标明和列名,例如 School, SchoolCourse, CourceID。
2. 数据表名使用单数而不是复数,例如 StudentCourse,而不是StudentCourses。
3. 数据表名不要使用空格。
4. 数据表名不要使用不必要的前缀或者后缀,例如使用School,而不是TblSchool,或者SchoolTable等等。
5. 数据库中的密码要加密,到应用中再解密。 (其实就是散列存储、单向加密)
6. 使用整数作为ID字段,也许现在没有这个必要,但是将来需要,例如关联表,索引等等。
7. 使用整数字段做索引,否则会带来很大的性能问题 。
8. 使用 bit 作为布尔字段,使用整数或者varcha是浪费。同时,这类字段应该以“Is”开头。
9. 要经过认证才能访问数据库,不要给每一个用户管理员权限。
10. 尽量避免使用“select *”,而使用“select [required_column_list]”以获得更好的性能。
11. 假如程序代码比较复杂,使用ORM框架,例如hibernate,iBatis。ORM框架的性能问题可以通过详细的配置去解决。
12. 分割不常使用的数据表到不同的物理存储以获得更好的性能。
13. 对于关键数据库,使用安全备份系统,例如集群,同步等等。
14. 使用外键,非空等限制来保证数据的完整性,不要把所有的东西都扔给程序。
15. 缺乏数据库文档是致命的。你应该为你的数据库设计写文档,包括触发器、存储过程和其他脚本。
16. 对于经常使用的查询和大型数据表,要使用索引。数据分析工具可以帮助你决定如何建立索引。
17. 数据库服务器和网页服务器应该放在不同的机器上。这回提高安全性,并减轻CPU压力。
18. Image和blob字段不应该定义在常用的数据表中,否则会影响性能。
19. 范式(Normalization)要按照要求使用以提高性能。Normalization做的不够会导致数据冗余,而过度Normalization 会导致太多的join和数据表,这两种情况都会影响性能。
20. 多花点时间在数据库设计上,否则你将来会付出加倍的时间来偿还。

1. Use well defined and consistent names for tables and columns (e.g. School, StudentCourse, CourseID …).
2. Use singular for table names (i.e. use StudentCourse instead of StudentCourses). Table represents a collection of entities, there is no need for plural names.
3. Don’t use spaces for table names. Otherwise you will have to use ‘{‘, ‘[‘, ‘“’ etc. characters to define tables (i.e. for accesing table Student Course you’ll write “Student Course”. StudentCourse is much better).
4. Don’t use unnecessary prefixes or suffixes for table names (i.e. use School instead of TblSchool, SchoolTable etc.).
5. Keep passwords as encrypted for security. Decrypt them in application when required.
6. Use integer id fields for all tables. If id is not required for the time being, it may be required in the future (for association tables, indexing …).
7. Choose columns with the integer data type (or its variants) for indexing. varchar column indexing will cause performance problems.
8. Use bit fields for boolean values. Using integer or varchar is unnecessarily storage consuming. Also start those column names with “Is”.
9. Provide authentication for database access. Don’t give admin role to each user.
10. Avoid “select *” queries until it is really needed. Use “select [required_columns_list]” for better performance.
11. Use an ORM (object relational mapping) framework (i.e. hibernate, iBatis …) if application code is big enough. Performance issues of ORM frameworks can be handled by detailed configuration parameters.
12. Partition big and unused/rarely used tables/table parts to different physical storages for better query performance.
13. For big, sensitive and mission critic database systems, use disaster recovery and security services like failover clustering, auto backups, replication etc.
14. Use constraints (foreign key, check, not null …) for data integrity. Don’t give whole control to application code.
15. Lack of database documentation is evil. Document your database design with ER schemas and instructions. Also write comment lines for your triggers, stored procedures and other scripts.
16. Use indexes for frequently used queries on big tables. Analyser tools can be used to determine where indexes will be defined. For queries retrieving a range of rows, clustered indexes are usually better. For point queries, non-clustered indexes are usually better.
17. Database server and the web server must be placed in different machines. This will provide more security (attackers can’t access data directly) and server CPU and memory performance will be better because of reduced request number and process usage.
18. Image and blob data columns must not be defined in frequently queried tables because of performance issues. These data must be placed in separate tables and their pointer can be used in queried tables.
19. Normalization must be used as required, to optimize the performance. Under-normalization will cause excessive repetition of data, over-normalization will cause excessive joins across too many tables. Both of them will get worse performance.
20. Spend time for database modeling and design as much as required. Otherwise saved(!) design time will cause (saved(!) design time) * 10/100/1000 maintenance and re-design time.

Mysql的管理维护工具maatkit

下载地址:http://maatkit.googlecode.com/files/maatkit-7540.tar.gz

Maatkit Essential command-line utilities for MySQL.
维护mysql的命令如下:
mk-archiver Archive rows from a MySQL table into another table or a file.
mk-checksum-filter Filter checksums from mk-table-checksum.
mk-config-diff Diff MySQL configuration files and server variables.
mk-deadlock-logger Extract and log MySQL deadlock information.
mk-duplicate-key-checker Find duplicate indexes and foreign keys on MySQL tables.
mk-error-log Find new and different MySQL error log entries.
mk-fifo-split Split files and pipe lines to a fifo without really splitting.
mk-find Find MySQL tables and execute actions, like GNU find.
mk-heartbeat Monitor MySQL replication delay.
mk-index-usage Read queries from a log and analyze how they use indexes.
mk-kill Kill MySQL queries that match certain criteria.
mk-loadavg Watch MySQL load and take action when it gets too high.
mk-log-player Replay MySQL query logs.
mk-merge-mqd-results Merge multiple mk-query-digest reports into one.
mk-parallel-dump (DEPRECATED) Dump MySQL tables in parallel.
mk-parallel-restore (DEPRECATED) Load files into MySQL in parallel.
mk-profile-compact Compact the output from mk-query-profiler.
mk-purge-logs Purge binary logs on a master based on purge rules.
mk-query-advisor Analyze queries and advise on possible problems.
mk-query-digest Analyze query execution logs and generate a query report, filter, replay, or transform queries for MySQL, PostgreSQL, memcached, and more.
mk-query-profiler Execute SQL statements and print statistics, or measure activity caused by other processes.
mk-show-grants Canonicalize and print MySQL grants so you can effectively replicate, compare and version-control them.
mk-slave-delay Make a MySQL slave server lag behind its master.
mk-slave-find Find and print replication hierarchy tree of MySQL slaves.
mk-slave-move Move a MySQL slave around in the replication hierarchy.
mk-slave-prefetch Pipeline relay logs on a MySQL slave to pre-warm caches.
mk-slave-restart Watch and restart MySQL replication after errors.
mk-table-checksum Perform an online replication consistency check, or checksum MySQL tables efficiently on one or many servers.
mk-table-sync Synchronize MySQL table data efficiently.
mk-table-usage Read queries from a log and analyze how they use tables.
mk-tcp-model Transform tcpdump into metrics that permit performance and scalability modeling.
mk-upgrade Execute queries on multiple servers and check for differences.
mk-variable-advisor Analyze MySQL variables and advise on possible problems.
mk-visual-explain Format EXPLAIN output as a tree.

ZRM for MySQL

ZRM是Zmanda公司出品的一套用Perl写的Mysql备份与还原工具,分商业版和社区版。
ZRM主要功能包括:
支持完整备份和增量备份;
支持逻辑备份和裸备份;
支持多种存储引擎;
支持远程备份;
备份时可选择压缩和加密;
还原操作简单;
有详细的报表信息;
可通过邮件通知你的备份结果;
可定义保留策略;
可指定计划任务定期备份;

可采用源码和rpm的方式,在安装有epel源的系统上,可直接yum install MySQL-zrm
http://www.zmanda.com/downloads/community/ZRM-MySQL/2.2/RPM/MySQL-zrm-2.2.0-1.noarch.rpm

http://www.zmanda.com/downloads/community/ZRM-MySQL/2.2/Source/MySQL-zrm-2.2.0.tar.gz

[root@monitor mysql-zrm]# cd /etc/mysql-zrm/
[root@monitor mysql-zrm]# ls
cactiback  mysql-zrm.conf  mysql-zrm-release  mysql-zrm-reporter.conf  RSS.header
说明:cactiback是备份项目名称,每个备份都需要有,是一个目录,里面还有配置文件;mysql-zrm.conf是默认配置模板

[root@monitor mysql-zrm]# cd cactiback/
[root@monitor cactiback]# ls
last_backup  mysql-zrm.conf
说明:last_backup是已经存在的备份信息

[root@monitor cactiback]# sed -n '/^#/!'p mysql-zrm.conf |sed '/^$/'d
backup-level=0
backup-mode=logical
destination=/db_back/
retention-policy=6M
compress=1
compress-plugin=/usr/bin/gzip
databases=cacti
user="root"
password="nagios"
host="localhost"
port=3306
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8
verbose=1
mailto="monit@163.com"

创建备份
[root@monitor cactiback]# mysql-zrm-scheduler --now --backup-set cactiback

查看备份
[root@monitor cactiback]# mysql-zrm-reporter --show restore-info --where backup-set=cactiback

REPORT TYPE : restore-info

backup_set  backup_date                  backup_level  backup_directory                          backup_status         comment
-----------------------------------------------------------------------------------------------------------------------------------------------------
cactiback  Thu 22 Mar 2012 04:47:24                0  /db_back/cactiback/20120322164724         Backup succeeded      ----
PM CST
cactiback  Fri 09 Mar 2012 04:26:25                0  /db_back/cactiback/20120309162625         Backup succeeded      ----
PM CST
cactiback  Fri 09 Mar 2012 04:14:22                0  /db_back/cactiback/20120309161422         Backup succeeded      ----
PM CST
增加计划任务
[root@monitor cactiback]# mysql-zrm-scheduler --add --interval weekly --start-time 02:02 --day-of-week 0 --backup-level 0 --backup-set cactiback

[root@localhost ~]#  crontab -l
2 2 * * 0 /usr/bin/zrm-pre-scheduler --action backup --backup-set cactiback --backup-level 0 --interval weekly
0 4 * * * /usr/bin/mysql-zrm --action purge