benepfist / xtrabackup-manager Goto Github PK

XBM code is written in the classic C/PHP style where errors are signaled by 
returning "false" or other similar value. The caller must check the return 
value from each function call and if necessary handle the error. Example:

scheduledBackup.class.php:

                       if( ! ($res = $conn->query($sql) ) ) {
                                $this->error = 'scheduledBackup->pollHasValidSeed: '."Error: Query: $sql \nFailed with MySQL Error: $conn->error";
                                return false;
                        }

                        if( $res->num_rows > 1 ) {
                                $this->error = 'scheduledBackup->pollHasValidSeed: '."Error: Found more than one valid seed for this backup. This should not happen.";
                                return false;
                        } elseif( $res->num_rows == 1 ) {


Unfortunately, this style makes the code hard to read.

An added complication is inside constructor methods of many classes, since it 
is not possible to return a value from the constructor. In the following 
example the constructor accepts any value, and errors are only checked and 
acted on later. In the worst case the same check needs to be done in many 
different places. All of this is bad design:

mysqlType.class.php

                function __construct($id) {
                        $this->id = $id;
                        $this->log = false;
                }

                function getInfo() {

                        global $config;

                        if(!is_numeric($this->id)) {
                                $this->error = 'mysqlType->getInfo: '."Error: The ID for this object is not an integer.";
                                return false;
                        }
                        ...




The preferable method is to instead use the exception paradigm, supported in 
PHP 5. For instance, the previous code would then be better written like this:

                function __construct($id) {
                        if(!is_numeric($id)) {
                                throw new Exception('mysqlType->getInfo: '."Error: The ID given is not an integer.");
                        }
                        $this->id = $id;
                        $this->log = false;
                }

                function getInfo() {

                        global $config;
                        ...

Similarly, from the calling function, it is possible to run through several 
statements at once, and catch any potential errors at the end of the code 
block, not between each statement:

try {
 function1();
 function2();
 function3();
catch(Exception $e) {
  print basename(__FILE__) . ': ' . $e->getMessage() . "\n";
  die();
}

Unfortunately, core PHP functions do not throw exceptions, so errors need to be 
checked the traditional way still.

Attached patch fixes some bugs in xbm-conftool to provide better usability:

 - There was a bug that exporting an empty MySQL table would not work. This is fixed by checking for strlen($data)>0.
 - Generally on errors xbm-conftool would leave files behind both in the confdata/ and /tmp/ directories and these would result in errors on re-run because MySQL refuses to overwrite files on SELECT INTO OUTFILE. Now added cleanup code that tries to delete working files before throwing exception.

Please review and commit.

7) choosing xtrabackup binary should be automatic.
It disturbs me that you have to define the mysql_type_id in
scheduled_backups. innobackupex figures the mysql version out
automatically, and stores it in a file xtrabackup_binary that is
stored in the backup directory (along with other meta data such as
checkpoints, binlog_info...). From there you can find out the correct
xtrabackup binary to use in following steps (like for --prepare).

 - identifying mysql version and using correct xtrabackup binary
should be transparent to end user.
 - when applying logs and doing incremental backups one should use the
same xtrabackup binary using the information recorded in
xtrabackup_binary.
 - when doing incremental backups, we should check the mysqld version (xtrabackup doesn't do/need this?) and assert that it is the same as it was for the full backup.
 - If minor version of mysqld has changed, emit warning but allow backup to proceed. If major version has changed, the incremental backup should abort and fail - user is required to start fresh from a full backup in such case.
 - note that storing mysql version into the backup meta data is actually a good innobackupex feature and we could contribute it there instead of inside XBM.

When running a backup or some task, ctrl-c and other signals are not caught and 
handled so the script will usually just die wherever it is -- this can cause 
XBM to leave behind stale temp files and other things that will never get 
cleaned up.

cleanup: Currently the value of ./xbm-backup -s ... is an integer, the PK/id of 
the row in the scheduled_backup table. This should instead be a user given 
short name (like hostname, or anything more descriptive).

It was agreed that integers/auto_increment will remain as the id / primary key 
for tables, and end user visible short names will be secondary indexes.

What steps will reproduce the problem?

My settings in includes/config.php do not lead to a valid MySQL server:

$ ./xbm-flush.php 

xbm-flush.php -- Xtrabackup Manager v0.1 - Copyright 2011 Marin Software

Flushing backup schedule to crontab for current user...
PHP Warning:  mysqli::mysqli(): Headers and client library minor version 
mismatch. Headers:50149 Library:50204 in 
/home/hingo/hacking/xtrabackup-manager/includes/db.classes.php on line 77
PHP Warning:  mysqli::mysqli(): (HY000/2002): Can't connect to local MySQL 
server through socket '/mysqldb/tmp/mysql.sock' (2) in 
/home/hingo/hacking/xtrabackup-manager/includes/db.classes.php on line 77
PHP Warning:  Invalid argument supplied for foreach() in 
/home/hingo/hacking/xtrabackup-manager/includes/service.classes.php on line 386
Completed OK!


What is the expected output? What do you see instead?

Should print an error end die().
Should definitively not print "Completed OK!"

For some reason this check:
            if($conn->connect_error) {
...on line 58 of db.classes.php isn't working.

Please use labels and text to provide additional information.

mysqli

Add functionality to xbm command-line client to be able to see which backup 
tasks are running and in what state they are in.

Heyho,

I Still got problems to run a successfull backup heres the full backup log:


2012-01-14 11:25:40 +0100 [INFO] : [ Initializing Scheduled Backup "backup" (ID 
#2) for host: localhost ...  ]
2012-01-14 11:25:40 +0100 [INFO] : [ Using Backup Strategy: Rotating sets of 
Incremental Backups ]
2012-01-14 11:25:40 +0100 [INFO] : [ Using DAY_OF_WEEK as the rotation 
method... ]
2012-01-14 11:25:40 +0100 [INFO] : [ No snapshots found for this scheduled 
backup at all - taking an initial full backup. ]
2012-01-14 11:25:40 +0100 [INFO] : [ Attempting to find available ports for 
use... ]
2012-01-14 11:25:40 +0100 [INFO] : [ Got lock on port 10000. ]
2012-01-14 11:25:41 +0100 [INFO] : [ Started Netcat (nc) listener on port 10000 
to receive backup tar stream into directory /home/xbm/backup/localhost/59 ... ]
2012-01-14 11:25:41 +0100 [INFO] : [ Running FULL xtrabackup snapshot of 
/home/xbm/backup via ssh: mysql@localhost ... ]
2012-01-14 11:42:12 +0100 [INFO] : [ Cleaning up files after failure... ]
2012-01-14 11:42:14 +0100 [ERROR] : [ 
genericBackupTaker->takeFullBackupSnapshot: Error: There was an error backing 
up - The process returned code 13. The output from the backup is as follows:


SERVER MODT

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 2009-2011.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql  Ver 14.14 Distrib 5.5.19, for Linux (x86_64) using 
readline 5.1
innobackupex: Using mysql server version Copyright (c) 2000, 2011, Oracle 
and/or its affiliates. All rights reserved.

innobackupex: Created backup directory /home/xbm/backup
120114 11:25:42  innobackupex: Starting mysql with options:  
--password=xxxxxxxx --user='backup' --unbuffered --
120114 11:25:42  innobackupex: Connected to database with mysql child process 
(pid=1063)
120114 11:25:44  innobackupex: Connection to database server closed

120114 11:25:44  innobackupex: Starting ibbackup with command: xtrabackup 
--backup --suspend-at-end --log-stream --target-dir=./
innobackupex: Waiting for ibbackup (pid=1072) to suspend
innobackupex: Suspend file '/home/mysql/data/xtrabackup_suspended'

xtrabackup: suspend-at-end is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/mysql/data
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 268435456
xtrabackup: use O_DIRECT
120114 11:25:44  InnoDB: Warning: allocated tablespace 191, old maximum was 9
xtrabackup: Stream mode.
>> log scanned up to (2410672570807)

120114 11:25:46  innobackupex: Continuing after ibbackup has suspended

innobackupex: Starting to backup InnoDB tables and indexes
innobackupex: from original InnoDB data directory '/home/mysql/data'
innobackupex: Backing up as tar stream 'ibdata1'
tar4ibd: using O_DIRECT for the input file.
>> log scanned up to (2410672570807)
innobackupex: Backing up file '/home/mysql/data/xxx/tracking.ibd'
innobackupex: Backing up files '/home/mysql/data/xxx/*.ibd' (11 files)
innobackupex: Backing up files '/home/mysql/data/xxx/*.ibd' (65 files)
>> log scanned up to (2410672570807)
>> log scanned up to (2410672571280)
>> log scanned up to (2410672572229)
>> log scanned up to (2410672572229)
>> log scanned up to (2410672572229)
>> log scanned up to (2410672572229)
>> log scanned up to (2410672573452)
>> log scanned up to (2410672573452)
>> log scanned up to (2410672573452)
>> log scanned up to (2410672573452)
>> log scanned up to (2410672573452)
>> log scanned up to (2410672573452)
>> log scanned up to (2410672573452)
>> log scanned up to (2410672573452)
>> log scanned up to (2410672610936)
>> log scanned up to (2410672610936)
>> log scanned up to (2410672610936)
>> log scanned up to (2410672610936)
>> log scanned up to (2410672610946)
>> log scanned up to (2410672610946)
>> log scanned up to (2410672611419)
>> log scanned up to (2410672611894)
>> log scanned up to (2410672611894)
>> log scanned up to (2410672611894)
>> log scanned up to (2410672611894)
>> log scanned up to (2410672611894)
>> log scanned up to (2410672611894)
>> log scanned up to (2410672611894)
>> log scanned up to (2410672611894)
>> log scanned up to (2410672611894)
>> log scanned up to (2410672611894)
>> log scanned up to (2410672611894)
>> log scanned up to (2410672614281)
>> log scanned up to (2410672614281)
>> log scanned up to (2410672614281)
>> log scanned up to (2410672614281)
>> log scanned up to (2410672614281)
>> log scanned up to (2410672614281)
>> log scanned up to (2410672614281)
>> log scanned up to (2410672614711)
>> log scanned up to (2410672614711)
>> log scanned up to (2410672615183)
>> log scanned up to (2410672615183)
>> log scanned up to (2410672615183)
>> log scanned up to (2410672615656)
>> log scanned up to (2410672615666)
>> log scanned up to (2410672615666)
>> log scanned up to (2410672616140)
>> log scanned up to (2410672616140)
>> log scanned up to (2410672616140)
>> log scanned up to (2410672616140)
>> log scanned up to (2410672616570)
>> log scanned up to (2410672616570)
>> log scanned up to (2410672616570)
>> log scanned up to (2410672617045)
>> log scanned up to (2410672618252)
>> log scanned up to (2410672618727)
>> log scanned up to (2410672618727)
>> log scanned up to (2410672618727)
>> log scanned up to (2410672618727)
>> log scanned up to (2410672619202)
>> log scanned up to (2410672619676)
>> log scanned up to (2410672619676)
>> log scanned up to (2410672619676)
>> log scanned up to (2410672620147)
>> log scanned up to (2410672620147)
>> log scanned up to (2410672620147)
>> log scanned up to (2410672620147)
>> log scanned up to (2410672620147)
>> log scanned up to (2410672620622)
>> log scanned up to (2410672620632)
>> log scanned up to (2410672620632)
>> log scanned up to (2410672621106)
>> log scanned up to (2410672621106)
>> log scanned up to (2410672621106)
>> log scanned up to (2410672621580)
>> log scanned up to (2410672622512)
>> log scanned up to (2410672622512)
>> log scanned up to (2410672622512)
>> log scanned up to (2410672622512)
>> log scanned up to (2410672622512)
>> log scanned up to (2410672625774)
>> log scanned up to (2410672625774)
>> log scanned up to (2410672625774)
>> log scanned up to (2410672626248)
>> log scanned up to (2410672626248)
>> log scanned up to (2410672626248)
>> log scanned up to (2410672626722)
>> log scanned up to (2410672626722)
>> log scanned up to (2410672626722)
>> log scanned up to (2410672626722)
>> log scanned up to (2410672627180)
>> log scanned up to (2410672627190)
>> log scanned up to (2410672627190)
>> log scanned up to (2410672627190)
>> log scanned up to (2410672627190)
>> log scanned up to (2410672627190)
>> log scanned up to (2410672627190)
>> log scanned up to (2410672627190)
>> log scanned up to (2410672627190)
>> log scanned up to (2410672627190)
>> log scanned up to (2410672627190)
>> log scanned up to (2410672628415)
>> log scanned up to (2410672628415)
>> log scanned up to (2410672628415)
>> log scanned up to (2410672628889)
>> log scanned up to (2410672628889)
>> log scanned up to (2410672628889)
>> log scanned up to (2410672628889)
>> log scanned up to (2410672628889)
>> log scanned up to (2410672628889)
>> log scanned up to (2410672628889)
>> log scanned up to (2410672628889)
>> log scanned up to (2410672629363)
>> log scanned up to (2410672629363)
>> log scanned up to (2410672629363)
>> log scanned up to (2410672629363)
>> log scanned up to (2410672629373)
>> log scanned up to (2410672629373)
>> log scanned up to (2410672629373)
>> log scanned up to (2410672629373)
>> log scanned up to (2410672629373)
>> log scanned up to (2410672629373)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410672629959)
>> log scanned up to (2410675315019)
>> log scanned up to (2410675427118)
>> log scanned up to (2410675427118)
>> log scanned up to (2410675436094)
>> log scanned up to (2410679126623)
>> log scanned up to (2410682677165)
>> log scanned up to (2410682985067)
>> log scanned up to (2410682985067)
>> log scanned up to (2410682986016)
>> log scanned up to (2410682986016)
>> log scanned up to (2410682986016)
>> log scanned up to (2410682986016)
>> log scanned up to (2410682986475)
>> log scanned up to (2410682986475)
>> log scanned up to (2410682986475)
>> log scanned up to (2410682987547)
>> log scanned up to (2410682988004)
>> log scanned up to (2410682988004)
>> log scanned up to (2410682988004)
>> log scanned up to (2410682988489)
>> log scanned up to (2410682988489)
>> log scanned up to (2410682988489)
>> log scanned up to (2410682988489)
>> log scanned up to (2410682988489)
>> log scanned up to (2410682988964)
>> log scanned up to (2410682988964)
>> log scanned up to (2410682988964)
>> log scanned up to (2410682989460)
>> log scanned up to (2410682990904)
>> log scanned up to (2410682990904)
>> log scanned up to (2410682990904)
>> log scanned up to (2410682990904)
>> log scanned up to (2410682990904)
>> log scanned up to (2410682991379)
>> log scanned up to (2410682991379)
>> log scanned up to (2410682991379)
>> log scanned up to (2410682991379)
>> log scanned up to (2410683041682)
>> log scanned up to (2410683041682)
>> log scanned up to (2410691836329)
>> log scanned up to (2410691836329)
>> log scanned up to (2410691836329)
>> log scanned up to (2410691836329)
>> log scanned up to (2410691836329)
>> log scanned up to (2410691836329)
>> log scanned up to (2410691836329)
>> log scanned up to (2410691836329)
>> log scanned up to (2410691836329)
>> log scanned up to (2410691836329)
>> log scanned up to (2410691836329)
>> log scanned up to (2410691836329)
>> log scanned up to (2410691837986)
>> log scanned up to (2410691837986)
innobackupex: Backing up files '/home/mysql/data/xxx/*.ibd' (47 files)
innobackupex: Backing up file '/home/mysql/data/xxx/customer.ibd'
innobackupex: Backing up files '/home/mysql/data/xbm/*.ibd' (11 files)
120114 11:42:02  innobackupex: Starting mysql with options:  
--password=xxxxxxxx --user='backup' --unbuffered --
120114 11:42:02  innobackupex: Connected to database with mysql child process 
(pid=4433)
>> log scanned up to (2410691837986)
innobackupex:: Not checking slave open temp tables for --safe-slave-backup 
because host is not a slave

120114 11:42:08  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex: subdirectories of '/home/mysql/data'
innobackupex: Backing up files 
'/home/mysql/data/xxx/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (84 
files)
innobackupex: Backing up files 
'/home/mysql/data/xxx/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 
files)
innobackupex: Backing up file '/home/mysql/data/xxx/visitorlog.frm'
innobackupex: Backing up file '/home/mysql/data/xxx/db.opt'
innobackupex: Backing up file '/home/mysql/data/xxx/tracking.frm'
innobackupex: Backing up files 
'/home/mysql/data/xxx/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (19 
files)
innobackupex: Backing up files 
'/home/mysql/data/xxx/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (66 
files)
innobackupex: Backing up files 
'/home/mysql/data/xxx/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (52 
files)
innobackupex: Backing up file '/home/mysql/data/xxx/db.opt'
innobackupex: Backing up file '/home/mysql/data/xxx/customer.frm'
innobackupex: Backing up files 
'/home/mysql/data/xbm/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (15 
files)
innobackupex: Backing up files 
'/home/mysql/data/performance_schema/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,
opt,par}' (18 files)
120114 11:42:09  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, 
.TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files

