Shulou(Shulou.com)05/31 Report--

This article mainly introduces xtrabackup how to achieve MySQL automatic backup and recovery, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1. Brief introduction of xtrabackup principle 1.1 xtrabackup

XtraBackup (PXB) tool is a backup tool for physical hot backup of MySQL database developed by Percona in perl language, which supports MySQl (Oracle), Percona Server and MariaDB, and all open source, which is really the conscience of the industry. Ali's RDS MySQL physical backup is based on this tool. Because the backup is done by physical copy, the speed is very fast, dozens of gigabytes of data can be completed in a few minutes, and it skillfully makes use of the mysql feature to achieve online hot backup, which does not have to close the database as before, and can directly complete the full backup and incremental backup of the entire library or part of the library. The new version of xtrabackup has been changed to cmake installation, which is a little different from before.

Version note: the version installed here is 2.4.6, and the deadlock will not be backed up after 2.3.3. If the database is mysql 5.7, 2.4.4 must be installed before it can be used. Of course, it will be downwards compatible.

Toolset: after the package is installed, the following executable files are available (version 2.4.6)

Bin/

├── innobackupex-> xtrabackup

├── xbcloud

├── xbcloud_osenv

├── xbcrypt

├── xbstream

└── xtrabackup

The most important of these are innobackupex, which is a perl script, and xtrabackup, which is a binary compiled by Candlestick +. Percona rewrote innobackupex in C language in version 2.3, innobackupex functions are all integrated into xtrabackup, there is only one binary, and for compatibility reasons, innobackupex is used as a soft link to xtrabackup. For secondary development, 2.3 frees itself from the burden of collaboration between the previous two processes and is architecturally better than the previous version. (after the release of Percona XtraBackup 2.3, the recommended backup method is to use xtrabackup scripts)

Xtrabackup is used to back up InnoDB tables, but cannot back up non-InnoDB tables and has no interaction with mysqld server; innobackupex scripts are used to back up non-InnoDB tables, call xtrabackup commands to back up InnoDB tables, and interact with mysqld server commands, such as FTWRL, SHOW SLAVE STATUS, and so on. To put it simply, innobackupex has a layer of encapsulation on top of xtrabackup.

In general, we want to back up the MyISAM table, although we may not use the MyISAM table ourselves, but the system table under the mysql library is MyISAM, so the backup is basically carried out through the innobackupex command; another reason is that we may need to save the site information.

Several other tools are relatively niche. Xbcrypt is used to encrypt and decrypt backup files; xbstream, similar to tar, is a stream file format implemented by Percona itself that supports concurrent writing; both are used for backup and decompression (if the backup uses encryption and concurrency). The xbcloud tool is used to download or upload all or part of xbstream files from the cloud to the cloud.

1.2 XtraBackup principle

Prior to version 2.3, the interaction and coordination between innobackupex and xtrabackup was achieved by controlling the creation and deletion of files. Version 2.3 integrates all innobackupex functions into xtrabackup, so there is no need for communication between them. The introduction here is based on the old architecture (version 2.2), but the principle is the same as 2.3, except for the difference in implementation.

The entire backup process is shown in the following figure:

1.3 PXB backup proc

1 > after innobackupex starts, it will first fork a process, start the xtrabackup process, and then wait for xtrabackup to back up the ibd data files.

2 > when xtrabackup backs up InnoDB-related data, there are two threads, one is the redo copy thread, which is responsible for copying redo files, and the other is the ibd copy thread, which is responsible for copying ibd files. There is only one redo copy thread, which starts before the ibd copy thread and ends after the ibd thread ends. After the xtrabackup process starts execution, start the redo copy thread to copy the redo logs sequentially from the latest checkpoint point, and then start the ibd data copy thread, where the innobackupex process has been waiting (waiting for the file to be created) during the xtrabackup copy ibd process.

3 > after the xtrabackup copy completes the idb, notify the innobackupex (by creating the file), while entering and waiting (the redo thread still continues to copy).

4 > after innobackupex receives the xtrabackup notification, execute FLUSH TABLES WITH READ LOCK (FTWRL) to obtain the consistency point, and then start backing up non-InnoDB files (including frm, MYD, MYI, CSV, opt, par, etc.). In the process of copying non-InnoDB files, because the database is global read-only, you should be especially careful if you back up the main database of the business. If there are more non-InnoDB tables (mainly MyISAM), the read-only time of the entire database will be longer, and this impact must be evaluated.

5 > when innobackupex has copied all non-InnoDB table files, notify xtrabackup (by deleting the file) and enter and wait (wait for another file to be created)

6 > xtrabackup stops the redo copy thread after receiving the non-InnoDB notification of the innobackupex backup, and then informs the innobackupex redo log that the copy is complete (by creating the file)

7 > after innobackupex receives the notification of the completion of the redo backup, it starts to unlock and execute UNLOCK TABLES

8 > finally, the innobackupex and xtrabackup processes finish the finishing work respectively, such as releasing resources, writing backup metadata information, and so on. Innobackupex waits for the xtrabackup child process to finish and then exits.

In the file copies described above, the backup process reads the data files directly through the operating system, only interacts with the database when executing the SQL command, and basically does not affect the operation of the database. When backing up non-InnoDB, there will be read-only for a period of time (if there is no MyISAM table, the read-only time is about a few seconds). When backing up InnoDB data files, it has no impact on the database at all, so it is a real hot standby.

The backup of InnoDB and non-InnoDB files is done by copying files, but the way is different, the former is done with page granularity (xtrabackup), the latter is cp or tar command (innobackupex), xtrabackup verifies the checksum value when reading each page to ensure that the data block is consistent, while innobackupex has already done flush (FTWRL) in the cp MyISAM file, and the files on disk are complete, so the final data files in the backup set are written completely.

1.4 incremental backup

PXB supports incremental backup, but you can only increment the InnoDB. InnoDB has a LSN number for each page, and the LSN is incremented globally. When the page is changed, the current LSN number is recorded. The larger the LSN in the page, the newer the current page (recently updated). Each backup records the LSN to which the current backup is backed up (in the xtrabackup_checkpoints file). Incremental backup only copies the page whose LSN is larger than the last backup, and skips less than the last backup. Each ibd file is finally backed up as an incremental delta file.

There is no incremental mechanism for MyISAM, and every incremental backup is a full copy.

The incremental backup process is the same as a full backup, except that it differs in the copy of the ibd file.

1.5 recovery process

If you look at the log of the restore backup set, you will find that it is very similar to when mysqld starts. In fact, the recovery of the backup set is similar to crash recover after mysqld crash.

The purpose of recovery is to restore the data in the backup set to a consistency point. The so-called consistency refers to the state of the data of each engine in the original database at a certain time point. For example, the data in MyISAM corresponds to 15:00 time point, and the data in InnoDB corresponds to 15:20. The data in this state is inconsistent. The consistency point corresponding to the PXB backup set is the point in time of the FTWRL at the time of backup, and the recovered data corresponds to the state of the original database FTWRL.

Because the database is read-only after the FTWRL, and the non-InnoDB data is copied with a global read lock, the non-InnoDB data itself corresponds to the FTWRL point in time. The copy of the ibd file of InnoDB is done before FTWRL, and the last update time point of different ibd files is different. The ibd file in this state cannot be used directly, but the redo log is copied continuously from the backup, and the final redo log point is obtained after holding the FTWRL, so the ibd data point after the redo application is also consistent with the FTWRL.

