Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the xtrabackup backup tool command how to carry out single-database incremental backup and restore. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Innobackupex parameter explanation:

-- defaults-file= [MY.CNF] this option is passed to the xtrabackup child process to read the default options from the specified file

-- apply-log restores from backup.

-- redo-only this option forces skipping the rollback phase and only redo. It is necessary to use it if you want to use incremental changes after backup.

-- copy-back copies data and index files from backup directory to datadir directory

-- remote-host=HOSTNAME is backed up to a remote host, using ssh

-- stream= [tar | cpio (notimplemented)] specifies the backup standard output format

-- tmpdir=DIRECTORY is the same as tmpdir by default. After using the-remote-host or-stream parameters, the transfer log files will be stored in a temporary directory

The-- use-memory=MB option is passed to the xtrabackup child process. Restore memory size

The-- parallel=NUMBER-OF-THREADS option is passed to the xtrabackup child process, which specifies the total number of data transfer threads. Default is 1

-- the throttle=IOS option is passed to the xtrabackup child process to limit the number of IO threads

The-- sleep=MS option is passed to the xtrabackup child process. How much MS time is paused per copy of 1MB data

The-- compress [= LEVEL] option is passed to the xtrabackup child process. The compression level is 0-9.1 for fast compression, 9 for optimal compression, and 0 for no compression. The default is 1.

The-- include=REGEXP option is passed to the xtrabackup child process. Match using rules

-- databases=LIST specifies backup database

-- tables-file=FILE

The-- uncompress option is passed to the xtrabackup child process. Do not compress compressed InnoDB data files

-- export is used only with the prepare option. Option is passed to the xtrabackup child process.

-- user=NAME

-- password=WORD

-- host=HOST

-- port=PORT

-- slave-info backup copies the slave server, and the master-slave information is recorded in the ibbackup_slave_info file

-- socket=SOCKET

-- no-timestamp does not create a new backup directory with the name of the current timestamp under the backup root

-- ibbackup=IBBACKUP-BINARYibbackup binary path

Table-level locking is prohibited by no-lock. All are used under InnoDB engine tables and unrelated binary log locations

-- scpopt=SCP-OPTIONS specifies the scp parameter

Innobackupex single library incremental recovery

[root@mini1] # mkdir-p / opt/mysqlbackup/ {full,inc}

# full backup

[root@mini1] # innobackupex-- user=root-- password=123456-- include=test / opt/mysqlbackup/full/

Mysql > select * from test.t1

+-+ +

| | id | name |

+-+ +

| | 1 | tom1 |

| | 2 | tom1 |

+-+ +

# insert data

Mysql > insert into test.t1 (name) values ('tom2')

# incremental backup 1

[root@mini1] # innobackupex-- user=root-- password=123456-- include=test-- incremental / opt/mysqlbackup/inc/-- incremental-basedir=/opt/mysqlbackup/full/2018-07-30mm 20-36-57 /

# insert data and add a new table

Mysql > insert into test.t1 (name) values ('tom3')

Mysql > create table test.t2 (id int)

Mysql > insert into test.t2 values (1)

Mysql > select * from test.t2

+-+

| | id |

+-+

| | 1 |

# incremental backup 2

[root@mini1] # innobackupex-- user=root-- password=123456-- include=test-- incremental / opt/mysqlbackup/inc/-- incremental-basedir=/opt/mysqlbackup/inc/2018-07-30 20-47-55 /

[root@mini1 ~] # ls / opt/mysqlbackup/full

2018-07-30 20-36-57

[root@mini1 ~] # ls / opt/mysqlbackup/inc/

2018-07-30 20-47-55 2018-07-30 20-51-21

# Delete database

Mysql > drop database test

[root@mini1 ~] # service mysqld stop

[root@mini1] # cd / opt/mysqlbackup/full/2018-07-30 20-36-57/test/

[root@mini1 test] # ls

Db.opt t1.frm t1.ibd

[root@mini1] innobackupex-- apply-log-- redo-only / opt/mysqlbackup/full/2018-07-30 20-34-34 /

[root@mini1] innobackupex-- apply-log-- redo-only / opt/mysqlbackup/full/2018-07-30 20-34-34 /-incremental-dir=/opt/mysqlbackup/inc/2018-07-30 20-38-12 /

[root@mini1] innobackupex-- apply-log-- redo-only / opt/mysqlbackup/full/2018-07-30 20-34-34 /-- incremental-dir=/opt/mysqlbackup/inc/2018-07-30 20-42-03 /

[root@mini1] innobackupex-- apply-log-- export / opt/mysqlbackup/full/2018-07-30 20-34-34 /

[root@mini1] cp-rf / opt/mysqlbackup/full/2018-07-30mm 20-34-34 Universe * / usr/local/mysql/data/

