PDA

View Full Version : How to enable SSL-enabled backups?



mrigank.mishra
September 13th, 2008, 05:03 AM
Hi!

I'm looking to take SSL-enabled backups. I've tried uncommenting the ssl-options line in mysql-zrm.conf on the ZRM server and setting the path to the certificate and keys..

But mysqlhotcopy returns with an error like:
Unknown option: ssl
Unknown option: ssl_ca
Unknown option: ssl_cert
Unknown option: ssl_key

Help needed.

I'm attaching the logs below:
backup:~ # mysql-zrm-scheduler --backup-set DailyBackup_SockCopy --backup-level 0 --now
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
DailyBackup_SockCopy:backup:INFO: START OF BACKUP
DailyBackup_SockCopy:backup:INFO: PHASE START: Initialization
DailyBackup_SockCopy:backup:INFO: Mail address: root@localhost is ok
DailyBackup_SockCopy:backup:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/DailyBackup_SockCopy/tmpth1yh.conf
DailyBackup_SockCopy:backup:INFO: {
DailyBackup_SockCopy:backup:INFO: verbose=1
DailyBackup_SockCopy:backup:INFO: encrypt-plugin=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SockCopy:backup:INFO: retention-policy=30D
DailyBackup_SockCopy:backup:INFO: decrypt-option=-d
DailyBackup_SockCopy:backup:INFO: password=******
DailyBackup_SockCopy:backup:INFO: tables=student
DailyBackup_SockCopy:backup:INFO: backup-mode=raw
DailyBackup_SockCopy:backup:INFO: ssl-options=--ssl --ssl_ca=/etc/ofi_mysql_cacert.pem --ssl_cert=/etc/ofi_mysql_client_cert.pem --ssl_key=/etc/ofi_mysql_client_key.pem
DailyBackup_SockCopy:backup:INFO: compress-plugin=/usr/bin/gzip
DailyBackup_SockCopy:backup:INFO: user=mysql_backup_usr
DailyBackup_SockCopy:backup:INFO: copy-plugin=/usr/share/mysql-zrm/plugins/socket-copy.pl
DailyBackup_SockCopy:backup:INFO: backup-level=0
DailyBackup_SockCopy:backup:INFO: quiet=0
DailyBackup_SockCopy:backup:INFO: encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SockCopy:backup:INFO: mailto=root@localhost
DailyBackup_SockCopy:backup:INFO: host=remote.mysql.net
DailyBackup_SockCopy:backup:INFO: database=sample
DailyBackup_SockCopy:backup:INFO: compress=/usr/bin/gzip
DailyBackup_SockCopy:backup:INFO: }
DailyBackup_SockCopy:backup:INFO: Getting mysql variables
DailyBackup_SockCopy:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" variables
DailyBackup_SockCopy:backup:INFO: datadir is /usr/local/mysql/var/
DailyBackup_SockCopy:backup:INFO: mysql_version is 5.0.67-log
DailyBackup_SockCopy:backup:INFO: InnoDB data file are /usr/local/mysql/var/ibdata1
DailyBackup_SockCopy:backup:INFO: InnoDB log dir is /usr/local/mysql/var/.
DailyBackup_SockCopy:backup:INFO: backup set being used is DailyBackup_SockCopy
DailyBackup_SockCopy:backup:INFO: backup-set=DailyBackup_SockCopy
DailyBackup_SockCopy:backup:INFO: backup-date=20080914002648
DailyBackup_SockCopy:backup:INFO: mysql-server-os=Linux/Unix
DailyBackup_SockCopy:backup:INFO: host=remote.mysql.net
DailyBackup_SockCopy:backup:INFO: backup-date-epoch=1221332208
DailyBackup_SockCopy:backup:INFO: retention-policy=30D
DailyBackup_SockCopy:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
DailyBackup_SockCopy:backup:INFO: mysql-version=5.0.67-log
DailyBackup_SockCopy:backup:INFO: backup-directory=/var/lib/mysql-zrm/DailyBackup_SockCopy/20080914002648
DailyBackup_SockCopy:backup:INFO: backup-level=0
DailyBackup_SockCopy:backup:INFO: backup-mode=raw
DailyBackup_SockCopy:backup:INFO: PHASE END: Initialization
DailyBackup_SockCopy:backup:INFO: PHASE START: Running pre backup plugin
DailyBackup_SockCopy:backup:INFO: Executing pre-backup-plugin
DailyBackup_SockCopy:backup:INFO: PHASE END: Running pre backup plugin
DailyBackup_SockCopy:backup:INFO: PHASE START: Flushing logs
DailyBackup_SockCopy:backup:INFO: Flushing the logs
DailyBackup_SockCopy:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" flush-logs
DailyBackup_SockCopy:backup:INFO: Getting master logname using command mysql --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show master status"
DailyBackup_SockCopy:backup:INFO: PHASE END: Flushing logs
DailyBackup_SockCopy:backup:INFO: Command used for getting engine type mysql --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show table status from \`sample\` like 'student'"
DailyBackup_SockCopy:backup:INFO: For database sample
DailyBackup_SockCopy:backup:INFO: and for table student
DailyBackup_SockCopy:backup:INFO: engine
DailyBackup_SockCopy:backup:INFO: MyISAM
DailyBackup_SockCopy:backup:INFO: PHASE START: Creating raw backup
DailyBackup_SockCopy:backup:INFO: Command used for raw backup is /usr/share/mysql-zrm/plugins/socket-copy.pl --mysqlhotcopy --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" --ssl --ssl_ca=/etc/ofi_mysql_cacert.pem --ssl_cert=/etc/ofi_mysql_client_cert.pem --ssl_key=/etc/ofi_mysql_client_key.pem --quiet sample./^student$/ "/var/lib/mysql-zrm/DailyBackup_SockCopy/20080914002648" > /tmp/W94Hw4xbHx 2>&1
DailyBackup_SockCopy:backup:ERROR: Output of command: 'mysqlhotcopy' is {
Unknown option: ssl
Unknown option: ssl_ca
Unknown option: ssl_cert
Unknown option: ssl_key
connect: Connection refused at /usr/share/mysql-zrm/plugins/socket-copy.pl line 255.
}
DailyBackup_SockCopy:backup:ERROR: mysqlhotcopy did not succeed. Command used is /usr/share/mysql-zrm/plugins/socket-copy.pl --mysqlhotcopy --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" --ssl --ssl_ca=/etc/ofi_mysql_cacert.pem --ssl_cert=/etc/ofi_mysql_client_cert.pem --ssl_key=/etc/ofi_mysql_client_key.pem --quiet sample./^student$/ "/var/lib/mysql-zrm/DailyBackup_SockCopy/20080914002648" > /tmp/W94Hw4xbHx 2>&1 Return value is 28416
DailyBackup_SockCopy:backup:INFO: PHASE START: Cleanup
DailyBackup_SockCopy:backup:INFO: backup-status=Backup failed
DailyBackup_SockCopy:backup:INFO: Backup failed
DailyBackup_SockCopy:backup:INFO: mailing file /tmp/YCLITXoISa
DailyBackup_SockCopy:backup:INFO: mail command is cat "/tmp/YCLITXoISa"|mail -s "[ZRM for MySQL Report] ERROR during backup of backup-set DailyBackup_SockCopy" root@localhost
DailyBackup_SockCopy:backup:INFO: PHASE END: Cleanup
DailyBackup_SockCopy:backup:INFO: END OF BACKUP
ERROR: /usr/bin/mysql-zrm did not finish successfully

paddy
September 13th, 2008, 03:06 PM
backup-mode=raw

backup-mode has to be logical for using SSL enabled backups.

If you want to use raw backups securely, use ssh-copy plugin.

Paddy

mrigank.mishra
September 15th, 2008, 05:15 AM
backup-mode=raw

backup-mode has to be logical for using SSL enabled backups.

If you want to use raw backups securely, use ssh-copy plugin.

Paddy

Thanks for the information..! This point has probably not been documented (or atleast conspicuously enough!) in the literature available from Zmanda...

I tried taking the backup using backup-mode=logical and it worked fine BUT, when I used a sniffer to grab the packates and analysed them, the packates were unencrypted (i.e. not using SSL..)..

This is probably because mysql command is not "recognizing" those options.... (Kindly refer to my first post..) Could you please detail the procedure on HOW TO ENABLE SSL ON MYSQL SERVER??? (I'm not talking about ZRM..)

Thanks.

paddy
September 15th, 2008, 01:12 PM
Thanks for the information..! This point has probably not been documented (or atleast conspicuously enough!) in the literature available from Zmanda...

I tried taking the backup using backup-mode=logical and it worked fine BUT, when I used a sniffer to grab the packates and analysed them, the packates were unencrypted (i.e. not using SSL..)..

This is probably because mysql command is not "recognizing" those options.... (Kindly refer to my first post..) Could you please detail the procedure on HOW TO ENABLE SSL ON MYSQL SERVER??? (I'm not talking about ZRM..)

Thanks.

I have made modifications to the docs so that it is more obvious.

Please see http://mysqlbackup.zmanda.com/index.php/Pre-Installation#SSL_Between_MySQL_Servers_and_server_ running_ZRM_for_MySQL for information on how to set up MySQL server for SSL connection.

Paddy

mrigank.mishra
September 15th, 2008, 08:56 PM
Please see http://mysqlbackup.zmanda.com/index.php/Pre-Installation#SSL_Between_MySQL_Servers_and_server_ running_ZRM_for_MySQL for information on how to set up MySQL server for SSL connection.

Paddy

I know that much already... But I'm facing a problem... The "mysql" command is not recognizing the "--ssl" options ... So the mysql server is not starting with SSL.... Could you perhaps help me fix that..? Since the SSL service is also dependent on the mysql server being started properly with the requisite ssl options...

Thanks,
Mrigank

paddy
September 16th, 2008, 06:04 AM
Your version of mysql is not built with ssl. See http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html for more information.

Paddy

mrigank.mishra
September 20th, 2008, 05:06 AM
Your version of mysql is not built with ssl. See http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html for more information.

Paddy

Hi!

I tried building mysql-5.1.28-rc.tar.gz with the following steps

# ./configure --with-ssl
# make
# make install
# cp etc/my.cnf.rpmsave /etc/my.cnf
# chown root /etc/my.cnf
# chgrp root /etc/my.cnf
# chmod 644 /etc/my.cnf
# vi /etc/my.cnf

added "user = mysql" immediately below [mysqld] clause.

# /usr/local/mysql/bin/mysql_install_db --user=mysql
# /usr/local/mysql/bin/mysqld_safe --user=mysql &

I have edited the my.cnf to add the SSL-options and have added the path of SSL certificates and keys as say, ssl-ca=/etc/cacert.pem etc.
My problem is that though SSL service starts with have_ssl and have_openssl options as YES, when I take the backup using Zmanda Resource Manager for MySQL, I find that the packets exchanged between the database and the backup server are not encrypted and protocol is "MySQL".

Trying to do the following command on the ZRM server side(which does not have MySQL installed) throws the error below:

backup:~ # mysql --ssl --help
mysql: unknown option '--ssl'
You have new mail in /var/mail/root

Help needed.

paddy
September 20th, 2008, 04:54 PM
Hi!

I tried building mysql-5.1.28-rc.tar.gz with the following steps

# ./configure --with-ssl


The doc says

For yaSSL:

shell> ./configure --with-yassl

For OpenSSL:

shell> ./configure --with-openssl

depending what you have installed on the build machine. You will need devel package.

Paddy

mrigank.mishra
September 23rd, 2008, 04:48 AM
The doc says

For yaSSL:

shell> ./configure --with-yassl

For OpenSSL:

shell> ./configure --with-openssl

depending what you have installed on the build machine. You will need devel package.

Paddy

Yes! It says so! But there is this line just above "the doc says..":
Before MySQL 5.1.11, you must use the appropriate option to select the SSL library that you want to use.

The distro we're talking about is 5.1.28 So we have to follow the method given along with the same:
shell> ./configure --with-ssl
That configures the distribution to use the bundled yaSSL library. To use OpenSSL instead, specify the --with-ssl option with the path to the directory where the OpenSSL header files and libraries are located:
shell> ./configure --with-ssl=path

I tried doing shell> ./configure --with-ssl=path but it failed during make..

But that is another problem...
I'm not running into problems on the MySQL server, I have problems on the ZRM server side. MySQL server is starting properly with the proper SSL options but ZRM isn't - mysql client on ZRM is not recognizing the SSL options.

Hope you will be able to help me sort that out.

Regards,
Mrigank

paddy
September 25th, 2008, 10:46 AM
I'm not running into problems on the MySQL server, I have problems on the ZRM server side. MySQL server is starting properly with the proper SSL options but ZRM isn't - mysql client on ZRM is not recognizing the SSL options.


I think you are running into the problem mentioned in this thread
http://forums.zmanda.com/showthread.php?p=4445

Paddy

mrigank.mishra
September 26th, 2008, 03:37 AM
I think you are running into the problem mentioned in this thread
http://forums.zmanda.com/showthread.php?p=4445

Paddy

I did that and got this:
backup:~ # mysql-zrm-scheduler --backup-set DailyBackup_SockCopy --backup-level 0 --now
schedule:INFO: ZRM for MySQL Community Edition - version 2.0
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
Variable "%MYSQLHOTCOPY" is not imported at /usr/lib/mysql-zrm/ZRM/MySQL.pm line 110.
Global symbol "%MYSQLHOTCOPY" requires explicit package name at /usr/lib/mysql-zrm/ZRM/MySQL.pm line 110.
syntax error at /usr/lib/mysql-zrm/ZRM/MySQL.pm line 113, near "}"
Global symbol "$comm" requires explicit package name at /usr/lib/mysql-zrm/ZRM/MySQL.pm line 115.
syntax error at /usr/lib/mysql-zrm/ZRM/MySQL.pm line 116, near "}"
Compilation failed in require at /usr/bin/mysql-zrm-backup line 37.
BEGIN failed--compilation aborted at /usr/bin/mysql-zrm-backup line 37.
ERROR: /usr/bin/mysql-zrm did not finish successfully


I had to restore the file back from a backup and the thing worked again...

Yes! Doing the following command produces the attached output:
backup:~ # mysqladmin --user="mysql_backup_usr" --password="******" --host="remote.mysql.net" --ssl --ssl-ca=/etc/cacert.pem --ssl-cert=/etc/cert.pem --ssl-key=/etc/client_key.pem status
mysqladmin: unknown option '--ssl'
You have new mail in /var/mail/root


Looks as if mysql on the ZRM machine isn't "built with SSL options". I don't remember having installed mysql on the ZRM machine. Is it a part of the ZRM package??

Would be happy if the person providing the solution in that case was nice enough to send in their views on my problem...

Regards,
Mrigank