MySQL-xtrabackup安装与使用

xtrabackup安装与使用

OS: CENTOS7.5

MYSQL版本： 5.7.22

XTRABACKUP版本： 2.4.9

1、rpm安装

yum -y install libev

yum -y install perl-DBI

yum -y install perl-DBD-MySQL

yum -y install perl-Digest-MD5

yum -y install perl-IO-Socket-SSL

2、备份

2.1 全备

#全备

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456  /innobackup/

# cat xtrabackup_checkpoints

backup_type = full-backuped

from_lsn = 0

to_lsn = 14385584

last_lsn = 14385593

compact = 0

recover_binlog_info = 0

 

#备份部分database

innobackupex --user=root --password=123456  --databases=test  /innobackup/

 

[root@mysqldb 2018-12-14_09-45-32]# innobackupex --user=root --password=123456  --databases=test /innobackup/

181214 10:07:14 innobackupex: Starting the backup operation

 

IMPORTANT: Please check that the backup run completes successfully.

           At the end of a successful backup run innobackupex

           prints "completed OK!".

181214 10:07:14  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/var/lib/mysql/mysql.sock' as 'root'  (using password: YES).

181214 10:07:14  version_check Connected to MySQL server

181214 10:07:14  version_check Executing a version check against the server...

181214 10:07:14  version_check Done.

181214 10:07:14 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /var/lib/mysql/mysql.sock

Using server version 5.7.22-log

innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /var/lib/mysql

xtrabackup: open files limit requested 0, set to 1024

xtrabackup: using the following InnoDB configuration:

xtrabackup:   innodb_data_home_dir = .

xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup:   innodb_log_group_home_dir = ./

xtrabackup:   innodb_log_files_in_group = 2

xtrabackup:   innodb_log_file_size = 50331648

InnoDB: Number of pools: 1

181214 10:07:15 >> log scanned up to (14384990)

xtrabackup: Generating a list of tablespaces

Skipping db: ./mysql

Skipping db: ./performance_schema

Skipping db: ./sys

Skipping db: ./jumpserver

Skipping db: ./test01

Skipping db: ./test02

Skipping db: ./test03

Skipping db: ./test04

InnoDB: Allocated tablespace ID 401 for test/t1, old maximum was 0

Skipping db: ./db1

Skipping db: ./db2

Skipping db: ./db3

181214 10:07:15 [01] Copying ./ibdata1 to /innobackup/2018-12-14_10-07-14/ibdata1

181214 10:07:16 [01]        ...done

181214 10:07:16 [01] Copying ./test/t1.ibd to /innobackup/2018-12-14_10-07-14/test/t1.ibd

181214 10:07:16 [01]        ...done

181214 10:07:16 [01] Copying ./test/t2.ibd to /innobackup/2018-12-14_10-07-14/test/t2.ibd

181214 10:07:16 [01]        ...done

181214 10:07:16 [01] Copying ./test/t3.ibd to /innobackup/2018-12-14_10-07-14/test/t3.ibd

181214 10:07:16 [01]        ...done

181214 10:07:16 >> log scanned up to (14384990)

181214 10:07:16 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...

181214 10:07:16 Executing FLUSH TABLES WITH READ LOCK...

181214 10:07:16 Starting to backup non-InnoDB tables and files

181214 10:07:16 [01] Skipping ./ibdata1.

181214 10:07:16 [01] Skipping ./ib_logfile1.

181214 10:07:16 [01] Skipping ./ib_logfile0.

181214 10:07:16 [01] Skipping ./auto.cnf.

Skipping db: ./mysql

Skipping db: ./performance_schema

Skipping db: ./sys

181214 10:07:16 [01] Skipping ./ca-key.pem.

181214 10:07:16 [01] Skipping ./ca.pem.

181214 10:07:16 [01] Skipping ./server-key.pem.

181214 10:07:16 [01] Skipping ./server-cert.pem.

