5.3 xtrabackup backup tool
5.3.1 introduction to xtrabackup tool
Percona:
Official website: www.percona.com com
percona-server
InnoDB --> XtraDB
Xtrabackup backup tool:
percona Provided mysql Database backup tool, the only open source tool that can innodb and xtradb Tools for database hot standby Manual: https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html download: https://www.percona.com/downloads/
xtrabackup features:
- The backup and restore process is fast and reliable
- The backup process does not interrupt ongoing transactions
- It can save disk space and traffic based on compression and other functions
- Automatic backup verification
- Open source, free
xtrabackup tool file composition:
Xtrabackup2. Before version 2, there were 4 executable files:
-
innobackupex: Perl script
-
xtrabackup: C/C + +, compiled binary program
-
xbcrypt: encryption and decryption
-
xbstream: stream file format supporting concurrent writing
explain:
- xtrabackup is used to back up InnoDB tables. It cannot back up non InnoDB tables and has no interaction with MySQL Server
- innobackupex script is used to back up non InnoDB tables. At the same time, it will call xtrabackup command to back up InnoDB tables. It will also interact with commands sent by MySQL Server, such as adding global read lock (FTWRL), SHOW SLAVE STATUS, etc. That is, innobackup ex is implemented by a layer of encapsulation on top of xtrabackup
New changes to xtrabackup
xtrabackup Version upgrade to 2.4 After, compared with the previous 2.1 There have been great changes: innobackupex All functions are integrated into xtrabackup There's only one inside binary In addition, for compatibility, innobackupex As xtrabackup Soft links, i.e xtrabackup Now supports non Innodb Table backup, and Innobackupex Removed in the next version, recommended by xtrabackup replace innobackupex
Backup generated related files
When using innobackupex backup, it will call xtrabackup to back up all InnoDB tables, copy all relevant files (. frm) about table structure definition, MyISAM, MERGE, CSV and ARCHIVE tables, and back up files related to triggers and database configuration information. These files will be saved to a directory named by time. During backup, innobackupex will also create the following files in the backup directory:
-
xtrabackup_info: text file, information related to the execution of innobackupex tool, including version, backup options, backup duration, backup LSN(log sequence number), and BINLOG location
-
xtrabackup_checkpoints: text file, backup type (such as full or incremental), backup status (such as whether it has been prepared) and LSN range information. Each InnoDB page (usually 16k in size) will contain a log serial number LSN. LSN is the system version number of the whole database system. The LSN related to each page can indicate how this page has changed recently
-
xtrabackup_binlog_info: text file, the binary log file currently used by MySQL server and the location of binary log events up to the moment of backup, which can be used to realize binlog based recovery
-
backup-my.cnf: text file, the configuration option information used in the backup command
-
xtrabackup_logfile: backup the generated binary log file
#Example: related documents [root@centos8 ~]#ll /backup/ total 12340 -rw-r----- 1 root root 487 Jun 12 15:07 backup-my.cnf drwxr-x--- 2 root root 272 Jun 12 15:07 hellodb -rw-r----- 1 root root 425 Jun 12 15:07 ib_buffer_pool -rw-r----- 1 root root 12582912 Jun 12 15:07 ibdata1 drwxr-x--- 2 root root 4096 Jun 12 15:07 mysql drwxr-x--- 2 root root 8192 Jun 12 15:07 performance_schema drwxr-x--- 2 root root 8192 Jun 12 15:07 sys -rw-r----- 1 root root 25 Jun 12 15:07 xtrabackup_binlog_info -rw-r----- 1 root root 135 Jun 12 15:07 xtrabackup_checkpoints -rw-r----- 1 root root 479 Jun 12 15:07 xtrabackup_info -rw-r----- 1 root root 2560 Jun 12 15:07 xtrabackup_logfile [root@centos8 ~]#cat /backup/xtrabackup_info uuid = 55a26ea0-ac7b-11ea-a8ab-000c293f7395 name = tool_name = xtrabackup tool_command = -uroot -pmagedu --backup --target-dir=/backup/ tool_version = 2.4.20 ibbackup_version = 2.4.20 server_version = 5.7.29-log start_time = 2020-06-12 15:07:08 end_time = 2020-06-12 15:07:10 lock_time = 1 binlog_pos = filename 'centos8-bin.000002', position '10185' innodb_from_lsn = 0 innodb_to_lsn = 2687527 partial = N incremental = N format = file compact = N compressed = N encrypted = N [root@centos8 ~]#cat /backup/xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 2687527 last_lsn = 2687536 compact = 0 recover_binlog_info = 0 flushed_lsn = 2687536 [root@centos8 ~]#cat /backup/xtrabackup_binlog_info centos8-bin.000002 10185 [root@centos8 ~]#cat /backup/backup-my.cnf # This MySQL options file was generated by innobackupex. # The MySQL server [mysqld] 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=false innodb_page_size=16384 innodb_log_block_size=512 innodb_undo_directory=./ innodb_undo_tablespaces=0 server_id=1 redo_log_version=1 server_uuid=6fb9641a-ac79-11ea-8bed-000c293f7395 master_key_id=0 [root@centos8 ~]#file /backup/xtrabackup_logfile /backup/xtrabackup_logfile: data
5.3.2 xtrabackup installation
stay EPEL Source medium yum install percona-xtrabackup Download and install the latest version: https://www.percona.com/downloads/XtraBackup/LATEST/ [root@centos8 ~]#ll percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm -rw-r--r-- 1 root root 8045696 Oct 12 08:42 percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm [root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm [root@centos8 ~]#rpm -ql percona-xtrabackup-24-2.4.20 [root@centos8 ~]#ll /usr/bin/innobackupex [root@centos8 ~]#file /usr/bin/xtrabackup
5.3.3 xtrabackup usage
xtrabackup tool needs three steps to backup and restore:
- Backup: make a full or incremental backup of the database
- Pre preparation: sort the backup data into a temporary directory before restoring
- Restore: copy the sorted data back to the database directory
Backup:
innobackupex [option] BACKUP-ROOT-DIR Option Description: --user: #This option indicates the backup account --password: #This option indicates the password for the backup --host: #This option indicates the address of the backup database --databases: #The parameter accepted by this option is the database name. If you want to specify multiple databases, they need to be separated by spaces; For example:"xtra_test dba_test"，At the same time, when specifying a database, you can also specify only one table. For example:"mydatabase.mytable". This option is for innodb The engine table is invalid, or will all be backed up innodb surface --defaults-file: #This option specifies which file to read MySQL configuration from and must be placed at the first option on the command line --incremental: #This option means to create an incremental backup. You need to specify -- incremental basedir --incremental-basedir: #This option specifies the directory of the previous full backup or incremental backup. It is used together with -- incremental --incremental-dir: #This option indicates the directory of the incremental backup during restore --include=name: #Specify the table name, format: databasename tablename
get ready:
innobackupex --apply-log [option] BACKUP-DIR Option Description: --apply-log: #Generally, after the backup is completed, the data can not be used for recovery operation, because the backed up data may contain uncommitted transactions or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file is still in an inconsistent state at this time. This option is used to roll back uncommitted transactions and synchronize committed transactions to the data file to make the data file in a consistent state --use-memory: #Used together with the -- apply log option, when preparing a backup, the memory size allocated for crash recovery, in bytes, can also be 1MB,1M,1G,1GB, etc. 1G is recommended --export: #It means that you can export a separate table and then import it into other Mysql --redo-only: #This option is used when preparing base full backup to merge incremental backups, but does not include merging the last incremental backup
Restore:
innobackupex --copy-back [option] BACKUP-DIR innobackupex --move-back [option] [--defaults-group=GROUP-NAME] BACKUP-DIR Option Description: --copy-back: #During data recovery, copy the backup data file to the datadir of MySQL server --move-back: #This option is similar to -- copy back, except that it does not copy files, but moves files to their destination. This option removes the backup file. You must be careful when using it. Usage scenario: there is not enough disk space to keep data files and backup copies at the same time --force-non-empty-directories #When this parameter is specified, make innobackupex -- copy back or -- moveback options transfer files to non empty directories, and existing files will not be overwritten. If the -- copy back and -- move back files need to copy an existing file in datadir from the backup directory, an error will be reported and failure will be reported # Restore considerations: 1. datadir Directory must be empty. Unless specified innobackupex --force-non-empty-directorires Option specified, otherwise--copy-back Options are not overwritten 2. stay restore before,must shutdown MySQL Instance cannot be a running instance restore reach datadir In the directory 3. Since the file attributes will be retained, in most cases, the owner of the file needs to be changed to before starting the instance mysql，These files will belong to the user who created the backup, implement chown -R mysql:mysql /data/mysql,The above needs to be called by the user innobackupex Previously completed
5.3.5 installing mysql environment [binary installation package]
Environment: two centos8 3 initialize the system and install it by using script plus binary installation package. mysql version 5.7.29
#Preparation before script execution root@kvm-centos8-3-1 my_sql]#mkdir -pv /data/mysql [root@kvm-centos8-3-1 my_sql]# cd /root/my_sql/ [root@kvm-centos8-3-1 my_sql]# ls install_mysql5.7or8.0_for_centos.sh mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz [root@kvm-centos8-3-1 my_sql]# pwd /root/my_sql #Create a my in the root directory_ SQL directory, put the script and binary installation package into this directory! #Execute mysql to enter the database [root@kvm-centos8-3-1 my_sql]# mysql -uroot -p132117 mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory #If an error is reported, the software package libncurses needs to be installed* yum -y install libncurses* #Import test data into the library! mysql -p132117 < hellodb_innodb.sql mysql: [Warning] Using a password on the command line interface can be insecure.
Formal Script
#!/bin/bash
#
#********************************************************************
#Author: Don't forget your original heart yhj
#QQ: 81213672
#Date: 2021-05-27
#FileName: install_mysql5.7_for_centos.sh
#URL: https://blog.csdn.net/weixin_43305348
#Description: The test script
#Copyright (C): 2021 All rights reserved
#********************************************************************
#MySQL Download URL: https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
. /etc/init.d/functions
SRC_DIR=`pwd`
MYSQL='mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz'
COLOR='echo -e \E[01;31m'
END='\E[0m'
MYSQL_ROOT_PASSWORD=132117
check (){
if [ $UID -ne 0 ]; then
action "Current user is not root,Installation failed" false
exit 1
fi
cd $SRC_DIR
if [ ! -e $MYSQL ];then
$COLOR"lack ${MYSQL}file"$END
$COLOR"Please put the relevant software in ${SRC_DIR}Under the directory"$END
exit
elif [ -e /usr/local/mysql ];then
action "Database already exists, installation failed" false
exit
else
return
fi
}
install_mysql(){
$COLOR"Start installation MySQL database..."$END
yum -y -q install libaio numactl-libs libaio &> /dev/null
cd $SRC_DIR
tar xf $MYSQL -C /usr/local/
MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'`
ln -s /usr/local/$MYSQL_DIR /usr/local/mysql
chown -R root.root /usr/local/mysql/
id mysql &> /dev/null || { useradd -s /sbin/nologin -r mysql ; action "establish mysql user"; }
echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
ln -s /usr/local/mysql/bin/* /usr/bin/
cat > /etc/my.cnf <<-EOF
[mysqld]
server-id=1
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF
mysqld --initialize --user=mysql --datadir=/data/mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
service mysqld start
[ $? -ne 0 ] && { $COLOR"Database startup failed, exit!"$END;exit; }
MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`
mysqladmin -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null
action "Database installation completed"
}
chown -R mysql.mysql /data/mysql/
check
install_mysql
5.3.6 installing mysql environment [rpm installation package]
You can choose between binary and rpm installation package methods
Environmental Science:
Two centos8 3 initialize the system and install it using rpm installation package. mysql version 5.7.29
1) # Download the complete installation package of mysql-5.27rpm wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar #You will be prompted to download the package if it depends on the server, so you only need to install it here 2) #decompression tar -xvf mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar #Start installation yum -y install mysql-community-common-5.7.29-1.el7.x86_64.rpm yum -y install mysql-community-libs-5.7.29-1.el7.x86_64.rpm yum -y install mysql-community-client-5.7.29-1.el7.x86_64.rpm yum -y install mysql-community-server-5.7.29-1.el7.x86_64.rpm 3) #Installation is completed, start-up, initial verification #systemcctl start mysqld.service #be careful! When mysql is used to enter the database here, because mysql 5 Because of the security policy of version 7.29, there will be a default password! [root@kvm-centos8-3-1 mysql]# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [root@kvm-centos8-3-1 mysql]# mysql -uroot -hlocalhost ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) #The login fails all the time. Use the following command to check the initial password of mysql database first [root@kvm-centos8-3-1 mysql]# grep 'temporary password' /var/log/mysqld.log 2021-05-27T04:55:04.916018Z 1 [Note] A temporary password is generated for root@localhost: fXWYM.jGY1NQ # Then log in with the above password and successfully enter the database! [root@kvm-centos8-3-1 mysql]# mysql -uroot -pfXWYM.jGY1NQ mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.7.29 Copyright (c) 2000, 2020, 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. #After entering and checking the database, you will be prompted to change your password first. Moreover, due to the password restriction rules, you can't use too simple passwords! mysql> show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. # First change to complex password login mysql> alter user 'root'@'localhost' identified by '6mZg8hJ@EZ'; Query OK, 0 rows affected (0.00 sec) #View and modify the initial password policy # View the complete initial password rules of MySQL mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | MEDIUM | | validate_password_special_char_count | 1 | +--------------------------------------+--------+ 7 rows in set (0.00 sec) #The length of the password is determined by validate_password_length, but can be modified with the following command set global validate_password_length=6; #validate_password_policy determines the password authentication policy. The default level is medium, which can be modified to low through the following command set global validate_password_policy=0; #verification mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 6 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | LOW | | validate_password_special_char_count | 1 | +--------------------------------------+-------+ 7 rows in set (0.00 sec) #Change the password again mysql> alter user 'root'@'localhost' identified by '123456'; Query OK, 0 rows affected (0.00 sec)
5.3.4 full backup and restore with xtrabackup
Note: Currently, percona xtrabackup-24-2.4.18-1 el8. x86_ 64.rpm does not support mariadb-10.3 on CentOS 8
New xtrabackup full backup and restore
Download installation package: wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.20/binary/redhat/8/x86_64/percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm # This case is based on mysql5.0 of CentOS 8 7. It also supports MySQL 5 5 and mariadb5 five #Environment: centos8 3-1 and CentOS8-3-2 machines simulate backup on 1 and recovery on 2 1 install xtrabackup package #Install MySQL 5.0 first seven [root@kvm-centos8-3-1]# yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm [root@kvm-centos8-3-1 my_sql]# rpm -ql percona-xtrabackup-24 [root@kvm-centos8-3-1 my_sql]# ll /usr/bin/innobackupex lrwxrwxrwx 1 root root 10 Apr 20 2020 /usr/bin/innobackupex -> xtrabackup [root@kvm-centos8-3-1 my_sql]# file /usr/bin/xtrabackup /usr/bin/xtrabackup: ELF 64-bit LSB executable, x86-64, version 1 (GNU/Linux), dynamically linked, interpreter /lib64/ld-linux-x86-64.so.2, for GNU/Linux 3.2.0, BuildID[sha1]=ad45538c7f190bf3875f39562c7e33c39ef9425c, stripped 2 Make a full backup to the original host/data/backup [root@kvm-centos8-3-1 ~]# xtrabackup -uroot -p132117 --backup --target-dir=/data/backup/ #View backup data [root@kvm-centos8-3-1 backup]# cd /data/backup/ [root@kvm-centos8-3-1 backup]# ll total 12340 -rw-r----- 1 root root 487 May 27 00:45 backup-my.cnf drwxr-x--- 2 root root 272 May 27 00:45 hellodb -rw-r----- 1 root root 425 May 27 00:45 ib_buffer_pool -rw-r----- 1 root root 12582912 May 27 00:45 ibdata1 drwxr-x--- 2 root root 4096 May 27 00:45 mysql drwxr-x--- 2 root root 8192 May 27 00:45 performance_schema drwxr-x--- 2 root root 8192 May 27 00:45 sys -rw-r----- 1 root root 33 May 27 00:45 xtrabackup_binlog_info -rw-r----- 1 root root 135 May 27 00:45 xtrabackup_checkpoints -rw-r----- 1 root root 492 May 27 00:45 xtrabackup_info -rw-r----- 1 root root 2560 May 27 00:45 xtrabackup_logfile #View source and backup data sizes [root@kvm-centos8-3-1 backup]# du -sh /data/mysql/ 135M /data/mysql/ [root@kvm-centos8-3-1 backup]# du -sh /data/backup/ 27M /data/backup/ Preparation: #The target host does not need to create a / backup directory, but directly copies the directory itself [root@kvm-centos8-3-1 ]#scp -r /root/my_sql/backup 192.168.0.102:/root/my_sql/ 3 Restore on target host 1)Pre preparation: ensure data security,First back up the backup directory [root@kvm-centos8-3-2 my_sql]# cp -a backup/ /tmp/ #To ensure data consistency, commit completed transactions and roll back unfinished transactions [root@kvm-centos8-3-2 my_sql]# cd /tmp/backup/ [root@kvm-centos8-3-2 my_sql]#xtrabackup --prepare --target-dir=./ #After finishing the sorting, check the contents and size of the directory again [root@kvm-centos8-3-2 backup]# du -sh ./ 143M ./ [root@kvm-centos8-3-2 backup]# ll total 131128 -rw-r----- 1 root root 487 May 27 00:37 backup-my.cnf drwxr-x--- 2 root root 272 May 27 00:37 hellodb -rw-r----- 1 root root 425 May 27 00:37 ib_buffer_pool -rw-r----- 1 root root 12582912 May 27 00:44 ibdata1 -rw-r----- 1 root root 50331648 May 27 00:44 ib_logfile0 -rw-r----- 1 root root 50331648 May 27 00:44 ib_logfile1 -rw-r----- 1 root root 12582912 May 27 00:44 ibtmp1 drwxr-x--- 2 root root 4096 May 27 00:37 mysql drwxr-x--- 2 root root 8192 May 27 00:37 performance_schema drwxr-x--- 2 root root 8192 May 27 00:37 sys -rw-r----- 1 root root 33 May 27 00:37 xtrabackup_binlog_info -rw-r--r-- 1 root root 32 May 27 00:44 xtrabackup_binlog_pos_innodb -rw-r----- 1 root root 135 May 27 00:44 xtrabackup_checkpoints -rw-r----- 1 root root 492 May 27 00:37 xtrabackup_info -rw-r----- 1 root root 8388608 May 27 00:44 xtrabackup_logfile -rw-r--r-- 1 root root 1 May 27 00:44 xtrabackup_master_key_id 2)Copy to database directory #Note: the database directory must be empty and the MySQL service cannot be started #Stop database service [root@kvm-centos8-3-2 backup]# service mysqld stop Shutting down MySQL.. SUCCESS! #Clear original data [root@kvm-centos8-3-2 backup]# rm -rf /data/mysql/* [root@kvm-centos8-3-2 backup]# ll /data/mysql/ total 0 #Restore the sorted data to the database [root@kvm-centos8-3-2 backup]# xtrabackup --copy-back --target-dir=/tmp/backup/ [root@kvm-centos8-3-2 backup]# ll /data/mysql/ total 122924 drwxr-x--- 2 root root 272 May 27 00:55 hellodb -rw-r----- 1 root root 425 May 27 00:55 ib_buffer_pool -rw-r----- 1 root root 12582912 May 27 00:55 ibdata1 -rw-r----- 1 root root 50331648 May 27 00:55 ib_logfile0 -rw-r----- 1 root root 50331648 May 27 00:55 ib_logfile1 -rw-r----- 1 root root 12582912 May 27 00:55 ibtmp1 drwxr-x--- 2 root root 4096 May 27 00:55 mysql drwxr-x--- 2 root root 8192 May 27 00:55 performance_schema drwxr-x--- 2 root root 8192 May 27 00:55 sys -rw-r----- 1 root root 32 May 27 00:55 xtrabackup_binlog_pos_innodb -rw-r----- 1 root root 492 May 27 00:55 xtrabackup_info -rw-r----- 1 root root 1 May 27 00:55 xtrabackup_master_key_id [root@kvm-centos8-3-2 backup]# du -sh /data/mysql/ 135M /data/mysql/ 3)Restore properties [root@centos8 ~]#chown -R mysql:mysql /data/mysql 4)Start service [root@centos8 ~]#service mysqld start
New xtrabackup incremental backup and restore
1 Backup process
1)Full backup:
[root@centos8 ~]#mkdir /backup/
[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/
2)Modify data for the first time
#Add data, simulate incremental backup
# Add data to the database on 8-3-1;
mysql> use hellodb;
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
#insert data
mysql> insert teachers (name,age,gender)values('Li Xiang','30','M');
Query OK, 1 row affected (0.02 sec)
mysql> insert teachers (name,age,gender)values('Wei Lai','28','M');
Query OK, 1 row affected (0.01 sec)
mysql> insert teachers (name,age,gender)values('Meng Xiang','29','F');
Query OK, 1 row affected (0.01 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | Li Xiang | 30 | M |
| 6 | Wei Lai | 28 | M |
| 7 | Meng Xiang | 29 | F |
+-----+---------------+-----+--------+
7 rows in set (0.01 sec)
3)First incremental backup
#In order to simulate the real environment, make some minor adjustments to the previous backup directory
[root@kvm-centos8-3-1 my_sql]# mkdir base
[root@kvm-centos8-3-1 my_sql]# mv backup/* base/
[root@kvm-centos8-3-1 my_sql]# mv base/ backup/
[root@kvm-centos8-3-1 backup]# ll
total 0
drwxr-xr-x 6 root root 238 May 27 02:19 base
[root@kvm-centos8-3-1 backup]# pwd
/root/my_sql/backup
[root@kvm-centos8-3-1 backup]# ll base/
total 12340
-rw-r----- 1 root root 487 May 27 00:45 backup-my.cnf
drwxr-x--- 2 root root 272 May 27 00:45 hellodb
-rw-r----- 1 root root 425 May 27 00:45 ib_buffer_pool
-rw-r----- 1 root root 12582912 May 27 00:45 ibdata1
drwxr-x--- 2 root root 4096 May 27 00:45 mysql
drwxr-x--- 2 root root 8192 May 27 00:45 performance_schema
drwxr-x--- 2 root root 8192 May 27 00:45 sys
-rw-r----- 1 root root 33 May 27 00:45 xtrabackup_binlog_info
-rw-r----- 1 root root 135 May 27 00:45 xtrabackup_checkpoints
-rw-r----- 1 root root 492 May 27 00:45 xtrabackup_info
-rw-r----- 1 root root 2560 May 27 00:45 xtrabackup_logfile
[root@kvm-centos8-3-1 backup]# xtrabackup -uroot -p132117 --backup --target-dir=/root/my_sql/backup/inc1 --incremental-basedir=/root/my_sql/backup/
# --Target dir: Specifies the backup directory
#Incremental basedir: Specifies the path of the full backup
[root@kvm-centos8-3-1 backup]# pwd
/root/my_sql/backup
[root@kvm-centos8-3-1 backup]# ll
drwxr-xr-x 6 root root 238 May 27 02:19 base
drwxr-x--- 6 root root 264 May 27 02:23 inc1
[root@kvm-centos8-3-1 backup]# du -sh inc1/
3.5M inc1/
#View xtrabackup related files
[root@kvm-centos8-3-1 inc1]# cat xtrabackup_info
uuid = 06bfe13b-beb4-11eb-ae0b-5254008b229e
name =
tool_name = xtrabackup
tool_command = -uroot -p132117 --backup --target-dir=/root/my_sql/backup/inc1 --incremental-basedir=/root/my_sql/backup/base
tool_version = 2.4.20
ibbackup_version = 2.4.20
server_version = 5.7.29-log
start_time = 2021-05-27 02:23:14
end_time = 2021-05-27 02:23:18
lock_time = 3
binlog_pos = filename 'kvm-centos8-3-1-bin.000002', position '11032'
innodb_from_lsn = 2687801
innodb_to_lsn = 2694249
partial = N
incremental = Y
format = file
compact = N
compressed = N
encrypted = N
[root@kvm-centos8-3-1 inc1]# cat xtrabackup_checkpoints
backup_type = incremental #Incremental backup
from_lsn = 2687801
to_lsn = 2694249
last_lsn = 2694258
compact = 0
recover_binlog_info = 0
flushed_lsn = 2694258
[root@kvm-centos8-3-1 inc1]# cat xtrabackup_binlog_info
kvm-centos8-3-1-bin.000002 11032
4)Second modification of data
mysql> insert teachers (name,age,gender)values('Xiao Hu','25','M');
Query OK, 1 row affected (0.01 sec)
mysql> insert teachers (name,age,gender)values('Gala','23','M');
Query OK, 1 row affected (0.02 sec)
mysql> insert teachers (name,age,gender)values('Ming','23','M');
Query OK, 1 row affected (0.01 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | Li Xiang | 30 | M |
| 6 | Wei Lai | 28 | M |
| 7 | Meng Xiang | 29 | F |
| 8 | Xiao Hu | 25 | M |
| 9 | Gala | 23 | M |
| 10 | Ming | 23 | M |
+-----+---------------+-----+--------+
10 rows in set (0.00 sec)
5)Second increment
[root@kvm-centos8-3-1 backup]# xtrabackup -uroot -p132117 --backup --target-dir=/root/my_sql/backup/inc2 --incremental-basedir=/root/my_sql/backup/inc1
#--incremental-basedir=/root/my_sql/backup/inc1 here is the second increment, so it is based on the increment after the first one!
#View xtrabackup related files
[root@kvm-centos8-3-1 backup]# cd inc2/
[root@kvm-centos8-3-1 inc2]# cat xtrabackup_info
uuid = 58d2aec5-beb8-11eb-ae0b-5254008b229e
name =
tool_name = xtrabackup
tool_command = -uroot -p132117 --backup --target-dir=/root/my_sql/backup/inc2 --incremental-basedir=/root/my_sql/backup/inc1
tool_version = 2.4.20
ibbackup_version = 2.4.20
server_version = 5.7.29-log
start_time = 2021-05-27 02:54:10
end_time = 2021-05-27 02:54:13
lock_time = 2
binlog_pos = filename 'kvm-centos8-3-1-bin.000002', position '11869'
innodb_from_lsn = 2694249
innodb_to_lsn = 2700660
partial = N
incremental = Y
format = file
compact = N
compressed = N
encrypted = N
[root@kvm-centos8-3-1 inc2]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2694249
to_lsn = 2700660
last_lsn = 2700669
compact = 0
recover_binlog_info = 0
flushed_lsn = 2700669
[root@kvm-centos8-3-1 inc2]# cat xtrabackup_binlog_info
kvm-centos8-3-1-bin.000002 11869
#Copy the data to the recovery host [because the backup directory already exists, the directory is backup2]
6)[root@kvm-centos8-3-1 my_sql]# scp -r backup root@192.168.0.102:/root/my_sql/backup2
#The following operations are performed on the 8-3-2 machine!
#The backup process generates three backup directories
[root@kvm-centos8-3-2 my_sql]# ll backup2/
total 0
drwxr-xr-x 6 root root 238 May 27 02:30 base
drwxr-x--- 6 root root 264 May 27 02:30 inc1
drwxr-x--- 6 root root 264 May 27 02:30 inc2
#Copy data, organize and use
[root@kvm-centos8-3-2 my_sql]# cp -ra backup2/ /tmp/
[root@kvm-centos8-3-2 backup2]# pwd
/tmp/backup2
[root@kvm-centos8-3-2 backup2]# du -sh base/
27M base/
2 reduction process
1)Prepare to complete the backup, this option--apply-log-only Prevent rollback of incomplete transactions
[root@kvm-centos8-3-2 backup2]# xtrabackup --prepare --apply-log-only --target-dir=/tmp/backup2/base
[root@kvm-centos8-3-2 backup2]# du -sh base/
35M base/
2)Merge 1st incremental backup to full backup
[root@kvm-centos8-3-2 backup2]# xtrabackup --prepare --apply-log-only --target-dir=/tmp/backup2/base --incremental-dir=/tmp/backup2/inc1
[root@kvm-centos8-3-2 backup2]# du -sh inc1/
12M inc1/
[root@kvm-centos8-3-2 backup2]# du -sh /root/my_sql/backup2/inc1/
3.5M /root/my_sql/backup2/inc1/
3)Merge the second incremental backup to full backup: the last restore does not need to add options--apply-log-only
[root@kvm-centos8-3-2 backup2]# xtrabackup --prepare --target-dir=/tmp/backup2/base --incremental-dir=/tmp/backup2/inc2
[root@kvm-centos8-3-2 backup2]# du -sh base/
143M base/
#Stop the service and clear the original data
[root@kvm-centos8-3-2 backup2]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@kvm-centos8-3-2 backup2]# rm -rf /data/mysql/*
4)Copy to database directory
[root@kvm-centos8-3-2 backup2]# xtrabackup --copy-back --target-dir=/tmp/backup2/base
[root@kvm-centos8-3-2 backup2]# ll /data/mysql/
total 122924
drwxr-x--- 2 root root 272 May 27 02:47 hellodb
-rw-r----- 1 root root 425 May 27 02:47 ib_buffer_pool
-rw-r----- 1 root root 12582912 May 27 02:47 ibdata1
-rw-r----- 1 root root 50331648 May 27 02:47 ib_logfile0
-rw-r----- 1 root root 50331648 May 27 02:47 ib_logfile1
-rw-r----- 1 root root 12582912 May 27 02:47 ibtmp1
drwxr-x--- 2 root root 4096 May 27 02:47 mysql
drwxr-x--- 2 root root 8192 May 27 02:47 performance_schema
drwxr-x--- 2 root root 8192 May 27 02:47 sys
-rw-r----- 1 root root 33 May 27 02:47 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 556 May 27 02:47 xtrabackup_info
-rw-r----- 1 root root 1 May 27 02:47 xtrabackup_master_key_id
5)Restore properties:
[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql
[root@kvm-centos8-3-2 backup2]# ll /data/mysql/
total 122924
drwxr-x--- 2 mysql mysql 272 May 27 02:47 hellodb
-rw-r----- 1 mysql mysql 425 May 27 02:47 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 May 27 02:47 ibdata1
-rw-r----- 1 mysql mysql 50331648 May 27 02:47 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 May 27 02:47 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 May 27 02:47 ibtmp1
drwxr-x--- 2 mysql mysql 4096 May 27 02:47 mysql
drwxr-x--- 2 mysql mysql 8192 May 27 02:47 performance_schema
drwxr-x--- 2 mysql mysql 8192 May 27 02:47 sys
-rw-r----- 1 mysql mysql 33 May 27 02:47 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 556 May 27 02:47 xtrabackup_info
-rw-r----- 1 mysql mysql 1 May 27 02:47 xtrabackup_master_key_id
6)Start service:
[root@kvm-centos8-3-2 backup2]# service mysqld start
Starting MySQL.Logging to '/data/mysql/mysql.log'.
SUCCESS!
7)verification
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | Li Xiang | 30 | M |
| 6 | Wei Lai | 28 | M |
| 7 | Meng Xiang | 29 | F |
| 8 | Xiao Hu | 25 | M |
| 9 | Gala | 23 | M |
| 10 | Ming | 23 | M |
+-----+---------------+-----+--------+
10 rows in set (0.00 sec)
As mentioned above, Xtrabackup realizes the complete process of full and incremental backup of the database. In the actual working environment, if you need to realize automatic backup, you can cooperate with shell script and task plan according to your actual needs!