innobackupex: Resuming ibbackup

xtrabackup: The latest check point (for incremental): '2410691837986'
>> log scanned up to (2410691837986)
xtrabackup: Transaction log of lsn (2410672566856) to (2410691837986) was 
copied.
120114 11:42:09  innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/home/xbm/backup'
innobackupex: MySQL slave binlog position: master host '', filename '', 
position 
Cannot open file /home/xbm/backup/xtrabackup_binary: Keine Berechtigung
 ]
2012-01-14 11:42:14 +0100 [INFO] : [ Released lock on port 10000. ]



well the backup says: 

Cannot open file /home/xbm/backup/xtrabackup_binary: Keine Berechtigung (Access 
Denied), but it seems to be the wrong path it trys to open? Shouldnt be the 
xtrabackup_binary file in /home/xbm/backup/localhost/ ?
Thanks for your help ^_~

Enviroment:
xtrabackup -version
xtrabackup version 1.6.4 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) 
(revision id: undefined)

cat /etc/debian_version
6.0.3

Put a bad cron_expression into scheduled_backups and try to xbm-flush

Remove last_backup time from the hosts table and create a new method on the 
host class to collect this from the backup_snapshots table directly.

According to PHP documentation here: 
http://us2.php.net/manual/en/function.date-default-timezone-set.php

