MySQL – Usefulness of Mysql.pdb File

You are using a very old, end-of-life, and unsupported version of MySQL.

There is not exactly a correct answer to your question, because it is impossible in many cases to safely back up or clone a MySQL server that is still running.

The only absolutely safe way to copy data using this method is by stopping the mysqld process, gracefully, and copying all of the files, for all of the databases.

If you are using MyISAM, which, again, you almost certainly should not, it is possible to copy a single database by copying all of the files in that database's named directory, but the tables have to be locked and flushed, or the backup will either not contain the most recent changes, or will be corrupt.

If you are using InnoDB, it is almost impossible to copy only one database from one server by copying files, unless you use the "transportable tablespaces" feature introduced in MySQL 5.6.

The correct answer to the question is "don't do it this way."

The correct solution is to use mysqldump to make the backup, which does not copy files at all. Instead, it creates a file containing the SQL statements necessary to recreate your tables and reinsert the data that was in those tables when the backup was made.

The output of mysqldump is also human-readable, so you can actually examine what's in the backup.

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

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.