Database xtrabackup scheduled hot backup production database

Posted by matjaz on Sun, 02 Jan 2022 15:36:28 +0100

1. Introduction to xtrabackup

  • MySQL cold standby, mysqldump and MySQL hot copy cannot realize incremental backup of the database. In the actual production environment, incremental backup is very practical. If the data is greater than 50G or 100G and the storage space is sufficient, full backup can be carried out every day. If the amount of data generated every day is large, the data backup strategy needs to be customized. For example, weekly full backup and Monday to Saturday incremental backup are available. Percona xtrabackup is a mainstream backup tool for incremental backup. xtrabakackup has two tools, xtrabakup and innobakupe.
  • Percona xtrabackup is a backup tool developed by percona company for physical hot backup of MySQL database. It supports mysql, Percona server and MariaDB. It is open source and free. It is a popular mainstream backup tool at present. Xtrabackup can only back up the tables of innoDB and xtraDB data engines, not MyISAM data tables.

2.Xtrabackup advantages

  • Fast backup speed and reliable physical backup
  • The backup process does not interrupt ongoing transactions (no table locking is required)
  • It can save disk space and traffic based on compression and other functions
  • Automatic backup verification
  • Fast restore speed
  • Backups can be transferred to another machine
  • Backup data without increasing server load

3. Create a backup dedicated user

If you want to use a user with the minimum permission for backup, you can create such a user based on the following command: if you want to use a user with the minimum permission for backup, you can create such a user based on the following command:

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456';　　#Create user
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser';　　#Reclaim all permissions for this user
mysql> GRANT RELOAD,LOCK TABLES,RELICATION CLIENT ON *.* TO 'bkpuser'@'localhost';　　#Authorization refresh, locking table, user viewing server status
mysql> FLUSH PRIVILEGES;　　#Refresh authorization table

4. Download and install xtrabackup

4.1 download xtrabackup

  • Because the server version is CentOS 6, the xtrabackup version cannot be too high. It is recommended to use version 2.4.7 here.

4.1.1libev

  • Download the dependent plug-in libev. The command is as follows:
wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm

4.1.2xtrabackup

  • Download xtrabackup in three steps. The commands are as follows

Note: mysql is required before installation. If wget command cannot be used, you can also download the file and manually upload it to the server

	wget 	https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.7-2.el6.x86_64.rpm
	wget 	https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/redhat/6/x86_64/percona-xtrabackup-24-debuginfo-2.4.7-2.el6.x86_64.rpm
	wget 	https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/redhat/6/x86_64/percona-xtrabackup-test-24-2.4.7-2.el6.x86_64.rpm

4.2 installation dependency

4.2.2 installing Perl DBD mysq

The command is as follows:

	yum install perl-DBD-MySQL

4.2.3 installing libev

The command is as follows:

	rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm

4.2.4 installing xtrabackup

The command is as follows:

	rpm -ivh percona-xtrabackup-24-debuginfo-2.4.7-2.el6.x86_64.rpm

4.2.5 installing xtrabackup

The command is as follows:

	rpm -ivh percona-xtrabackup-24-2.4.7-2.el6.x86_64.rpm

4.2.6 installing xtrabackup test

The command is as follows:

	rpm -ivh percona-xtrabackup-test-24-2.4.7-2.el6.x86_64.rpm

4.2.7 problems encountered

a. The following error occurs:

resolvent:

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
yum -y install rsync perl l perl-Digest-MD5

b. The following error occurs:

resolvent:
Xtrabackup test is used for testing. If it cannot be installed, it can be skipped without affecting normal use.

4.3 verification

After installation, enter the command, as shown in the following figure:

rpm -qa|grep xtrabackup

5. Full backup

5.1 backup preparation

  • Before backup, we need to confirm the mysql configuration file (usually in / etc/my.cnf) and the sock file address (usually in / tmp/mysql.sock)
    If you are not sure, you can use the command to search:
find / -name mysql.sock
find / -name my.cnf

5.2 create backup directory

mkdir /data/back_up
cd /data/back_up

5.3 full backup command

innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=xxx--password=xxx /data/back_up

Parameter introduction:

