Procedure for Incomplete recovery across instances based on innobakcupex

Procedure for Incomplete recovery across instances based on innobakcupex

On the basis of Hot Standby, MySQL can achieve full or incomplete recovery of the original Instance. In many cases, DRBD or MHA is deployed on the original instance. In this case, the recovery based on the original Instance will affect the original fault site and architecture, you can recover lost or abnormal data through cross-instance recovery. Cross-instance recovery also allows instance-level database migration based on the entire instance. The following describes Incomplete recovery based on cross-instance.

1. Main Steps
A. Prepare a new instance.
B. Perform prepare and recover Based on Hot Standby
C. Copy the complete backup to the new instance (if cross-host copy is required to the new host)
D. Start a new instance.
E. Restore binlog to fault point as needed
F. verification results

2. Demonstrate incomplete cross-instance recovery

-- Note: The following demo is completed on the same host.
-- Source instance port and data file path: 3306/data/mysqldata
-- New instance port and data file path: 3307/data/recoverdata
A. Prepare a new instance.
-- Create a data path for the new instance
SHELL # mkdir-p/data/recoverdata

-- Initialize a new instance
SHELL # cp/etc/my. cnf/etc/my3307.cnf
SHELL # vi/etc/my3307.cnf -- modify related configuration options, paths, port numbers, servier_id, etc.
SHELL #/app/soft/mysql/scripts/mysql_install_db -- user = mysql -- ldata =/data/recoverdata -- basedir =/app/soft/mysql \
> -- Defaults-file =/etc/my3307.cnf
 
SHELL #/app/soft/mysql/bin/mysqld_safe -- defaults-file =/etc/my3307.cnf &
SHELL #/app/soft/mysql/bin/mysqladmin-u root password '***'-P3307-S/tmp/mysql3307.sock
SHELL #/app/soft/mysql/bin/mysqladmin-uroot-p ***-P3307-S/tmp/mysql3307.sock shutdown

B. Perform prepare and recover Based on Hot Standby
-- The current backup status is:
-- 20150128 full backup, 20150129 Incremental backup, 20150130 Incremental backup, and 20150131 Incremental backup. The backup time is three o'clock every day.
-- Restore to 23:53:54
-- The backup path is as follows:
SHELL # pwd
/Backup/hotbak/physical/20150128
SHELL # ls
Base_20150128 inc_20150129 inc_20150130 inc_20150131

-- Prepare full backup
SHELL # innobackupex -- apply-log -- redo-only -- user = root -- password = *** -- port = 3307 \
-- Socket =/tmp/mysql3307.sock -- defaults-file =/etc/my3307.cnf/backup/hotbak/physical/20150128/base_20150128

-- Prepare Incremental Backup
SHELL # innobackupex -- apply-log -- redo-only -- user = root -- password = *** -- port = 3307 \
-- Socket =/tmp/mysql3307.sock -- defaults-file =/etc/my3307.cnf/backup/hotbak/physical/20150128/base_20150128 \
-- Incremental-dir =/backup/hotbak/physical/20150128/inc_20150129

-- Prepare Incremental Backup
SHELL # innobackupex -- apply-log -- redo-only -- user = root -- password = *** -- port = 3307 \
-- Socket =/tmp/mysql3307.sock -- defaults-file =/etc/my3307.cnf/backup/hotbak/physical/20150128/base_20150128 \
-- Incremental-dir =/backup/hotbak/physical/20150128/inc_20150130

-- Prepare: add the backup. Note that the -- redo-only option is not required for the last time. uncommitted transactions are rolled back.
SHELL # innobackupex -- apply-log -- user = root -- password = *** -- port = 3307 -- socket =/tmp/mysql3307.sock \
-- Defaults-file =/etc/my3307.cnf/backup/hotbak/physical/20150128/base_20150128 \
-- Incremental-dir =/backup/hotbak/physical/20150128/inc_20150131

C. Copy the complete backup to the new instance.
SHELL # cp-R/backup/hotbak/physical/20150128/base_20150128/*/data/recoverdata
SHELL # chown mysql: mysql-R/data/recoverdata

# Author: Leshami
# Blog: http://blog.csdn.net/leshami

D. Start a new instance.
-- Start a new instance and verify its integrity
SHELL #/app/soft/mysql/bin/mysqld_safe -- defaults-file =/etc/my3307.cnf &

E. Restore binlog to fault point as needed
-- Assume that we need to restore to 23:53:54, We need to extract the binlog for Incomplete recovery.
-- Obtain the last binlog log and location of hot standby
SHELL # more/backup/hotbak/physical/20150128/inc_20150131/xtrabackup_binlog_info
Mysql-bin.000036 100130712
SHELL # mysqlbinlog/data/mysqldata/mysql-bin.000036 -- start-position = 100130712 -- stop-datetime = "23:53:54 "\
> | Mysql-uroot-p ***-P3307-S/tmp/mysql3307.sock

F. verification results
Mysql> select * from test. heartbeat;
+ -------------------------- + ----------- + -------------------- + ----------- + ----------------------- + ------------------- +
| Ts | server_id | file | position | relay_master_log_file | exec_master_log_pos |
+ -------------------------- + ----------- + -------------------- + ----------- + ----------------------- + ------------------- +
| 2015-01-31T23: 53: 53.001690 | 11 | mysql-bin.000459 | 703735593 | NULL |
+ -------------------------- + ----------- + -------------------- + ----------- + ----------------------- + ------------------- +

MySQL Data Recovery-binlog

Clear binlog logs in MySQL

How to safely delete binlog logs under MySQL

MySQL -- binlog log data recovery

How does MySQL Delete binlog and restore Data?

Introduction and Analysis of Three MySQL binlog formats

MySQL uses binlog Incremental backup + restore instance

MySQL deletes binlog logs and restores data.

This article permanently updates the link address: