PDA

View Full Version : mysqldump SSL issues



ryan@fj55.net
December 7th, 2006, 10:24 AM
Hello,

I just got a new server and started trying to use ZRM for mysql. I've hit a problem when trying to do a backup of a remote database. I have SSL setup and can use the mysql client to connect through SSL, but I can't get mysqldump to connect for some reason. It is probably something stupid I have overlooked. Below is an example:

09:57:29 root@backup_server ~ $ mysql --ssl-ca=/etc/mysql-zrm/openssl/cacert.pem --ssl-cert=/etc/mysql-zrm/openssl/client-cert.pem --ssl-key=/etc/mysql-zrm/openssl/client-key.pem -u backup -h database_server -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2775 to server version: 5.0.27-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

09:58:08 backup:(none) > \s
--------------
mysql Ver 14.12 Distrib 5.0.26, for pc-linux-gnu (i686) using readline 5.1

Connection id: 2775
Current database:
Current user: backup@backup_server
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: /usr/bin/less
Using outfile: ''
Using delimiter: ;
Server version: 5.0.27-log
Protocol version: 10
Connection: database_server via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 11 hours 25 min 36 sec

Threads: 13 Questions: 37455 Slow queries: 7 Opens: 156 Flush tables: 1 Open tables: 142 Queries per second avg: 0.911
--------------

09:58:15 backup:(none) > exit
Bye
09:58:31 root@backup_server ~ $ mysqldump --ssl-ca=/etc/mysql-zrm/openssl/cacert.pem --ssl-cert=/etc/mysql-zrm/openssl/client-cert.pem --ssl-key=/etc/mysql-zrm/openssl/client-key.pem -u backup -h database_server -A -p > /tmp/backup.sql
Enter password:
mysqldump: Got error: 2026: SSL connection error when trying to connect
09:59:18 root@backup_server ~ $


Any help would be appreciated,
Ryan

ppragin
December 7th, 2006, 04:22 PM
Hello,
Please try following this HowTo and let me know if you still have issues.
Pavel Pragin


How perform MySQL ZRM backups over SSL
--------------------------------------------------------------------

Server Info
iron: ZRM Backup Server RHEL4 and MySQL client
rock: MySQL server RHEL4 running SSL

Sections in this Document
1. Download and compile MySQL with SSL enabled on server "rock"
2. Install MySQL on client "iron"
3. Configure SSL authetication for MySQL client "iron" to connect to server "rock"
4. ZRM Configuration on the client iron

Note: If MySQL is installed and SSL is enabled on both Server and Client:
Skip Steps 1-4 in section 1
Skip Steps 1-2 in section 2


Download and compile MySQL with SSL enabled on server "rock"

1.Decompress the MySQL source in to /usr/local/src directory:
[root@rock src]# gzip -d mysql-4.1.21.tar.gz
[root@rock src]# tar -xf mysql-4.1.21.tar

2. Check to make sure OpenSSL is installed
[root@rock mysql-4.1.21]# rpm -qa | grep openssl
openssl-0.9.7a-43.1

3. Compile MySQL with SSL enabled

cd /usr/local/src/mysql-4.1.21

./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --disable-maintainer-mode -with-mysqld-user=mysql --with-unix-socket-path=/tmp/mysql.sock --without-comment --without-debug -without-bench --with-openssl

make

make install

4. Configure MySQL

cd /usr/local/src/mysql-4.1.21
[root@rock mysql-4.1.21]# ./scripts/mysql_install_db
[root@rock mysql-4.1.21]# groupadd mysql
[root@rock mysql-4.1.21]# useradd -g mysql -c "MySQL Server" mysql
[root@rock mysql-4.1.21]# chown -R root:mysql /usr/local/mysql
[root@rock mysql-4.1.21]# chown -R mysql:mysql /usr/local/mysql/data
[root@rock mysql-4.1.21]# cp support-files/my-medium.cnf /etc/my.cnf
[root@rock mysql-4.1.21]# chown root:sys /etc/my.cnf
[root@rock mysql-4.1.21]# chmod 644 /etc/my.cnf
[root@rock mysql-4.1.21]# echo "/usr/local/mysql/lib/mysql" >> /etc/ld.so.conf
[root@rock mysql-4.1.21]# ldconfig
[root@rock mysql-4.1.21]# cp ./support-files/mysql.server /etc/rc.d/init.d/mysql
[root@rock mysql-4.1.21]# chmod +x /etc/rc.d/init.d/mysql
[root@rock mysql-4.1.21]# /sbin/chkconfig --level 3 mysql on
[root@rock mysql-4.1.21]# cd /usr/local/mysql/bin
[root@rock bin]# for file in *; do ln -s /usr/local/mysql/bin/$file /usr/bin/$file; done

