GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

How to backup using SSL

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to backup using SSL

    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:

    Code:
    [client]
    
    user="root"
    password="our-password-here"
    ssl-ca=/mysql/certs/ca-bundled.pem
    However when we try and run the XtraBackup with:
    Code:
    innobackupex --defaults-file=~/.my.cnf --stream=tar ./ | gzip - > ~/mysql.backup.tar.gz
    We are getting:

    Code:
    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

  • #2
    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.

    Comment


    • #3
      Hi.

      Tried without --defaults-file still getting:

      Code:
      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:

      Code:
      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

      Comment


      • #4
        Any updates on this? Blocking.

        Comment


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

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

          Comment


          • #6
            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.

            Comment


            • #7
              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: )

              Comment


              • #8
                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

                Comment


                • #9
                  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.

                  Comment


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

                    Comment


                    • #11
                      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

                      Comment


                      • #12
                        Any more ideas? Still waiting on this.

                        Comment


                        • #13
                          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
                          Last edited by justink101; 12-12-2014, 10:10 PM.

                          Comment

                          Working...
                          X