PDA

View Full Version : cannot do secure backups



dthfoo
August 5th, 2008, 09:09 AM
Hi everybody!

I am evaluating ZRM for the company I work for but am having some issues getting either ssh-copy or SSL encryption working.

ssh-copy;

when i try a test backup it runs through perfectly but using tcpdump I can see the packets in plain text, which is obviously not what I wanted.

here is the output from a test run which shows the config;

[root@plukbase3 live-cluster]# mysql-zrm --action backup --backup-set live-cluster --verbose
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LANG = "en_UK"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LANG = "en_UK"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
backup:INFO: ZRM for MySQL Community Edition - version 2.0
backup:INFO: Reading options from file /etc/mysql-zrm/mysql-zrm.conf
live-cluster:backup:INFO: START OF BACKUP
live-cluster:backup:INFO: PHASE START: Initialization
live-cluster:backup:INFO: Reading options from file /etc/mysql-zrm/live-cluster/mysql-zrm.conf
live-cluster:backup:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/live-cluster/tmpbeUqd.conf
live-cluster:backup:INFO: {
live-cluster:backup:INFO: backup-level=0
live-cluster:backup:INFO: destination=/srv/backups
live-cluster:backup:INFO: databases=cardhandler
live-cluster:backup:INFO: host=172.16.0.150
live-cluster:backup:INFO: backup-mode=logical
live-cluster:backup:INFO: password=******
live-cluster:backup:INFO: ssl-options=--ssl --ssl-ca=/etc/pki/tls/webservices-cachain.pem --ssl-cert=/etc/pki/tlsm
live-cluster:backup:INFO: user=backup
live-cluster:backup:INFO: copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
live-cluster:backup:INFO: }
live-cluster:backup:INFO: Getting mysql variables
live-cluster:backup:INFO: mysqladmin --user="backup" --password="*****" --host="172.16.0.150" variables
live-cluster:backup:INFO: datadir is /var/lib/mysql/
live-cluster:backup:INFO: mysql_version is 5.0.45-community
live-cluster:backup:WARNING: Binary logging is off.
live-cluster:backup:INFO: InnoDB data file are /var/lib/mysql/ibdata1
live-cluster:backup:INFO: InnoDB log dir is /var/lib/mysql/.
live-cluster:backup:INFO: backup set being used is live-cluster
live-cluster:backup:INFO: backup-set=live-cluster
live-cluster:backup:INFO: backup-date=20080805154501
live-cluster:backup:INFO: mysql-server-os=Linux/Unix
live-cluster:backup:INFO: host=172.16.0.150
live-cluster:backup:INFO: backup-date-epoch=1217947501
live-cluster:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
live-cluster:backup:INFO: mysql-version=5.0.45-community
live-cluster:backup:INFO: backup-directory=/srv/backups/live-cluster/20080805154501
live-cluster:backup:INFO: backup-level=0
live-cluster:backup:INFO: backup-mode=logical
live-cluster:backup:INFO: PHASE END: Initialization
live-cluster:backup:INFO: PHASE START: Running pre backup plugin
live-cluster:backup:INFO: Executing pre-backup-plugin
live-cluster:backup:INFO: PHASE END: Running pre backup plugin
live-cluster:backup:INFO: PHASE START: Flushing logs
live-cluster:backup:INFO: Flushing the logs
live-cluster:backup:INFO: mysqladmin --user="backup" --password="*****" --host="172.16.0.150" flush-logs
live-cluster:backup:INFO: Getting master logname using command mysql --user="backup" --password="*****" --host="172.16."
live-cluster:backup:INFO: PHASE END: Flushing logs
live-cluster:backup:INFO: PHASE START: Creating logical backup
live-cluster:backup:INFO: Command used for logical backup is mysqldump --opt --extended-insert --single-transaction --c"
live-cluster:backup:INFO: Logical backup done for the following database(s)
cardhandler
live-cluster:backup:INFO: logical-databases=cardhandler
live-cluster:backup:INFO: PHASE END: Creating logical backup
live-cluster:backup:INFO: PHASE START: Calculating backup size & checksums
live-cluster:backup:INFO: last-backup=/srv/backups/live-cluster/20080805154438
live-cluster:backup:INFO: backup-size=2.23 MB
live-cluster:backup:INFO: PHASE END: Calculating backup size & checksums
live-cluster:backup:INFO: read-locks-time=00:00:00
live-cluster:backup:INFO: read-locks-time=00:00:00
live-cluster:backup:INFO: flush-logs-time=00:00:00
live-cluster:backup:INFO: backup-time=00:00:00
live-cluster:backup:INFO: backup-status=Backup succeeded
live-cluster:backup:INFO: Backup succeeded
live-cluster:backup:INFO: PHASE START: Running post backup plugin
live-cluster:backup:INFO: Executing post-backup-plugin
live-cluster:backup:INFO: PHASE END: Running post backup plugin
live-cluster:backup:INFO: PHASE START: Cleanup
live-cluster:backup:INFO: PHASE END: Cleanup
live-cluster:backup:INFO: END OF BACKUP


