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

Xtrabackup backup tool

Download address https://www.percona.com/downloads/XtraBackup/LATEST/

Xtrabackup has two main tools: xtrabackup and innobackupex

1. Xtrabackup can only back up InnoDB and XtraDB data tables, but not MyISAM data tables and table structure.

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.

Xtrabackup

General parameters during backup

-- defaults-file=#

The path to the default configuration file, if not, xtrabackup will look for the configuration file / etc/my.cnf, / etc/mysql/my.cnf, / usr/local/etc/my.cnf, ~ / .my.cnf from the following location, and read the [mysqld] and [xtrabackup] configuration segments in the configuration file. In [mysqld], you only need to specify datadir, innodb_data_home_dir, innodb_data_file_path, innodb_log_group_home_dir, innodb_log_files_in_group and innodb_log_file_size6 parameters to make xtrabackup work properly.

-- defaults-extra-file=#

If this parameter is used, the configuration file specified here will be read again after the global configuration file has been read

-- target-dir=name

C directory path of the backup file

-- backup

Implement backup to target-dir

-- prepare

Implement preparation before recovery of backup files (generate InnoDB log file)

-- print-param

Print the parameters required for backup or restore

-- use-memory=#

This parameter is used in prepare to control the amount of memory used by innodb instances in prepare.

-- suspend-at-end

Generate a xtrabackup_suspended file in the target-dir directory, suspend the xtrabackup process, and keep synchronizing changes in the data file to the backup file until the user manually deletes the xtrabackup_suspended file

-- throttle=#

The number of IO per second to limit the number of backup operations used to minimize the impact of backup on normal database business.

-- log-stream

This parameter is used when backup, outputs the contents of xtrabackup_logfile to standard output, and automatically uses the suspend-at-end parameter, which is used by the stream mode of the innobackupex script.

-- incremental-lsn=name

For incremental backups, only copy the ibd pages whose LSN is newer than the value specified by this parameter. Which LSN did the previous backup go to? you can see the xtrabackup_checkpoints file of the previous backup set.

-- incremental-basedir=name

This parameter is used in backup to back up a newer idb pages than the backup set at the location specified by this parameter.

-- incremental-dir=name

This parameter is used in prepare to specify the storage path of .delta files and log files generated during prepare.

-- databases=name

-- tables=name

Used when backing up data files of type file-per-table, using regular expressions to specify the innodb tables to be backed up

-- datadir=name

The data file directory of the MySQL database.

-- stream

Generate compressed files, the speed is relatively slow

Full backup restore:

Xtrabackup-defaults-file=/data/work/mysql-5.5.38/data/mysql3306/my3306.cnf-backup-target-dir=/data/bak_test/1

Prepare the backup before recovery.

Xtrabackup-defaults-file=/data/work/mysql-5.5.38/data/mysql3306/my3306.cnf-prepare-target-dir=/data/bak_test/1

Incremental backup:

Prepare for incremental backup before recovery

Xtrabackup-defaults-file=/data/work/mysql-5.5.38/data/mysql3306/my3306.cnf-prepare-target-dir=/data/bak_test/2

Copy and back up to the database directory

Modify permissions and restart data

Restart

Innobackupex

General parameter

-- defaults-file

Same as xtrabackup-- defaults-file parameter

-- apply-log

Encapsulation of xtrabackup-- prepare parameters

-- copy-back

Copy the backup data files to the datadir of the MySQL server during data recovery

-- remote-host=HOSTNAME

Store backup data on the process server through ssh

-- stream= [tar]

