How to backup using SSL

justink101 · December 30, 2020, 4:21pm

We are trying to do a backup of Percona using XtraBackup on a instance that uses SSL. We are able to login simply by typing mysql with the mysql user because the user has a .my.cnf with:


[client]

user="root"
password="our-password-here"
ssl-ca=/mysql/certs/ca-bundled.pem

However when we try and run the XtraBackup with:

innobackupex --defaults-file=~/.my.cnf --stream=tar ./ | gzip - > ~/mysql.backup.tar.gz

We are getting:

140527 15:35:59 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=~/.my.cnf;mysql_read_default_group=xtrabackup' (using password: NO).
innobackupex: Error: Failed to connect to MySQL server: DBI connect(';mysql_read_default_file=~/.my.cnf;mysql_read_default_group=xtrabackup','',...) failed: SSL connection error at /usr/bin/innobackupex line 2945

niljoshi · December 30, 2020, 4:31pm

Hi,

As .my.cnf is already in home dir, can you try to run innobackupex without --defaults-file option and check? Also try to check with --user and --password options if it works with that or not. Thanks.

justink101 · December 30, 2020, 4:31pm

Hi.

Tried without --defaults-file still getting:

140623 13:03:33 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
innobackupex: Error: Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','', ...) failed: SSL connection error at /usr/bin/innobackupex line 2995

Then tried with: --user root and --password and got:

140623 13:02:28 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
innobackupex: Error: Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','ro ot',...) failed: SSL connection error at /usr/bin/innobackupex line 2995

Any other ideas? I can confirm I am able to log into mysql with the user simply using:

mysql

It reads all the directives from ~/.my.cnf

justink101 · December 30, 2020, 4:31pm

Any updates on this? Blocking.

justink101 · December 30, 2020, 4:31pm

Any further ideas? This seems like a core bug in xtrabackup:

failed: SSL connection error at /usr/bin/innobackupex line 2995

miguelangelnieto · December 30, 2020, 4:31pm

Hi,

I’m checking with the latest available version of xtrabackup 2.2.3 and it works for me. I have an user with name “backup” and password “backup” that requires SSL.

cat .my.cnf

[client]
user=backup
password=backup

ssl-ca=/etc/mysql/certs/ca-cert.pem

innobackupex /tmp/

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

140805 03:04:59 innobackupex: Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup’ (using password: NO).
140805 03:04:59 innobackupex: Connected to MySQL server
140805 03:04:59 innobackupex: Executing a version check against the server…
140805 03:04:59 innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints “completed OK!”.

innobackupex: Using mysql server version 5.6.19-67.0-log

innobackupex: Created backup directory /tmp/2014-08-05_03-04-59

140805 03:04:59 innobackupex: Starting ibbackup with command: xtrabackup --defaults-group=“mysqld” --backup --suspend-at-end --target-dir=/tmp/2014-08-05_03-04-59 --tmpdir=/tmp --extra-lsndir=’/tmp’

[…]

I’m not able to reproduce your problem. So please, check that you are using the latest available version. Also, provide the full my.cnf and I will try to reproduce the problem.

justink101 · December 30, 2020, 4:31pm

Hi, thanks for the reply.

The only difference I see, is that I am using ca-bundle.pem which has two certificates, where you are using ca-cert.pem.

My .my.cnf is:
ssl-ca=/mysql/certs/ca-bundled.pem

Here is my version:
xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revision id: )

miguelangelnieto · December 30, 2020, 4:31pm

Try to specify the three different files (CA, Cert and Key) in separate lines in your .my.cnf:

ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/client-cert.pem
ssl-key=/etc/mysql/certs/client-key.pem

If you don’t specify some of those options it will just use those configured in the global my.cnf (that’s what happened in my example). I am using 2.2.3 with Percona Server 5.6.19-67 and I can confirm it works as expected. These are the parameters in my global my.cnf:

[mysqld]
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

and these are the parameters in my .my.cnf:

[client]
user=backup
password=backup

ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/client-cert.pem
ssl-key=/etc/mysql/certs/client-key.pem

justink101 · December 30, 2020, 4:31pm

Using:

[client]
user=“root”
password="*******************"

ssl-ca=/mysql/certs/ca-cert.pem
ssl-cert=/mysql/certs/client-cert.pem
ssl-key=/mysql/certs/client-key.pem

Does not work, and also I can’t connect with the mysql client (ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)). I MUST use ca-bundled.pem which connects with the mysql client, but XtraBackup is failing.

ssl-ca=/mysql/certs/ca-bundled.pem

ca-bundled.pem is just server-cert.pem and ca-cert.pem concatenated into a single file.

miguelangelnieto · December 30, 2020, 4:31pm

Since it works for me and I cannot reproduce the problem, I still need the my.cnf. Please, attach it.

justink101 · December 30, 2020, 4:31pm

Sure, here is the full my.cnf. Let me know anything else you need. Thanks so much! [mysqld]
datadir=/mysql/data
socket=/mysql/mysqld.sock
symbolic-links=0
default-storage-engine=InnoDB
slow_query_log=1
long_query_time=2
slow_query_log_file=/var/log/mysql_slow.log
expire_logs_days=30
max_connections=50
bind-address=XXX.XXX.XXX.XX
port=3306
max_allowed_packet=4M
net_retry_count=5
max_connect_errors=100
wait_timeout=14400
connect_timeout=10
open_files_limit=65535
innodb_open_files=65535
key_buffer_size=256M
innodb_buffer_pool_size=4096M
innodb_log_buffer_size=4M
group_concat_max_len=16k
max_sort_length=16k
max_length_for_sort_data=16k
query_cache_type=0
query_cache_size=0
innodb_thread_concurrency=8
thread_handling=pool-of-threads
thread_pool_size=12
thread_pool_oversubscribe=5
thread_concurrency=8
thread_cache_size=32
table_open_cache=65535
table_definition_cache=65535
tmp_table_size=33554432
max_heap_table_size=33554432
innodb_log_file_size=1024M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=0
log-warnings
innodb_flush_neighbors=0
user=mysql
old_passwords=0
secure_file_priv=/mysql/data

ssl-ca=/mysql/certs/ca-cert.pem
ssl-cert=/mysql/certs/server-cert.pem
ssl-key=/mysql/certs/server-key.pem
[mysqld_safe]
syslog
pid-file=/var/run/mysqld/mysqld.pid

justink101 · December 30, 2020, 4:31pm

Any more ideas? Still waiting on this.

justink101 · December 30, 2020, 4:32pm

Bump on this.

To confirm, I can connect using:

mysql

Here is the output/proof of running STATUS within mysql client:

https://gist.github.com/nodesocket/97ed8da40b5a0b42a287

I am using the credentials and ssl-ca defined in ~/.my.cnf, however XtraBackup is failing with:

innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 2995
main::mysql_connect(‘abort_on_error’, 1) called at /usr/bin/innobackupex line 1530
innobackupex: Error: Failed to connect to MySQL server: DBI connect(’;mysql_read_default_file=~/.my.cnf;mysql_read_default_group=xtrabackup’,’’,… .) failed: SSL connection error at /usr/bin/innobackupex line 2979