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.
4 Comments. Leave new
Singer had some feedback for me from a sysadmin perspective-
* the user running the backup must be a member of the group which the mysql datadir and files belong to
* the files under the datadir must have have least privileges of 0740
* the directories under the datadir, which correspond to folders, must have privileges of 0770 at least
We initially had some issues using the –tmpdir option, perhaps related to our destination directory issues; regardless, we did more testing and couldn`t seem to repeat those issues. What this means for us though is a more elegant solution where we do not need to maintain a separate my.cnf. Our new innobackupex command is as follows:
$ innobackupex –defaults-file=/etc/my.cnf –user=root –stream=tar –tmpdir=./ ./ | gzip – > /home/pythian/backups/2011-08-04_backup.tar.gz
Do you have experience with this tool and user disk quotas? Or connecting it to syslog?
It would be nice to be able to audit logs for permission/space warnings.
Asked around, we do not have any experience directly relevant to user disk quotas with this tool. You are right, it would be nice! As it stands, we use separate monitoring on client systems to check the syslog.