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

Brief introduction:

Xtrabackup is a mysql database backup tool provided by percona, which supports online hot backup (backup does not affect data read and write).

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 is modified by referring to InnoDB Hotbackup's innoback script. Innobackupex is a perl script encapsulation that encapsulates xtrabackup. It is mainly for the convenience of backing up the tables of the InnoDB and MyISAM engines at the same time, but you need to add a read lock when processing myisam. And added some options to use. For example, slave-info can record backup recovery, as some information needed by slave, according to this information, you can easily use backup to redo slave.

This article describes how to use the innobackupex tool to do full and incremental backups.

Installation:

Yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpmyum install percona-xtrabackup-24# can use innobackupex-verion to check the version. If the version number appears, the installation is successful # innobackupex-- help can view the parameters help

Prepare before backup:

Change InnoDB to independent tablespace mode, that is, set innodb_file_per_table=1 under [mysqld] in my.cnf

It is recommended to create a separate backup directory example: / xbackup

Complete:

Innobackupex-- defaults-file=/etc/my.cnf-- user=root-- password='yourpassword' / xbackup# appears completed OK! The backup is successful and the file is saved to a directory named with a timestamp.

Restore:

If all are restored, you need to stop the mysql service first, and make sure there are no files in the mysqldata directory.

Service mysqld stopinnobackupex-apply-log / xbackup/2017-09-07 transactions 09-50-11 apply-log is called the preparation phase, which is to maintain data consistency, roll back uncommitted transactions during the backup, and commit committed transactions innobackupex-defaults-file=/etc/my.cnf-copy-back / xbackup/2017-09-07 transactions 09-50-11/chown-R mysql:mysql / mysqldataservice mysqld start

Single table recovery

Innobackupex-- apply-log-- export / xbackup/2017-09-07 mysql:mysql 15-53-53 IMPORT TABLESPACE # if T1 table data is deleted incorrectly, make sure that the table structure exists ALTER TABLE T1 DISCARD TABLESPACE;cp / xbackup/2017-09-07 table 15-53-53/test/t1. {ibd,exp,cfg} / mysqldata/test/chown-R mysql:mysql / mysqldataALTER TABLE T1 IMPORT TABLESPACE

Incremental backup and recovery:

Innobackupex-- defaults-file=/etc/my.cnf-- user=root-- password='xxxxxx'-- no-timestamp-- incremental / xbackup/inc1-- incremental-basedir=/xbackup/2017-09-07 09-50-1 recovery service mysqld stopinnobackupex-- apply-log / xbackup/2017-09-07 09-50-11 /-- incremental-dir=/xbackup/inc1/innobackupex-- copy-back / xbackup/2017-09-07 09-50-11/chown-R mysql:mysql / mysqldataservice mysqld start

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.