.. date_default_timezone_set() should be used to explicitly set the timezone 
used by date/time functions -- XBM uses this for logging purposes.

Need to add a configurable to config.php to set timezone and then call 
date_default_set_timezone a part of the init process.

When ssh:ing into the remote host, XBM should create an empty
working directory and cd into it, then run innobackupex and xtrabackup
from there. It is not nice to create lot's of files in the home
directory where you end up after login.

Please review

Currently when running backup snapshots all xtrabackup output is buffered in 
memory and only flushed to the logs/STDOUT when the command returns, it would 
be nice if output could somehow be progressively flushed so you could see 
progress as it happens, rather than waiting until completion/error.

Currently XBM is rather sloppy in it's use of DB connections, constantly 
instantiating a new Db connection for almost every query.

The code should make use of a singleton so that the same dbConnetion can be 
re-used once established.

Test restore of snapshot created by MERGE method against a real DB.

Provide better output into the info log / host log at the end of backups where 
the snapshot retention process is being applied.

Hi Lachlan

Just a quickie: I think you should have a link to your blog on the front page, 
since it is currently the main source of news about this project. I suggest you 
add a new section like

News

In addition to the mailing list, the best way to follow up on Xtrabackup 
Manager news is <a href="">Lachlan's blog</a>.

Schedule two backup jobs for different hosts at the exact same time with email 
alerts enabled.

