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:

  1. You have an operational MySQL instance, listening on standard port 3306, using data dir /var/lib/mysql
  2. Your backup is located at: /home/backups/holland/sib/20150705_120502/backup.tar.gz
  3. The database name is “my_db_name”
  4. The table we’re going to extract partial data from is “the_table”

 

Step 1: Extract the backup files and set ownerships

cd /home/backups/holland/sib/20150705_120502/
mkdir data
cd data
tar -xif backup.tar.gz
chown -R mysql:mysql /home/backups/holland/sib/20150705_120502/

Step 2: Create a fake mysql configuration file for the new instance (my.cnf)

contents of /home/backups/holland/sib/20150705_120502/data/my.cnf  (to start the new instance with a different working directory and port)

[mysqld]
datadir=/home/backups/holland/sib/20150705_120502/data
socket=/home/backups/holland/sib/20150705_120502/data/Xmysql.sock
user=mysql
port=3307
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections=10
tmpdir=/home/backups/holland/sib/20150705_120502/data/tmp
log-bin=/home/backups/holland/sib/20150705_120502/data/Xlogbin
innodb_buffer_pool_size=524288000
innodb_force_recovery=6
[mysqld_safe]
log-error=/home/backups/holland/sib/20150705_120502/data/Xmysqld.log
pid-file=/home/backups/holland/sib/20150705_120502/data/Xmysqld.pid

Step 3: Run the mysql server

[root@sib12 data]# mysqld_safe --defaults-file=/home/backups/holland/sib/20150705_120502/data/my.cnf 
150706 10:37:03 mysqld_safe Logging to '/home/backups/holland/sib/20150705_120502/data/Xmysqld.log'.
150706 10:37:03 mysqld_safe Starting mysqld daemon with databases from /home/backups/holland/sib/20150705_120502/data

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.

Step 5: Extract your partial data using mysqldump

mysqldump --extended-insert=FALSE --replace -t -h 127.0.0.1 --port 3307 -uroot -p my_db_name the_table --where="id IN (1,2,3,4,7,9,12)" > partial_data.sql
  • -t parameter removes the table definition from the dump file (also removes the dangerous drop statement at the start of it)
  • –replace writes REPLACE statement instead of INSERT, so that we can update our existing corrupted data instead of trying to insert new records
  • Note the port is 3307 at which the new MySQL instance is listening

Step 6: Feed the recovered data back into production MySQL instance

mysql -uroot -h 127.0.0.1 --port 3306 -p my_db_name < partial_data.sql

Step 7: You’re done

Let’s kill the second instance by pressing “Ctrl+\” at the terminal you’ve started the mysql instance and clean the extracted backup files.