5.Start the MySQL database:
[root@rock bin]# service mysql start
Starting MySQL... [ OK ]

6. Change the root password for MySQL database
[root@rock bin]# mysqladmin -u root password zmanda

7. Login and create "mysql-backup" user for ZRM and set correct privileges
[root@rock bin]# mysql -u root -pzmanda

mysql> GRANT LOCK TABLES, SELECT, FILE, CREATE, DROP, INDEX, SHUTDOWN, ALTER, INSERT, SUPER, RELOAD ON *.* TO 'backup-user'@'iron' IDENTIFIED BY 'zmanda';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

8. Allow MySQL user "backup-users" to connect from client "iron"
[root@rock bin]# mysql -u root -pzmanda

mysql> use mysql;
Database changed

mysql> UPDATE user SET HOST='iron' WHERE User='backup-user';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> FLUSH PRIVILEGES;

9. Check to make sure SSL is enabled
[root@rock bin]# mysql -u root -pzmanda

mysql> SHOW VARIABLES LIKE 'have_openssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl | YES |
+---------------+-------+
1 row in set (0.01 sec)


Install MySQL on client "iron"
1. Decompress the MySQL source code
[root@iron /]# cd /usr/local/src
[root@iron src]# gzip -d mysql-4.1.21.tar.gz
[root@iron src]# tar -xf mysql-4.1.21.tar

2. Compile the MySQL on the client
[root@iron /]# cd /usr/local/src/mysql-4.1.21
[root@iron mysql-4.1.21]# ./configure --prefix=/usr/local/mysql --with-openssl
[root@iron mysql-4.1.21]# make
[root@iron mysql-4.1.21]# make install
[root@iron src]# echo "/usr/local/mysql/lib/mysql" >> /etc/ld.so.conf
[root@iron src]# ldconfig
[root@iron ~]# mkdir /sslkeys

3. Test connection between MySQL server "rock" amd MySQL client "iron"
From client iron:
[root@iron mysql-4.1.21]# mysql -u backup-user -pzmanda -h rock
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 15 to server version: 4.1.21-log
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> quit
Bye

4. Install perl-DBD-MySQL and perl-DBI modules required by MySQL ZRM
[root@iron ppragin]# rpm -ivh perl-DBI-1.48-4.i386.rpm
warning: perl-DBI-1.48-4.i386.rpm: Header V3 DSA signature: NOKEY, key ID 4f2a6fd2
Preparing... ########################################### [100%]
1:perl-DBI ########################################### [100%]

[root@iron src]# rpm -ivh perl-DBD-MySQL-2.9007-1.i386.rpm
warning: perl-DBD-MySQL-2.9007-1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 4f2a6fd2
error: Failed dependencies:
libmysqlclient.so.14 is needed by perl-DBD-MySQL-2.9007-1.i386

Note: Its possible that you will get this error above, its due to the fact that MySQL was compiled and not
installed from a rpm. To get passed this error please use this comand:

[root@iron src]# rpm -ivh --nodeps perl-DBD-MySQL-2.9007-1.i386.rpm
warning: perl-DBD-MySQL-2.9007-1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 4f2a6fd2
Preparing... ########################################### [100%]
1:perl-DBD-MySQL ########################################### [100%]