[root@mini1 data] # service mysqld restart

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 1 | tom1 |

| | 2 | tom1 |

| | 3 | tom1 |

| | 4 | tom1 |

| | 5 | tom1 |

| | 6 | tom1 |

+-+ +

6 rows in set (0.00 sec)

Mysql > select * from T2

->

+-+

| | id |

+-+

| | 1 |

| | 3 |

| | 2 |

+-+

Innobackupex backup multiple libraries

[root@mini1 full] # innobackupex-user=root-password=123456-databases= "test test1" / opt/mysqlbackup/full/

Xtrabackup backs up a single table

[root@mini1] # mkdir-p / opt/mysqlbackup/ {full,inc}

[root@mini1] # mysql-uroot-p123456

Mysql > use test

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | sys |

| | test |

| | test1 |

+-+

Mysql > select * from test.t1

+-+ +

| | id | name |

+-+ +

| | 1 | tom1 |

| | 2 | tom1 |

+-+ +

[root@mini1 ~] # xtrabackup-user=root-password=123456-backup-target-dir=/opt/mysqlbackup/full/full_$ (date +% F_%H%M%S)-databases= "test"

Mysql > insert into test.t1 (name) values ('tom1')

[root@mini1] # xtrabackup-- user=root-- password=123456-- backup-- target-dir=/opt/mysqlbackup/inc/inc_$ (date +% F_%H%M%S)-- incremental-basedir=/opt/mysqlbackup/full/full_2018-07-30mm 204200 /-- databases='test'

Mysql > insert into test.t1 (name) values ('tom1')

Mysql > insert into test.t2 (name) values ('tom1')

[root@mini1] # xtrabackup-- user=root-- password=123456-- backup-- target-dir=/opt/mysqlbackup/inc/inc_$ (date +% F_%H%M%S)-- incremental-basedir=/opt/mysqlbackup/inc/inc_2018-07-30mm 205007 /-- databases='test'

Mysql > drop table test.t1

Mysql > delete from test.t2 where id=1

[root@mini1] # xtrabackup-prepare-apply-log-only-target-dir=/opt/mysqlbackup/full/full_2018-07-30mm 204200 /

[root@mini1] # cat / opt/mysqlbackup/full/full_2018-07-30_204200/xtrabackup_checkpoints

Backup_type = log-applied

From_lsn = 0

To_lsn = 2609032

Last_lsn = 2609041

Compact = 0

Recover_binlog_info = 0

[root@mini1] # xtrabackup-- prepare-- apply-log-only-- target-dir=/opt/mysqlbackup/full/full_2018-07-30mm 204200 /-- incremental-dir=/opt/mysqlbackup/inc/inc_2018-07-30mm 205007 /

[root@mini1] # xtrabackup-- prepare-- apply-log-only-- target-dir=/opt/mysqlbackup/full/full_2018-07-30mm 204200 /-- incremental-dir=/opt/mysqlbackup/inc/inc_2018-07-30mm 205157 /

[root@mini1] # xtrabackup-- prepare-- target-dir=/opt/mysqlbackup/full/full_2018-07-30mm 204200 /

[root@mini1] # cat / opt/mysqlbackup/full/full_2018-07-30_204200/xtrabackup_checkpoints

Backup_type = full-prepared

From_lsn = 0

To_lsn = 2613778

Last_lsn = 2613787

Compact = 0

Recover_binlog_info = 0

[root@mini1 ~] # service mysqld stop

[root@mini1] # cd / opt/mysqlbackup/full/full_2018-07-30mm 204200 /

[root@mini1 full_2018-07-30mm 204200] # rsync-rvt-- exclude 'xtrabackup_checkpoints'-- exclude' xtrabackup_logfile'. / / usr/local/mysql/data/

[root@mini1 full_2018-07-30mm 204200] # chown-R mysql:mysql / usr/local/mysql/

[root@mini1 full_2018-07-30mm 204200] # service mysqld start

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | T1 |

| | T2 |

+-+

2 rows in set (0.00 sec)

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 1 | tom1 |

| | 2 | tom1 |

| | 3 | tom1 |

| | 4 | tom1 |

+-+ +

4 rows in set (0.00 sec)

Mysql > select * from T2

+-+ +

| | id | name |

+-+ +

| | 1 | tom1 |

| | 2 | tom1 |

| | 3 | tom1 |

+-+ +

3 rows in set (0.00 sec)

Xtrabackup backup multiple libraries

[root@mini1 /] # xtrabackup-defaults-file=/etc/my.cnf-user=root-password=123456-backup-target-dir=/opt/mysqlbackup/full/3-databases='test test1'

Mysql > insert into test.t1 (name) values ('tom1')

The above is the xtrabackup backup tool command shared by Xiaobian for you how to restore a single incremental backup. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.