An error like this will occur for one of the backup jobs, while the other 
succeeds:

A fatal error occurred while attempting to run the following backup

Host: XXXXXX - XXXXX
Scheduled Backup: nightlyBackup
Exception details:
queueManager->getTicketNumber: Error: Query: INSERT INTO queue_tickets 
(queue_ticket_id, queue_name, entry_time, pid) VALUES (NULL, 
'scheduledBackup:GLOBAL', NOW(), 6598)
Failed with MySQL Error: Duplicate entry 'scheduledBackup:GLOBAL-2012-01-12 
18:00:00' for key 2Trace:
#0 
/export/home/xbm/xtrabackup-manager/includes/backupSnapshotTaker.class.php(148):
 queueManager->getTicketNumber('scheduledBackup...')
#1 /export/home/xbm/xtrabackup-manager/xbm-backup(108): 
backupSnapshotTaker->takeScheduledBackupSnapshot(Object(scheduledBackup))
#2 {main}

Write phpdoc style comments into the code.

5) Verification
I want to verify the backup after it completes.
 - copy the database and start a mysql server on top of it.
 - I'm planning to use something like maatkit checksum script to make
a "before and after" kind of verification. Details to be investigated.
 - could also do sanity checks for instance that size of data is
approximately right compared to origin and/or last weeks backup.