SSL encryption;

trying to use SSL encryption the backup fails as it can't connect to the remote mysql instance, probably because it isn't appending my "ssl-options" to the command!

here is the log file output

backup:INFO: Reading options from file /etc/mysql-zrm/mysql-zrm.conf
live-cluster:backup:INFO: START OF BACKUP
live-cluster:backup:INFO: PHASE START: Initialization
live-cluster:backup:INFO: Reading options from file /etc/mysql-zrm/live-cluster/mysql-zrm.conf
live-cluster:backup:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/live-cluster/tmpiWo3e.conf
live-cluster:backup:INFO: {
live-cluster:backup:INFO: backup-level=0
live-cluster:backup:INFO: destination=/srv/backups
live-cluster:backup:INFO: databases=cardhandler
live-cluster:backup:INFO: host=172.16.0.150
live-cluster:backup:INFO: backup-mode=logical
live-cluster:backup:INFO: password=******
live-cluster:backup:INFO: ssl-options=--ssl --ssl-ca=/etc/pki/tls/webservices-cachain.pem --ssl-cert=/etc/pki/tlsm
live-cluster:backup:INFO: user=backup
live-cluster:backup:INFO: copy-plugin=/usr/share/mysql-zrm/plugins/socket-copy.pl
live-cluster:backup:INFO: }
live-cluster:backup:INFO: Getting mysql variables
live-cluster:backup:INFO: mysqladmin --user="backup" --password="*****" --host="172.16.0.150" variables
live-cluster:backup:ERROR: Output of command: 'mysqladmin --user="backup" --password="*****" --host="172.16.0.150" vari{
mysqladmin: connect to server at '172.16.0.150' failed
error: 'Access denied for user 'backup'@'plukbase3' (using password: YES)'
}
live-cluster:backup:ERROR: Cannot connect to mysql server!
live-cluster:backup:INFO: PHASE START: Cleanup
live-cluster:backup:INFO: PHASE END: Cleanup
live-cluster:backup:INFO: END OF BACKUP


as you may see, it has recognised my ssl-options but never uses them!

I have tried the following from the same machine;

mysqladmin --user="backup" --password="******" --host="172.16.0.150" --ssl --ssl-ca=/etc/pki/tls/webservices-cachain.pem --ssl-cert=/etc/pki/tls/admin@plukbase3.prolog.uk.com-cert.pem --ssl-key=/etc/pki/tls
admin@plukbase3.prolog.uk.com-key.pem status

and it works fine.

Any help would be appreciated,

Cheers

Dan

kkg
August 5th, 2008, 10:09 AM
This is a bug.

Edit /usr/lib/mysql-zrm/ZRM/MySQL.pm

In the function addMySQLParams() modify the following lines

if( $inputs{"ssl-options"} ){
if( $_[0] ne $MYSQLHOTCOPY && $inputs{"copy-plugin"} &&
$_[0] eq $inputs{"copy-plugin"}) {
$comm .= " ".$inputs{"ssl-options"};
}
}

to

if( $inputs{"ssl-options"} ){
if( $_[0] ne $MYSQLHOTCOPY ){
$comm .= " ".$inputs{"ssl-options"};
}
}

and let us know if it works finr.
--kkg

dthfoo
August 5th, 2008, 11:07 AM
kkg,

made edits, ran test and it all works! Superb.

Any clues about the ssh-copy stuff as that would be most useful as we don't then have to open up another port and thereby have to justify it in terms of PCI compliance (credit card stuff).

Thanks for your speedy and precise help.

Cheers

Dan

kkg
August 5th, 2008, 07:05 PM
Hi Dan,

Good to know that fixed your problem. This fix will be present in the next community release.

ssh-copy and socket-copy is meant for only raw backups. For logical backups we use mysqldump to create the dump and hence you need to use the ssl-options parameter.

--kkg

dthfoo
August 6th, 2008, 01:43 AM
No worries,

I've ascertained that mysql-zrm needs access to mysql on port 3306 anyway so we're going to have to open up that port anyway plus logical backups are essential for us so, as you say, SSL encryption is the way forward for us.

Thanks for the help,

Dan.