181214 10:07:16 [01] Skipping ./client-key.pem.

181214 10:07:16 [01] Skipping ./client-cert.pem.

181214 10:07:16 [01] Skipping ./private_key.pem.

181214 10:07:16 [01] Skipping ./public_key.pem.

Skipping db: ./jumpserver

Skipping db: ./test01

Skipping db: ./test02

Skipping db: ./test03

Skipping db: ./test04

181214 10:07:16 [01] Skipping ./mysql-bin.000003.

181214 10:07:16 [01] Copying ./test/db.opt to /innobackup/2018-12-14_10-07-14/test/db.opt

181214 10:07:16 [01]        ...done

181214 10:07:16 [01] Copying ./test/t1.frm to /innobackup/2018-12-14_10-07-14/test/t1.frm

181214 10:07:16 [01]        ...done

181214 10:07:16 [01] Copying ./test/t2.frm to /innobackup/2018-12-14_10-07-14/test/t2.frm

181214 10:07:16 [01]        ...done

181214 10:07:16 [01] Copying ./test/t3.frm to /innobackup/2018-12-14_10-07-14/test/t3.frm

181214 10:07:16 [01]        ...done

181214 10:07:16 [01] Skipping ./ibtmp1.

181214 10:07:16 [01] Skipping ./mysql.sock.lock.

Skipping db: ./db1

Skipping db: ./db2

Skipping db: ./db3

181214 10:07:16 [01] Skipping ./mysql-bin.000001.

181214 10:07:16 [01] Skipping ./mysql-bin.000002.

181214 10:07:16 [01] Skipping ./mysql-bin.000004.

181214 10:07:16 [01] Skipping ./ib_buffer_pool.

181214 10:07:16 [01] Skipping ./mysql-bin.000005.

181214 10:07:16 [01] Skipping ./mysql-bin.000006.

181214 10:07:16 [01] Skipping ./mysql-bin.index.

181214 10:07:16 Finished backing up non-InnoDB tables and files

181214 10:07:16 [00] Writing /innobackup/2018-12-14_10-07-14/xtrabackup_binlog_info

181214 10:07:16 [00]        ...done

181214 10:07:16 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

xtrabackup: The latest check point (for incremental): '14384981'

xtrabackup: Stopping log copying thread.

.181214 10:07:16 >> log scanned up to (14384990)

 

181214 10:07:17 Executing UNLOCK TABLES

181214 10:07:17 All tables unlocked

181214 10:07:17 [00] Copying ib_buffer_pool to /innobackup/2018-12-14_10-07-14/ib_buffer_pool

181214 10:07:17 [00]        ...done

181214 10:07:17 Backup created in directory '/innobackup/2018-12-14_10-07-14/'

MySQL binlog position: filename 'mysql-bin.000006', position '154'

181214 10:07:17 [00] Writing /innobackup/2018-12-14_10-07-14/backup-my.cnf

181214 10:07:17 [00]        ...done

181214 10:07:17 [00] Writing /innobackup/2018-12-14_10-07-14/xtrabackup_info

181214 10:07:17 [00]        ...done

xtrabackup: Transaction log of lsn (14384981) to (14384990) was copied.

181214 10:07:17 completed OK!

2.2 增量备份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental /innobackup/incremental/ --incremental-basedir=/innobackup/2018-12-14_09-45-32/

/innobackup/2018-12-14_09-45-32/ : 此为全备存放的路径，或者基于上次增量备份的路径

 

[root@mysqldb 2018-12-14_11-12-02]# cat xtrabackup_checkpoints

backup_type = incremental

from_lsn = 14384981

to_lsn = 14385584

last_lsn = 14385593

compact = 0

recover_binlog_info = 0

2.3 恢复

2.3.1 预处理备份

如果是全备恢复，无增量则无需加--redo-only参数

innobackupex --apply-log /innobackup/2018-12-14_10-07-14

利用 --apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态

 