Therefore, the recovery process only involves the recovery of InnoDB files, and non-InnoDB data is immobile. After the backup and restore is complete, you can copy the data file to the corresponding directory and start it through mysqld.

Note: the above principles are extracted from Ali Cloud Database Kernel monthly report.

1.6 implementation details

1 > File permissions

Xtrabackup opens the data file of innodb in read-write mode, and then copies it. In fact, it will not modify this file. In other words, the user running xtrabackup must have read and write access to the data file of innodb. Why use rw mode? Isn't it good to use direct read mode? Because xtrabackup uses its built-in innodb library to open files, and innodb libraries open files when it is rw.

2 > adjust the operating system buffer

Because XtraBackup replicates large amounts of data from the file system, it uses posix_fadvise () as much as possible to tell OS not to cache read data because it will not be reused to improve performance. If a backup needs to cache a few gigabytes of data, it will put a lot of pressure on the virtual memory of OS, and other processes, such as mysqld, are likely to be swap, so the system will be greatly affected. Xtrabackup avoids this situation through posix_fadvise ():

Posix_fadvise (file,0,0,POSIX_FADV_DONTNEED)

Moreover, xtrabackup requires the operating system to do more pre-read optimizations on the source files:

Posix_fadvise (file,0,0,POSIX_FADV_SEQUENTIAL)

3 > copy data files

In the process of backing up innodb page, XtraBackup reads and writes 1MB data, 1MB/16KB=64 page at a time. This is not configurable. After reading the 1MB data, XtraBackup iterates through the 1MB data page by page, and uses the buf_page_is_corrupted () function of innodb to check whether the data on this page is normal. If the data is abnormal, reread the page, up to 10 times (Note: page in doublewrite buffer will skip this check).

When copying transactions log (redo log), the data of 512KB is read and written each time. It is also not configurable.

2. Xtrabackup installation

L rpm installation (download: https://pan.baidu.com/s/1sl4jByP)

L binary installation / unzip installation (download: https://pan.baidu.com/s/1c1Vyt3q)

L compile and install

1 > rpm installation

This installation method is relatively simple, just download the appropriate rpm installation package to install (note to install the appropriate dependency package as prompted). The required libev.so.4 () installation package: https://pan.baidu.com/s/1i4EZfwT

[root@orcl ~] # cat / etc/issue

Red Hat Enterprise Linux Server release 6.5 (Santiago)

[root@orcl ~] # uname-r

2.6.32-431.el6.x86_64

[root@orcl] # rpm-ivh libev-4.15-1.el6.rf.x86_64.rpm

Warning: libev-4.15-1.el6.rf.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY

Preparing... # [100%]

1:libev # # [100%]

# linux 6.5 also needs to install the following dependencies

# yum-y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

[root@orcl] # rpm-ivh percona-xtrabackup-24-2.4.6-2.el6.x86_64.rpm

Warning: percona-xtrabackup-24-2.4.6-2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

Preparing... # [100%]

1:percona-xtrabackup-24 # # [100%]

[root@orcl] # xtrabackup-- version

Xtrabackup version 2.4.6 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 8ec05b7)

2 > binary installation / decompression installation

This installation method is also very simple, there is no need to install dependencies, just extract the installation files, to facilitate the creation of soft connections

# tar zxvf percona-xtrabackup-2.4.6-Linux-x86_64.tar.gz # there are three directories under the decompressed directory: bin man percona-xtrabackup-2.4-test

# mv percona-xtrabackup-2.4.6-Linux-x86_64 / usr/local/xtrabackup

# ln-sf / usr/local/xtrabackup/bin/* / usr/bin/ # make a soft connection to the command

[root@centos6] # xtrabackup-- version

Xtrabackup version 2.4.6 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 8ec05b7)

3 > compile and install

This kind of installation method is troublesome, needs to install a lot of dependencies, and takes a lot of time, so I won't introduce it here.

3. Xtrabackup option

The xtrabackup tool has many parameters, which can be queried on the official website (xtrabackup parameter option | innobackupex parameter option). Some commonly used parameters of innobackupex are briefly introduced here.

3.1 innobackupex parameter options

-- defaults-file= [MY.CNF] / / specifies the configuration file: default options can only be read from a given file. And must be the first option on the command line; it must be a real file, it cannot be a symbolic link.

-- databases=# / / specifies the database and table to be backed up in the format:-- database= "db1 [.tb1] db2 [.tb2]" separated by spaces between multiple libraries. If this option is not specified, all databases will be backed up.

-- include=REGEXP / / specifies the databases and tables to back up in the form of regular expressions, in the format-- include=' ^ mydb [.] mytb', matching each table in each library one by one, so all libraries are created, but empty directories. -- include is passed to xtrabackup-- tables.

-- tables-file=FILE / / the argument to this option needs to be a file name, and each line in this file contains the full name of the table to be backed up in the format databasename.tablename. This option is passed to xtrabackup-- tables-file, and unlike the-- tables option, only the library of the table to be backed up is created.

Note: some backups (--include,-- tables-file,-- database) need to enable innodb_file_per_table.

-- compact / / create a compact backup, ignore all secondary index pages, and only back up data page; by rebuilding the index in-- apply-log-- rebuild-indexs.

-- compress / / this option instructs xtrabackup to compress the backed-up InnoDB data file and generate a * .qp file.

-- decompress / / extract the qp file. In order to extract the file, you must install the qpress tool. Percona XtraBackup does not automatically delete compressed files. In order to clean up the backup directory, users should manually delete * .qp files: find / data/backup-name "* .qp" | xargs rm.

-- no-timestamp / / specifies this option that backups will be stored directly in the BACKUP-DIR directory without creating a timestamp folder.

-- apply-log / / apply the xtrabackup_logfile transaction log file in BACKUP-DIR. In general, after the backup is complete, the data cannot be used for restore operations because the backed up data may contain transactions that have not yet been committed 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 role of "preparation" is to make the data file consistent by rolling back uncommitted transactions and synchronizing committed transactions to the data file.

-- use-memory=# / / this option accepts a character parameter (1m apply-log 1MB, 1MB magic 1GB, default 100m), used only with-- crash-recovery, which specifies the memory used for crash recovery (crash recovery) in case of prepare.

-- copy-back / / copy all files that were previously backed up to their original path. However, there cannot be any files or directories under the original path unless the-- force-non-empty-directories option is specified.

-- force-non-empty-directories / / specify this option to enable-- copy-back and-- move-back to copy files to a non-empty directory, that is, there can be other files in the original data directory, but there cannot be files with the same name as the restore files, otherwise the restore will fail.

-- rsync / / this option optimizes the transfer of local files (non-InnoDB). The rsync tool copies all non-InnoDB files at once instead of creating a separate cp for each file, which is very efficient when backing up and restoring many databases and tables. This option cannot be used with-- stream.

-- incremental / / this option tells xtrabackup to create an incremental backup instead of a full backup. It is passed to the xtrabackup child process. When this option is specified, you can set-- incremental-lsn or-- incremental-basedir. If neither of these options is specified,-- incremental-basedir is passed to the xtrabackup default value, which is the first timestamp backup directory of the underlying backup directory.

-- incremental-basedir=DIRECTORY / / this option accepts a string parameter that specifies the full backup directory of the base dataset for incremental backups. It is used with-- incremental.

-- incremental-dir=DIRECTORY / / this option accepts a string parameter that specifies the directory where the incremental backup will be combined with a full backup for a new full backup. It is used with the-- incremental option.

