MariaDB multi-source slave is not detected

[Ludovic LANGE]

I'm trying to backup a MariaDB server which is a slave (of a multi-source replication), however innobackupex doesn't detect it as such:

--------------------------------------------------------------------------
150122 10:28:53 innobackupex: Continuing after ibbackup has suspended
innobackupex:: Not checking slave open temp tables for --safe-slave-backup because host is not a slave
--------------------------------------------------------------------------

Looking at how the detection is done, I can understand why it says so:
--------------------------------------------------------------------------
sub get_mysql_slave_status {
  mysql_query($_[0], "SHOW SLAVE STATUS");
}
...
   get_mysql_slave_status($con);

   if (defined($con->{slave_status}->{Read_Master_Log_Pos}) and
       defined($con->{slave_status}->{Slave_SQL_Running})) {
         $host_is_slave = 1;
   }
--------------------------------------------------------------------------

Indeed, SHOW SLAVE STATUS has empty output on this server:

--------------------------------------------------------------------------
MariaDB [(none)]> SHOW SLAVE STATUS;
Empty set (0.00 sec)
--------------------------------------------------------------------------

This is because, on MariaDB >= 10.0.1, multi-source replication changes things a bit: https://mariadb.com/kb/en/mariadb/documentation/managing-mariadb/replication/standard-replication/multi-source-replication/

In particular, SHOW SLAVE STATUS only show a *default* replication, which is not mandatory to have.
You can in addition have others replications defined, all of which have a connection_name that you need to add to the command in order to have more info:

--------------------------------------------------------------------------
MariaDB [(none)]> SHOW SLAVE 'instance7-XXXXXXXXX' STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
...
--------------------------------------------------------------------------

You can also use the SHOW ALL SLAVES STATUS to have info on all the slaves defined:

--------------------------------------------------------------------------
MariaDB [(none)]> show all slaves status\G
*************************** 1. row ***************************
              Connection_name: instance1-XXXXX
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
--------------------------------------------------------------------------

This surely has multiple implications for Xtrabackup to support this multi-source replication:
- Detection can be easy : checking server version, using SHOW ALL SLAVE STATUS to detect multi-source replication and acting as such.
- Stopping / restarting slave(s) needs to be enhanced to iterate on all active slave(s) connections.
- The CHANGE MASTER commands may also need to be handled (may be differently if the connection is in GTID or non-GTID mode)
There may also be other implications that I did not thought of.

--------------------------------------------------------------------------
$ cat /etc/redhat-release
CentOS release 6.6 (Final)
--------------------------------------------------------------------------
$ rpm -q percona-xtrabackup
percona-xtrabackup-2.2.8-5059.el6.x86_64
--------------------------------------------------------------------------
$ innobackupex --version
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
--------------------------------------------------------------------------
$ xtrabackup --version
xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
--------------------------------------------------------------------------
$ mysql --version
mysql Ver 15.1 Distrib 10.0.15-MariaDB, for Linux (x86_64) using readline 5.1
--------------------------------------------------------------------------

[Nilnandan Joshi (nilnandan-joshi)]

Confirmed with MariaDB 10.0.1, If @@default_master_connection will be null, it will not show result in "show slave status \G"

mysql> \s
--------------
mysql Ver 14.14 Distrib 5.6.22-71.0, for debian-linux-gnu (x86_64) using EditLine wrapper
...
Server version: 5.5.5-10.0.15-MariaDB-log MariaDB Server

mysql> select @@default_master_connection;
+-----------------------------+
| @@default_master_connection |
+-----------------------------+
| |
+-----------------------------+
1 row in set (0.00 sec)

mysql> show slave status \G
Empty set (0.00 sec)

mysql> show all slaves status\G
*************************** 1. row ***************************
              Connection_name: master1
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 24025
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 326
               Relay_Log_File: mysql_sandbox24026-relay-bin-master1.000004
                Relay_Log_Pos: 535
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 326
              Relay_Log_Space: 853
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
         Retried_transactions: 0
           Max_relay_log_size: 1073741824
         Executed_log_entries: 6
    Slave_received_heartbeats: 0
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos: 0-1-12
*************************** 2. row ***************************
              Connection_name: master2
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 24027
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 312
               Relay_Log_File: mysql_sandbox24026-relay-bin-master2.00000...

[Shahriyar Rzayev (rzayev-sehriyar)]

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