如果是全备+增量恢复，则需-redo-only参数，且增量备份按顺序需要合并到全备中

innobackupex --apply-log  --redo-only /innobackup/2018-12-14_11-15-18

innobackupex --apply-log --redo-only /innobackup/2018-12-14_11-15-18  --incremental-dir=/innobackup/incremental/2018-12-14_11-26-49

innobackupex --apply-log /innobackup/2018-12-14_11-15-18

2.3.2 恢复

恢复时，需要确保my.cnf指定的datadir目录为空

innobackupex --defaults-file=/etc/my.cnf --copy-back /innobackup/2018-12-14_11-15-18/

 

2.4 流备份

a、备份到本地

# innobackupex --stream=tar /tmp >/backup/bak.tar ###非压缩方式

# innobackupex --stream=tar /tmp |gzip >/backup/bakz.tar.gz ###压缩方式

# ls -hltr

total 42M

-rw-r--r-- 1 root root 39M Apr 15 17:23 bak.tar

-rw-r--r-- 1 root root 3.3M Apr 15 17:24 bakz.tar.gz

 

b、备份到远程

# innobackupex --stream=tar /tmp | ssh root@192.168.1.7 \ "cat - > /backup/bak.tar" ###非压缩方式

# innobackupex --stream=tar /tmp | ssh root@192.168.1.7 \ "gzip >/backup/bak.tar.gz" ###压缩方式

 

a、备份到本地

# innobackupex --stream=xbstream /tmp >/backup/bak.xbstream ###非压缩方式

# innobackupex --stream=xbstream --compress /tmp >/backup/bak_compress.xbstream ###压缩方式

# ls -hltr

total 43M

-rw-r--r-- 1 root root 37M Apr 15 17:41 bak.xbstream

-rw-r--r-- 1 root root 6.0M Apr 15 17:41 bak_compress.xbstream

 

###解压备份

# mkdir bk bk_compress

# xbstream -x < bak.xbstream -C /backup/bk ###解压xbstream格式

 

###解压xbstream格式，compress参数的备份

# xbstream -x < bak_compress.xbstream -C /backup/bk_compress ###首先解压xbstream

# for bf in `find . -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) && rm $bf; done ###再解压qp压缩格式

# innobackupex --decompress /backup/bk_compress ###如果xtrabackup版本大于2.1.4，可以直接通过该方式解压

 

b、备份到远程

###使用压缩备份到远程并解压

# innobackupex --stream=xbstream --compress /tmp | ssh root@192.168.1.7 "xbstream -x -C /backup/stream"

3、innobackupex 参数说明

--defaults-file

指定my.cnf参数文件的位置[此配置文件里必须指定datadir]

--apply-log

同xtrabackup的--prepare参数,一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据 文件仍处理不一致状态。--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。

--copy-back

做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir

--remote-host=HOSTNAME

通过ssh将备份数据存储到进程服务器上

--stream=[tar]

备份文件输出格式, 该文件可在XtarBackup binary文件中获得. 在使用参数stream=tar备份的时候,你的xtrabackup_logfile可能会临时放在/tmp目录下,如果你备份的时候并发写入较大的话,xtrabackup_logfile可能会很大(5G+),很可能会撑满你的/tmp目录,可以通过参数--tmpdir指定目录来解决这个问题.

--tmpdir=DIRECTORY

当有指定--remote-host or --stream时, 事务日志临时存储的目录, 默认采用MySQL配置文件中所指定的临时目录tmpdir

--redo-only --apply-log

强制备份日志时只redo,跳过rollback,这在做增量备份时非常必要

--use-memory=*

该参数在prepare的时候使用,控制prepare时innodb实例使用的内存

--databases=LIST

列出需要备份的databases,如果没有指定该参数,所有包含MyISAM和InnoDB表的database都会被备份

--slave-info

备份从库, 加上--slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于:CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0

--socket=SOCKET

指定mysql.sock所在位置，以便备份进程登录mysql.