Backup jobs do not allow specification of mysql server host, port or defaults group

[GoogleCodeExporter]

In a non-standard mysql host/port environment or when using mysqld_multi with 
or without multiple servers on a single host require specification of the mysql 
server host, port and/or defaults group passed to innobackupex, and currently 
can not be specified during "xbm backup add"

I have made the required modifications to support this feature.  Below is a 
patch that will allow the specification of <mysql_host>, <mysql_port>, and 
<mysql_defaults_group> when utilizing "xbm backup edit".  These are only 
available during edit and not when adding backups to maintain compatibility 
with original documented syntax.  These modifications, if accepted, should be 
added to the documentation for backup edit.  The built-in help is updated int 
he patch.

5 files are patched, includes/cliHandler.class.php, 
includes/genericBackupTaker.class.php, includes/scheduledBackup.class.php, 
includes/service.classes.php, and sql/schema_init.sql

!! PLEASE NOTE
- these patches also contains the fix for spaces in cron backup names reported 
in issue #66
- these patches updates the SQL schema and increments the version number. This 
number may or may not be correct in the future and should be adjusted as needed

diff -ru xtrabackup-manager-r229/ xtrabackup-manager/
diff -ru xtrabackup-manager-r229/includes/cliHandler.class.php 
xtrabackup-manager/includes/cliHandler.class.php
--- xtrabackup-manager-r229/includes/cliHandler.class.php       2012-02-29 
18:03:42.000000000 -0500
+++ xtrabackup-manager/includes/cliHandler.class.php    2013-01-22 
23:23:40.659456944 -0500
@@ -723,6 +723,8 @@
                                                echo("  Name: ".$sbInfo['name']."  Active: ".$sbInfo['active']."  Cron_Expression: ".$sbInfo['cron_expression']."\n");
                                                echo("  Backup User: ".$sbInfo['backup_user']."  Datadir: ".$sbInfo['datadir_path']."\n");
                                                echo("  MySQL User: ".$sbInfo['mysql_user']."  Lock Tables: ".$sbInfo['lock_tables']."\n");
