XtraBackup on MultiInstance server connects to default instance for slave info

[Will Gunty]

I am running a server with 6 instances on it. I created a my.cnf that contained only the one instance I wished to copy.

However, the binlog info when running a stream with --slave-info are from the first instance (which has a socket that is linked to at /var/lib/mysql/mysql.sock.

Here is the command I am using:

sudo innobackupex --stream=tar /tmp/ --defaults-file=/tmp/instance03.cnf --slave-info --user=<username> --password=<password> | ssh <user>@<destination_host>"tar xfi - -C /mysql/snapshot/"

It appears that the correct data files are copied over, just the wrong master info is given.

[Will Gunty (ccx-will-ehv)]

An addition to this. It appears that it is trying to connect *only* to /var/lib/mysql/mysql.sock for connections.

I just removed the file (a link to the first instance's socket) and got this error:

120706 15:04:23 innobackupex: Starting mysql with options: --defaults-file='/tmp/instance03.cnf' --password=xxxxxxxx --user='<user>' --unbuffered --120706 15:04:23 innobackupex: Connected to database with mysql child process (pid=23948)
innobackupex: Error: mysql child process has died: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

This socket is not specified anywhere in the global my.cnf, or the cnf in the --defaults-file directive

[Will Gunty (ccx-will-ehv)]

This is with XtraBackup 1.6.6. Not yet tested with 2.0.x

[Will Gunty (ccx-will-ehv)]

Using --socket gets around this.

I believe this is because there is a socket configured in the mysqld section, but no client section in the temporary conf.

There is some ambiguity as to whether innobackupex should connect to the socket listed in the mysqld section (as that is the instance that is being backed up) or if it should be looking for a socket in the client section of my.cnf to connect to.

[Alexey Kopytov (akopytov)]

This looks like a documentation issue to me. Since innobackupex calls the mysql command line client to issue queries (including FTWRL to get a consistent backup and SHOW MASTER/SLAVE to get the corresponding metadata), it should also pass the correct host/user/password/port/socket in case multiple server instances are running on the same host. That's what what the corresponding option in innobackupex are for.

Now the problem is that unlike innobackupex, both server and the mysql command line client have built-in defaults, so they can be used even without connection parameter explicitly specified. Which is what happens in this case. What can we do about it? We can either require connection arguments on the innobackupex command line, or document that in case of multiple server instances one must specify the correct connection parameters in order for innobackupex to talk to the correct server.

The former is annoying, especially on hosts with a single server instance. So I prefer the latter. Changing to a documentation request.

[Shahriyar Rzayev (rzayev-sehriyar)]

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