xtrabackup工具的使用

xtrabackup工具的使用：
一、安装
1）由于centos6官方停止维护，所有国内的yum源都会404错误，阿里云的其实是改了位置，解决方法：
vim /etc/yum/pluginconf.d/fastestmirror.conf
把enbale=0改成1
curl -o /etc/yum.repos.d/CentOS-Base.repo https://www.xmpan.com/Centos-6-Vault-Aliyun.repo
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-xtrabackup-24
查看xtrabackup的版本信息
[root@web discuz]# xtrabackup -v
xtrabackup: recognized server arguments: --datadir=/data --log_bin=/mybinlog/mysql-bin --server-id=1 --innodb_file_per_table=1 --innodb_buffer_pool_size=8M
xtrabackup version 2.4.21 based on MySQL server 5.7.32 Linux (x86_64) (revision id: 5988af5)

查看安装后包含的工具：
[root@web discuz]# rpm -ql percona-xtrabackup-24|grep bin|xargs ls -h1
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup

#innobackupex 逐步停用
#xbcloud和xbcloud_osenv云备份
#xbcrypt加密备份
#xbstream是xtrabackup的流数据功能，通过流数据功能，可将备份内容打包并传给管道后的压缩工具进行压缩；
#xtrabackup是主程序；
xtrabackup的选项:
--backup 创建一个备份,一般与--target-dir选项配合使用

--apply-log-only 在--prepare阶段,防止因为增量备份的不完整而导致产生会滚操作
--compress 对输出的备份文件进行压缩,压缩格式为.qp
--call-backup从备份目录复制备份文件至my.cnf中指定的datadir的路径
--databases 备份指定的数据库 "databasename1[.table_name1]databasename2[.table_name2] . . ."
--databases-exclude=name 备份时排除指定的数据库
--defaults-file=[MY.CNF]
--default-port
--default-user
--default-password
--incremental 增量备份
--incremental-basedir=DIRECTORY 指定增量备份的基础备份
--incremental-dir=DIRECTORY指定存放增量备份文件的目录
--prepare
--server-id
--slave-info
--use-memory=#
2）完整备份
[root@web]# xtrabackup --backup --user=root --password=root --port=3306 --default-file=/etc/my.cnf --target-dir=/backup/full

....................
201210 16:22:11 [00]        ...done
xtrabackup: Transaction log of lsn (450343033) to (450343042) was copied.
201210 16:22:11 completed OK!

查看备份的文件
[root@web discuz]# ls /backup/full/ -lh
total 77M
-rw-r----- 1 root root  487 Dec 10 16:22 backup-my.cnf
drwxr-x--- 2 root root 4.0K Dec 10 16:22 data
drwxr-x--- 2 root root 4.0K Dec 10 16:22 girls
-rw-r----- 1 root root  357 Dec 10 16:22 ib_buffer_pool
-rw-r----- 1 root root  76M Dec 10 16:21 ibdata1
drwxr-x--- 2 root root 4.0K Dec 10 16:22 magedu
drwxr-x--- 2 root root 4.0K Dec 10 16:22 mysql
drwxr-x--- 2 root root 4.0K Dec 10 16:22 performance_schema
drwxr-x--- 2 root root  12K Dec 10 16:22 sys
drwxr-x--- 2 root root  60K Dec 10 16:22 ultrax
drwxr-x--- 2 root root 4.0K Dec 10 16:22 wordpress
-rw-r----- 1 root root   25 Dec 10 16:22 xtrabackup_binlog_info
-rw-r----- 1 root root  141 Dec 10 16:22 xtrabackup_checkpoints
-rw-r----- 1 root root  535 Dec 10 16:22 xtrabackup_info
-rw-r----- 1 root root 2.5K Dec 10 16:22 xtrabackup_logfile
drwxr-x--- 2 root root  16K Dec 10 16:22 zabbix

选择magedu库插入数据，然后进行增量备份
mysql> use magedu;
Database changed
mysql> show tables;
+------------------+
| Tables_in_magedu |
+------------------+
| m                |
+------------------+
1 row in set (0.00 sec)

mysql> select * from m;
+------+------------+
| id   | name       |
+------+------------+
|    1 | xiaozhao   |
|    2 | zhouzhiruo |
|    3 | yangmi     |
|    4 | ajiao      |
+------+------------+
4 rows in set (0.01 sec)

mysql> insert into m values (5,'longzeluola'),(6,'canglaoshi');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from m;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | xiaozhao    |
|    2 | zhouzhiruo  |
|    3 | yangmi      |
|    4 | ajiao       |
|    5 | longzeluola |
|    6 | canglaoshi  |
+------+-------------+
6 rows in set (0.00 sec)

注意：第一次增量备份，需要指定完整备份节点
[root@web discuz]#mkdir -pv /backup/incremental/inc1
[root@web discuz]#xtrabackup --backup --user=root --password=root --port=3306 --default-file=/etc/my.cnf --target-dir=/backup/incremental/inc1 --incremental-basedir=/backup/full

