MySQL Data migration

Installing third-party software xtrabackup incremental backups of data

Xtrabackup Introduction:

Online Hot Backup tool, the backup process does not lock the library table,

Back up table records only, no backup table structure

The storage engine for the table must be innodb/xtradb

You must have a full backup before you know that the data is new when you perform the backup.

Installing Xtrabackup:perl (Dbd::mysql) Perl (time::hires) installs two dependent packages first

Rpm-q Perl-time-hires

RPM-IVH percona-xtrabackup-2.1.6-702.rhel6.x86_64.rpm

Provides 2 backup commands:

XTRABACKUP:C Program, Support INNODB/XTRADB

Innobackupex: Xtrabackup is encapsulated in Perl scripts and also supports MyISAM

xtrabackup_56 command Syntax format:

(Full backup Full recovery incremental backup incremental recovery)

————————————

xtrabackup_56 < options >

--backup Backing up data

--prepare Recovering data

--target-dir= Directory name Specifies the directory where the backup file is stored

--datadir=/var/lib/mysql specifying the location of the database directory

--incremental-basedir= Directory Name Incremental backup, specify the directory where the last backup file was stored

--incremental-dir= directory name increment when recovering data, specify the directory where the recovery files are used

————————————————————————————————————————————————————————

Experimental operation: (first to make a full backup of the library, when adding data, make an incremental backup sequence number 1, then increase the data, make an incremental backup 2, then increase the data, make an incremental 3, and then increase the data, full backup + incremental backup)

(Recovery: Restore the full backup, then restore the incremental backup, in order from the first one to recover, here is the corresponding serial number in the xtrabackup_checkpoints, and finally all the backup file copy will be the corresponding database directory, restart service)

"Note that this can only be recovered once."

1. Backup first: Full backup + incremental backup

DB1.A 5-999 full backup

xtrabackup_56--backup--datadir=/var/lib/mysql--target-dir=/allbak

301 1th Incremental Backup

xtrabackup_56--backup--datadir=/var/lib/mysql--target-dir=/new1--incremental-basedir=/allbak

8-801 Incremental backup for the 2nd time

xtrabackup_56--backup--datadir=/var/lib/mysql--target-dir=/new2--incremental-basedir=/new1

3-777 Incremental backup for the 3rd time

xtrabackup_56--backup--datadir=/var/lib/mysql--target-dir=/new3--incremental-basedir=/new2

2.xtrabackup steps to recover data:

1) Prepare to recover data

xtrabackup_56--prepare--datadir=/var/lib/mysql--target-dir=/allbak

xtrabackup_56--prepare--datadir=/var/lib/mysql--target-dir=/allbak--incremental-dir=/new1

xtrabackup_56--prepare--datadir=/var/lib/mysql--target-dir=/allbak--incremental-dir=/new2

xtrabackup_56--prepare--datadir=/var/lib/mysql--target-dir=/allbak--incremental-dir=/new3

2) Copy the backup file back to the corresponding database directory

[Email protected] ~]# cp/allbak/bbsdb/a.ibd/var/lib/mysql/bbsdb/

CP: Do you want to overwrite "/var/lib/mysql/bbsdb/a.ibd"? Y

[Email protected] ~]#

3) Restart the database service

Service MySQL Restart

4) See if the recovery was successful

SELECT * from BBSDB.A;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

__________________________________________________________________________

[Email protected] ~]# Cd/var/lib/mysql

[[email protected] mysql]# ls

AUTO.CNF Ib_logfile0 Performance_schema

Host.tedu.cn.err Ib_logfile1 python

Host.tedu.cn.pid MySQL Rpm_upgrade_history

Ibdata1 Mysql.sock Rpm_upgrade_marker-last

__________________________________________________________________________

Transaction log/var/lib/mysql/

Ib_logfile0

IB_LOGFILE1---SQL command

IDBATA1 Record Data information

Xtrabackup_checkpoints LSN serial number corresponding file

Xtrabackup_logfile the SQL command file for the serial number

Backup process

In an incremental backup, how to know which records are newly generated in all records

In an incremental backup: How to know if there is new data to write, but also to back up the new data

This article is from the "12336621" blog, please be sure to keep this source http://12346621.blog.51cto.com/12336621/1903267

MySQL Data migration