xtrabackup 2.3.6 assumes SSL to be used when server has ssl certs configured

[johan]

Pre req:

- 3 Nodes PXC 5.6.32-78.1-56
- Percona Xtrabackup 2.3.6 on all nodes
- wsrep_sst_method=xtrabackup-v2

SST works fine until i set up SSL, and then i have in my.cnf:

[mysqld]
...
wsrep_sst_auth=backupuser:123pass
ssl_cert=/etc/ssl/galera/cluster_20/server.crt
ssl_key=/etc/ssl/galera/cluster_20/server.key
ssl_ca=/etc/ssl/galera/cluster_20/server_ca.crt
...

[xtrabackup]
user=backupuser
password=123pass

--

Now:

- Kill one node, remove grastate.dat (to provoke an SST), start the node.
- The xtrabackup (on donor) fails to connect:

vagrant@n4:~$ sudo cat /var/lib/mysql/innobackup.backup.log
161130 13:36:30 innobackupex: Starting the backup operation

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

161130 13:36:30 Connecting to MySQL server host: localhost, user: backupuser, password: set, port: 3306, socket: /var/lib/mysql/mysql.sock
Failed to connect to MySQL server: SSL connection error: error:00000001:lib(0):func(0):reason(1).

---
The problem is that i have never even told the backupuser to require SSL:

Host: localhost
User: backupuser
... < sufficient grants, password is correct etc>
ssl_type:
...

---
It looks like that https://bugs.launchpad.net/percona-xtrabackup/+bug/1546872
introduced this problem.

Moreover, it looks like xtrabackup looks for the ssl settings in [mysqld] section.
Should it not look for ssl settings in the client section?

Best regards
Johan

[Ramesh Sivaraman (rameshvs02)]

Hi Johan,

I could not reproduce the issue as per your step. Please find the attached testcase which I followed.

Thanks
Ramesh

[Sergei Glushchenko (sergei.glushchenko)]

It is known behaviour/issue that xtrabackup reading its settings from both [xtrabackup] and [mysqld]. See bug 1551706 for example. This is a price we pay for being able to read InnoDB and MySQL configuration. I agree that it has to be improved by filtering out which settings to read from each of sections.

As workaround for your issue I can suggest to add
[sst]
inno-backup-opts="--skip-ssl"

as per https://www.percona.com/doc/percona-xtradb-cluster/5.6/manual/xtrabackup_sst.html

[Shahriyar Rzayev (rzayev-sehriyar)]

Same with ordinary MySQL, could not reproduce:

[mysqld]
#user = sh
#port = 5632
#socket = /tmp/mysql_sandbox5632.sock
basedir = /home/sh/MySQLPakcages/mysql-5.6.32/5.6.32
datadir = /home/sh/sandboxes/msb_5_6_32/data
tmpdir = /home/sh/sandboxes/msb_5_6_32/tmp
lower_case_table_names = 0
pid-file = /home/sh/sandboxes/msb_5_6_32/data/mysql_sandbox5632.pid
bind-address = 127.0.0.1
# slow-query-log = on
# slow-query-log-file=/home/sh/sandboxes/msb_5_6_32/data/msandbox-slow.log
# general-log = on
# general-log-file=/home/sh/sandboxes/msb_5_6_32/data/msandbox-general.log
#
# additional options passed through 'my_clause'
#
log-error=msandbox.err

ssl-ca=/home/sh/MySQLPakcages/newcerts/ca.pem
ssl-cert=/home/sh/MySQLPakcages/newcerts/server-cert.pem
ssl-key=/home/sh/MySQLPakcages/newcerts/server-key.pem

[xtrabackup]
user=msandbox
password=msandbox
port = 5632
socket = /tmp/mysql_sandbox5632.sock

root@sh-ubuntu:~# xtrabackup --defaults-file=/home/sh/sandboxes/msb_5_6_32/my.sandbox.cnf --backup --datadir=/home/sh/sandboxes/msb_5_6_32/data/ --target-dir=/home/backup_dir/full/ --no-version-check
161201 15:48:26 Connecting to MySQL server host: localhost, user: msandbox, password: set, port: 5632, socket: /tmp/mysql_sandbox5632.sock
Using server version 5.6.32-debug
xtrabackup version 2.3.6 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 7686bfc)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/sh/sandboxes/msb_5_6_32/data/

Also the error is quite weird:
Failed to connect to MySQL server: SSL connection error: error:00000001:lib(0):func(0):reason(1).

From documentation:
http://dev.mysql.com/doc/refman/5.6/en/creating-ssl-files-using-openssl.html

 Whatever method you use to generate the certificate and key files, the Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. A typical error in this case is:

ERROR 2026 (HY000): SSL connection error:
error:00000001:lib(0):func(0):reason(1)

[David Kedves (kedazo)]

Well the thing is in a proper SSL setup (eg.: paid certificates, properly setuped SSL CA, not some openssl hand generated) the MySQL server certificate may have restrictions, like these:

  X509v3 extensions:
  X509v3 Basic Constraints: critical
     CA:FALSE, pathlen:1
  X509v3 Key Usage: critical
     Digital Signature, Key Encipherment, Key Agreement
  X509v3 Extended Key Usage:
     TLS Web Server Authentication
  X509v3 Subject Key Identifier:
    24:58:BE:72:9F:14:7F:1F:9C:32:09:03:CD:7D:B0:D0:D5:DE:85:E3

So it can't be used as a client... but as [xtrabackup] reads the ssl-cert and ssl-key .. options from [mysqld] too, i can't see any way to get rid of this...

I've tried and these workaround are OK
 1) disable SSL as suggested (eg. putting ssl=0 into [xtrabackup]
 2) issue a client cert/key pair for xtrabackup and put the setting into [xtrabackup] ssl-cert, ssl-key

But using some unrestricted cert (which can be used both as a TLS client and server) maybe not an option for some entreprise users... not sure how to get this fixed in xtrabackup..

[Sergei Glushchenko (sergei.glushchenko)]

xtrabackup is reading server certificate and using it as client. xtrabackup shouldn't do so.

Core issue is that xtrabackup is reading user,password,ssl and other client-specific options from [mysqld]. Once we fix that, this issue will also be fixed.

Related bug 1551706. I raised it's priority.