-- redo-only / / use this option when preparing basic full backups and merging all incremental backups (except for the last addition). It is passed directly to xtrabackup's xtrabackup-- apply-log-only option, which causes xtrabackup to skip the "undo" phase and only do "redo" operations. This is necessary if incremental backups are applied to this full set later. For more information, see the xtrabackup documentation.

-- parallel=NUMBER-OF-THREADS / / this option takes an integer argument that specifies the number of threads the xtrabackup child process applies to backing up files at the same time. Note that this option applies only at the file level, that is, if you have multiple .ibd files, they will be copied in parallel; if your tables are stored together in a tablespace file, it will not work.

3.2 xtrabackup parameter options

-- apply-log-only / / this option causes only the redo phase to be performed when preparing a backup (prepare), which is important for incremental backups.

4. Xtrabackup full backup recovery 4.1 full backup 4.1.1 backup preparation

# # create a user pxb for backup and recovery and grant permissions

Mysql > create user pxb@'localhost' identified by '123456'

Mysql > grant reload,process,lock tables,replication client on *. * to pxb@localhost

# these permissions can only be completed: full, incremental backup, restore

Generally, if you need partial backup, export table, import table, you also need: grant create tablespace on *. * to 'bkpuser'@'localhost'

If you also need to optimize the locks during the backup to prevent blocking of all DML, you also need to:

Grant process,super on *. * to 'bkpuser'@'localhost'

# # create a backup directory

[root@orcl ~] # mkdir-pv / data/pxb

Mkdir: created directory `/ data'

Mkdir: created directory `/ data/pxb'

4.1.2 full library backup

[root@orcl] # innobackupex-defaults-file=/etc/my.cnf-- user=pxb-password=123456-- socket=/app/mysql/tmp/mysql.sock / data/pxb/

180321 11:29:47 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!".

180321 11:29:47 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/app/mysql/tmp/mysql.sock' as' pxb' (using password: YES).

180321 11:29:47 version_check Connected to MySQL server

180321 11:29:47 version_check Executing a version check against the server...

180321 11:29:47 version_check Done.

180321 11:29:47 Connecting to MySQL server host: localhost, user: pxb, password: set, port: 3306, socket: / app/mysql/tmp/mysql.sock

Using server version 5.5.32-log

Innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 8ec05b7)

Xtrabackup: uses posix_fadvise ().

Xtrabackup: cd to / app/mysql-5.5.32/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:10M:autoextend

Xtrabackup: innodb_log_group_home_dir =. /

Xtrabackup: innodb_log_files_in_group = 2

Xtrabackup: innodb_log_file_size = 5242880

InnoDB: Number of pools: 1

180321 11:29:47 > > log scanned up to (1610330)

Xtrabackup: Generating a list of tablespaces

180321 11:29:48 [01] Copying. / ibdata1 to / data/pxb/2018-03-21 September 11-29-47/ibdata1

180321 11:29:48 [01]... done

180321 11:29:48 > > log scanned up to (1610330)

180321 11:29:49 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...

180321 11:29:49 Executing FLUSH TABLES WITH READ LOCK...

180321 11:29:49 Starting to backup non-InnoDB tables and files

.

180321 11:29:50 Executing UNLOCK TABLES

180321 11:29:50 All tables unlocked

180321 11:29:50 Backup created in directory'/ data/pxb/2018-03-21pm 11-29-47max'

MySQL binlog position: filename 'mysql-bin.000001', position' 366'

180321 11:29:50 [00] Writing backup-my.cnf

180321 11:29:50 [00]... done

180321 11:29:50 [00] Writing xtrabackup_info

180321 11:29:50 [00]... done

Xtrabackup: Transaction log of lsn (1610330) to (1610330) was copied.

180321 11:29:50 completed OK!

4.1.3 View the generated file

You can see the entire backup process: connect to the database, start copying redo log, copy innodb table files, lock tables, copy non-innodb table files, stop copying redo log, and unlock.

[root@orcl] # ls-lrht / data/pxb/2018-03-21 / 11-29-47 /

Total 19M

-rw-r- 1 root root 18m Mar 21 11:29 ibdata1

Drwxr-x--- 2 root root 4.0K Mar 21 11:29 mysql

Drwxr-x--- 2 root root 4.0K Mar 21 11:29 shaw_db

Drwxr-x--- 2 root root 4.0K Mar 21 11:29 performance_schema

-rw-r- 1 root root 21 Mar 21 11:29 xtrabackup_binlog_info

-rw-r- 1 root root 2.5K Mar 21 11:29 xtrabackup_logfile

-rw-r- 1 root root 113 Mar 21 11:29 xtrabackup_checkpoints

-rw-r- 1 root root 417 Mar 21 11:29 backup-my.cnf

-rw-r- 1 root root 530 Mar 21 11:29 xtrabackup_info

Among them, the database files are stored under mysql/, performance_schema/, shaw_db/.

Backup-my.cnf, configuration option information used by backup command

[root@orcl 2018-03-2111-29-47] # cat backup-my.cnf

# This MySQL options file was generated by innobackupex.

# The MySQL server

[mysqld]

Innodb_checksum_algorithm=innodb

Innodb_log_checksum_algorithm=innodb

Innodb_data_file_path=ibdata1:10M:autoextend

Innodb_log_files_in_group=2

Innodb_log_file_size=5242880

Innodb_fast_checksum=false

Innodb_page_size=16384

Innodb_log_block_size=512

Innodb_undo_directory=.

Innodb_undo_tablespaces=0

Server_id=3

Redo_log_version=0

Ib_buffer_pool, the hot data in buffer pool, when you set innodb_buffer_pool_dump_at_shutdown=1, when you close MySQL, the hot data in memory will be saved in the ib_buffer_pool file on disk, located in the data directory.

Ibdata1, backup of shared tablespace files

[root@orcl 2018-03-2111-29-47] # file ibdata1

Ibdata1: data

The binary log files currently in use by the xtrabackup_binlog_info,mysql server and the location of the binary log events up to the moment of backup

[root@orcl 2018-03-2111-29-47] # cat xtrabackup_binlog_info

Mysql-bin.000001 366

Xtrabackup_checkpoints, backup type (such as full or incremental), backup status (such as whether it is already prepared status), and LSN (log serial number) scope information

[root@orcl 2018-03-2111-29-47] # cat xtrabackup_checkpoints

Backup_type = full-backuped

From_lsn = 0

To_lsn = 1610330

Last_lsn = 1610330

Compact = 0

Recover_binlog_info = 0

Xtrabackup_info, record the basic backup information, uuid, backup command, backup time, binlog, LSN, and other encryption and compression information.

[root@orcl 2018-03-2111-29-47] # cat xtrabackup_info

Uuid = 1cbf36cc-2cb8-11e8-8495-000c29ee24c9

Name =

Tool_name = innobackupex

Tool_command =-defaults-file=/etc/my.cnf-- user=pxb-password=...-- socket=/app/mysql/tmp/mysql.sock / data/pxb/

Tool_version = 2.4.6

Ibbackup_version = 2.4.6

Server_version = 5.5.32-log

Start_time = 2018-03-21 11:29:47

End_time = 2018-03-21 11:29:50

Lock_time = 0

Binlog_pos = filename 'mysql-bin.000001', position' 366'

Innodb_from_lsn = 0

Innodb_to_lsn = 1610330

Partial = N

Incremental = N

Format = file

Compact = N

Compressed = N

Encrypted = N

Xtrabackup_logfile, the redo log file backed up.

[root@orcl 2018-03-2111-29-47] # file xtrabackup_logfile