– defaults file: configuration file
– socket: socket file address
– user: data user name
– password: database password
The last / data / backup all is the backup directory
– databases specifies the database
– incremental create incremental backup
– incremental basedir specifies the directory containing the full backup
– incremental dir specifies the directory containing the incremental backup
–tmpdir=DIRECTORY
When – remote host or -- stream is specified, the temporary directory tmpdir specified in the MySQL configuration file is used as the directory for temporary storage of transaction logs by default
– apply log preprocesses the backup
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 still handles the inconsistent state at this time. The main function of "preparation" is to roll back uncommitted transactions and synchronize committed transactions to the data file, so as to make the data file in a consistent state.
– redo only does not roll back uncommitted transactions
– copy back restore backup directory

After the command is executed, the last few lines will display something like: "120407 09:01:04 innobackupex: completed OK!", Indicates that the backup was successful.

5.4 background execution command

nohup innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=xxx--password=xxx /data/backupall &


Using background execution generates a nohup Out is used to log.

5.5 result viewing

  • After execution, a folder of current time will be generated to save backup data:


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 trigger and database configuration information. These files will be saved to a directory named by time. While backing up, innobackupex also creates the following files in the backup directory:

1.xtrabackup_checkpoints – backup type (such as full or incremental), backup status (such as whether it has been prepared) and LSN (log serial number) range information:
Each InnoDB page (usually 16k in size) will contain a log serial number, i.e. 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.
2.xtrabackup_binlog_info – the binary log file currently used by the mysql server and the location of the binary log time at the moment of backup.
3.xtrabackup_binlog_pos_innodb – current position of binary log files and binary log files used for InnoDB or XtraDB tables.
4.xtrabackup_binary - executable file of xtrabackup used in backup;
5.backup-my.cnf - configuration option information used in the backup command:
When using innobackupex for backup, you can also use the – no timestamp option to prevent the command from automatically creating a directory named by time: in this way, the innobackupex command will create a BACKUP-DIR directory to store backup data.

At this point, the backup operation is over!

6. Backup and recovery

6.1 prepare a full backup

  • 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. The main function of "preparation" is to roll back uncommitted transactions and synchronize committed transactions to data files, so that the data files used are in a consistent state.

  • The – apply log option of innobackupex command can be used to realize the above functions, such as the following command:

innobackupex --apply-log /data/back_up

If the execution is correct, the last lines of information output are usually as follows:

120407 09:01:04 innobackupex: completed OK!

In the process of "preparation", innobackupex can also use the – user memory option to specify the size of memory it can use, which is 100M by default If enough memory space is available, more memory can be allocated to the prepare process to improve the speed of completing the backup.

6.2 recovering data from a full backup

Note: MySQL does not need to be started for recovery

  • The – copy back option of innobackupex command is used for the recovery operation, which performs the recovery process by copying all data related files to the mysql server DATADIR directory. Innobackupex via backup my CNF to get information about the DATADIR directory.
innobackupex --defaults-file=/etc/my.cnf --copy-back /data/back_up
  • Note: in the above command, / data/back_up is the backup directory, not the data directory. The data directory of the mysql database we backed up and restored must be consistent.
    After the command is executed, the last few lines will display something like: "120407 09:01:04 innobackupex: completed OK!", Indicates that the recovery was successful.

After the data is restored to the DATADIR directory, you also need to ensure that the owners and groups of all data files are the correct users, such as mysql. Otherwise, you need to modify the owners and groups of data files before starting mysqld. For example:

chown -R mysql.mysql /data/mysql_data/

At this point, the backup and recovery is over!

Summarize the three steps of full database backup and recovery:

  • innobackupex full backup and specify the backup directory path;
  • Before recovery, you need to use the – apply log parameter to merge data files to ensure data consistency requirements;
  • When recovering, directly use the – copy back parameter. Note that in my The path of the data file directory to be specified in CNF.

7. Actual combat

The specific operation commands are as follows:

7.1 backup