Configure SSL authetication for MySQL client "iron" to connect to server "rock"
From Server rock:
Login to "rock" and become "root". Then use the following shell command to create the server and client side certificates
1. mkdir /sslcert
2. cd /sslcert
3. DIR=`pwd`/openssl
4. PRIV=$DIR/private
5. mkdir $DIR $PRIV $DIR/newcerts
6. cp /usr/share/ssl/openssl.cnf $DIR
7. replace ./demoCA $DIR -- $DIR/openssl.cnf
8. touch $DIR/index.txt
9. echo "01" > $DIR/serial

10. Generate the Certificate of Authority(CA)
openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem -config $DIR/openssl.cnf

Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
Country Name (2 letter code) [GB]:US
State or Province Name (full name) [Berkshire]:CA
Locality Name (eg, city) [Newbury]:san jose
Organization Name (eg, company) [My Company Ltd]:zmanda
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:rock
Email Address []:ppragin@zmanda.com

Note : if you were requested to enter "PEM pass", please enter different "PEM pass" in the following steps.

11. Create server request and key
openssl req -new -keyout $DIR/server-key.pem -out $DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf

Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
Country Name (2 letter code) [GB]:US
State or Province Name (full name) [Berkshire]:CA
Locality Name (eg, city) [Newbury]:san jose
Organization Name (eg, company) [My Company Ltd]:zmanda
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:rock
Email Address []:ppragin@zmanda.com
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []: LEAVE BLANK
An optional company name []: LEAVE BLANK

12. Remove the passphrase from the server key
[root@rock sslcert]# openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem
Enter pass phrase for /sslcert/openssl/server-key.pem:
writing RSA key

13. Sign server certificate "Use password assigned in Step 10 "
[root@rock sslcert]# openssl ca -policy policy_anything -out $DIR/server-cert.pem -config $DIR/openssl.cnf -infiles $DIR/server-req.pem

