Shulou(Shulou.com)06/01 Report--

Xtrabackup is a data backup tool for InnoDB, supports online hot backup (backup does not affect data read and write), and is a good substitute for commercial backup tool InnoDB Hotbackup.

Xtrabackup has two main tools: xtrabackup and innobackupex

(1) xtrabackup can only back up InnoDB and XtraDB data tables, but not MyISAM data tables.

(2) innobackupex-1.5.1 encapsulates xtrabackup, which is a script package, so it can back up innodb and myisam at the same time, but it needs to add a read lock when dealing with myisam.

(3) the efficiency of backup should be about 10 times that of MYSQLDUMP, the tool that comes with mysql.

XtraBackup is based on the crash-recovery function of InnoDB. It will copy the data file of innodb, because the table is not locked, the copied data is inconsistent, and crash-recovery is used during recovery to make the data consistent.

InnoDB maintains a redo log, also known as transaction log, a transaction log that contains all changes to innodb data. When InnoDB starts, it checks data file and transaction log first, and does two steps:

When XtraBackup backs up, it copies innodb data page by page without locking the table. At the same time, XtraBackup has another thread monitoring transactions log, and once the log changes, it copies the changed logpages away. Why are you in such a hurry to copy away? because the size of the transactions log file is limited, when it is full, it will start all over again, so the new data may overwrite the old data.

During the prepare process, XtraBackup uses the copied transactions log to crash recovery the backed-up innodb data file

Download address

Http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.1.6/binary/Linux/x86_64/percona-xtrabackup-2.1.6-702-Linux-x86_64.tar.gz

Decompression

Tar percona-xtrabackup-2.1.6-702-Linux-x86_64.tar.gz

Cd percona-xtrabackup-2.1.5-Linux-x86_64/bin

Just copy to the / usr/bin directory.

Cp * / usr/bin

Modify the configuration file of an earlier version of mysql

Vi / etc/my.cnf

# add the following under [mysqld]

Ignore-builtin-innodb

Plugin-load=innodb=ha_innodb_plugin.so

Plugin_dir=/usr/lib64/mysql/plugin

Backup all

/ usr/bin/innobackupex-defaults-file=/etc/my.cnf-socket=/var/lib/mysql/mysql.sock-user=root-password=123456 / home/databackup/whole/

Incremental backup

/ usr/bin/innobackupex-- defaults-file=/etc/my.cnf-- socket=/var/lib/mysql/mysql.sock-- user=root-- password=123456-- incremental / home/databackup/one-- incremental-basedir=/home/databackup/whole/2016-05-10 million 16-32-48

Backup zabbix Library

/ usr/bin/innobackupex-defaults-file=/etc/my.cnf-user=root-password=123456--port=3306-database=zabbix / home/databackup/zabbix

Delete database

/ etc/init.d/mysqld stop

Cd / var/lib/mysql/

Rm-rf *

Prepare fully recovered data

/ usr/bin/innobackupex-- defaults-file=/etc/my.cnf-- socket=/var/lib/mysql/mysql.sock-- user=root-- password=123.com-- apply-log-- redo-only / home/databackup/whole/2016-05-10 million 16-32-48

Prepare data for incremental recovery

/ usr/bin/innobackupex-- defaults-file=/etc/my.cnf-- socket=/var/lib/mysql/mysql.sock-- user=root-- password=123.com-- apply-log-- redo-only / home/databackup/whole/2016-05-10 million 16-32-48-- incremental-dir=/home/databackup/one/2016-05-10 million 17-37-11

True restore operation

/ usr/bin/innobackupex-- defaults-file=/etc/my.cnf-- socket=/var/lib/mysql/mysql.sock-- user=root-- password=123.com-- copy-back / home/databackup/whole/2016-05-10 million 16-32-48

# modify the owner and group of the recovered data

Chown-R mysql:mysql *

Start mysql

/ etc/init.d/mysqld start

# recovery based on point-in-time using binary logs

Cat / home/databackup/one/2016-05-10, 17-37-11/xtrabackup_binlog_info

Mysql-bin.000012 349

# Export the sql operation after the last incremental backup with the mysqlbinlog tool

Mysqlbinlog-- start-position=349 / var/lib/mysql/mysql-bin.000001 > / tmp/position.sql

Import data

Mysql > source / tmp/position.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.