Archive for July, 2015

Once you have a busy database server, you would probably know that “mysqldump” is not the best option for backups. It is very slow and involves the database server itself for processing the backup dump, therefore slowing down the application too.

Percona has provided Xtrabackup (innobackupex) to get hot-backups, which means it doesn’t affect your mysql server at all. Instead it would copy the mysql data files and allows you to revert to your full backup by recovering the original data files inside /var/lib/mysql/. (Also take a look at holland backup manager if you’re not familiar with it)

Recently I experienced a bug in the application level which led to corrupted data in a single table and did not want to recover the whole database to yesterday’s backup. Therefor I decided to use my full backup to start another MySQL server instance, extract the required data and feed it back to the operational MySQL database. Here’s how I did it:

Assume:

You have an operational MySQL instance, listening on standard port 3306, using data dir /var/lib/mysql

Your backup is located at: /home/backups/holland/sib/20150705_120502/backup.tar.gz

Step 4: Connect to the second instance to check if it is running

mysql -uroot -h 127.0.0.1 --port 3307 -p my_db_name

you can examine the old/backup data to see if you’re on the track or not. If the command above leads to any error, there is probably an error in the mysql startup. so check the log file you’ve mentioned in my.cnf above.