Hi

This are comments based on reviewing, not a bug as such. In the absence of 
mailing list, I'll use the issue tracker, but I'm abusing it by posting entire 
review in one rather than making separate issues:

Filenames:

Since these are intended to be run from the command line, it is nicer if you 
drop the ".php" extension. Just name the scripts:
xbm-backup
xbm-flush
xbm-info


**************************************************************
sql/schema_init.sql
**************************************************************
--
-- Table structure for table `mysql_types`
--

DROP TABLE IF EXISTS `mysql_types`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `mysql_types` (
  `mysql_type_id` int(10) unsigned NOT NULL auto_increment,
  `type_name` varchar(256) NOT NULL default '',
  `xtrabackup_binary` varchar(128) NOT NULL default '',
  PRIMARY KEY  (`mysql_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `mysql_types`
--

LOCK TABLES `mysql_types` WRITE;
/*!40000 ALTER TABLE `mysql_types` DISABLE KEYS */;
INSERT INTO `mysql_types` VALUES (1,'Percona Server 5.1 w/ InnoDB 
Plugin','xtrabackup'),(2,'MySQL 5.1 w/ InnoDB Plugin','xtrabackup'),(3,'Percona 
Server 5.0 w/ built-in InnoDB','xtrabackup_51'),(4,'MySQL 5.0 w/ built-in 
InnoDB','xtrabackup_51'),(5,'MySQL 5.1 w/ built-in 
InnoDB','xtrabackup_51'),(6,'Percona Server 5.5','xtrabackup_55'),(7,'MySQL 
5.5','xtrabackup_55');
/*!40000 ALTER TABLE `mysql_types` ENABLE KEYS */;
UNLOCK TABLES;

*****************************************************

First comment:

Do you think it would be wiser to use a short string for id:
  `mysql_type_id` char(12) NOT NULL

The idea here is to use values such as 
ps51pl, my51pl, ps50, my50, my51, ps55, my55
instead of
1, 2, 3...
...for id. It seems it could be useful if we ensure that these id's are 
globally constant, rather than allowing autoincrement to generate them on a 
per-database basis. Since the database is small, it isn't a big problem to use 
a short string instead of int. For instance, if user has by hand added more 
MySQL variants (MariaDB or Drizzle, say), and we later provide an update script 
with numbers 8,9,10... this will be a problem.

Btw, why are these not listed in chronological order? Would be easier to read.

Second comment:

Why is this needed at all?

As I'm doing now I:

 - do full backup with innobackupex. This automatically uses the correct xtrabackup binary. It records a file xtrabackup_binary in the backup directory that contains the name of the xtrabackup version used.

 - for incremental backups and --prepare I read the content of xtrabackup_binary and use that to invoke xtrabackup.

 - should the mysql server version at some point not match what is recorded for the full backup, it is a good thing that you get an error. The user should not be able to choose and mix different xtrabackup binaries into the same backup.


****************************************************
DROP TABLE IF EXISTS `scheduled_backups`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `scheduled_backups` (
  `scheduled_backup_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(256) NOT NULL default '',
****************************************************

Same comment. Since the scheduled_backup_id is given as an argument to 
xbm-backup.php, it should not be a random "magic number", imho it should be 
user settable id.

Perhaps something like 
scheduled_backup_id="webdb1"
name="Remote backup of main website MySQL data"

"name" could then be changed to "description"



***********************************
xbm-backup.php

print("\nxbm-backup.php -- ".XBM_RELEASE_VERSION."\n");
***********************************

Instead of hard coding the name of the script (ie the filename), use:

  $me = basename(__FILE__)
  print("\n$me -- yadayadayada\n");
See http://php.net/manual/en/reserved.variables.server.php


Taken both suggestions above, command line invocation would be:
xbm-backup -s webdb1


(Ok, office day is over here, I'll look into this more tomorrow.)

XBM should do some checks before starting a backup to make sure that things 
like xtrabackup and innobackupex are available in the path on the machine where 
the backup job is running.

This avoids wasting time taking/copying a large backup and then failing when 
attempting to apply log.

If we can do checks at the beginning, then we can fail quickly, which is much 
nicer for the administrator (discover failures fast and fix them and not have 
to wait a long time for a backup to almost complete, then fail)

4) Binary log (PiT)
Related to the previous point, I also need to copy the binary log from
the server, to enable point in time recovery.
Bonus for creating a CLI/GUI for such recovery - that is likely way
into the future :-) (Currently our recovery command is just cp/scp
anyway!)

I added a tool to manipulate the in database settings:

xbm-conftool -e exports configuration tables as CSV files into ./confdata/.

You can then manipulate them with a spreadsheet or text editor.

xbm-conftool -i imports the CSV files back into MySQL.

The current approach is to always overwrite all existing configuration. It is 
possible to also create a mode where new configurations are added to old ones, 
but the current mode should in any case be the default as it is easier to 
comprehend.

If you commit this, I will of course document it.

Continuing from yesterday...


*************************************
includes/config.php
*************************************

Why do you have lots of configs here and then more in the database? Since we 
have a database anyway, it seems most user configurable settings should be 
stored there. That way they can be edited from a gui, once it exists.
This file should include:
 - how to connect to database
 - PHP constants

It could be argued that this file is easier as long as a gui doesn't exist, but 
a simple settings table with "setting, value, description" is relatively easy 
to edit with something like phpMyAdmin or MySQL Workbench.

These are specific to your internal environment:
$config['SYSTEM']['xbm_hostname'] = 'bup06-int';
$config['ALERTS']['email'] = '[email protected]';

Also this one:
$config['DB']['socket'] = '/mysqldb/tmp/mysql.sock';
...could rather be /var/run/mysqld/mysqld.sock which is the default for deb/rpm 
installations of MySQL. (Assuming you run a dedicated server as backup manager, 
it is not unreasonable to think that it would use the main MySQL instance for 
itself.)


**********************************
service.classes.php / class cronFlusher
**********************************

Tip (for the future, current code works)
I usually just use 
http://fi.php.net/manual/en/function.file-put-contents.php
http://fi.php.net/manual/en/function.file-get-contents.php
...to quickly write a file or read it.

In
            exec("crontab $tmpName", $output, $returnVar);
...use 
crontab -u $config['SYSTEM']['user'] $tmpName

This will prevent anyone from accidentally installing this as their own 
crontab. In fact, it allows me to do sudo xbm_flush from wherever, and it will 
use crontab of the xbm user.

Hi, if i run my backup with:

xbm backup run localhost backupname

i get this:
2011-12-28 21:54:02 [INFO] : [ Initializing Scheduled Backup "backupname" (ID 
#2) for host: localhost ...  ]
2011-12-28 21:54:02 [INFO] : [ Using Backup Strategy: Rotating sets of 
Incremental Backups ]
2011-12-28 21:54:02 [INFO] : [ Using DAY_OF_WEEK as the rotation method... ]
2011-12-28 21:54:02 [INFO] : [ No snapshots found for this scheduled backup at 
all - taking an initial full backup. ]
2011-12-28 21:54:02 [INFO] : [ Attempting to find available ports for use... ]
2011-12-28 21:54:02 [INFO] : [ Got lock on port 10000. ]
2011-12-28 21:54:02 [INFO] : [ Started Netcat (nc) listener on port 10000 to 
receive backup tar stream into directory /home/xbm/backup/localhost/23 ... ]
2011-12-28 21:54:02 [INFO] : [ Running FULL xtrabackup snapshot of 
/home/xbm/backup via ssh: mysql@localhost ... ]
2011-12-28 21:54:12 [INFO] : [ Cleaning up files after failure... ]
2011-12-28 21:54:12 [ERROR] : [ genericBackupTaker->takeFullBackupSnapshot: 
Error: There was an error backing up - The process returned code 25. The output 
from the backup is as follows:
Warning: Permanently added 'localhost' (RSA) to the list of known hosts.

[MOTD of my server...]

domain.com [IP4V-Adress] 10000 (webmin) : Connection refused


heres the stacktrace..

Trace:
#0 /home/xbm/xtrabackup-manager/includes/rotatingBackupTaker.class.php(205): 
genericBackupTaker->takeFullBackupSnapshot(Object(scheduledBackup), 
Object(backupSnapshotGroup))
#1 /home/xbm/xtrabackup-manager/includes/backupSnapshotTaker.class.php(182): 
rotatingBackupTaker->takeScheduledBackupSnapshot(Object(scheduledBackup))
#2 /home/xbm/xtrabackup-manager/includes/cliHandler.class.php(875): 
backupSnapshotTaker->takeScheduledBackupSnapshot(Object(scheduledBackup))
#3 /home/xbm/xtrabackup-manager/includes/cliHandler.class.php(97): 
cliHandler->handleBackupActions(Array)
#4 /home/xbm/xtrabackup-manager/xbm(36): cliHandler->handleArguments(Array)
#5 {main}


additional info:

ssh mysql@localhost with xbm logged in works, seems to be not a key issue, 
thanks for your help ^_~


PS: Rev 201

If for some reason an error occurs during a process that has instantiated a 
netcat listener, and that error did not cause some kind of EOF to get written 
to the netcat listener, then the process will continue to sit there waiting for 
a connection.

In this case, PHP will block on pclose() until netcat returns. 

xbm needs to be able to kill the netcat listener when an error is encountered 
so that it can exit cleanly.

When collapsing deltas/incrementals into another backup dir, the 
xtrabackup_slave_info file is not replaced.

Once deltas are applied, we should make sure the slave info in the dir is 
correct.

Unclear whether this should be an XtraBackup Manager function or innobackupex.

Perhaps we can have XtraBackup Manager handle it as a fallback in case 
innobackupex doesn't. This would be the most compatible.

Hi

It seems sql/schema_init.sql is out of date. Code tries to query nonexistent 
tables:

*****
./xbm-flush 

xbm-flush -- Xtrabackup Manager v0.1 - Copyright 2011 Marin Software

Flushing backup schedule to crontab for xbm user...

There was an error flushing to crontab: scheduledBackup->getInfo: Error: Query: 
SELECT sb.*, bs.strategy_code FROM scheduled_backups sb JOIN backup_strategies 
bs ON sb.backup_strategy_id=bs.backup_strategy_id WHERE scheduled_backup_id=1 
Failed with MySQL Error: Table 'xbm.backup_strategies' doesn't exist

*****

Also the examples.sql file doesn't apply against the schema_init.sql schema.

Finally, when fixing this, could you also make sure that confdata/ contains 
example csv files that match current schema. Ie before committing, run 
schema_init.sql, then examples.sql then do "./xbm-conftool -e".

Add process id (pid) to running_backups table/object so that you can see which 
backups are truly still running and which are not.

This can avoid unnecessary aborting of running backups when stale entries exist 
and perhaps facilitate auto-clean up?

6) Retention policy
 - once backup is verified, copy it to some final destination (like in
another datacenter, to tape, whatever).
 -> Actually, the above must be done the opposite way: First store in final destination, then copy back from there and verify it works!
 - delete old backups

Add functionality to restore any backup snapshot to a destination directory on 
a remote host.

Test a restore of a FULL backup type of a real DB.

Hey its me again,
im sorry for all the problems i got with your code :(.
but im still not able to run a backup with your manager yet :O.

Well im running rev 208 and got a problem with the backup:

here are the relevant lines of the backup log:


...
>> log scanned up to (2253216389137)
innobackupex: Backing up files '/home/mysql/data/xbm/*.ibd' (11 files)
120108 21:44:39  innobackupex: Starting mysql with options:  --password='pw' 
--user='backup' --unbuffered --
120108 21:44:39  innobackupex: Connected to database with mysql child process 
(pid=8854)
>> log scanned up to (2253228467497)
innobackupex:: Not checking slave open temp tables for --safe-slave-backup 
because host is not a slave
>> log scanned up to (2253241335165)

120108 21:44:45  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex: subdirectories of '/home/mysql/data'
innobackupex: Backing up files 
'/home/mysql/data/xxx/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (84 
files)
innobackupex: Backing up files 
'/home/mysql/data/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' 
(72 files)
innobackupex: Backing up file '/home/mysql/data/xxx/visitorlog.frm'
innobackupex: Backing up file '/home/mysql/data/xxx/db.opt'
innobackupex: Backing up file '/home/mysql/data/xxx/xxx.frm'
innobackupex: Backing up files 
'/home/mysql/data/xxx/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (19 
files)
innobackupex: Backing up files 
'/home/mysql/data/xxx/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (59 
files)
innobackupex: Backing up files 
'/home/mysql/data/xxx/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (52 
files)
innobackupex: Backing up file '/home/mysql/data/xxx/db.opt'
innobackupex: Backing up file '/home/mysql/data/xxx/xxx.frm'
innobackupex: Backing up files 
'/home/mysql/data/xbm/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (15 
files)
innobackupex: Backing up files 
'/home/mysql/data/performance_schema/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,
opt,par}' (18 files)
120108 21:44:47  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, 
.TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files
innobackupex: Resuming ibbackup

