How to Run a Streaming Backup with innobackupex
Aug 2, 2011 / By Sandi Barr
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:
- 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.
- 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.
- A user who has SUPER privileges must be used to connect to the target database.
- 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.