Innobackupex does not work with .mylogin.cnf

[Przemek]

I am creating hashed authentication file - .mylogin.cnf using
 mysql_config_editor set --login-path=client --host=localhost --user=root --password

After making it:

root@ubuntu-14:~# mysql_config_editor print --all
[client]
user = root
password = *****
host = localhost

The Innobackupex (from both Xtrabackup 2.2.5 and 2.1.9) works well on latest Ubuntu and Fedora, but does not work on Centos 6 and Debian 7.

Systems, where this works:

root@ubuntu-14:~# cat /etc/issue
Ubuntu 14.04.1 LTS \n \l

root@ubuntu-14:~# dpkg -l|grep -i mysql
ii libdbd-mysql-perl 4.025-1 amd64 Perl5 database interface to the MySQL database
ii libmysqlclient-dev 5.6.21-1ubuntu14.04 amd64 MySQL development headers
ri libmysqlclient18:amd64 5.6.21-1ubuntu14.04 amd64 MySQL shared client libraries
ii mysql-apt-config 0.3.1-1ubuntu14.04 all Auto configuration for MySQL APT Repo.
ii mysql-common 5.6.21-1ubuntu14.04 amd64 MySQL Common
ii mysql-community-client 5.6.21-1ubuntu14.04 amd64 MySQL Client
ii mysql-community-server 5.6.21-1ubuntu14.04 amd64 MySQL Server

ii percona-xtrabackup-21 2.1.9-746-1.trusty amd64 Open source backup tool for InnoDB and XtraDB

root@ubuntu-14:~# innobackupex back/

(...)
141031 14:48:47 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
141031 14:48:47 innobackupex: Connected to MySQL server
141031 14:48:47 innobackupex: Executing a version check against the server...
141031 14:48:47 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.21

innobackupex: Created backup directory /root/back/2014-10-31_14-48-47

141031 14:48:47 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/root/back/2014-10-31_14-48-47 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=15135) to suspend
(...)
141031 14:48:51 innobackupex: completed OK!

on Fedora:
[root@pmlap ~]# cat /etc/issue
Fedora release 20 (Heisenbug)

[root@pmlap ~]# rpm -qa|grep -i mysql
mysql-community-libs-5.6.21-1.fc20.x86_64
mysql-connector-python-2.0.1-1.fc20.noarch
mysql-community-common-5.6.21-1.fc20.x86_64
perl-DBD-MySQL-4.024-1.fc20.x86_64
mysql-community-server-5.6.21-1.fc20.x86_64
mysql-community-client-5.6.21-1.fc20.x86_64
mysql-community-release-fc20-5.noarch

On Centos6:

[root@vagrant-centos65 ~]# rpm -qa|grep -i mysql
mysql-community-release-el6-5.noarch
mysql-community-common-5.6.21-2.el6.x86_64
mysql-community-client-5.6.21-2.el6.x86_64
mysql-utilities-1.5.2-1.el6.noarch
perl-DBD-MySQL-4.013-3.el6.x86_64
mysql-community-libs-5.6.21-2.el6.x86_64
mysql-community-server-5.6.21-2.el6.x86_64
mysql-connector-python-2.0.1-1.el6.noarch

[root@vagrant-centos65 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*D6C8439DDDCD2E078F3F49A6BBBC76E2EEE2455E' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

[root@vagrant-centos65 ~]# innobackupex --socket=/data/mysql/mysql.sock backup/

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

141031 14:58:47 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/data/mysql/mysql.sock' (using password: NO).
innobackupex: Error: Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;mysql_socket=/data/mysql/mysql.sock','',...) failed: Access denied for user 'root'@'localhost' (using password: NO) at /usr/bin/innobackupex line 2945

[Sergei Glushchenko (sergei.glushchenko)]

  Here is how I see the issue
  - innobackupex uses DBD::MySQL to talk to MySQL Server
  - DBD::MySQL relies on libmysqlclient shipped with MySQL
  - .mylogin.cnf support is built into libmysqlclient, this file is
    always read
  - in order to properly support .mylogin.cnf
    - DBD::MySQL must be linked with correct version of libmysqlclient
    - DBD::MySQL must provide an option to specify "--load-path"
    - innobackupex must support "--load-path" option
    - libmysqlclient must expose API to set "--load-path" value,
      currently it only can be read from command line

  I suggest first to check which version of libmysqlclient used by
  DBD::MySQL on CentOS 6.

[Alexey Kopytov (akopytov)]

DBD::MySQL is linked against older libmysqlclient.16 (i.e. without .mylogin.cnf) support in every supported distribution, even the latest CentOS and Ubuntu releases. But yes, even if that wasn't the case, both libmysqlclient and DBD::MySQL need to be modified to support the --load-path option, which is not something we can control.

Which means the only option is to either 1) implement full support for .mylogin.cnf in innobackupex. Which is of course non-trivial. Percona Toolkit has a similar request, so you may want to check with Frank on this; or 2) implement https://blueprints.launchpad.net/percona-xtrabackup/+spec/rewrite-innobackupex-in-c :)

[Sergei Glushchenko (sergei.glushchenko)]

Oracle's mysql-utilities use my_print_defaults to decrypt .mylogin.cnf and then just work with it's output. We can do the same for innobackupex.

[Raghavendra D Prabhu (raghavendra-prabhu)]

* On Wed, Dec 03, 2014 at 12:02:34PM -0000, Sergei Glushchenko <email address hidden> wrote:
>Oracle's mysql-utilities use my_print_defaults to decrypt .mylogin.cnf
>and then just work with it's output. We can do the same for
>innobackupex.

@Sergei

Note that this will make PXB dependant on mysql client packages again, something
that was removed earlier. This time it will be dependant on both client package
and perl-DBD-MySQL.

@Alexey

perl-DBD-MySQL in CentOS7 is built against libmysqlclient.so.18.

http://pkgs.org/centos-7/centos-x86_64/perl-DBD-MySQL-4.023-5.el7.x86_64.rpm.html

libmysqlclient.so.18()(64bit)
libmysqlclient.so.18(libmysqlclient_18)(64bit)

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-413