I've had to do mysql database restores based on lvm snapshot backups, one too many times... from damaging sql statements. So here is some notes from the last one for future reference:
* Prior to restore, make sure no writes are coming to the corresponding db schema.
* Restore the database schema from the daily archived snapshot:
service msyqld stop
mv /var/lib/mysql/db1 /var/lib/mysql/db1_old
cd /var/lib
tar -xzf /opt/bak/db/latest_db.tgz mysql/db1
tar -xvzf /opt/bak/db/latest_db.tgz mysql/snapshot.log
service mysqld startNote: The above process is for database schema with MyISAM tables only, so just restoring the corresponding db folder was sufficient.
The snapshot.log file has the status of the master/slave replicated positions shown via "show master status\G" and "show slave status\G". Additionally, I also pipe in the file sizes of the mysql bin logs at the time the lvm snapshot was taken.
Once the database has been restored from the daily snapshot archive, begin restoring from the binary logs to bring the data up to date.
* Extract from bin log using position recorded in the snapshot.log file:
mysqlbinlog --database db1 --start-position=XXXXXX mysql-bin.[0-9]* >db1.sql* Edit the db1.sql file and remove the unwanted transaction.
Note: If you do not have exact positions, you could playback the logs with an approximate start and end date in the below format:
--start-datetime="YYYY-MM-DD HH:MM:SS" --stop-datetime="YYYY-MM-DD HH:MM:SS"* However, If duplicate error occurs, change "INSERT" to "INSERT IGNORE" or "REPLACE" to overwrite:
perl -pi -e 's/^INSERT /INSERT IGNORE /g' db1.sql* Check to make sure that no other database schema is being used, if found you will have to remove those lines as well if playing back just a single schema:
grep -i "^use" db1.sql* Run import:
mysql db1 <db1.sqlWith that import, the datbase schema should be caught up and restored back to it's current state with the unwanted transaction/s removed.
