I think I have found the issue.
The backups are stored our data domain, so when performing the restore we select the backups from this location. I think there is some type of connection issue between this server and the data domain. Because when I move the backup files over to the server and restore from the server directly, I cannot reproduce the issue and the restores are probably 10x faster.I set a continuous restore of a database (11 GB) that has been running for about 25 minutes and has completed the restore successfully 23 times with no hanging
My thinking is that the connection issue probably lies somewhere with our test server still. Because we use the Data Domain for all of our production backups (although it is changing) and have no problems restoring elsewhere, including the databases I have been working with. Not to mention, we have automated nightly restores on other servers that have never failed.
I think this at least pinpoints the issue. I will look into where the connection issue is and work with the server\networking guys to see if we can resolve it.
Thanks you guys for your quick assistance
OK, following @jynus pointers, I finally got the backups to restore. I ended up creating another mysql instance on the server and restoring there.
Just in case someone runs into the same problem, the steps I took are the following...
My environment is CentOS 6.4, MySQL 5.1 with stock InnoDB as far as I can tell, innobackupex --version
gives me InnoDB Backup Utility v1.5.1-xtrabackup
, xtrabackup --version
gives me xtrabackup version 2.1.8 for Percona Server 5.1.73
and the server has Plesk 11.0.9 installed.
Firstly I set up another mysql instance by copying and modifying the default /etc/init.d/mysql
startup script. The problem that I had there was that to load a different config file for that server, the --defaults-file
parameter that mysqld_safe
needs, has to be given before any other parameters (see here). I then copied the default /etc/my.cnf
file and modified the values in there (port, log locations etc) to suit my setup.
To log in to the new mysql instance and set up a new root user, the first mysqld_safe
run was with the --skip-grant-tables
option in the init script (see here).
To be able to manage the new mysql instance with phpmyadmin I created a new database server in Plesk (Server->Database Servers->Add Database Server) and entered the new port and/or new ip the server is running on and the new admin user I set up in the previous step. Plesk won't let you use a "root" username so I had to add a different username with root privileges in the previous step.
After that, the server can be started/stopped/reloaded using something like /etc/init.d/mysql-backup start
.
For the actual recovery process I extracted the backup file (taking care to use the -i
flag for tar) and once in the backup dir, I applied the log using the xtrabackup
executable itself (xtrabackup --prepare --target-dir=/path/to/extracted/backup/
) because innobackupex
requires that it connect to a running mysql server to determine the version, something that I didn't want to figure out how to do.
I stopped my new mysql server, copied over the folder that contained my database to the datadir, copied over the ibdata1 file, changed permissions and started the server.
I could then browse/manage my backup database using phpmyadmin.
Best Answer
The Progress backup is a block-level image of the database & bi file, so there's no way to only restore certain tables. You'll need to