Database backup: Xtrabackup realizes full backup and incremental backup

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:

  1. Backup: make a full or incremental backup of the database
  2. Pre preparation: sort the backup data into a temporary directory before restoring
  3. 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!

Keywords: Database MySQL MariaDB backup

Added by paolo on Tue, 08 Feb 2022 17:33:59 +0200