Mani's Blog

November 10, 2011

MySQL: Point_In_Time Recovery using Xtrabackup

Filed under: Backup,MySQL,Xtrabackup — mani @ 6:11 am

Point_In_Time Recovery

This blog would be helpful If you are using XtraBackup to backup MySQL databases and try to restore and recover databases.  This recovery would be needed when user/admin commits errors like running any statements(truncate table, drop table, etc.) mistakenly. In this case you may need to take your DB to Point-in-Time before the incident.

  1. Restore the database from selected backup. Backup file location may be different for everyone. /local/mysql/data is datadir location.
    cd /local/mysql/data
    tar -ixvzf /local/mysql/backups/xnomdb001n2/2011-10-26_01-00-02_35569.tar.gz
  2. Apply the log
    cd /local/mysql/data
    innobackupex-1.5.1 --apply-log ./
  3. Fix the privileges
    chown -R mysql:mysql *
  4. Start MySQL
    /etc/init.d/mysql start
  5. Identify the binlog file and position
    root@@xnomdb912n2:/local/mysql/data#cat xtrabackup_binlog_info
    binary-logs.000138      36567332
  6. Capture binlog files info from current production server
    (root@localhost) [(none)]> show binary logs;
    +--------------------+------------+
    | Log_name           | File_size  |
    +--------------------+------------+
    | binary-logs.000131 |    9774400 |
    | binary-logs.000132 | 1073742520 |
    | binary-logs.000133 | 1073741997 |
    | binary-logs.000134 | 1073743592 |
    | binary-logs.000135 | 1073745994 |
    | binary-logs.000136 | 1073742312 |
    | binary-logs.000137 | 1073742367 |
    | binary-logs.000138 | 1073742549 |
    | binary-logs.000139 | 1073760891 |
    | binary-logs.000140 550608024 |
    +--------------------+------------+
    10 rows in set (0.00 sec)
  7. Identify the current binlog position from the production.
    (root@localhost) [(none)]> show master status;
    +--------------------+-----------+--------------+------------------+
    | File               | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
    +--------------------+-----------+--------------+------------------+
    | binary-logs.000140 | 550917898 |              |                  |
    +--------------------+-----------+--------------+------------------+
    1 row in set (0.00 sec)
    (root@localhost) [(none)]>
  8. scp the required binlogs files to recovery server
    Example:
    scp binary-logs.000138 root@xnomdb912n2:/local/mysql/mojo
    scp binary-logs.000139 root@xnomdb912n2:/local/mysql/mojo
  9. Generate SQL file off of binlog, which generates all transaction statements into a file. Look at the file and find position and/or time, so the DB can be recovered to that point-in-time.
    mysqlbinlog /local/mysql/mojo/binary-logs.000138   /local/mysql/mojo/binary-logs.000139 --start-position=36567332 > /local/mysql/mojo/mybinlog.sql
  10. Apply transactions to MySQL engine.
    mysqlbinlog /local/mysql/mojo/binary-logs.000138 /local/mysql/mojo/binary-logs.000139 --start-position=36567332 --stop-datetime="11-10-26 3:13:51" | mysql -u root -p
Advertisements

Blog at WordPress.com.