[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/　　#stay master Full library backup on#Syntax explanation:
#--user=root specifies the backup user
#--password=123456 specifies the backup user password
#--Host specifies the host
#/backups specifies the backup directory
[root@master backups]# ll
total 0
drwxr-x--- 7 root root 232 Jul 30 11:01 2018-07-30_11-01-37
[root@master backups]# ll 2018-07-30_11-01-37/　　#View backup data
total 77856
-rw-r----- 1 root root      418 Jul 30 11:01 backup-my.cnf　　#Backup the configuration option information file used
-rw-r----- 1 root root 79691776 Jul 30 11:01 ibdata1　　#data file
drwxr-x--- 2 root root       20 Jul 30 11:01 kim
drwxr-x--- 2 root root     4096 Jul 30 11:01 mysql
drwxr-x--- 2 root root     4096 Jul 30 11:01 performance_schema
drwxr-x--- 2 root root       20 Jul 30 11:01 repppp
drwxr-x--- 2 root root     4096 Jul 30 11:01 wordpress
-rw-r----- 1 root root       21 Jul 30 11:01 xtrabackup_binlog_info　　#The binary log file currently used by the mysql server and the location information file of the binary log time at this time
-rw-r----- 1 root root      113 Jul 30 11:01 xtrabackup_checkpoints　　#Backup type, status, and LSN status information file
-rw-r----- 1 root root      482 Jul 30 11:01 xtrabackup_info
-rw-r----- 1 root root     2560 Jul 30 11:01 xtrabackup_logfile　　　　#Log files backed up

7.1 recovery

[root@slave ~]# /etc/init.d/mysqld stop　　#Stop mysql on slave
Shutting down MySQL.. SUCCESS! 

[root@slave tools]# yum install -y percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm 　　#Installing xtrabackup
[root@master backups]# scp -r 2018-07-30_11-01-37/ root@192.168.56.12:/backups/　　 #Copy backup data from master
[root@slave tools]# innobackupex --apply-log /backups/2018-07-30_11-01-37/　　　　　 #Merge data so that the data file is in a consistent state
180729 23:18:23 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.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: cd to /backups/2018-07-30_11-01-37/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(3127097)
......
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3129915
180729 23:18:30 completed OK!
[root@slave ~]# rm -rf /usr/local/mysql/data/　　#Delete the original data on the slave
[root@slave ~]# vim /etc/my.cnf　　#Configure my CNF's data directory path, otherwise an error will be reported, which should be consistent with the master
datadir=/usr/local/mysql/data
[root@slave ~]# innobackupex --copy-back /backups/2018-07-30_11-01-37/　　#Data recovery on slave
180729 23:32:03 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".
......
180729 23:32:08 completed OK!　　#When you see completed OK, you are back to normal
[root@slave ~]# ll /usr/local/mysql/data/　　#Viewing the data directory on the slave, you can see that the data has been recovered, but the owner will have problems and need to be modified. Therefore, the running user of mysql is generally used for recovery, otherwise the owner and group information need to be modified
total 188432
-rw-r----- 1 root root 79691776 Jul 29 23:32 ibdata1
-rw-r----- 1 root root 50331648 Jul 29 23:32 ib_logfile0
-rw-r----- 1 root root 50331648 Jul 29 23:32 ib_logfile1
-rw-r----- 1 root root 12582912 Jul 29 23:32 ibtmp1
drwxr-x--- 2 root root       20 Jul 29 23:32 kim
drwxr-x--- 2 root root     4096 Jul 29 23:32 mysql
drwxr-x--- 2 root root     4096 Jul 29 23:32 performance_schema
drwxr-x--- 2 root root       20 Jul 29 23:32 repppp
drwxr-x--- 2 root root     4096 Jul 29 23:32 wordpress
-rw-r----- 1 root root      482 Jul 29 23:32 xtrabackup_info
[root@slave ~]# chown -R mysql.mysql /usr/local/mysql/data/　　#Modify primary group
[root@slave ~]# /etc/init.d/mysqld start　　#mysql start 
Starting MySQL. SUCCESS! 
[root@slave ~]# mysql -uroot -p -e "show databases;"　　#Check the data and whether to restore it
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kim                |
| mysql              |
| performance_schema |
| repppp             |
| wordpress          |
+--------------------+

8.linux script timing

8.1 creating scripts

Execute command:

[root@elcndc2xabx07t back_up]# vim mysql_bak.sh

Script content:

# !/bin/sh
DATE=`date +%Y%m%d_%H%M%S`
# TODO
cd /data/back_up
innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=password --stream=tar ----tmpdir=/data/tmp /data/back_up | gzip > /data/back_up/back_up_$DATE.tar.gz
find /data/back_up -name "*.tar.gz" -type f -mtime +7 -exec rm -rf {} \; > /dev/null 2>&1

8.2 grant Execution Authority

[root@elcndc2xabx07t back_up]# chmod -R 777 mysql_bak.sh

View after execution:

8.3 perform the test manually

[root@elcndc2xabx07t back_up]# ./mysql_bak.sh

8.4 scheduled task crontab

Add scheduled task: execute script at 3 o'clock.

[root@elcndc2xabx07t back_up]# crontab -e

View scheduled tasks:

[root@elcndc2xabx07t back_up]# crontab -l

Topics: MySQL

Hot Topics

©2024 Programmer Think