Xtrbackup of MySQL incremental backup
I. Software version
Click (here) to collapse or open
Platform: Centos 7
Database version: MySQL 5.7
Xtrabackup version: xtrabackup version 2.4.8 II. Installation: binary decompression installation
Click (here) to collapse or open
[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 add environment variable export PATH=$PATH:/usr/local/xtrabackup/bin
Third, create backup users
Click (here) to collapse or open
Mysql > create user xtbakup@'localhost' identified by 'oracle'
Mysql > grant reload,process,lock tables,replication client on *. * to xtbakup@localhost; IV. Perform a full library backup
Click (here) to collapse or open
[root@my01 ~] # innobackupex-- defaults-file=/etc/my.cnf-- user=xtbakup-- password=oracle-- socket=/data/db/mysql/1221/mysql.sock / data/db/ xtbakup V, simulated deletion and recovery
Click (here) to collapse or open
[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
View database status
[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).
Application log
[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
InnoDB: Doing recovery: scanned up to log sequence number 9239093
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 logfile. / ib_logfile101 size to 48 MB
InnoDB: Setting logfile. / ib_logfile1 size to 48 MB
InnoDB: Renaming logfile. / 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
InnoDB: Doing recovery: scanned up to log sequence number 9239573
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
Restore
[root@my01 2018-03-20 16-02-00] # innobackupex-- defaults-file=/etc/my.cnf-- copy-back-- rsync / data/db/xtbakup/2018-03-20 years 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)
Create a table to insert data
Click (here) to collapse or open
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), (2)), (3)), (4))
Query OK, 4 rows affected (0.01sec)
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)
VII. The first incremental backup
Click (here) to collapse or open
[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. 8. Second incremental backup
Click (here) to collapse or open
Mysql > insert into T1 values (101 values), (102)), (103)), (104)
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)
Incremental backup 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-169. Analog data loss
Click (here) to collapse or open
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) 10. Restore data
Click (here) to collapse or open
Add the first incremental backup to the full backup
[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
Add the second incremental backup to the full backup (note: do not add redo-only)
* * Note * * when doing incremental backup and restore No redo-only parameter is added when the last incremental backup is added to the full backup *
[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 all the backups together
[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
Recover data
[root@my01 db] # innobackupex-- defaults-file=/etc/my.cnf-- copy-back-- rsync / data/db/xtbakup/2018-03-20 million 16-02-00 /
Validating data
Click (here) to collapse or open
[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)
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 >
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)