[root@web ~]# ll /backup/incremental/inc1/ -lh
total 2.6M
-rw-r----- 1 root root  487 Dec 10 17:49 backup-my.cnf
drwxr-x--- 2 root root 4.0K Dec 10 17:49 data
drwxr-x--- 2 root root 4.0K Dec 10 17:49 girls
-rw-r----- 1 root root  382 Dec 10 17:49 ib_buffer_pool
-rw-r----- 1 root root 2.4M Dec 10 17:49 ibdata1.delta
-rw-r----- 1 root root   60 Dec 10 17:49 ibdata1.meta
drwxr-x--- 2 root root 4.0K Dec 10 17:49 magedu
drwxr-x--- 2 root root 4.0K Dec 10 17:49 mysql
drwxr-x--- 2 root root 4.0K Dec 10 17:49 performance_schema
drwxr-x--- 2 root root  12K Dec 10 17:49 sys
drwxr-x--- 2 root root  60K Dec 10 17:49 ultrax
drwxr-x--- 2 root root 4.0K Dec 10 17:49 wordpress
-rw-r----- 1 root root   21 Dec 10 17:49 xtrabackup_binlog_info
-rw-r----- 1 root root  147 Dec 10 17:49 xtrabackup_checkpoints
-rw-r----- 1 root root  586 Dec 10 17:49 xtrabackup_info
-rw-r----- 1 root root 2.5K Dec 10 17:49 xtrabackup_logfile
drwxr-x--- 2 root root  20K Dec 10 17:49 zabbix

再次插入数据，然后进行第二次增量备份，注意指定incremental-basedir是指定第一次增量备份的目录mysql> insert into m values (7,'xiaozemaliya');
Query OK, 1 row affected (0.03 sec)
[root@web ~]# mkdir /backup/incremental/inc2
[root@web ~]# xtrabackup --backup --user=root --password=root --port=3306 --default-file=/etc/my.cnf --target-dir=/backup/incremental/inc3 --incremental-basedir=/backup/incremental/inc2
xtrabackup: recognized server arguments: --datadir=/data --log_bin=/mybinlog/mysql-bin --server-id=1 --innodb_file_per_table=1 --innodb_buffer_pool_size=8M
xtrabackup: recognized client arguments: --socket=/tmp/mysql.sock --port=3306 --backup=1 --user=root --password=* --port=3306 --target-dir=/backup/incremental/inc3 --incremental-basedir=/backup/incremental/inc2
201216 11:13:01  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
201216 11:13:01  version_check Connected to MySQL server
..........................................
..........................................
201216 11:13:25 [00] Writing /backup/incremental/inc3/xtrabackup_info
201216 11:13:25 [00]        ...done
xtrabackup: Transaction log of lsn (450347265) to (450347274) was copied.
201216 11:13:25 completed OK!

模拟删除数据后使用备份恢复
[root@web ~]# service mysqld stop
Shutting down MySQL... SUCCESS!
[root@web data]# mv ./* /tmp/mysqldatabackup/
[root@web data]# ls

准备完整备份文件
[root@web data]# xtrabackup --prepare --apply-log-only --target-dir=/backup/full/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1
.........................................
InnoDB: Shutdown completed; log sequence number 450343051
InnoDB: Number of pools: 1
201216 15:54:50 completed OK!
准备第一次增量备份文件
[root@web data]# xtrabackup --prepare --apply-log-only --target-dir=/backup/full --incremental-dir=/backup/incremental/inc1
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1
.........................................
201216 15:57:21 [00] Copying /backup/incremental/inc1//xtrabackup_info to ./xtrabackup_info
201216 15:57:21 [00]        ...done
201216 15:57:21 completed OK!
准备第二次增量备份文件
[root@web data]# xtrabackup --prepare --target-dir=/backup/full --incremental-dir=/backup/incremental/inc2
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1
............................................
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 450347560
201216 15:59:10 completed OK!
恢复数据:
[root@web data]# xtrabackup --copy-back --target-dir=/backup/full
xtrabackup: recognized server arguments: --datadir=/data --log_bin=/mybinlog/mysql-bin --server-id=1 --innodb_file_per_table=1 --innodb_buffer_pool_size=8M
xtrabackup: recognized client arguments: --socket=/tmp/mysql.sock --port=3306 --copy-back=1 --target-dir=/backup/full
xtrabackup version 2.4.21 based on MySQL server 5.7.32 Linux (x86_64) (revision id: 5988af5)
201216 16:07:04 [01] Copying ib_logfile0 to /data/ib_logfile0
201216 16:07:05 [01]        ...done
201216 16:07:05 [01] Copying ib_logfile1 to /data/ib_logfile1
.............................................
201216 16:07:27 [01]        ...done
201216 16:07:27 [01] Copying ./performance_schema/replication_applier_status_by_worker.frm to /data/performance_schema/replication_applier_status_by_worker.frm
201216 16:07:27 [01]        ...done
201216 16:07:27 completed OK!
对目录属主和属组授权
[root@web data]# ll
total 188548
drwxr-x--- 2 root root     4096 Dec 16 16:07 data
drwxr-x--- 2 root root     4096 Dec 16 16:07 girls
-rw-r----- 1 root root      357 Dec 16 16:07 ib_buffer_pool
[root@web data]# chown -R mysql.mysql /data
[root@web data]# ll
total 188548
drwxr-x--- 2 mysql mysql     4096 Dec 16 16:07 data
drwxr-x--- 2 mysql mysql     4096 Dec 16 16:07 girls
-rw-r----- 1 mysql mysql      357 Dec 16 16:07 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Dec 16 16:07 ibdata1
[root@web data]# service mysqld start
Starting MySQL.Logging to '/data/web.err'.
... SUCCESS!
恢复后查询数据验证
root@web data]# mysql -uroot -p
Enter password:
mysql> use magedu
Database changed
mysql> select * from m;
+------+--------------+
| id   | name         |
+------+--------------+
|    1 | xiaozhao     |
|    2 | zhouzhiruo   |
|    3 | yangmi       |
|    4 | ajiao        |
|    5 | longzeluola  |
|    6 | canglaoshi   |
|    7 | xiaozemaliya |
+------+--------------+
7 rows in set (0.03 sec)