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
- The database name is “my_db_name”
- 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.