xtrabackup: The latest check point (for incremental): '2253217679581'
>> log scanned up to (2253245277198)
xtrabackup: Transaction log of lsn (2251838533076) to (2253245277198) was 
copied.
innobackupex:: Starting slave SQL thread
innobackupex: Error: mysql child process has died: ERROR 1200 (HY000) at line 
5: The server is not configured as slave; fix in config file or with CHANGE 
MASTER TO
 while waiting for reply to MySQL request: 'START SLAVE SQL_THREAD;' at /usr/bin/innobackupex line 336.
 ]
2012-01-08 21:44:53 +0100 [INFO] : [ Released lock on port 10000. ]


Well it looks like xbm beleaves that im running a master/slave system?
Im not running anything like that, did i missconfigure my backup or is there 
anything else wrong?

thanks and keep your good work running! :)

What steps will reproduce the problem?
1. mysql xbm -pxbm< sql/examples.sql

What is the expected output? What do you see instead?
"error in your SQL query"


Please use labels and text to provide additional information.

Attached patch removes a stray comma.

Move stuff from config.php to a config mysql table - it is a more
GUI friendly place to keep such things, also prefer to have 1 place
for configs, not two. This can be done in a way that doesn't change
how current code works, I'll add a utility class that just populates
the $config array from the database so it is identical to what it
looks like now. The knowledge of how to connect to mysql must of
course remain in config.php :-)