+                                               echo("  MySQL Host: 
".((!empty($sbInfo['mysql_host']))?$sbInfo['mysql_host']:'localhost')."  Mysql 
Port: ".((!empty($sbInfo['mysql_port']))?$sbInfo['mysql_port']:'3306')."\n");
+                                               echo("  MySQL Defaults Group: 
".((!empty($sbInfo['mysql_defaults_group']))?$sbInfo['mysql_defaults_group']:'my
sqld')." )\n");
                                                echo("  Backup Volume: ".$volInfo['name']." ( ".$volInfo['path']." )\n");
                                                echo("  Backup Strategy: ".$stratInfo['strategy_name']." ( ".$stratInfo['strategy_code']." )\n");
                                                if($sbInfo['throttle'] == 0 ) {
@@ -759,6 +761,9 @@
                                                $errMsg .= "    datadir_path - The datadir path for MySQL on the remote host\n";
                                                $errMsg .= "    mysql_user - The MySQL username for innobackupex to use for communicating with MySQL\n";
                                                $errMsg .= "    mysql_password - The MySQL password for the above\n";
+                                               $errMsg .= "    mysql_host - 
The MySQL host for the above (default localhost or defined in my.cnf)\n";
+                                               $errMsg .= "    mysql_port - 
The MySQL port for the above (default 3306 or defined in my.cnf)\n";
+                                               $errMsg .= "    
mysql_defaults_group - The MySQL group name in my.cnf which should be used. 
This is needed for mysqld_multi deployment\n";
                                                $errMsg .= "    lock_tables - Whether FLUSH TABLES WITH READ LOCK should be used for MyISAM consistency (Y/N)\n";
                                                $errMsg .= "    active - Whether this Scheduled Backup task is activated and should run (Y/N)\n";
                                                $errMsg .= "    throttle - How many MB/sec to throttle this backup to (0 to disable throttling)\n";
diff -ru xtrabackup-manager-r229/includes/genericBackupTaker.class.php 
xtrabackup-manager/includes/genericBackupTaker.class.php
--- xtrabackup-manager-r229/includes/genericBackupTaker.class.php       
2012-02-15 18:03:54.000000000 -0500
+++ xtrabackup-manager/includes/genericBackupTaker.class.php    2013-01-22 
22:56:10.397387056 -0500
@@ -160,9 +160,13 @@
                                        // Proceed with running the backup

                                        // Build the command...
+                                       
$mysqlHost=(!empty($sbInfo['mysql_host']))?"--host=".$sbInfo['mysql_host']:'';
+                                       
$mysqlPort=(!empty($sbInfo['mysql_port']))?"--port=".$sbInfo['mysql_port']:'';
+                                       
$mysqlDefgrp=(!empty($sbInfo['mysql_defaults_group']))?"--defaults-group=".$sbIn
fo['mysql_defaults_group']:'';
                                        $xbCommand = 'ssh -o StrictHostKeyChecking=no -p '.$hostInfo['ssh_port'].' '.$sbInfo['backup_user'].'@'.$hostInfo['hostname'].
                                                                " 'cd $tempDir ; innobackupex --ibbackup=".$xbBinary." --stream=tar ".$sbInfo['datadir_path']." --user=".$sbInfo['mysql_user'].
-                                                               " 
--password=".$sbInfo['mysql_password']." --slave-info --safe-slave-backup 
--tmpdir=".$tempDir;
+                                                               " 
--password=".$sbInfo['mysql_password']." --slave-info --safe-slave-backup 
--tmpdir=".$tempDir.
+                                                               " 
".$mysqlHost." ".$mysqlPort." ".$mysqlDefgrp;

                                        // If table locking for the backup is disabled add the --no-lock option to innobackupex
                                        if($sbInfo['lock_tables'] == 'N') {
@@ -428,11 +432,15 @@
                                        $xbBinary = $scheduledBackup->getXtraBackupBinary();

                                        // Command should look like this:
+                                       
$mysqlHost=(!empty($sbInfo['mysql_host']))?"--host=".$sbInfo['mysql_host']:'';
+                                       
$mysqlPort=(!empty($sbInfo['mysql_port']))?"--port=".$sbInfo['mysql_port']:'';
+                                       
$mysqlDefgrp=(!empty($sbInfo['mysql_defaults_group']))?"--defaults-group=".$sbIn
fo['mysql_defaults_group']:'';
                                        $xbCommand = "ssh -o StrictHostKeyChecking=no -p ".$hostInfo['ssh_port']." ".$sbInfo['backup_user']."@".$hostInfo['hostname'].
                                                                " 'cd $tempDir ; innobackupex --ibbackup=".$xbBinary." --slave-info --incremental-lsn=".$lsn." ".$tempDir."/deltas".
                                                                " --user=".$sbInfo['mysql_user']." --safe-slave-backup ".
-                                                               " 
--password=".$sbInfo['mysql_password']." --no-timestamp --incremental 
--throttle=".$scheduledBackup->getXtraBackupThrottleValue()." 1>&2 '";
-
+                                                               " 
--password=".$sbInfo['mysql_password']." --no-timestamp --incremental 
--throttle=".$scheduledBackup->getXtraBackupThrottleValue()." 1>&2 '".
+                                                               " 
".$mysqlHost." ".$mysqlPort." ".$mysqlDefgrp;
+
                                        // Set up how we'll interact with the IO file handlers of the process
                                        $xbDescriptors = Array(
                                                                                0 => Array('file', '/dev/null', 'r'), // Process will read from /dev/null
diff -ru xtrabackup-manager-r229/includes/scheduledBackup.class.php 
xtrabackup-manager/includes/scheduledBackup.class.php
--- xtrabackup-manager-r229/includes/scheduledBackup.class.php  2012-01-27 
19:45:50.000000000 -0500
+++ xtrabackup-manager/includes/scheduledBackup.class.php       2013-01-22 
22:47:20.809456945 -0500
@@ -480,6 +480,55 @@
                        return;
                }

+               // Validate a mysql password
+               public static function validateMysqlHost($host) {
+
+                       if(!isSet($host)) {
+                               throw new InputException("Error: Expected a 
MySQL host as a parameter, but did not get one.");
+                       }
+
+                       if(strlen($host) < 1 || strlen($host) > 256 ) {
+                               throw new InputException("Error: MySQL host 
must be between 1 and 256 characters in length.");
+                       }
+
+                       // if not IP then assume hostname and check resolvable
+                       if (ip2long($host) == False){
+                               if (gethostbyname($host) == $host){
+                                       throw new InputException("Error: Unable 
to resolve host name {$host}");
+                               }
+                       }
+
+                       return;
+               }
+
+               // Validate mysql server port
+               public static function validateMysqlPort($port) {
+                       if(!isSet($port)) {
+                               throw new InputException("Error: Expected a 
Mysql server port parameter, but did not get one.");
+                       }
+
+                       if(intval($port) < 1 || intval($port) > 65535 ) {
+                               throw new InputException("Error: Mysql server 
port must be between 1 and 65535.");
+                       }
+
+                       return;
+
+               }
+
+               // Validate a mysql password
+               public static function validateMysqlDefaultsGroup($group) {
+
+                       if(!isSet($group)) {
+                               throw new InputException("Error: Expected a 
MySQL defaults group as a parameter, but did not get one.");
+                       }
+
+                       if(strlen($group) < 1 || strlen($group) > 256 ) {
+                               throw new InputException("Error: MySQL defaults 
group must be between 1 and 256 characters in length.");
+                       }
+
+                       return;
+               }
+
                // Validate a backup user
                public static function validateBackupUser($user) {

@@ -683,6 +732,21 @@
                                        $sql = "UPDATE scheduled_backups SET mysql_password='".$conn->real_escape_string($value)."' WHERE scheduled_backup_id=".$this->id;
                                break;

+                               case 'mysql_host':
+                                       self::validateMysqlHost($value);
+                                       $sql = "UPDATE scheduled_backups SET 
mysql_host='".$conn->real_escape_string($value)."' WHERE 
scheduled_backup_id=".$this->id;
+                               break;
+
+                               case 'mysql_port':
+                                       self::validateMysqlPort($value);
+                                       $sql = "UPDATE scheduled_backups SET 
mysql_port='".$conn->real_escape_string($value)."' WHERE 
scheduled_backup_id=".$this->id;
+                               break;
+
+                               case 'mysql_defaults_group':
+                                       
self::validateMysqlDefaultsGroup($value);
+                                       $sql = "UPDATE scheduled_backups SET 
mysql_defaults_group='".$conn->real_escape_string($value)."' WHERE 
scheduled_backup_id=".$this->id;
+                               break;
+
                                case 'lock_tables':
                                        // Force param to upper case..
                                        $value = strtoupper($value);
diff -ru xtrabackup-manager-r229/includes/service.classes.php 
xtrabackup-manager/includes/service.classes.php
--- xtrabackup-manager-r229/includes/service.classes.php        2012-02-29 
18:03:42.000000000 -0500
+++ xtrabackup-manager/includes/service.classes.php     2013-01-25 
18:56:15.780707009 -0500
@@ -511,7 +511,6 @@
                        scheduledBackup::validateMysqlUser($mysqlUser);
                        scheduledBackup::validateMysqlPass($mysqlPass);

-
                        // Lookup host by name
                        $hostGetter = new hostGetter();
                        $hostGetter->setLogStream($this->log);
@@ -779,7 +778,7 @@
                                                        break;

                                                }
-                                               $cron .= 
$scheduledBackupInfo['cron_expression'].' '.$XBM_AUTO_INSTALLDIR.'/xbm backup 
run '.$hostInfo['hostname'].' '.$scheduledBackupInfo['name']." quiet\n";
+                                               $cron .= 
$scheduledBackupInfo['cron_expression'].' '.$XBM_AUTO_INSTALLDIR.'/xbm backup 
run '.$hostInfo['hostname'].' "'.$scheduledBackupInfo['name']."\" quiet\n";
                                        } else {
                                                continue;
                                        }
diff -ru xtrabackup-manager-r229/sql/schema_init.sql 
xtrabackup-manager/sql/schema_init.sql
--- xtrabackup-manager-r229/sql/schema_init.sql 2012-02-16 17:43:14.000000000 
-0500
+++ xtrabackup-manager/sql/schema_init.sql      2013-01-22 22:43:12.158206789 
-0500
@@ -201,6 +201,9 @@
   `datadir_path` varchar(1024) NOT NULL default '',
   `mysql_user` char(16) NOT NULL,
   `mysql_password` varchar(256) NOT NULL default '',
+  `mysql_host` varchar(256) NOT NULL default '',
+  `mysql_port` int(10) unsigned default NULL,
+  `mysql_defaults_group` varchar(256) NOT NULL default '',
   `lock_tables` enum('Y','N') default 'N',
   `host_id` int(10) unsigned NOT NULL,
   `active` enum('Y','N') default 'Y',
@@ -265,7 +268,7 @@

 LOCK TABLES `schema_version` WRITE;
 /*!40000 ALTER TABLE `schema_version` DISABLE KEYS */;
-INSERT INTO `schema_version` VALUES (1006);
+INSERT INTO `schema_version` VALUES (1007);
 /*!40000 ALTER TABLE `schema_version` ENABLE KEYS */;
 UNLOCK TABLES;

Original issue reported on code.google.com by brian...@gmail.com on 26 Jan 2013 at 1:02

[GoogleCodeExporter]

Here is patch file for patch above.  

to apply:
patch -p0 < xtrabackup-manager-multi-patch.patch

Original comment by brian...@gmail.com on 26 Jan 2013 at 1:34

Attachments:

• xtrabackup-manager-multi-patch.patch