xtrabackup only performs a full back up if is executed with super user privileges

[smashingx]

I executed this command to backup my database:

xtrabackup --backup --databases='database' --target-dir=/home/user/backups --datadir=/var/lib/mysql/

But I get the following error:

160520 02:00:54 version_check Done.
160520 02:00:54 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: /var/lib/mysql/mysql.sock
Using server version 5.5.44-MariaDB
xtrabackup version 2.4.2 based on MySQL server 5.7.11 Linux (x86_64) (revision id: 8e86a84)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to the directory.

I solved it by running the same command with sudo, the problem is that the backup directory gets created as root so my user doesn’t have access to that directory so I always have to change the ownership recursively for that directory so I can be able to read it. This method isn’t pretty efficient to me.

• Is there any other alternative to do this?
• Do I always have to
  execute this command with sudo?

[jrivera]

Since you can execute sudo, sudo as mysql user and run xtrabackup.

sudo -u mysql -s xtrabackup --user=mysql_user --password=mysql_passwd --backup --target-dir=/$TARGETDIR --datadir=/$DATADIR 

The folder will be owned by mysql system user instead of root.

You will need to execute with sudo because the tool needs filesystem privileges on the $DATADIR.

[smashingx]

Well I guess my question would be how can I use this tool without sudo? so I don’t have to change permissions to the backup folder every time I make a backup since it is not pretty efficient.

[jrivera]

You can do this in two ways:

• login as mysql user and run the tool as mysql system user
• change ownership of mysql’s datadir to that of a user that can run the tool. As an example:

[sandbox@zabbix multi_msb_5_6_26]$ ls -ll node1/data/
total 197560
-rw-rw-r-- 1 sandbox sandbox 56 Nov 20 2015 auto.cnf
-rw-rw---- 1 sandbox sandbox 50331648 May 31 00:21 ib_logfile0
-rw-rw---- 1 sandbox sandbox 50331648 Nov 20 2015 ib_logfile1
-rw-rw---- 1 sandbox sandbox 79691776 May 31 00:21 ibdata1
-rw-r----- 1 sandbox sandbox 50508 May 31 00:21 msandbox.err
drwx------ 2 sandbox sandbox 4096 Nov 20 2015 mysql
-rw-rw---- 1 sandbox sandbox 4569694 Nov 21 2015 mysql-bin.000001
-rw-rw---- 1 sandbox sandbox 11707993 Nov 24 2015 mysql-bin.000002
-rw-rw---- 1 sandbox sandbox 143 Jan 21 22:25 mysql-bin.000003
-rw-rw---- 1 sandbox sandbox 4886566 May 7 04:18 mysql-bin.000004
-rw-rw---- 1 sandbox sandbox 583667 May 19 09:21 mysql-bin.000005
-rw-rw---- 1 sandbox sandbox 120 May 31 00:21 mysql-bin.000006
-rw-rw---- 1 sandbox sandbox 114 May 31 00:21 mysql-bin.index
-rw-rw---- 1 sandbox sandbox 5 May 31 00:21 mysql_sandbox15227.pid
drwx------ 2 sandbox sandbox 4096 Nov 20 2015 performance_schema
drwx------ 2 sandbox sandbox 6 Nov 20 2015 test
drwx------ 2 sandbox sandbox 8192 Nov 20 2015 zabbix

[sandbox@zabbix multi_msb_5_6_26]$ xtrabackup --backup --user=root --password=msandbox --port=15227 --host=127.0.0.1 --target-dir=/home/sandbox/backups --datadir=/home/sandbox/sandboxes/multi_msb_5_6_26/node1/data/
160531 00:24:24 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1;port=15227;mysql_socket=/var/lib/mysql/mysql.sock' as 'root' (using password: YES).
160531 00:24:24 version_check Connected to MySQL server
160531 00:24:24 version_check Executing a version check against the server...
160531 00:24:24 version_check Done.
160531 00:24:24 Connecting to MySQL server host: 127.0.0.1, user: root, password: set, port: 15227, socket: /var/lib/mysql/mysql.sock
Using server version 5.6.26-log
xtrabackup version 2.3.4 based on MySQL server 5.6.24 Linux (x86_64) (revision id: e80c779)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/sandbox/sandboxes/multi_msb_5_6_26/node1/data/
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648

[sandbox@zabbix multi_msb_5_6_26]$ ls ~/backups/ -l
total 131068
-rw-rw---- 1 sandbox sandbox 387 May 31 00:24 backup-my.cnf
-rw-rw---- 1 sandbox sandbox 79691776 May 31 00:24 ibdata1
drwx------ 2 sandbox sandbox 0 May 31 00:24 mysql
drwx------ 2 sandbox sandbox 0 May 31 00:24 performance_schema
drwx------ 2 sandbox sandbox 0 May 31 00:24 test
-rw-rw---- 1 sandbox sandbox 21 May 31 00:24 xtrabackup_binlog_info
-rw-rw---- 1 sandbox sandbox 115 May 31 00:24 xtrabackup_checkpoints
-rw-rw---- 1 sandbox sandbox 591 May 31 00:24 xtrabackup_info
-rw-rw---- 1 sandbox sandbox 2560 May 31 00:24 xtrabackup_logfile
drwx------ 2 sandbox sandbox 0 May 31 00:24 zabbix

As you can see above, if you are logged in as the owner of the datadir (could be mysql or any other system user who owns mysql’s datadir) you can run xtrabackup without using sudo and the backup’s files and folder’s ownership is what’s appropriate in your case.