Email alerts are not easily readable and don't identify which host the alert 
originated from. In environments with multiple hosts, it should be easy to know 
which backup server generated the alert as well as quick to see the exact error 
that occurred without having to go through too much log spool.

Could you change the license to GPLv2 or later? That is what MySQL and 
xtrabackup use. I know these are separate scripts and PHP code even, but still, 
just in case we want to mix and match something from innobackupex or something. 

From Prashant N on mailing list:

I tried to delete the scheduled backup set by:

./xbm backup delete "myhost" "NightlyBackup"

This results in:
=================================
The Scheduled Backup Task for host: myhost with name: NightlyBackup
has 2 backup snapshots associated with it.

If you delete this Scheduled Backup Task, all associated backup
snapshots will be removed -- this cannot be undone.

PHP Fatal error:  Call to undefined function readline() in
/home/xbm/xtrabackup-manager/includes/cliHandler.class.php on line 808

make --datadir optional
 a) innobackupex doesn't need --datadir=".$sbInfo['datadir_path'], it
can pick it up from my.cnf
 b) xtrabackup doesn't need to know the datadir at all for --prepare phase.
I'd like to make it possible to leave this parameter empty. (In
backupSnapshotTaker and scheduledBackup classes.)

queueManager->getTicket will generally try to obtain a ticket in the queue for 
a backup up to 5 times. Due to a bug in the logic, it would return no ticket 
number at all if it was unable to obtain a ticket after 5 attempts.