Xtrabackup_logfile: data

4.2 full recovery

# # close the database and delete the data file

Mysql > create table shaw_db.t_zhongbak as select * from mysql.user;-data that is not backed up will be lost

[root@orcl ~] # / etc/init.d/mysqld stop

Shutting down MySQL.... [OK]

[root@orcl ~] # cd / app/mysql/

[root@orcl mysql] # mv data/ data_bak/

[root@orcl mysql] # mkdir data

4.2.1 Application Lo

Prepare (prepare) a full backup (two prepare, redo log generated during the first application of the backup, roll forward and rollback: transactions committed by replay in redo log, transactions not committed by rollback):-- apply-log (/ data/pxb/2018-03-21transactions 11-29-47 / is the backup directory, backup_type = full-prepared in the xtrabackup_checkpoints file after execution)

[root@orcl] # innobackupex-- apply-log / data/pxb/2018-03-2111-29-47 /

180321 11:51:10 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!".

.

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1610792

180321 11:51:14 completed OK!

# #-apply-log will call xtrabackup-- prepare twice, the first time to roll forward and rollback, and the second time to generate iblogfile [0 | 1]

4.2.2 restore full readiness

Directly copy all the files from prepare above to the datadir directory of mysqld (the configuration information in my.cnf will be read).

-- points for attention of copy--back

The directory for 1 > datadir must be empty, or use the-- force-non-empty-directories option

2 > mysqld must be closed. If the import is partially restored, it cannot be closed.

3 >-after the copy-back is completed, you need to modify the file permissions in the datadir directory: chown-R mysql:mysql / var/lib/mysql

# # perform recovery operation

[root@orcl] # innobackupex-defaults-file=/etc/my.cnf-copy-back-rsync / data/pxb/2018-03-21 11-29-47 /

180321 12:20:29 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!".

Innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 8ec05b7)

Error: datadir must be specified.

# # adding datadir Directory

[root@orcl ~] # vi / etc/my.cnf

Datadir = / app/mysql/data

[root@orcl] # innobackupex-defaults-file=/etc/my.cnf-copy-back-rsync / data/pxb/2018-03-21 11-29-47 /

180321 12:25:39 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!".

Innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 8ec05b7)

180321 12:25:39 [01] Copying ib_logfile0 to / app/mysql/data/ib_logfile0

180321 12:25:39 [01]... done

.

Copying. / performance_schema/events_waits_summary_by_thread_by_event_name.frm to / app/mysql/data/performance_schema/events_waits_summary_by_thread_by_event_name.frm

180321 12:25:41 [01]... done

180321 12:25:41 [01] Copying. / xtrabackup_info to / app/mysql/data/xtrabackup_info

180321 12:25:41 [01]... done

180321 12:25:41 completed OK!

# # change data/ directory permissions and start mysql

[root@orcl] # chown-R mysql. / app/mysql/data

# # starting mysql Service

[root@orcl ~] # / etc/init.d/mysqld start

Starting MySQL.. [OK]

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | shaw_db |

+-+

Mysql > use shaw_db

Database changed

Mysql > show tables

+-+

| | Tables_in_shaw_db |

+-+

| | t_user |

| | t_zhong |

+-+

2 rows in set (0.00 sec)

5. Full backup and recovery of xtrabackup

Before an incremental backup, a complete backup must be established. The first incremental backup is based on the full backup, the second incremental backup is based on the first incremental backup, and so on.

5.1 incremental backup 1

# # create a new table, insert write data, and then make an incremental backup

Mysql > create table shaw_db.t_shaw as select * from mysql.user

Mysql > create table shaw_db.t_zhongbak as select * from shaw_db.t_zhong

# # based on completeness: (/ data/pxb/2018-03-21: 12-32-32 /)

[root@orcl] # innobackupex-- defaults-file=/etc/my.cnf-- user=pxb-- password=123456-- socket=/app/mysql/tmp/mysql.sock / data/pxb/inc-- incremental-basedir=/data/pxb/2018-03-2111 12-32-32 /-- parallel=2

180321 12:47:17 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!".

180321 12:47:17 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/app/mysql/tmp/mysql.sock' as' pxb' (using password: YES).

.

180321 12:47:20 Executing UNLOCK TABLES

180321 12:47:20 All tables unlocked

180321 12:47:20 Backup created in directory'/ data/pxb/inc/2018-03-21 March 12-47-17 max'

MySQL binlog position: filename 'mysql-bin.000001', position' 350'

180321 12:47:20 [00] Writing backup-my.cnf

180321 12:47:20 [00]... done

180321 12:47:20 [00] Writing xtrabackup_info

180321 12:47:20 [00]... done

Xtrabackup: Transaction log of lsn (1631682) to (1631682) was copied.

180321 12:47:20 completed OK!

[root@orcl] # cat / data/pxb/inc/2018-03-21, 12-47-17/xtrabackup_checkpoints

Backup_type = incremental # # indicates that it is an incremental backup

From_lsn = 1615487

To_lsn = 1631682

Last_lsn = 1631682

Compact = 0

Recover_binlog_info = 0

5.2 incremental backup 2

# # create some new tables, insert write data, and then make incremental backups

Mysql > create table shaw_db.t_shaw2 as select * from mysql.user

Mysql > create table shaw_db.t_zhongbak2 as select * from shaw_db.t_zhong

# # based on increment 1: (/ data/pxb/inc/2018-03-21: 12-47-17 /)

[root@orcl] # innobackupex-- defaults-file=/etc/my.cnf-- user=pxb-- password=123456-- socket=/app/mysql/tmp/mysql.sock-- incremental / data/pxb/inc/-- incremental-basedir=/data/pxb/inc/2018-03-2111 12-47-17 /-- parallel=4

180321 12:54:18 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!".

180321 12:54:18 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/app/mysql/tmp/mysql.sock' as' pxb' (using password: YES).

180321 12:54:18 version_check Connected to MySQL server

180321 12:54:18 version_check Executing a version check against the server...

180321 12:54:18 version_check Done.

180321 12:54:18 Connecting to MySQL server host: localhost, user: pxb, password: set, port: 3306, socket: / app/mysql/tmp/mysql.sock

.

180321 12:54:22 Executing UNLOCK TABLES

180321 12:54:22 All tables unlocked

180321 12:54:22 Backup created in directory'/ data/pxb/inc/2018-03-21 January 12-54-18 max'

MySQL binlog position: filename 'mysql-bin.000001', position' 589'

180321 12:54:22 [00] Writing backup-my.cnf

180321 12:54:22 [00]... done

180321 12:54:22 [00] Writing xtrabackup_info

180321 12:54:22 [00]... done

Xtrabackup: Transaction log of lsn (1648889) to (1648889) was copied.

180321 12:54:22 completed OK!

[root@orcl] # cat / data/pxb/inc/2018-03-21, 12-54-18/xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 1631682

To_lsn = 1648889

Last_lsn = 1648889

Compact = 0

Recover_binlog_info = 0

5.3 recovery of incremental backup

The recovery of incremental backup requires three steps

1 > restore a full backup

2 > restore incremental backup to full backup (add-- redo-only parameter for the first incremental backup and remove-- redo-only for the last incremental backup)

3 > restore the overall full backup and roll back the uncommitted data

5.3.1 prepare a complete set

[root@orcl] # innobackupex-- apply-log-- redo-only / data/pxb/2018-03-2111 12-32-32 /

180321 14:21:11 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!".

.

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1615930

InnoDB: Number of pools: 1

