Innobakcupex cross-instance Incomplete recovery steps, innobakcupex cross-instance

Innobakcupex cross-instance Incomplete recovery steps, innobakcupex cross-instance

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 example shows the source instance port and data file path on the same host: 3306/data/mysqldata-new instance port and data file path: 3307/data/recoverdataa, prepare a new instance -- create a new instance data path SHELL # mkdir-p/data/recoverdata -- initialize the new instance SHELL # cp/etc/my. cnf/etc/my3307.cnfSHELL # vi/etc/my3307.cnf -- modify related configuration options, path, port number, 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.sockSHELL #/app/soft/mysql/bin/mysqladmin-uroot-p ***-P3307-S/tmp/mysql3307.sock shutdownb, based on Hot Standby prepare and recover -- the current backup is: -- 20150128 full backup, 20150129 Incremental backup, 20150130 Incremental backup, 20150131 Incremental backup, backup time is 3 o'clock every day -- recovery to 23:53:54 -- backup path is as follows: SHELL # pwd/backup/hotbak/phy Sical/20150128 SHELL # lsbase_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 adds a backup SHELL # innobackupex -- apply-log -- redo-only -- user = root -- password = *** -- port = 3307 \ -- socket =/tmp/mysql3307.so Ck -- 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/2015012 8/inc_20150130 -- prepare to add backup. Note that the -- redo-only option is not required for the last time, uncommitted transactions will roll 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_20150131c copy the complete backup to the new instance SHELL # cp-R/backup/hotbak/physical/20150128/base_20150128/*/data/recoverdataSH ELL # chown mysql: mysql-R/data/recoverdata # Author: Leshami # Blog: http://blog.csdn.net/leshamid、start a new instance -- start the new instance and verify the integrity of the SHELL #/app/soft/mysql/bin/mysqld_safe -- defaults-file =/etc/my3307.cnf & e, recover binlog we need to recover to 23:53:54, binlog needs to be extracted for Incomplete recovery. -- Get 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.sockf, verification result mysql> select * from test. heartbeat; + region + ----------- + ---------------- + ----------- + region + | ts | server_id | file | position | locations | exec_master_log_pos | + region + ----------- + region + | 2015-01-31T23: 53: 53.001690 | 11 | mysql-bin.000459 | 703735593 | NULL | + ------------------------------ + ----------- + ---------------------- + ----------------------------- + --------------------- +