Backup file output format, tar using tar4ibd, the file can be obtained in the XtarBackup binary file. If-stream=tar is specified at the time of the backup, the directory where the tar4ibd file is located must be in $PATH (because you are using tar4ibd unzipping, which is available in XtraBackup's binary package).

In the use of parameter stream=tar backup, your xtrabackup_logfile may be temporarily placed in the / tmp directory, if you backup and write large xtrabackup_logfile may be very large (5G +), it is likely to fill your / tmp directory, you can use the parameter-tmpdir to specify the directory to solve this problem.

-- tmpdir=DIRECTORY

When-- remote-host or-- stream is specified, the directory where the transaction log is temporarily stored defaults to the temporary directory tmpdir specified in the MySQL configuration file

-redo-only-apply-log group

Only redo is forced to back up the log, skipping rollback. This is necessary when doing incremental backups.

-- use-memory=#

This parameter is used in prepare to control the amount of memory used by innodb instances in prepare.

-- throttle=IOS

Same as xtrabackup-- throttle parameter

-- sleep= is used by ibbackup to specify how many milliseconds the process stops copying each time 1m of data is backed up. It is also to minimize the impact on normal business during backup. For more information, please see the manual of ibbackup.

-- compress [= LEVEL]

Compression of backup data rows, which only supports ibbackup,xtrabackup, has not been implemented yet

-- include=REGEXP

The encapsulation of the xtrabackup parameter, tables, also supports ibbackup. Backup contains library tables, for example:-- include= "test.*", which means backing up all tables in the test library. If you need a full backup, omit this parameter; if you need to back up the two tables under the test library: test1 and test2, write:-- include= "test.test1 | test.test2". You can also use wildcards, such as:-- include= "test.test*".

-- databases=LIST

List the databases that needs to be backed up. If this parameter is not specified, all database containing MyISAM and InnoDB tables will be backed up

-- uncompress

Decompress the backup data file and support ibbackup,xtrabackup. This function has not been implemented yet.

-- slave-info

Backup slave library, plus-slave-info backup directory will generate an additional xtrabackup_slave_info file, where the master log file and offset will be saved, the file content is similar to: CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0

-- socket=SOCKET

Specify the location of the mysql.sock so that the backup process logs in to mysql. Exe.

-- databases=name

Specify the library to back up

-- tables=name

Used when backing up data files of type file-per-table, using regular expressions to specify the innodb tables to be backed up

-- the last backup directory in incremental-basedir=

-- incremental incremental backup

Full innobackupex-user=root-password=xxxxx--defaults-file=/data/work/mysql-5.5.38/data/mysql3306/my3306.cnf / data/bak_test/3

Preparation before recovery xtrabackup-- defaults-file=/data/work/mysql-5.5.38/data/mysql3306/my3306.cnf-- prepare-target-dir=/data/bak_test/3/2015-05-289-59-28

Apply-log, in order to speed up, it is generally recommended to set-- use-memory. After this step is completed, the backup files under the directory / backup/mysql/data/2013-10-29 / 09-05-25 are ready.

Copy the data to the database data directory to modify the startup data.

Incremental backup:

Incremental backups need to be based on full, assuming that we already have a full (x) or based on the previous incremental backup, incremental backups based on the full table.

-- defaults-file=/data/work/mysql-5.5.38/data/mysql3306/my3306.cnf-- user=root-- password=xxxx-- incremental-basedir=/data/bak_test/3/2015-05-289 10-23-35-- incremental / data/bak_test/

Previously prepared reduction

Pattern

Innobackupex-apply-log-redo-only BASE-DIR

Innobackupex-apply-log-redo-only BASE-DIR-incremental-dir=INCREMENTAL-DIR-1

Innobackupex-apply-log BASE-DIR-incremental-dir=INCREMENTAL-DIR-2

BASE-DIR refers to the full directory, INCREMENTAL-DIR-1 refers to the first incremental backup, INCREMENTAL-DIR-2 refers to the second incremental backup, and so on.

The first step is to redo the committed logs in all backup directories

Innobackupex-- apply-log-- redo-only / data/bak_test/3/2015-05-28 10-23-35

Innobackupex-- apply-log-- redo-only / data/bak_test/3/2015-05-289 10-23-35-- incremental-dir=/data/bak_test/2015-05-28 8 10-34-11

Roll back outstanding logs:

Innobackupex-- apply-log / data/bak_test/3/2015-05-28 10-23-35

Prepare data prepare, prepare xtrabackup before recovery-- defaults-file=/data/work/mysql-5.5.38/data/mysql3306/my3306.cnf-- prepare-- target-dir=/data/bak_test/3/2015-05-28 percent 10-23-35

Copy the data to the database directory and modify the permissions to start the data.

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.