Not the answer you need?
Register and ask your own question!

How to backup using SSL

justink101justink101 EntrantCurrent User Role Beginner
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

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    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.
  • justink101justink101 Entrant Current User Role Beginner
    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
  • justink101justink101 Entrant Current User Role Beginner
    Any updates on this? Blocking.
  • justink101justink101 Entrant Current User Role Beginner
    Any further ideas? This seems like a core bug in xtrabackup:

    failed: SSL connection error at /usr/bin/innobackupex line 2995
  • miguelangelnietomiguelangelnieto Member Inactive User Role Beginner
    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/

    InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
    and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

    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.
  • justink101justink101 Entrant Current User Role Beginner
    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: )
  • miguelangelnietomiguelangelnieto Member Inactive User Role Beginner
    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
  • justink101justink101 Entrant Current User Role Beginner
    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.
  • miguelangelnietomiguelangelnieto Member Inactive User Role Beginner
    Since it works for me and I cannot reproduce the problem, I still need the my.cnf. Please, attach it.
  • justink101justink101 Entrant Current User Role Beginner
    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
  • justink101justink101 Entrant Current User Role Beginner
    Any more ideas? Still waiting on this.
  • justink101justink101 Entrant Current User Role Beginner
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.