180321 14:21:13 completed OK!

5.3.2 apply increment 1 to a full backup

[root@orcl] # innobackupex-- apply-log-- redo-only / data/pxb/2018-03-2111 12-32-32 /-incremental-dir=/data/pxb/inc/2018-03-21 12-47-17 /

180321 14:22: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!".

.

180321 14:22:44 [00]... done

180321 14:22:44 [00] Copying / data/pxb/inc/2018-03-21 billion 12-47-17//xtrabackup_info to. / xtrabackup_info

180321 14:22:44 [00]... done

180321 14:22:44 completed OK!

5.3.3 apply increment 2 to the full backup without adding the-redo-only parameter

[root@orcl] # innobackupex-- apply-log / data/pxb/2018-03-2111 12-32-32 /-- incremental-dir=/data/pxb/inc/2018-03-21 12-54-18 /

180321 14:24:29 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!".

5.3.4 perform an apply operation on all the combined full backups to roll back the uncommitted data

[root@orcl] # innobackupex-- apply-log / data/pxb/2018-03-2111 12-32-32 /

180321 14:26:11 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!".

.

InnoDB: 5.7.13 started; log sequence number 1615930

InnoDB: xtrabackup: Last MySQL binlog file position 589, file name. / mysql-bin.000001

Xtrabackup: error: The transaction log file is corrupted.

Xtrabackup: error: The log was not applied to the intended LSN!

Xtrabackup: Log applied to lsn 1615930

Xtrabackup: The intended lsn is 1648889

5.3.5 restore the database

# # Delete data directory first

[root@orcl ~] # rm-rf / app/mysql/data

[root@orcl ~] # mkdir / app/mysql/data

# # restoring Database

[root@orcl] # innobackupex-- defaults-file=/etc/my.cnf-- copy-back-- rsync / data/pxb/2018-03-2111 12-32-32 /

180321 14:31:56 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!".

.

180321 14:31:58 [01] Copying. / xtrabackup_binlog_pos_innodb to / app/mysql/data/xtrabackup_binlog_pos_innodb

180321 14:31:58 [01]... done

180321 14:31:58 completed OK!

[root@orcl] # chown-R mysql:mysql / app/mysql/data

[root@orcl ~] # / etc/init.d/mysqld start

Starting MySQL...The server quit without updating PID file [FAILED] sql/data/orcl.pid.

[root@orcl ~] # ps-ef | grep mysql

[root@orcl ~] # killall mysqld

[root@orcl ~] # / etc/init.d/mysqld start

Starting MySQL.. [OK]

Mysql > use shaw_db

Database changed

Mysql > show tables

+-+

| | Tables_in_shaw_db |

+-+

| | t_shaw |

| | t_shaw2 |

| | t_zhong |

| | t_zhongbak |

| | t_zhongbak2 |

+-+

6. Innobackupex option optimization, best practices

When backing up a non-innodb database, the entire database is locked with the: flush tables with read lock global lock. If there is a long query running in the database, the FTWRL is not available and will be blocked, blocking all DML operations. At this point, even if we kill the FTWRL global lock, we will not be able to recover from the blocking. In addition, after we successfully acquired the FTWRL global lock, the whole database is also locked in the process of copy non-transactional files. So we should make the FTWRL process as short as possible. When copy files of non-transaction engine data, the innodb transaction engine is blocked. Of course, it also blocks all other non-transactional engines.)

-- ftwrl-wait-timeout=60 prevents blocking

-- rsync reduces FTWRL time to shorten locking time for backing up non-transactional engine tables

-- parallel=4 open parallel

-- memory used during use-memory=4G crash recovery

-- lock-wait-timeout=60 this option means: when we are in FTWRL, if we have a long query, we can wait for 60s at most. If the long query is executed within 60 seconds, we can successfully execute FTWRL. If the query is not finished within 60 seconds, we will simply report an error and quit. Default value is 0

-- rsync uses this option to reduce the lock time for backing up non-transactional engine tables, which can be accelerated if you need to back up a large number of databases and tables.

-- parallel=# can be parallel in backup phase, compression / decompression phase, encryption / decryption phase and-- apply-log,--copy-back phase.

-- use-memory=# uses this option in the crash recovery phase, that is, the-- apply-log phase

7. Automatic backup script

# mkdir-p / data/pxb/baklog

7.1 script content

# backup.sh

#! / bin/sh

# on xtrabackup 2.2.8

# when it is executed for the first time, it will check whether there is a full backup, otherwise create a full library backup first

# when you run it again, it makes incremental backups based on previous full or incremental backups based on the settings in the script

# ocpyang@126.com

Commands for INNOBACKUPEX_PATH=innobackupex # INNOBACKUPEX

Command path of INNOBACKUPEXFULL=/usr/bin/$INNOBACKUPEX_PATH # INNOBACKUPEX

# mysql target server and username and password

MYSQL_CMD= "- host=192.168.12.55-user=root-password=111111-port=3306"

User name and password of MYSQL_UP= "--user=root-- password='111111'-- port=3306" # mysqladmin

TMPLOG= "/ data/pxb/innobackupex.$$.log"

Configuration file for MY_CNF=/etc/my.cnf # mysql

MYSQL=/app/mysql/bin/mysql

MYSQL_ADMIN=/app/mysql/bin/mysqladmin

Home directory of BACKUP_DIR=/data/pxb # backup

FULLBACKUP_DIR=$BACKUP_DIR/full # Directory of full library backup

Directory for INCRBACKUP_DIR=$BACKUP_DIR/incre # incremental backups

Interval period of FULLBACKUP_INTERVAL=86400 # full library backups, time: seconds

KEEP_FULLBACKUP=1 # keeps at least a few full library backups

Logfiledate= backup.`date +% Y% m% d% H% M`.txt

# start time

STARTED_TIME= `date +% s`

#

# display an error and exit

#

Error ()

{

Echo "$1" 1 > & 2

Exit 1

}

# check the execution environment

If [!-x $INNOBACKUPEXFULL]; then

Error "$INNOBACKUPEXFULL is not installed or linked to / usr/bin."

Fi

If [!-d $BACKUP_DIR]; then

Error "backup destination folder: $BACKUP_DIR does not exist."

Fi

Mysql_status= `netstat-nl | awk'NR > 2 {if ($4 ~ /. *: 3306 /) {print "Yes"; exit 0}}'`

If ["$mysql_status"! = "Yes"]; then

Error "MySQL is not up and running."

Fi

If! `MYSQL_ 'exit' | $MYSQL-s $MYSQL_ CMD`; then

Error "the database username or password provided is incorrect!"

Fi

# header information of backup

Echo "-"

Echo

Echo "$0: MySQL backup script"

Echo "starts with: `date +% F'% T'% w`"

Echo

# create directories for full and differential backups

Mkdir-p $FULLBACKUP_DIR

Mkdir-p $INCRBACKUP_DIR

# find the latest full backup

LATEST_FULL_BACKUP= `find $FULLBACKUP_DIR-mindepth 1-maxdepth 1-type d-printf "% P\ n" | sort-nr | head-1`

# find the latest backup time that was recently modified

LATEST_FULL_BACKUP_CREATED_TIME= `stat-c% Y $FULLBACKUP_DIR/$LATEST_FULL_ backUP`

# if full and effective incremental backup is performed, otherwise perform a full backup

If ["$LATEST_FULL_BACKUP"-a `expr $LATEST_FULL_BACKUP_CREATED_TIME + $FULLBACKUP_INTERVAL + 5`-ge $STARTED_TIME]; then

