MySQL third-party backup tool xtrabackup instructions for use

1,Xtrabackupinstallation

1) Yum installation method:

yuminstallhttp://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Yumlist | grep Percona

Yuminstall Xtrabackup

2) Apt installation method:

Apt-keyadv--keyserver keys.gnupg.net--recv-keys 1c4cbdcdcd2efd2a

Debhttp://repo.percona.com/apt VERSION Main

Deb-srchttp://repo.percona.com/apt VERSION Main

Apt-getupdate

Apt-getinstall Percona-xtrabackup

2, fully prepared:

INNOBACKUPEX--DEFAULTS-FILE=/ETC/MY.CNF--host=127.0.0.1-port=3306--user=root--password=123456--slave-info-- Safe-slave-backup--no-timestamp/data/mysqlbak/2015.sql1717

Parameter description:

--slave-info will print the file name and offset location record of the binary log of Master, and also save to Xtrabackup_slave_info file .

--safe-slave-backup pauses The slave SQL thread and waits until the temporary table is not open to start the backup . after the backup is finished The SQL thread starts automatically so that you can ensure consistent replication status .

--no-timestamp is to let Innobackupex not automatically generate a timestamp of the directory name /home/mysqlbak/2013***, but stored in the /home/ Mysqlbak/base

3, full-standby recovery:

rm-rf/usr/local/mysql/data/*; # test Environment Delete Data Catalog

Innobackupex--apply-log/data/mysqlbak/2015.sql1717; Prepare

Innobackupex--copy-back/data/mysqlbak/2015.sql1717; Recovery

chown-r Mysql.mysql/usr/local/mysql/data; Modify Directory Permissions

/etc/init.d/mysqlstart # start MySQL

4, partial backup:

Innobackupex Partial backup , the following three parameters can be selected :

1.--databases= "Database1[.table1] ..." For example:--databases= "Employees sales.orders"

2.--tables-file=tables.txt, Tables.txt each line of the file has a database.table

3.--include=regexp, support for regular, such as:--include= ' ^database (1|2) \.reports '

after you are ready to "partial backup " and copy recovery " partial backup " to an empty DataDir, Make sure that the DataDir mysql database , otherwise MySQL will not start .
if not , you will need to create the system's database before booting.

5, incremental backup:

The first incremental backup needs to be done on a full backup basis:

Innobackupex--defaults-file=/etc/my.cnf--host=127.0.0.1-port=3306--user=root--password=123456--no-timestamp-- Incremental/data/mysqlbak/2015.sql1717-zengliang--incremental-basedir=/data/mysqlbak/2015.sql1717

333795

Parameter description:

–incremental Specify the destination directory where the incremental backup resides

–incremental-basedir= Specifying a full backup directory

# You can also make incremental backups on a full-standby basis each time, rather than based on the last one.

6, incremental recovery:

the restore operation for an incremental backup is a bit different from a full restore, and you must first use –apply-log–redo-only to operate on the full backup directory and all the incremental backup directories, and then you can restore the operation as if you were restoring the full backup.

1) prepare a fully prepared log

Innobackupex--apply-log--redo-only--use-memory=4g/data/mysqlbak/2015.sql1717

2) consolidate incremental backups into full-scale backups

INNOBACKUPEX--DEFAULTS-FILE=/ETC/MYSQL/MY.CNF--host=127.0.0.1-port=3306--user=root--password=123456-- Slave-info--safe-slave-backup--apply-log--use-memory=4g/data/mysqlbak/2015.sql1717--incremental-dir=/data/ Mysqlbak/2015.sql1717-zengliang

3) Copy this fully-prepared directory to the data MySQL data directory

Innobackupex--copy-back/data/mysqlbak/2015.sql1717

7, attachedXtrabackupparameter Description:

--print-defaults Displays the default options. Xtrabackup read the my.cnf file by default, the reading order is/etc/my.cnf/etc/mysql/my.cnf/usr/local/etc/my.cnf ~/.my.cnf

--no-defaults ignore any my.cnf file options

--defaults-file=# reads the specified my.cnf file

--defaults-extra-file=# and read another file .

--target-dir=name destination directory, default directory in./xtrabackup_backupfiles/, relative to DataDir directory

--backup Backup

--stats Calculate DataDir Catalog Statistics

--prepare Recover from backup

--export When you restore, create a file to import to another database

--apply-log-only when recovering, the stop recovery process does not make LSN using log only

--print-param Print out Parameters

--use-memory= #buffer_pool_size value

--suspend-at-end when backing up, create a xtrabackup_suspended file until the backup is complete and delete

--throttle=# Limit Total IO

--log-stream record standard output information xtrabackup_logfile

--extra-lsndir=name for backup only, save another copy of the xtrabackup_checkpoints file

--incremental-lsn=name only for backup, incremental backup

--incremental-basedir=name only for backup, incremental backup directory

--incremental-dir=name applies to prepare only, restores. delta files and log files in the specified directory

--tables=name Filter Some tables

--tables_file=name filtering database.table List Files

--create-ib-logfile

- H,--datadir=name DataDir Catalogue

- t,--tmpdir=name tmpdir Catalogue

--parallel=# The default is 1. The number of parallel threads that transmit data files. No effect of any flow pattern

--innodb_* about the InnoDB parameter

This article from "Yang Cloud" blog, reproduced please contact the author!

MySQL third-party backup tool xtrabackup instructions for use