This would cause an exception when later trying to use 
queueManager->checkFrontOfQueue.

Store size in bytes of each backup snapshot in the backup_snapshots table.

Ability to have different backup strategy and method
 - backup strategy (just full dump, continuous increment, full dump +
increments cycle...)
 - backup interval (nightly, weekly, monthly... both for new fresh
full dump and increments)
 - backup method (Maybe I want to do a mysqldump once in a while.
Having a logical backup as fallback option makes me sleep better at
night.

The cron_expression kind of is the interval already.

What you have now I call "continuous increment" strategy. I'm planning
to do a variation where I keep a staging copy to do incremental
backups on, but then also make a copy where I do the crash recovery
part of xtrabackup --prepare to the end. Knowing that it succeeded
with that gives me confidence that once I need to do recovery, mysqld
will actually start on the data and not hit a recovery bug.

To enable this, what is currently backupSnapshotTaker must be
abstracted away, so I can create many such classes.
 - create "backupTaker" interface or abstract class.
 - create a backupTakerFactory class that returns a backupTaker of the
type requested. Type must be defined in scheduled_backup table or
somewhere.
 - more complex than just a suite of classes implementing/inheriting
one interface. It seems to me strategy/interval/method are separate
dimensions and can be combined arbitrarily.

hi there,
would be nice if you could add a ssh port option, my server is running on port 
2000 so i had to do some code fixes for me to get it working..

When running an incremental backup of a system that has had enough changed 
files, XtraBackup Manager hangs during the step where it is copying the deltas 
back over the network to the XtraBackup Manager server.

I believe this is caused by the stream buffer that the netcat receiver + tar 
extractor thread on the XtraBackup Server filling up, which causes writes to 
STDERR or STDOUT to block and subsequently, no more data can be received 
through the pipeline.

Line 121:
                // If this one is already running, exit - we NEVER run two at once.
                if($scheduledBackup->isRunning) {
                    $this->infolog->write("Detected this scheduled backup job is already running, exiting...", XBM_LOG_ERROR);
                    $this->error = 'backupSnapshotTaker->takeScheduledBackupSnapshot: '."Error: Detected this scheduled backup job is already running.";
                    return false;
                }


...other code...


Line 255:
                // Set the state of the snapshot to RUNNING
                if( ! $snapshot->setStatus('RUNNING') ) {
                    return $this->errorExit('backupSnapshotTaker->takeScheduledBackupSnapshot: '.$snapshot->error, $runningBackup, $snapshot);
                }

(And same later for full snapshot.)

You need to set the RUNNING status immediately as you've checked it on line 
121. Otherwise there is no guarantee the status couldn't change between that 
and line 255.

In SQL there's an even better safeguard: You can make sure that the 
setStatus('RUNNING') only succeeds by saying:

UPDATE backup_snapshots SET status='RUNNING' WHERE backup_snapshot_id=? AND 
status NOT IN ('RUNNING');

Running the above in MySQL, you will get as return value the number of rows 
updated. If it is 1, then it succeeded. If it is 0, then the status already was 
set 'RUNNING' by someone else and the calling function therefore failed in 
acquiring the RUNNING state.

Write some basic documentation within the Google Code Project Wiki that 
outlines how to setup and use the tool. Currently all configuration is done by 
direct DB queries, as this may/will change, documentation only needs to be 
simple.

It would be helpful if the entries generated in the crontab file had a comment 
line with some of the more important scheduling parameters in it, specifically 
rotation scheme and day_of_week.

It would be good for balancing and troubleshooting purposes to be able to just 
look at the crontab and see when the full backups are scheduled to kick off.

Add functionality to restore any backup snapshot to a location local to the 
backup host