Quick post today, because I wanted to share a snippet (actually, a patch) that allows the great xtrabackup tool to take backups of MariaDB when multi source replication is in use.
Granted, this is supported already, except the slave(s) position is not saved when the backup is taken, and this somewhat defeats the purpose of the backup, which should be used to rebuild the server and resync it with its master(s) in case of disaster.
A workaround for using xtrabackup with MariaDB exists, but only works when you have a single master: just set the default_master_connection variable in your my.cnf to the name of your master connection, and you are set - in fact, in this situation, SHOW SLAVE STATUS will work exactly the same as in stock MySQL, and xtrabackup is happy.
The trouble begins when you have more than one master.
Each master connection has its own name, and in order to get the slave position you have to issue a SHOW SLAVE 'connectionname' STATUS.
A more easy way to get information for all of them is to issue a SHOW ALL SLAVES STATUS, which will report the same information as SHOW SLAVE STATUS for each connection that you have defined.
The bad news is that this syntax is not understood by xtrabackup, so if you happen to use MariaDB with multiple masters, you have no way to save the slave position of each connection when you take a backup.
So here comes the little snippet below - actually a UNIX style diff, that will work against the innobackupex script which is part of xtrabackup, tested with 2.2.12 and above. It will patch the script so that information for each connection is properly saved into the xtrabackup_slave_info file when backup is taken.
This will not work on version 2.3 because the innobackupex script is now an hard link to the xtrabackup binary, so no easy patching is possible.
Please note - only tested with old school replication, so if you are using GTIDs you'll need to modify the patch, but shouldn't be too hard if you follow the changes I did.
Hope this helps. Enjoy!
$ diff -u /usr/bin/innobackupex /usr/bin/innobackupex.rick
--- /usr/bin/innobackupex 2016-02-02 11:20:55.000000000 +0100
+++ /usr/bin/innobackupex.rick 2017-03-07 16:29:19.000000000 +0100
@@ -3263,12 +3263,27 @@
if (!defined($master) || !defined($filename) || !defined($position)) {
my $now = current_time();
+ my $mspos = '';
- print STDERR "$now $prefix Failed to get master binlog coordinates " .
- "from SHOW SLAVE STATUS\n";
- print STDERR "$now $prefix This means that the server is not a " .
- "replication slave. Ignoring the --slave-info option\n";
+ # Rick
+ print STDERR "$now trying multisource replication setup\n";
+ my $rows = $con->{dbh}->selectall_hashref("SHOW ALL SLAVES STATUS", 'Connection_name');
+ foreach my $conn_id (keys %$rows) {
+ $mspos = $mspos
+ . "Connection $rows->{$conn_id}{Connection_name} "
+ . "Master Host $rows->{$conn_id}{Master_Host}, "
+ . "File $rows->{$conn_id}{Relay_Master_Log_File}, "
+ . "Position $rows->{$conn_id}{Exec_Master_Log_Pos}\n";
+ }
+ write_to_backup_file("$slave_info", $mspos);
+ if ($mspos eq '') {
+
+ print STDERR "$now $prefix Failed to get master binlog coordinates " .
+ "from SHOW SLAVE STATUS\n";
+ print STDERR "$now $prefix This means that the server is not a " .
+ "replication slave. Ignoring the --slave-info option\n";
+ }
return;
}