Kernel:
$ cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core)
$ uname -r
3.10.0-693.el7.x86_64
Download and install xtrabackup:
$ wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.12-r170eb8c- el7-x86_64-bundle.tar
$ tar -xf Percona-XtraBackup-2.4.12-r170eb8c-el7-x86_64-bundle.tar
$ yum -y install percona-xtrabackup-24-2.4.12-1 .el7.x86_64.rpm
$ which xtrabackup
/usr/bin/xtrabackup
$ innobackupex -v
xtrabackup: recognized server arguments: –innodb_buffer_pool_size=1024M –datadir=/data/mysql /data –server-id=1
Create test data:
mysql> mysql -uroot -p
mysql> create database opop charset=’utf8mb4′;
mysql> use opop;
mysql> create table user(id int unsigned primary key auto_increment,name varchar(30));
mysql> show tables;
mysql> insert into user(name) values(‘ jack’),(‘tom’),(‘lily’),(‘lucy’);
mysql> select from user;
mysql> commit;
mysql> select from user;
Xtrabackup full backup and recovery
$ innobackupex –defaults-file=/etc/my.cnf –user=sstuser –password=”123456″ –backup. # The last point is the current directory
xtrabackup: recognized server arguments: –innodb_buffer_pool_size=1024M –datadir=/data/mysql/data –server-id=1
xtrabackup: recognized client arguments: –innodb_buffer_pool_size=1024M –datadir=/data/mysql/data –server-id=1 –backup=1
181025 08:27:43 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!”.
181025 08:27:43 version_check Connecting to MySQL server with DSN’dbi:mysql:;mysql_read_default_group=xtrabackup’ as’sstuser’ (using password: YES).
181025 08:27:43 version_check Connected to MySQL server
181025 08:27:43 version_check Executing a version check against the server…
181025 08:27:43 version_check Done.
181025 08:27:43 Connecting to MySQL server host: localhost, user: sstuser, password: set, port: not set, socket: not set
Using server version 5.7.23-23-57
innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/data
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 = ./
xtrabackup = ./
xtrabackup = innodb_group_files: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
181025 08:27:43 >> log scanned up to (2605686)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
181025 08:27:44 [01] Copying ./ibdata1 to /root/2018-10-25_08-27-43/ibdata1
181025 08:27:44 [ 01] …done
….
….
181025 08:27:46 Executing LOCK TABLES FOR BACKUP… #Translation: execute lock table for backup
181025 08:27:46 Starting to backup non-InnoDB tables and files
181025 08:27:46 [01] Copying ./ mysql/db.opt to /root/2018-10-25_08-27-43/mysql/db.opt
181025 08:27:46 [01] …done
181025 08:27:46 [ 01] Copying ./mysql/db.frm to /root/2018-10-25_08-27-43/mysql/db.frm
181025 08:27:46 [01] …done
.. ..
….
181025 08:27:47 [01] Copying ./opop/user.frm to /root/2018-10-25_08-27-43/opop/user .frm
181025 08:27:47 [01] …done
181025 08:27:47 Finished backing up non-InnoDB tables and files
181025 08:27:47 Executing LOCK BINLOG FOR BACKUP … #Execute lock binary file to start backup
181025 08:27:47 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS… #Execute flush no_write_to_binlog engine log
xtrabackup: The latest check point (for incremental): ‘2605677’
xtrabackup: Stopping log copying thread. #Stop the log copy thread.
.181025 08:27:47 >> log scanned up to (2605686) #Log scanned up to (2605686)
181025 08:27:47 Executing UNLOCK BINLOG #Execute unlock binlog
181025 08:27:47 Executing UNLOCK TABLES #Execute unlocking table
181025 08:27:47 All tables unlocked 025 to pool_00_07/08:0027 Unlocking table
181025 08:27:47 Backup created in directory’/root/2018-10-25_08-27-43/ ‘#Create a backup in the /root/2018-10-25_08-27-43/ directory
181025 08:27:47 [00] Writing /root/2018-10-25_08-27-43/backup-my. cnf
181025 08:27:47 [00] …done
181025 08:27:47 [00] Writing /root/2018-10-25_08-27-43/xtrabackup_info
181025 08: 27:47 [00] …done
xtrabackup: Transaction log of lsn (2605677) to (2605686) was copied. # has copied the transaction log of lsn (2605677) to (2605686).
181025 08:27:48 completed OK! #Completed!
A directory named after the current time will be generated in the current directory:
#This is the relevant backup file, and you can also see the name of the library we created
Use the following command to keep the relevant data files in a consistent state
[[ email protected] ~]# innobackupex –apply-log /root/2018-10-25_08-27-43/
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_db_undo-server=0. –redo-log-version=1
xtrabackup: recognized client arguments: –innodb_checksum_algorithm=crc32 –innodb_log_checksum_algorithm=strict_crc32 –innodb_data_file_path=ibdata1:12M:autoextend –innodb_log_files_in_fast=2 –innodb_log_files_in_fast=2 –innodb_checksum_files_in_fast=2 –innodb_checksum_algorithm=strict_crc32 =0 –innodb_page_size=16384 –innodb_log_block_size=512 –innodb_undo_directory =./ –innodb_undo_tablespaces=0 –server-id=1 –redo-log-version=1
181025 08:57:41 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.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
xtrabackup: cd to /root/2018-10-25_08-27-43/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2605677)
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 InbrnoDB configuration for recovery: p: 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
xtralogbackup: innodb_group = 1
xtralogbackup: innodb = 8 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 __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
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 2605677
InnoDB: Do ing recovery: scanned up to log sequence number 2605686 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
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: Waiting for purge to start
InnoDB: 5.7.19 started ; log sequence number 2605686
xtrabackup: Recovered WSREP position: bbe63ad7-d767-11e8-9ffc-073dc1824390:8
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown…
InnoDB: Shutdown completed; log sequence number 2605783
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .< br>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 = innodb_log_file_size =no 50331DB
available support MutexInnoDB
and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
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=2605783
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 2606092
InnoDB: Doing recovery: scanned up to log sequence number 2606101 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
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: Waiting for purge to start
InnoDB: 5.7.19 started; log sequence number 2606101
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting .
InnoDB: Starting shutdown…
InnoDB: Shutdown completed; log sequence number 2606120
181025 08:57:48 completed OK!
Restore
First delete the corresponding table
Note: It should be noted here that the library is not deleted, because an error will be reported when the library is deleted, because it can’t find the corresponding library, you need to create a new library with the corresponding name and then restore it< br>mysql -uroot -p
mysql> drop table opop.user;
mysql> select * from opop.user;
ERROR 1146 (42S02): Table’opop.user’ doesn’t exist< br>mysql> use opop;
mysql> show tables;
mysql> exit
[[emailprotected] ~]# innobackupex –defaults-file=/etc/my.cnf –copy-back /root/2018-10-25_08-27-43/
< br>
Note: Remember to restart here and be in the Percona XtraDB cluster The way of starting and restarting the master node and other nodes is different, because this document is a continuation of the previous document
Log in to the database to check the recovery status
Xtrabackup incremental backup and recovery
Incremental backup can only be applied to InooDB or XtraDB tables. For M For the yISAM table, the increment is the same as the full backup. On the basis of the above, continue to create an opop2 library and then build a table in the opop-2 library and insert data
mysql> create database opop2 charset=’utf8mb4′;
mysql> use opop2;
mysql> create table user2(id int unsigned primary key auto_increment,name varchar(30));
mysql> insert into user2(name) values(‘zhangsan’),( ‘lisi’),(‘wangwu’),(‘maliu’);
mysql> commit;
mysql> select from user2;
Backup start
Omit the middle part …………
#–incremental /backup/ Specify increase The directory for the backup of the volume backup file
#–incremental-basedir Specify the directory for the last full or incremental backup
Delete a piece of data to test incremental recovery (here, delete the incremental backup data)
mysql -uroot -p
mysql> use opop2;
mysql> show tables ;
mysql> desc user2;
mysql> delete from user2 where name=’lisi’;
mysql> select from user2; #Confirm that the lisi user has been deleted
mysql> exit< br>The process of incremental recovery is as follows
[[emailprotected] ~]# innobackupex –apply-log –redo-only /root/2018-10-25_08-27-43/ #The first global backup Data Directory
[[email protected] ~]# innobackupex –apply-log –redo-only /root/2018-10-25_08-27-43/ –incremental-dir=/root/2018-10- 25_11-00-25/ #The second incremental backup data directory
Note: This is equivalent to a combined action
Restore all data:
[[emailprotected] ~]# innobackupex –defaults -file=/etc/my.cnf –copy-back /root/2018-10-25_08-27-43/
[[emailprotected] data]# systemctl restart [emailprotected]
#Because this experiment is a Percona XtraDB Cluster cluster, so use this command, if it is not a Percona XtraDB Cluster cluster, On centos, use systemctl or service to start or restart
[[emailprotected] data]# mysql -uroot -p
