How to Run a Streaming Backup with innobackupex

On many of our clients, we have a need to run XtraBackup as a regular OS user. Aside from running into the issue where tar4ibd was not provided with Percona’s xtrabackup-1.6.2.tar.gz package, our main issues have been with permissions when attempting a streaming backup.

I have found the following:

1. The user needs permissions for a temp directory to stream to/from. The my.cnf of the target database cannot be used because the user does not have permission to write to /tmp/mysql-stdout, so we set a tmpdir in a separate defaults-file.
2. A backup target directory must be used that the user has read/write permissions to. It seems to me a target directory should not be needed for a streaming backup, but it is.
3. A user who has SUPER privileges must be used to connect to the target database.
4. Group and datadir permissions:

• The primary group for the user should be the same as the mysql user running the target database, i.e. pythian:mysql.
• The user must have 770 access to the database schema directories.
• The user must have 740 access to the all files under the mysql datadir.

Here is the innobackupex command that works for us.

$ innobackupex --defaults-file=/home/pythian/my.cnf --user=root --stream=tar ./ | gzip - > /home/pythian/backups/2011-08-02_backup.tar.gz

So if you are seeing errors such as,

tar: -: Cannot write: Broken pipe
sh: /tmp/mysql-stout: Permission denied

do yourself a favor and run through this permission checklist. I hope it saves a lot of headaches.