Xtrabackup Failing: An Elusive Server Change

The Elusive Problem

Recently a customer wanted to reprovision cluster Replicas using the tprovision script. tprovision is a powerful script included in Tungsten Cluster that easily instantiates database hosts from a running database in the cluster. It does this by taking a backup and streaming it to the target, and re-establishing replication. There are several backup methods to choose from, and the customer wanted to use xtrabackup, as it is familiar to them and also does not lock the source database.

During the provision, the script produced errors related to xtrabackup. It appeared that the tprovision script was not specifying the correct arguments to xtrabackup, since it could not find the correct MySQL socket file. But the customer easily fixed this: they simply added the --host option along with the local host name in the script and it worked.

Analyzing the Fix

Why would specifying a host name fix the socket issue with xtrabackup? On the surface, specifying the local host name seems to have no relation to finding the MySQL socket. However, there is something more subtle happening: Specifying a host name makes xtrabackup connect to MySQL via TCP instead of the local socket. And this is the clue needed to properly address the issue.

The Permanent Fix

Since xtrabackup by default uses a socket to connect to MySQL, it failed in this case because it could not find the socket file. It tried to use the default location - /var/lib/mysql/mysql.sock, but the socket was not there and thus an error was produced. This customer had changed the MySQL data directory and socket location from the default locations to a new location. Specifying the host name causes xtrabackup to connect to MySQL via TCP, and since the port was standard (3306), xtrabackup was able to connect to the database with no issue using the temporary fix. The permanent fix to this was simple:

[client]
socket=/path/to/mysqld.sock

This goes into my.cnf. Client programs read this section to get info about how to connect to the local MySQL. After adding this, xtrabackup read this section and knew where exactly to find the socket file. Now that xtrabackup was working after this change, provisioning works without any additional modifications. This small change not only helps xtrabackup connect to the MySQL server via sockets, but also helps any MySQL client program.