# if the latest full directory is not expired, create a new directory under the incremental backup directory with the latest full file name

Echo-e "full backup $LATEST_FULL_BACKUP has not expired and will be used as the incremental backup base directory name based on the $LATEST_FULL_BACKUP name"

Echo ""

NEW_INCRDIR=$INCRBACKUP_DIR/$LATEST_FULL_BACKUP

Mkdir-p $NEW_INCRDIR

# find out whether the latest incremental backup exists. Specify the path of a backup as the basis for incremental backup

LATEST_INCR_BACKUP= `find $NEW_INCRDIR-mindepth 1-maxdepth 1-type d-printf "% P\ n" | sort-nr | head-1`

If [! $LATEST_INCR_BACKUP]; then

INCRBASEDIR=$FULLBACKUP_DIR/$LATEST_FULL_BACKUP

Echo-e "incremental backups will use $INCRBASEDIR as the backup base directory"

Echo ""

Else

INCRBASEDIR=$INCRBACKUP_DIR/$ {LATEST_FULL_BACKUP} / ${LATEST_INCR_BACKUP}

Echo-e "incremental backups will use $INCRBASEDIR as the backup base directory"

Echo ""

Fi

Echo "uses $INCRBASEDIR as the base directory for this incremental backup."

$INNOBACKUPEXFULL-defaults-file=$MY_CNF-use-memory=4G $MYSQL_CMD-incremental $NEW_INCRDIR-incremental-basedir $INCRBASEDIR > $TMPLOG 2 > & 1

# keep a detailed log of a backup

Cat $TMPLOG > / data/pxb/baklog/$logfiledate

If [- z "`OKBY-1$ TMPLOG | grep 'completed tail' `"]; then

Echo "$INNOBACKUPEX command execution failed:"; echo

Echo-e "- $INNOBACKUPEX_PATH error -"

Cat $TMPLOG

Rm-f $TMPLOG

Exit 1

Fi

THISBACKUP= `awk-"/ Backup created in directory/ {split (\\ $0, p,\"'\ "); print p [2]}" $TMPLOG`

Rm-f $TMPLOG

Echo-n "Database successfully backed up to: $THISBACKUP"

Echo

# prompt the starting point of the backup file that should be retained

LATEST_FULL_BACKUP= `find $FULLBACKUP_DIR-mindepth 1-maxdepth 1-type d-printf "% P\ n" | sort-nr | head-1`

NEW_INCRDIR=$INCRBACKUP_DIR/$LATEST_FULL_BACKUP

LATEST_INCR_BACKUP= `find $NEW_INCRDIR-mindepth 1-maxdepth 1-type d-printf "% P\ n" | sort-nr | head-1`

RES_FULL_BACKUP=$ {FULLBACKUP_DIR} / ${LATEST_FULL_BACKUP}

RES_INCRE_BACKUP= `dirname ${INCRBACKUP_DIR} / ${LATEST_FULL_BACKUP} / ${LATEST_INCR_BACKUP} `

Echo

Echo-e'\ e [31m NOTE:----.\ e [m'# red

Echo-e "must keep a full copy of $KEEP_FULLBACKUP, that is, all incremental backups in the full ${RES_FULL_BACKUP} and ${RES_INCRE_BACKUP} directories."

Echo-e'\ e [31m NOTE:----.\ e [m'# red

Echo

Else

Echo "* *"

Echo-e "is performing a new full backup... just a moment, please."

Echo "* *"

$INNOBACKUPEXFULL-defaults-file=$MY_CNF-use-memory=4G $MYSQL_CMD $FULLBACKUP_DIR > $TMPLOG 2 > & 1

# keep a detailed log of a backup

Cat $TMPLOG > / data/pxb/baklog/$logfiledate

If [- z "`OKBY-1$ TMPLOG | grep 'completed tail' `"]; then

Echo "$INNOBACKUPEX command execution failed:"; echo

Echo-e "- $INNOBACKUPEX_PATH error -"

Cat $TMPLOG

Rm-f $TMPLOG

Exit 1

Fi

THISBACKUP= `awk-"/ Backup created in directory/ {split (\\ $0, p,\"'\ "); print p [2]}" $TMPLOG`

Rm-f $TMPLOG

Echo-n "Database successfully backed up to: $THISBACKUP"

Echo

# prompt the starting point of the backup file that should be retained

LATEST_FULL_BACKUP= `find $FULLBACKUP_DIR-mindepth 1-maxdepth 1-type d-printf "% P\ n" | sort-nr | head-1`

RES_FULL_BACKUP=$ {FULLBACKUP_DIR} / ${LATEST_FULL_BACKUP}

Echo

Echo-e'\ e [31m NOTE:----.\ e [m'# red

Echo-e "No incremental backup, you must keep $KEEP_FULLBACKUP complete, that is, full ${RES_FULL_BACKUP}."

Echo-e'\ e [31m NOTE:----.\ e [m'# red

Echo

Fi

7.2 Test script

# # first backup:

[root@orcl ~] # sh mysqlbackup.sh

-

Mysqlbackup.sh: MySQL backup script

It started at: 2018-03-21 16:11:21 3

* *

Performing a new full backup. Just a moment, please.

* *

The database was successfully backed up to: / data/pxb/full/2018-03-21, 16-11-21 /

NOTE:----.

No incremental backup, you must keep 1 complete copy / data/pxb/full/2018-03-21 / 16-11-21.

NOTE:----.

[root@orcl ~] # ls-lrht / data/pxb/full/

Total 4.0K

Drwxr-x--- 5 root root 4.0K Mar 21 16:11 2018-03-21 16-11-21

[root@orcl ~] # ls-lrht / data/pxb/incre/

Total 0

[root@orcl ~] # ls-lrht / data/pxb/baklog/

Total 20K

-rw-r--r-- 1 root root 18K Mar 2116: 11 backup.201803211611.txt

# # viewing backup logs

[root@orcl] # tail-4 / data/pxb/baklog/backup.201803211611.txt

180321 16:11:23 [00] Writing xtrabackup_info

180321 16:11:23 [00]... done

Xtrabackup: Transaction log of lsn (1615930) to (1615930) was copied.

180321 16:11:23 completed OK!

# # second backup

[root@orcl ~] # sh mysqlbackup.sh

-

Mysqlbackup.sh: MySQL backup script

It started at: 2018-03-21 16:15:11 3

Full backup 2018-03-21 16-11-21 has not expired and will be used as the incremental backup base directory name based on 2018-03-21 16-11-21 name

The incremental backup will use / data/pxb/full/2018-03-21 16-11-21 as the backup base directory

Use / data/pxb/full/2018-03-21 16-11-21 as the base directory for this incremental backup.

The database was successfully backed up to: / data/pxb/incre/2018-03-21cm 16-11-21b 2018-03-21b 16-15-11 /

NOTE:----.

You must keep 1 full, that is, all incremental backups in the / data/pxb/full/2018-03-21 16-11-21 and / data/pxb/incre/2018-03-21 16-11-21 directories.

NOTE:----.

# # third backup

[root@orcl ~] # sh mysqlbackup.sh

-

Mysqlbackup.sh: MySQL backup script

It started at: 2018-03-21 16:15:50 3

Full backup 2018-03-21 16-11-21 has not expired and will be used as the incremental backup base directory name based on 2018-03-21 16-11-21 name

The incremental backup will be based on / data/pxb/incre/2018-03-21cm 16-11-21max 2018-03-21cm 16-15-11.