Using configuration from /sslcert/openssl/openssl.cnf
Enter pass phrase for /sslcert/openssl/private/cakey.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 1 (0x1)
Validity
Not Before: Sep 15 14:54:57 2006 GMT
Not After : Sep 15 14:54:57 2007 GMT
Subject:
countryName = US
stateOrProvinceName = CA
localityName = san jose
organizationName = zmanda
commonName = rock
emailAddress = ppragin@zmanda.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
77:47:35:77:D1:D6:52:76:5F:B0:15:0C:9B:07:F8:17:86 :6F:A2:66
X509v3 Authority Key Identifier:
keyid:2D:9D:E9:15:CE:90:91:35:8D:9E:68:64:63:E1:9E :4A:F9:44:2B:AA
DirName:/C=US/ST=CA/L=san jose/O=zmanda/CN=rock/emailAddress=ppragin@zmanda.com
serial:00
Certificate is to be certified until Sep 15 14:54:57 2007 GMT (365 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

14. Create client request and key
openssl req -new -keyout $DIR/client-key.pem -out $DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf

Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
Country Name (2 letter code) [GB]:US
State or Province Name (full name) [Berkshire]:CA
Locality Name (eg, city) [Newbury]:san jose
Organization Name (eg, company) [My Company Ltd]:zmanda
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:iron
Email Address []:ppragin@zmanda.com
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []: LEAVE BLANK
An optional company name []: LEAVE BLANK

15. Remove a passphrase from the client key
[root@rock sslcert]# openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem
Enter pass phrase for /sslcert/openssl/client-key.pem:
writing RSA key

16. Sign client certificate "Use password you assigned in Step 10"
[root@rock sslcert]# openssl ca -policy policy_anything -out $DIR/client-cert.pem -config $DIR/openssl.cnf -infiles $DIR/client-req.pem
Using configuration from /sslcert/openssl/openssl.cnf
Enter pass phrase for /sslcert/openssl/private/cakey.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 2 (0x2)
Validity
Not Before: Sep 15 14:58:55 2006 GMT
Not After : Sep 15 14:58:55 2007 GMT
Subject:
countryName = US
stateOrProvinceName = CA
localityName = san jose
organizationName = zmanda
commonName = iron
emailAddress = ppragin@zmanda.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
1B:4E:02:7B:BE:EE:A6:78:F9:3F:0A:F0:87:68:50:CE:89 :3A:65:BE
X509v3 Authority Key Identifier:
keyid:2D:9D:E9:15:CE:90:91:35:8D:9E:68:64:63:E1:9E :4A:F9:44:2B:AA
DirName:/C=US/ST=CA/L=san jose/O=zmanda/CN=rock/emailAddress=ppragin@zmanda.com
serial:00
Certificate is to be certified until Sep 15 14:58:55 2007 GMT (365 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

17. List of keys that have been created
[To be used on the Client]
ssl-ca=/sslcert/openssl/cacert.pem
ssl-cert=/sslcert/openssl/client-cert.pem
ssl-key=/sslcert/openssl/client-key.pem

[To be used on the Server]
ssl-ca=/sslcert/openssl/cacert.pem
ssl-cert=/sslcert/openssl/server-cert.pem
ssl-key=/sslcert/openssl/server-key.pem

18. Edit the /etc/my.cnf file on the server and append these lines to the end:

[mysqld]
ssl-ca=/sslcert/openssl/cacert.pem
ssl-cert=/sslcert/openssl/server-cert.pem
ssl-key=/sslcert/openssl/server-key.pem

19. Copy client keys to the client iron
scp /sslcert/openssl/cacert.pem root@iron:/sslkeys
scp /sslcert/openssl/client-cert.pem root@iron:/sslkeys
scp /sslcert/openssl/client-key.pem root@iron:/sslkeys

ZRM Configuration on the client iron

1. Install the ZRM rpm on the client
[root@iron latest]# rpm -ivh MySQL-zrm-1.0.2-1.noarch.rpm
warning: MySQL-zrm-1.0.2-1.noarch.rpm: Header V3 DSA signature: NOKEY, key ID 3c5d1c92
Preparing... ########################################### [100%]
1:MySQL-zrm ########################################### [100%]

2. Create a new ZRM onfiguration
[root@iron /]# mkdir /etc/mysql-zrm/DailyBackup
[root@iron DailyBackup]# cd /etc/mysql-zrm/
[root@iron mysql-zrm]# cp *.conf /etc/mysql-zrm/DailyBackup

3. Edit the /etc/mysql-zrm/DailyBackup/mysql-zrm.conf and modify as follows
database="mysql"
user="backup-user"
password="zmanda"
host="rock"
ssl-options="--ssl --ssl-ca=/sslkeys/cacert.pem --ssl-cert=/sslkeys/client-cert.pem --ssl-key=/sslkeys/client-key.pem"
mailto="root@localhost"
mysql-binpath="/usr/local/mysql/bin"
backup-mode=logical

4. Start a full backup from the client iron
DailyBackup]# mysql-zrm-scheduler --backup-set DailyBackup --now

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
INFO: mysql-zrm-version ZRM for MySQL Enterprise Edition - version 1.0
INFO: backup-set=DailyBackup
INFO: backup-date=20060922113007
INFO: backup-date-epoch=1158949807
INFO: mysql-zrm-version=ZRM for MySQL Enterprise Edition - version 1.0
INFO: mysql-version=4.1.21-log
INFO: backup-directory=/var/lib/mysql-zrm/DailyBackup/20060922113007
INFO: backup-level=0
INFO: logical-databases=mysql
INFO: read-locks-time=00:00:01
INFO: flush-logs-time=00:00:00
INFO: backup-time=00:00:01
INFO: backup-size=0.23 MB
INFO: next-binlog=rock-bin.000006
INFO: last-backup=/var/lib/mysql-zrm/DailyBackup/20060922112933
INFO: /var/lib/mysql-zrm/DailyBackup/20060922113007/backup.sql=5aac588ccceb7b41de70b7c64cd716b3
INFO: backup-status=Backup succeeded
INFO: Backup succeeded
/usr/bin/mysql-zrm started successfully

momo
February 3rd, 2009, 12:12 PM
This mysql bug gives this error :

http://bugs.mysql.com/bug.php?id=27669

It is not on ZRM side. You can try to mysqldump with SSL to see if the bug affect you. Try to force SSL by adding REQUIRE SSL at the end of your GRANT command.

mysqldump -u {user} -p -h {server ip} --ssl-ca /dev/null {database to dump} > {a filename to dump in}