2

I'm trying to create a backup script in php but can't make it work..

First I tried to run this from the command line:

innobackupex --user=root --password=xxx --databases="dbtest" /var/www_backup

It works and dumped all db files in the directory

Then I tried this from the command line:

innobackupex --user=root --password=xxx --databases="dbtest" --stream=tar ./ | gzip -c -1 > /var/www_backup/`date +%Y-%m-%d-%H-%M-%S`.tar.gz

It created a .tar.gz file, but when opening the file it only contains a backup-my.cnf..?! The filesize of backup-my.cnf is about 244 bytes, but the whole .tar.gz file is about 2mb!?? That doesn't make sense... something is wrong with the file..

Then I tried to run the first working command from the webserver

$syntax = 'innobackupex --user='.$mysql_user.' --password='.$mysql_pass.' --databases="'.$mysql_db.'" /var/www_backup';
exec($syntax, $output, $return);
echo "output\n";
print_r($output);
echo "return\n";
print_r($return);

No file is created but this is returned

output
Array
(
)
return
3

php already has access to the target directory

chown -R www-data /var/www_backup
clarkk
  • 27,151
  • 72
  • 200
  • 340

1 Answers1

3

It created a .tar.gz file, but when opening the file it only contains a backup-my.cnf..?!

You must use the -i option for tar to extract or view the contents of a tar file created by Percona XtraBackup. This is documented, and a reminder is output at the end of the innobackupex process:

. . .
130630 10:40:23  innobackupex: Connection to database server closed
innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.
130630 10:40:23  innobackupex: completed OK!

Omitting the -i option results in the behavior you saw: only the first file in the tar archive is visible.

Then I tried to run the first working command from the webserver... No file is created... PHP already has access to the target directory

The innobackupex process must have read privileges on the data and log files under the MySQL datadir, in addition to write privilege to save the output of the backup.

You should have seen errors like this:

130630 10:36:20  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Error in opening ./ibdata1

But it was probably lost because PHP's exec() call discards stderr output.

Re your comment of 8/4:

Okay I tried this myself with the most recent XtraBackup and most recent Percona Server.

I need to test this from the shell so I can see error output. When you run it only from your PHP script, you aren't seeing the errors as they occur. Once you get it working, you should be able to change the gid to www-data and have it continue to work.

I tested by changing the group ownership of all files to my own user's gid. 

$ cd /var/lib/mysql     # use your datadir if it is different
$ chgrp -R billkarwin .

No need to chmod anything on a default installation. But your file permissions are probably not right at this point, so you need to restore them to something that works:

$ cd /var/lib/mysql     # use your datadir if it is different
$ chmod -R 660 .
$ chmod 770 . */.
$ chmod 777 mysql.sock

Then I can run innobackupex, but I have to specify the mysql credentials:

$ innobackupex --user=root --password=XXXX /tmp

130804 08:56:33  innobackupex: Connecting to MySQL server with DSN
'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
130804 08:56:33  innobackupex: Connected to MySQL server
. . .
130804 09:53:46  innobackupex: Connection to database server closed
130804 09:53:46  innobackupex: completed OK!

Note that you could also add an [xtrabackup] group to your user's own $HOME/.my.cnf so you don't have to put the credentials on the command-line.

$ cat > ~/.my.cnf
[xtrabackup]
user=root
password=XXXX
^D
$ innobackupex /tmp
Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • how can you then open and extract the files in windows? – clarkk Jul 01 '13 at 09:21
  • You can install [Cygwin](http://cygwin.org) tools to get GNU tar on Windows, so you can use the `-i` option. – Bill Karwin Jul 01 '13 at 17:41
  • sorry for being away for a while.. how do you give read privileges to the innobackupex process so it can be running from php? – clarkk Jul 19 '13 at 09:31
  • this return neither any output `echo shell_exec($syntax);` – clarkk Jul 19 '13 at 09:41
  • You either have to run PHP as a uid or gid that has privileges on the MySQL data directory, or else run `shell_exec("sudo innobackupex ...")` and give sudo privileges to your PHP process. – Bill Karwin Jul 19 '13 at 15:16
  • ok.. then I will give PHP read permission to the mysql directory.. have done this `chgrp -R www-data /var/lib/mysql && chmod -R 740 /var/lib/mysql` but still no output.. – clarkk Aug 04 '13 at 12:48