Not the answer you need?
Register and ask your own question!

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

smashingxsmashingx EntrantCurrent User Role Beginner
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?

Comments

  • jriverajrivera Percona Support Engineer Percona Staff Role
    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.
  • smashingxsmashingx Entrant Current User Role Beginner
    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.
  • jriverajrivera Percona Support Engineer Percona Staff Role
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.