Use / data/pxb/incre/2018-03-21 16-11-21 Universe 2018-03-21 16-15-11 as the base directory for this incremental backup.

The database was successfully backed up to: / data/pxb/incre/2018-03-21cm 16-11-21b 2018-03-21b 16-15-50 /

NOTE:----.

You must keep 1 full, that is, all incremental backups in the / data/pxb/full/2018-03-21 16-11-21 and / data/pxb/incre/2018-03-21 16-11-21 directories.

NOTE:----.

# # backup logs

[root@orcl ~] # ls-lrht / data/pxb/baklog/

Total 60K

-rw-r--r-- 1 root root 18K Mar 2116: 11 backup.201803211611.txt

-rw-r--r-- 1 root root 20K Mar 2116: 15 backup.201803211615.txt

-rw-r--r-- 1 root root 20K Mar 2116: 17 backup.201803211617.txt

7.3 resume testing

# # prepare data first

Mysql > use shaw_db

Mysql > create table t_zhong (id int,name varchar (20))

Mysql > insert into t_zhong values (100th century name')

# # then execute a backup script

[root@orcl ~] # sh mysqlbackup.sh

-

Mysqlbackup.sh: MySQL backup script

It started at: 2018-03-21 16:26:28 3

Full backup 2018-03-21 16-11-21 has not expired and will be used as the incremental backup base directory name based on 2018-03-21 16-11-21 name

The incremental backup will be based on / data/pxb/incre/2018-03-21cm 16-11-21max 2018-03-21cm 16-17-13.

Use / data/pxb/incre/2018-03-21cm 16-11-21 Universe 2018-03-21cm 16-17-13 as the base directory for this incremental backup.

The database was successfully backed up to: / data/pxb/incre/2018-03-21cm 16-11-21b 2018-03-21b 16-26-28 /

NOTE:----.

You must keep 1 full, that is, all incremental backups in the / data/pxb/full/2018-03-21 16-11-21 and / data/pxb/incre/2018-03-21 16-11-21 directories.

NOTE:----.

# # deleting database data directory

[root@orcl ~] # rm-rf / app/mysql/data/

[root@orcl ~] # mkdir / app/mysql/data/

[root@orcl ~] # killall mysql

[root@orcl ~] # killall mysqld

[root@orcl ~] # / etc/init.d/mysqld start

Starting MySQL.The server quit without updating PID file (/ [FAILED] l/data/orcl.pid).

# # prepare to restore the database

= > 1 be ready

[root@orcl] # innobackupex-- apply-log-- redo-only / data/pxb/full/2018-03-21 16-11-21 /

# apply incremental backup

[root@orcl] # ls-lrht / data/pxb/incre/2018-03-21 / 16-11-21 /

Total 16K

Drwxr-x--- 5 root root 4.0K Mar 21 16:15 2018-03-2111 16-15-11

Drwxr-x--- 5 root root 4.0K Mar 21 16:15 2018-03-2111 16-15-50

Drwxr-x--- 5 root root 4.0K Mar 21 16:17 2018-03-2111 16-17-13

Drwxr-x--- 5 root root 4.0K Mar 21 16:26 2018-03-2111 16-26-28

= > 2 apply incremental backup

[root@orcl] # innobackupex-- apply-log-- redo-only / data/pxb/full/2018-03-21 January 16-11-21 /-- incremental-dir=/data/pxb/incre/2018-03-21 March 16-11-21 Universe 2018-03-21 March 16-15-11 /

[root@orcl] # innobackupex-- apply-log-- redo-only / data/pxb/full/2018-03-21 January 16-11-21 /-- incremental-dir=/data/pxb/incre/2018-03-21 March 16-11-21 Universe 2018-03-21 March 16-15-50 /

[root@orcl] # innobackupex-- apply-log-- redo-only / data/pxb/full/2018-03-21 January 16-11-21 /-- incremental-dir=/data/pxb/incre/2018-03-21 March 16-11-21 Universe 2018-03-21 March 16-17-13 /

= > 3 apply the last incremental backup

[root@orcl] # innobackupex-- apply-log / data/pxb/full/2018-03-21mm 16-11-21 /-- incremental-dir=/data/pxb/incre/2018-03-21mm 16-11-21Univ 2018-03-21mm 16-17-13 /

= > 4 execute full application and roll back uncommitted transactions

[root@orcl] # innobackupex-- apply-log / data/pxb/full/2018-03-21 16-11-21 /

= > 5 restore the database

[root@orcl] # innobackupex-- defaults-file=/etc/my.cnf-- copy-back-- rsync / data/pxb/full/2018-03-21 16-11-21 /

# # start the database and check

[root@orcl] # chown-R mysql. / app/mysql/data/

[root@orcl ~] # / etc/init.d/mysqld start

Starting MySQL.. [OK]

Mysql > use shaw_db

Mysql > select * from t_zhong

+-+ +

| | id | name |

+-+ +

| | 100 | name |

+-+ +

8. Attached, automatic recovery script

# modify according to the situation

Xtrabackup automatic restore

*

Application scenarios:

*

1. The backup directory is the architecture of / backup/full and / backup/incre, with the former keeping complete and the latter saving incremental backups

two。 If the full directory is / backup/full/2015-04-08 / 15-14-33, then the full directory name will be 2015-04-08 / 15-14-33

As the directory name of the incremental backup under / backup/incre/. The original intention of this design is that as long as it is complete and does not expire,

The incremental backup after this full file does not expire.

3. When restoring, the script automatically finds the latest full and the latest fully named incremental backup directory, and the incremental backup

Apply the log to the full in order, and finally complete the restore.

*

Script

*

#! / bin/sh

#

# how to use:

#. / restore.sh / incremental backup parent directory

# ocpyang@126.com

# NOTE: make sure that the mysql service is stopped and the data and log directories are empty before starting the recovery, as shown in

# rm-rf / usr/local/mysql/innodb_data/*

# rm-rf / usr/local/mysql/data/*

# rm-rf / usr/local/mysql/mysql_logs/innodb_log/*

INNOBACKUPEX=innobackupex

INNOBACKUPEX_PATH=/usr/local/xtrabackup/bin/$INNOBACKUPEX

TMP_LOG= "/ var/log/restore.$$.log"

MY_CNF=/usr/local/mysql/my.cnf

BACKUP_DIR=/backup # your backup home directory

FULLBACKUP_DIR=$BACKUP_DIR/full # Directory of full library backup

Directory for INCRBACKUP_DIR=$BACKUP_DIR/incre # incremental backups

MEMORY=4096M # the number of memory limits used when restoring

ERRORLOG= `grep-I "^ log-error" $MY_CNF | cut-d =-f 2`

MYSQLD_SAFE=/usr/local/mysql/bin/mysqld_safe

MYSQL_PORT=3306

#

# display error

#

Error ()

{

Echo "$1" 1 > & 2

Exit 1

}

#

# check innobackupex error output

#

Check_innobackupex_fail ()

{

If [- z "`OKBY-2 $TMP_LOG | grep 'completed tail' `"]; then

Echo "$INNOBACKUPEX command execution failed:"; echo

Echo "- error output of $INNOBACKUPEX -"

Cat $TMP_LOG

# keep a detailed log of a backup

Logfiledate= restore.`date +% Y% m% d% H% M`.txt

Cat $TMP_LOG > / backup/$logfiledate

Rm-f $TMP_LOG

Exit 1

Fi

}

# option detection

If [!-x $INNOBACKUPEX_PATH]; then

Error "$INNOBACKUPEX_PATH does not exist in the specified path, please confirm that it is installed or verify that the link is correct."

Fi

If [!-d $BACKUP_DIR]; then

Error "backup directory $BACKUP_DIR does not exist."

Fi

If [$#! = 1]; then

Error "how to use: $0 uses the absolute path of the restore directory"

Fi

If [!-d $1]; then

Error "specified backup directory: $1 does not exist."

Fi

PORTNUM00= `netstat-lnt | grep ${MYSQL_PORT} | wc-l`

If [$PORTNUM00 = 1]

Then

Echo-e'\ e [31m NOTE:--.\ e [m'# red

Echo-e'\ e [31m mysql is running, please close mysql. \ e [m'# red

Echo-e'\ e [31m NOTE:--.\ e [m'# red

Exit 0

Fi

Input_value=$1

Intpu_res= `echo ${input_value%/*} `

# Some info output

Echo "-"

Echo

Echo "$0: MySQL restore script"

START_RESTORE_TIME= `date +% F'% T''% w`

Echo "Database restore begins with: $START_RESTORE_TIME"

Echo

# PARENT_DIR= `dirname ${intpu_res} `

PARENT_DIR=$ {intpu_res}

If [$PARENT_DIR = $FULLBACKUP_DIR]; then

FULLBACKUP=$ {intpu_res}

Echo "restore full backup: `FULLBACKUP` basename $FULBACKUP`"

Echo

Else

If [$PARENT_DIR = $INCRBACKUP_DIR]; then

FULL= `ls-t $FULLBACKUP_DIR | head-1`

FULLBACKUP=$FULLBACKUP_DIR/$FULL

If [!-d $FULLBACKUP]; then

Error "complete: $FULLBACKUP does not exist."

Fi

INCR= `ls-t $INCRBACKUP_DIR/$FULL/ | head-1`

Echo "restore will start at full $FULL and end with incremental $INCR."

Echo

Echo "Prepare: full backup set."

Echo "* *"

$INNOBACKUPEX_PATH-defaults-file=$MY_CNF-apply-log-redo-only-use-memory=$MEMORY $FULLBACKUP > $TMP_LOG 2 > & 1

Check_innobackupex_fail

# Prepare incremental backup set, that is, the incremental backup is applied to the full directory, from the old to the latest according to the incremental backup order

For i in `find $PARENT_DIR/$FULL-mindepth 1-maxdepth 1-type d-printf "% P\ n" | sort-n `

Do

# judge the latest and complete lsn

# check_full_file= `find $FULLBACKUP/-mindepth 1-maxdepth 1-type d-printf "% P\ n" | sort-nr | head-1`

Check_full_lastlsn=$FULLBACKUP/xtrabackup_checkpoints

Fetch_full_lastlsn= `grep-I "^ last_lsn" ${check_full_lastlsn} | cut-d =-f 2`

# determine the LSN of the first incremental backup in an incremental backup

Check_incre_file= `find $PARENT_DIR/$FULL-mindepth 1-maxdepth 1-type d-printf "% P\ n" | sort-n | head-1`

Check_incre_lastlsn=$PARENT_DIR/$FULL/$i/xtrabackup_checkpoints

Fetch_incre_lastlsn= `grep-I "^ last_lsn" ${check_incre_lastlsn} | cut-d =-f 2`

Echo "LSN:$ {fetch_full_lastlsn} of full backup"

Echo "LSN:$ {fetch_incre_lastlsn} of incremental backup"

If ["${fetch_incre_lastlsn}"-eq "${fetch_full_lastlsn}"]; then

Echo "* *"

Echo "LSN doesn't need prepare!"

Echo "* *"

Echo

Break

Else

Echo "Prepare: incremental backup set $I."

Echo "* *"

$INNOBACKUPEX_PATH-defaults-file=$MY_CNF-apply-log-redo-only-use-memory=$MEMORY $FULLBACKUP-incremental-dir=$PARENT_DIR/$FULL/$i > $TMP_LOG 2 > & 1

Check_innobackupex_fail

If [$INCR = $I]; then

Break

Fi

Fi

# judging LSN

Done

Else

Error "unknown backup type"

Fi

Fi

Echo "prepare: full set rolls back uncommitted transactions."

$INNOBACKUPEX_PATH-defaults-file=$MY_CNF-apply-log-use-memory=$MEMORY $FULLBACKUP > $TMP_LOG 2 > & 1

Check_innobackupex_fail

Echo "* *"

Echo "database restore... just a moment, please"

Echo "* *"

$INNOBACKUPEX_PATH-defaults-file=$MY_CNF-copy-back $FULLBACKUP > $TMP_LOG 2 > & 1

Check_innobackupex_fail

Rm-f $TMP_LOG

Echo "1. Congratulations, the restoration is successful!."

Echo "* *"

# modify directory permissions

Echo "modify permissions for the mysql directory."

Mysqlcnf= "/ usr/local/mysql/my.cnf"

Mysqldatadir= `grep-I "^ basedir" $mysqlcnf | cut-d =-f 2`

`echo 'chown-R mysql:mysql' ${mysqldatadir} `

Echo "2. Permission modified successfully!"

Echo "* *"

# start mysql automatically

INIT_NUM=1

If [!-x $MYSQLD_SAFE]; then

Echo "mysql installation startup file is not installed to $MYSQLD_SAFE or does not have execute permission"

Exit 1 # 0 means successful execution, 1 means unsuccessful execution

Else

Echo "start a service with native mysql port: $MYSQL_PORT"

$MYSQLD_SAFE-- defaults-file=$MY_CNF > / dev/null &

While [$INIT_NUM-le 6]

Do

PORTNUM= `netstat-lnt | grep ${MYSQL_PORT} | wc-l`

Echo "mysql is starting. Just a moment, please."

Sleep 5

If [$PORTNUM = 1]

Then

Echo "mysql * started successfully *"

Exit 0

Fi

INIT_NUM=$ (($INIT_NUM + 1))

Done

Echo-e "mysql failed to start or took too long to start. Please check the error log `echo 'cat' ${ERRORLOG} `"

Echo "* *"

Exit 0

Fi

END_RESTORE_TIME= `date +% F'% T''% w`

Echo "Database restore is completed at: $END_RESTORE_TIME"

Exit 0

*

Execution result:

*

The implementation results are as follows:

#. / restore.sh / backup/incre/

-

. / restore.sh: MySQL restore script

Database restore began at: 2015-04-08 15:17:14 3

The restore will start at 2015-04-08 15-14-33 and end with increment 2015-04-08 15-16-06.

Prepare: full backup set.

* *

LSN: 62974601 for full backup

LSN of incremental backup: 124278446

Prepare: incremental backup set 2015-04-08. 15-15-25.

* *

LSN: 124278446 for full backup

LSN of incremental backup: 185584722

Prepare: incremental backup set 2015-04-08. 15-16-06.

* *

Prepare: the full set rolls back uncommitted transactions.

* *

Database restore is in progress. Just a minute please

* *

1. Congratulations, the restoration is successful!.

* *

Modify the permissions of the mysql directory.

two。 Permission modified successfully!

* *

Start a service with a native mysql port of 3306

3.mysql is starting. Just a moment, please.

3.mysql is starting. Just a moment, please.

3.mysql is starting. Just a moment, please.

Mysql * started successfully *

Thank you for reading this article carefully. I hope the article "how to realize MySQL automatic backup and recovery by xtrabackup" shared by the editor will be helpful to everyone. At the same time, I also hope you can support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.