MySQL增量备份之xtrbackup

一、软件版本

点击(此处)折叠或打开

成都创新互联成立与2013年，先为青阳等服务建站，青阳等地企业，进行企业商务咨询服务。为青阳企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

平台：Centos 7
数据库版本：MySQL 5.7
xtrabackup 版本：xtrabackup version 2.4.8

二、安装方式：二进制解压安装

点击(此处)折叠或打开

[root@my01 xtrabackup]# tar zxvf percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz
[root@my01 xtrabackup]# mv percona-xtrabackup-2.4.8-Linux-x86_64 /usr/local/xtrabackup
添加环境变量
export PATH=$PATH:/usr/local/xtrabackup/bin

三、创建备份用户

点击(此处)折叠或打开

mysql> create user xtbakup@'localhost' identified by 'oracle';
mysql> grant reload,process,lock tables,replication client on *.* to xtbakup@localhost;

四、执行全库备份

点击(此处)折叠或打开

[root@my01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=xtbakup --password=oracle --socket=/data/db/mysql/1221/mysql.sock /data/db/xtbakup

五、模拟删库并进行恢复

点击(此处)折叠或打开

[root@my01 ~]# service mysql stop
Shutting down MySQL..
[root@my01 db]# ls
mysql xtbakup
[root@my01 db]# mv mysql/ mysql_bak/
[root@my01 db]# ls
mysql_bak xtbakup
查看数据库状态
[root@my01 ~]# service mysql status
MySQL is not running
[root@my01 ~]# service mysql start
Starting MySQL.Logging to '/data/db/mysql/1221/mariadb.log'.
2018-03-20T03:26:56.919210Z mysqld_safe Directory '/data/db/mysql/1221' for UNIX socket file don't exists.
ERROR! The server quit without updating PID file (/data/db/mysql/1221/my01.pid).
应用日志
[root@my01 db]# innobackupex --apply-log /data/db/xtbakup/2018-03-20_16-02-00/
180320 16:01:55 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
xtrabackup: cd to /data/db/xtbakup/2018-03-20_16-02-00/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(9239084)
xtrabackup: using the following InnoDB configuration for recovery:
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 = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
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 = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 9239084
InnoDB: Doing recovery: scanned up to log sequence number 9239093 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 9239093 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 9239093
InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 9239112
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
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: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=9239112
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 9239564
InnoDB: Doing recovery: scanned up to log sequence number 9239573 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 9239573 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1

恢复

[root@my01 2018-03-20_16-02-00]# innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/db/xtbakup/2018-03-20_16-02-00/

[root@my01 ~]# cd /data/
[root@my01 data]# ls
db kafka-logs zookeeper
[root@my01 data]# cd db/
[root@my01 db]# ls
mysql mysql_bak xtbakup
[root@my01 db]# chown -R mysql.mysql mysql
[root@my01 db]# ll
total 0
drwxr-x---. 3 mysql mysql 18 Mar 20 11:29 mysql
drwxr-xr-x. 3 mysql mysql 18 Aug 18 2017 mysql_bak
drwxr-xr-x. 3 root root 33 Mar 20 11:03 xtbakup
[root@my01 db]# service mysql start
Starting MySQL.Logging to '/data/db/mysql/1221/mariadb.log'.
SUCCESS!

[root@my01 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t |
| test |
+----------------+
2 rows in set (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 81920 |
+----------+
1 row in set (0.11 sec)

六、创建表插入数据

点击(此处)折叠或打开

mysql> create table t1 (id int,name varchar(40));
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t |
| t1 |
| test |
+----------------+
3 rows in set (0.00 sec)
mysql> insert into t1 values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | ddd |
+------+------+
4 rows in set (0.00 sec)

七、第一次增量备份

点击(此处)折叠或打开

[root@my01 db]# ls
incdata mysql mysql_bak xtbakup
[root@my01 incdata]# pwd
/data/db/incdata
[root@my01 incdata]# innobackupex --defaults-file=/etc/my.cnf --user=xtbakup --password=oracle --socket=/data/db/mysql/1221/mysql.sock --incremental /data/db/incdata --incremental-basedir=/data/db/xtbakup/2018-03-20_16-02-00/
[root@my01 incdata]# ls
2018-03-20_16-04-16
[root@my01 incdata]# cd 2018-03-20_16-04-16/
[root@my01 2018-03-20_16-04-16]# ls
backup-my.cnf ibdata1.delta mysql scott test xtrabackup_checkpoints xtrabackup_logfile
ib_buffer_pool ibdata1.meta performance_schema sys xtrabackup_binlog_info xtrabackup_info
[root@my01 2018-03-20_16-04-16]# more xtrabackup_checkpoints
backup_type = incremental
from_lsn = 9251193
to_lsn = 9257319
last_lsn = 9257328
compact = 0
recover_binlog_info = 0

八、第二次增量备份

点击(此处)折叠或打开

mysql> insert into t1 values (101,'aaa'),(102,'bbb'),(103,'ccc'),(104,'ddd');
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | ddd |
| 101 | aaa |
| 102 | bbb |
| 103 | ccc |
| 104 | ddd |
+------+------+
8 rows in set (0.00 sec)
增量备份2
[root@my01 db]# innobackupex --defaults-file=/etc/my.cnf --user=xtbakup --password=oracle --socket=/data/db/mysql/1221/mysql.sock --incremental /data/db/incdata --incremental-basedir=/data/db/incdata/2018-03-20_16-04-16

九、模拟数据丢失

点击(此处)折叠或打开

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t |
| test |
+----------------+
2 rows in set (0.00 sec)

十、恢复数据

点击(此处)折叠或打开

将第一次的增量备份添加到全备份
[root@my01 db]# innobackupex --apply-log --redo-only /data/db/xtbakup/2018-03-20_16-02-00/ --incremental-dir=/data/db/incdata/2018-03-20_16-04-16
将第二次的增量备份添加到全备份（注意：不添加redo-only）
*****************************注意******************************
***************************************************************
做增量备份还原时，最后一次的增量备份添加到全备中时不添加redo-only参数
***************************************************************
[root@my01 db]# innobackupex --apply-log /data/db/xtbakup/2018-03-20_16-02-00/ --incremental-dir=/data/db/incdata/2018-03-20_16-09-04
把所有的备份和到一起进行一次apply-log
[root@my01 xtbakup]# innobackupex --apply-log /data/db/xtbakup/2018-03-20_16-02-00/
180320 16:17:37 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
xtrabackup: cd to /data/db/xtbakup/2018-03-20_16-02-00/
xtrabackup: This target seems to be already prepared.
InnoDB: Number of pools: 1
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
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
xtrabackup: using the following InnoDB configuration for recovery:
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
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 9262120
InnoDB: xtrabackup: Last MySQL binlog file position 1842, file name mysql-bin.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 9262139
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
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: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 9262139
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 9262158
180320 16:17:40 completed
恢复数据
[root@my01 db]# innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/db/xtbakup/2018-03-20_16-02-00/

十一、验证数据

点击(此处)折叠或打开

[root@my01 ~]# service mysql status
ERROR! MySQL is not running
[root@my01 ~]# service mysql start
Starting MySQL.Logging to '/data/db/mysql/1221/mariadb.log'.
SUCCESS!
[root@my01 ~]#
[root@my01 ~]#
[root@my01 ~]#
[root@my01 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19-log MySQL Community Server (GPL)
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| scott |
| sys |
| test |
+--------------------+
6 rows in set (0.01 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t |
| t1 |
| test |
+----------------+
3 rows in set (0.00 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | ddd |
| 101 | aaa |
| 102 | bbb |
| 103 | ccc |
| 104 | ddd |
+------+------+
8 rows in set (0.00 sec)

网页名称：MySQL增量备份之xtrbackup
文章来源：http://cdxtjz.com/article/jgeecp.html

MySQL增量备份之xtrbackup

其他资讯