Shulou(Shulou.com)06/01 Report--

Use Xtrabackup for MySQL backups:

I. installation

1. Introduction

Xtrabackup is a mysql database backup tool provided by percona. According to officials, it is the only open source tool in the world that can provide hot backup for innodb and xtradb databases. Features:

(1) the backup process is fast and reliable

(2) the backup process will not interrupt the transaction in progress.

(3) it can save disk space and traffic based on compression and other functions.

(4) automatic backup verification

(5) Fast reduction speed.

2. Installation

The latest version of the software is available from http://www.percona.com/software/percona-xtrabackup/. This article is based on the system of CentOS 6.5. therefore, you can download and install the corresponding version of the rpm package directly, and the process will not be demonstrated here.

[root@localhost ~] # wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm

[root@localhost] # yum install percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm

There are two main tools in Xtrabackup:

Xtrabackup: a tool for hot backup of data in innodb and xtradb tables. You cannot back up other types of tables or data table structures.

Innobackupex: a perl script that encapsulates xtrabackup and provides the ability to back up myisam tables.

Second, the realization of backup

1. Full backup

# innobackupex-user=DBUSER-password=DBUSERPASS / path/to/BACKUP-DIR/

1) create a MySQL account for the backup operation:

MariaDB [(none)] > grant reload,lock tables,replication client,create tablespace,super,process on *. * to 'backup'@'%' identified by' pancou'

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.00 sec)

2) create a backup file directory:

[root@localhost] # mkdir-p / hotbackup

Make a full backup of the database, and if you do a backup on the slave library, add the-- slave_info-safe-slave-backup parameter to get the binlog information of the replication master library. The default backup saves the binlog information of the current library to the xtrabackup_binlog_info file, and the-- slave_info parameter saves the binlog information of the replication master library to xtrabackup_slave_info. If you use backup to add replication, be careful to select the correct binlog information.

3) create a configuration file for innobackupex:

[root@localhost ~] # vim / usr/local/etc/my.cnf

[mysqld]

Datadir = "/ data/mysql/"

Innodb_data_home_dir = "/ data/mysql/"

Innodb_data_home_path = "ibdata1:10M:autoextend"

Innodb_log_group_home_dir = "/ data/mysql/"

Innodb_log_file_in_group = 2

Innodb_log_file_size = 1G

4) make a full backup:

[root@localhost] # innobackupex-- user=backup-- password=pancou / hotbackup/full

170427 17:00:51 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!".

170427 17:00:52 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as' backup' (using password: YES).

170427 17:00:52 version_check Connected to MySQL server

170427 17:00:52 version_check Executing a version check against the server...

170427 17:00:52 version_check Done.

170427 17:00:52 Connecting to MySQL server host: localhost, user: backup, password: set, port: not set, socket: not set

Using server version 10.1.22-MariaDB

Innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 6f7a799)

Xtrabackup: uses posix_fadvise ().

Xtrabackup: cd to / data/mysql

Xtrabackup: open files limit requested 0, set to 60000

Xtrabackup: using the following InnoDB configuration:

Xtrabackup: innodb_data_home_dir =.

Xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

Xtrabackup: innodb_log_group_home_dir =. /

.

.... .

170427 17:00:57 Executing UNLOCK TABLES

170427 17:00:57 All tables unlocked

170427 17:00:57 Backup created in directory'/ hotbackup/full/2017-04-27017-00-51max'

MySQL binlog position: filename 'mysql-bin.000007', position' 941, GTID of the last change'0-1-270'

170427 17:00:57 [00] Writing backup-my.cnf

170427 17:00:57 [00]... done

170427 17:00:57 [00] Writing xtrabackup_info

170427 17:00:57 [00]... done

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

170427 17:00:57 completed OK!

From the information, you will find that the backup was created at'/ hotbackup/full/2017-04-27017-00-51ax'.

[root@localhost] # innobackupex-- defaults-file=/etc/my.cnf-- user=backup-- password=pancou-- socket=/var/lib/mysql/mysql.sock / hotbackup/full

Other options:

-- no-timestamp. If you specify this option, the backup will be backed up directly in BACKUP-DIR, and no timestamp folder will be created.

-- default-file, which specifies the configuration file that is used to configure options for innobackupex.

[root@localhost full] # innobackupex-defaults-file=/etc/my.cnf-user=backup-password=pancou-socket=/var/lib/mysql/mysql.sock / hotbackup/full-no-timestamp

.

170427 17:14:43 Executing UNLOCK TABLES

170427 17:14:43 All tables unlocked

170427 17:14:43 Backup created in directory'/ hotbackup/full/'

MySQL binlog position: filename 'mysql-bin.000007', position' 941, GTID of the last change'0-1-270'

170427 17:14:43 [00] Writing backup-my.cnf

170427 17:14:43 [00]... done

170427 17:14:43 [00] Writing xtrabackup_info

170427 17:14:43 [00]... done

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

170427 17:14:43 completed OK!

When using innobakupex backup, it invokes xtrabackup to back up all InnoDB tables, copies all related files (.frm) about the table structure definition, and related files for MyISAM, MERGE, CSV, and ARCHIVE tables, as well as files related to triggers and database configuration information. These files are saved to a directory with time commands.

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 prepared status) and LSN (log serial number) scope information

Each InnoDB page (usually 16k in size) contains a log sequence number, LSN. LSN is the system version number of the entire database system, and the LSN associated with each page can indicate how the page has changed recently.

(2) xtrabackup_binlog_info-the location of the binary log files currently in use by the mysql server and the binary log events up to the moment of backup.

(3) xtrabackup_binlog_pos_innodb-the current position of the binary log file and the binary log file used for the InnoDB or XtraDB table.

(4) xtrabackup_binary-the executable file of xtrabackup used in backup

(5) backup-my.cnf-configuration option information used for backup commands

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 the backup data.

[root@localhost full] # ls / hotbackup/full/

Backup-my.cnf ib_logfile1 performance_schema testdb xtrabackup_checkpoints

Ibdata1 ibtmp1 sakila xtrabackup_binlog_info xtrabackup_info

Ib_logfile0 mysql test xtrabackup_binlog_pos_innodb xtrabackup_logfile

MariaDB [testdb] > drop table emp

Query OK, 0 rows affected (0.10 sec)

2. Restore a full backup

1) prepare (prepare) a full backup

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 function of "preparation" is to make the data file consistent by rolling back the uncommitted transaction and synchronizing the committed transaction to the data file.

The-- apply-log option of the innobakupex command can be used to achieve the above functionality. Such as the following command:

# innobackupex-- apply-log / path/to/BACKUP-DIR

If executed correctly, the last few lines of information output are usually as follows:

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

120407 9:01:36 InnoDB: Starting shutdown...

120407 9:01:40 InnoDB: Shutdown completed; log sequence number 92036620

120407 09:01:40 innobackupex: completed OK!

In the process of implementing preparation, innobackupex can also use the-- use-memory option to specify the amount of memory it can use, which is usually 100m by default. If enough memory is available, you can allocate more memory to the prepare process to improve its completion speed.

[root@localhost full] # innobackupex-- apply-log-- use-memory=4G / hotbackup/full/

.... .

InnoDB: 5.7.13 started; log sequence number 21843477

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 21843496

170427 17:23:19 completed OK!

2) formally restore data from a full backup

Note: MySQL should be closed before recovery

The-- copy-back option of the innobackupex command is used to perform the restore operation, which performs the restore process by copying all data-related files to the mysql server DATADIR directory. Innobackupex uses backup-my.cnf to get information about the DATADIR directory.

# innobackupex-- copy-back / path/to/BACKUP-DIR

If executed correctly, the last lines of the output information are usually as follows:

Innobackupex: Starting to copy InnoDB log files

Innobackupex: in'/ backup/2012-04-07 / 08-17-03'

Innobackupex: back to original InnoDB log directory'/ mydata/data'

Innobackupex: Finished copying back files.

120407 09:36:10 innobackupex: completed OK!

Make sure that "innobackupex: completed OK!" appears on the top line of the above information.

When the data is restored to the DATADIR directory, you also need to make sure that the owner and group of all data files are the correct users, such as mysql, otherwise, you need to modify the owner and group of the data file before starting mysqld. Such as:

# chown-R mysql:mysql / mydata/data/

[root@localhost ~] # service mysql stop

Shutting down MySQL... [OK]

[root@localhost ~] # mv / data/mysql/ / opt/mysql_bak

[root@localhost ~] # ls / data/

[root@localhost ~] # mkdir / data/mysql

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

[root@localhost ~] # ls / data/

[root@localhost] # innobackupex-- defaults-file=/usr/local/etc/my.cnf-- copy-back / hotbackup/full/

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

[root@localhost ~] # service mysql start

Starting MySQL.170427 17:35:13 mysqld_safe Logging to'/ data/mysql/localhost.err'.

170427 17:35:13 mysqld_safe Starting mysqld daemon with databases from / data/mysql

[OK]

MariaDB [testdb] > show tables

+-+

| | Tables_in_testdb |

+-+

| | emp |

+-+

1 row in set (0.00 sec)

3. Use innobackupex for incremental backup and recovery

Each InnoDB page contains a LSN message, and whenever the relevant data changes, the LSN of the related page will automatically grow. This is the basis on which InnoDB tables can be backed up incrementally, that is, innobackupex is achieved by backing up pages that have changed since the last full backup.

To achieve the first incremental backup, you can use the following command:

# innobackupex-incremental / backup-incremental-basedir=BASEDIR

Where BASEDIR refers to the directory where the full backup is located, and after the execution of this command, the innobackupex command creates a new time-named directory in the / backup directory to hold all incremental backup data. In addition, when you perform an incremental backup again after performing an incremental backup, its-- incremental-basedir should point to the directory where the last incremental backup is located.

It is important to note that incremental backups can only be applied to InnoDB or XtraDB tables, and for MyISAM tables, incremental backups are actually performed as full backups.

1), perform incremental backup

MariaDB [testdb] > insert into emp (name) values ('tom'), (' jerry')

Query OK, 2 rows affected (0.03 sec)

Records: 2 Duplicates: 0 Warnings: 0

MariaDB [testdb] > select * from emp

+-+ +

| | id | name |

+-+ +

| | 1 | Z1 |

| | 2 | z2 |

| | NULL | tom |

| | NULL | jerry |

+-+ +

4 rows in set (0.00 sec)

[root@localhost full] # innobackupex-user=backup-password=pancou-incremental / hotbackup/increment-1-incremental-basedir=/hotbackup/full/-no-timestamp

MariaDB [testdb] > create table test (id int (3), name char (6))

Query OK, 0 rows affected (0.22 sec)

MariaDB [testdb] > insert into test (name) values ('lucy'), (' jerry')

Query OK, 2 rows affected (0.04 sec)

Records: 2 Duplicates: 0 Warnings: 0

MariaDB [testdb] > select * from test

+-+ +

| | id | name |

+-+ +

| | NULL | lucy |

| | NULL | jerry |

+-+ +

2 rows in set (0.00 sec)

[root@localhost full] # innobackupex-user=backup-password=pancou-incremental / hotbackup/increment-2-incremental-basedir=/hotbackup/increment-1-no-timestamp

2) restore incremental backup

The recovery of an incremental backup generally requires three steps:

Restore basic backup (full)

Restore an incremental backup to a base backup (the-redo-only parameter is required for the incremental backup at the beginning of recovery, and the-redo-only parameter is removed from the last incremental backup)

Restore the overall underlying backup and roll back uncommitted data

There are some differences between prepare incremental backups and collating full backups, especially the following:

(1) transactions that have been committed need to be "replayed" on each backup (including full and individual incremental backups). After replay, all backup data will be merged into the full backup.

(2) "rollback" uncommitted transactions based on all backups.

Two parameters:

-- apply-log: indicates that only committed transaction logs in the XtraBackup log are applied, and uncommitted transaction data is rolled back.

-- redo-only: indicates that only committed transaction logs in the XtraBackup log are applied, and uncommitted transaction data is not rolled back

Therefore, the operation becomes:

# innobackupex-apply-log-redo-only BASE-DIR

Then execute:

# innobackupex-apply-log-redo-only BASE-DIR-incremental-dir=INCREMENTAL-DIR-1

Then comes the second increment:

# innobackupex-apply-log-redo-only BASE-DIR-incremental-dir=INCREMENTAL-DIR-2

Then there is the third increment (note that if it is the last incremental backup, remove the-redo-only parameter and roll back the data in the xtrabackup log that has not yet been committed):

# innobackupex-apply-log BASE-DIR-incremental-dir=INCREMENTAL-DIR-3

Perform an apply operation on all the combined basic backups to roll back the uncommitted data:

# innobackupex-apply-log-use-memory=4G BASE-DIR

Where BASE-DIR refers to the directory where the full backup is located, INCREMENTAL-DIR-1 refers to the directory of the first incremental backup, INCREMENTAL-DIR-2 refers to the directory of the second incremental backup, and so on, that is, if there are multiple incremental backups, do the same each time.

An example:

Complete:

[root@localhost] # innobackupex-defaults-file=/etc/my.cnf-user=backup-password=pancou-socket=/var/lib/mysql/mysql.sock / hotbackup/full-no-timestamp

.... .

170428 12:58:31 Executing UNLOCK TABLES

170428 12:58:31 All tables unlocked

170428 12:58:31 Backup created in directory'/ hotbackup/full/'

MySQL binlog position: filename 'mysql-bin.000001', position' 1818, GTID of the last change'0-1-9'

170428 12:58:31 [00] Writing backup-my.cnf

170428 12:58:31 [00]... done

170428 12:58:31 [00] Writing xtrabackup_info

170428 12:58:31 [00]... done

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

170428 12:58:31 completed OK!

First incremental backup:

MariaDB [testdb] > create table T3 (id int (3), name char (8))

Query OK, 0 rows affected (0.21 sec)

MariaDB [testdb] > insert into T3 values (1), (2)

Query OK, 2 rows affected (0.05sec)

Records: 2 Duplicates: 0 Warnings: 0

MariaDB [testdb] > select * from T3

+-+ +

| | id | name |

+-+ +

| | 1 | tom |

| | 2 | jerry |

+-+ +

2 rows in set (0.00 sec)

[root@localhost] # innobackupex-defaults-file=/etc/my.cnf-- user=backup-- password=pancou-- socket=/var/lib/mysql/mysql.sock-- incremental-basedir=/hotbackup/full-- incremental / hotbackup/increment-1-- no-timestamp

.... ....

170428 13:02:52 Executing UNLOCK TABLES

170428 13:02:52 All tables unlocked

170428 13:02:52 Backup created in directory'/ hotbackup/increment-1/'

MySQL binlog position: filename 'mysql-bin.000001', position' 2304, GTID of the last change'0-1-12'

170428 13:02:52 [00] Writing backup-my.cnf

170428 13:02:52 [00]... done

170428 13:02:52 [00] Writing xtrabackup_info

170428 13:02:52 [00]... done

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

170428 13:02:52 completed OK!

Second incremental backup:

MariaDB [testdb] > insert into T3 values (3MIT Linda`), (4MIT Linda`)

Query OK, 2 rows affected (0.04 sec)

Records: 2 Duplicates: 0 Warnings: 0

MariaDB [testdb] > select * from T3

+-+ +

| | id | name |

+-+ +

| | 1 | tom |

| | 2 | jerry |

| | 3 | lucy |

| | 4 | linda |

+-+ +

4 rows in set (0.00 sec)

[root@localhost] # innobackupex-defaults-file=/etc/my.cnf-- user=backup-- password=pancou-- socket=/var/lib/mysql/mysql.sock-- incremental-basedir=/hotbackup/increment-1-- incremental / hotbackup/increment-2-- no-timestamp

. .

170428 13:04:52 Executing UNLOCK TABLES

170428 13:04:52 All tables unlocked

170428 13:04:52 Backup created in directory'/ hotbackup/increment-2/'

MySQL binlog position: filename 'mysql-bin.000001', position' 2477, GTID of the last change'0-1-13'

170428 13:04:52 [00] Writing backup-my.cnf

170428 13:04:52 [00]... done

170428 13:04:52 [00] Writing xtrabackup_info

170428 13:04:52 [00]... done

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

170428 13:04:52 completed OK!

Third incremental backup:

MariaDB [testdb] > insert into T3 values (5meme Zaofeng'), (6meme Jinglinchong')

Query OK, 2 rows affected (0.05sec)

Records: 2 Duplicates: 0 Warnings: 0

MariaDB [testdb] > select * from T3

+-+ +

| | id | name |

+-+ +

| | 1 | tom |

| | 2 | jerry |

| | 3 | lucy |

| | 4 | linda |

| | 5 | xiaofeng |

| | 6 | linchong |

+-+ +

6 rows in set (0.00 sec)

[root@localhost] # innobackupex-defaults-file=/etc/my.cnf-- user=backup-- password=pancou-- socket=/var/lib/mysql/mysql.sock-- incremental-basedir=/hotbackup/increment-2-- incremental / hotbackup/increment-3-- no-timestamp

.... ....

170428 13:09:24 Executing UNLOCK TABLES

170428 13:09:24 All tables unlocked

170428 13:09:24 Backup created in directory'/ hotbackup/increment-3/'

MySQL binlog position: filename 'mysql-bin.000001', position' 2657, GTID of the last change'0-1-14'

170428 13:09:24 [00] Writing backup-my.cnf

170428 13:09:24 [00]... done

170428 13:09:24 [00] Writing xtrabackup_info

170428 13:09:24 [00]... done

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

170428 13:09:24 completed OK!

[root@localhost ~] # ls / hotbackup/

Full increment-1 increment-2 increment-3

Prepare for recovery:

[root@localhost] # innobackupex-- apply-log-- redo-only-- use-memory=4 / hotbackup/full/

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 21860430

InnoDB: Number of pools: 1

170428 12:38:26 completed OK!

[root@localhost] # innobackupex-apply-log-redo-only-use-memory=4 / hotbackup/full/-incremental-dir=/hotbackup/increment-1/

170428 13:11:12 [01] Copying / hotbackup/increment-1/testdb/test.frm to. / testdb/test.frm

170428 13:11:12 [01]... done

170428 13:11:12 [00] Copying / hotbackup/increment-1//xtrabackup_binlog_info to. / xtrabackup_binlog_info

170428 13:11:12 [00]... done

170428 13:11:12 [00] Copying / hotbackup/increment-1//xtrabackup_info to. / xtrabackup_info

170428 13:11:12 [00]... done

170428 13:11:12 completed OK!

[root@localhost] # innobackupex-apply-log-redo-only-use-memory=4 / hotbackup/full/-incremental-dir=/hotbackup/increment-2/

170428 13:11:22 [01] Copying / hotbackup/increment-2/testdb/db.opt to. / testdb/db.opt

170428 13:11:22 [01]... done

170428 13:11:22 [01] Copying / hotbackup/increment-2/testdb/t1.frm to. / testdb/t1.frm

170428 13:11:22 [01]... done

170428 13:11:22 [01] Copying / hotbackup/increment-2/testdb/test.frm to. / testdb/test.frm

170428 13:11:22 [01]... done

170428 13:11:22 [00] Copying / hotbackup/increment-2//xtrabackup_binlog_info to. / xtrabackup_binlog_info

170428 13:11:22 [00]... done

170428 13:11:22 [00] Copying / hotbackup/increment-2//xtrabackup_info to. / xtrabackup_info

170428 13:11:22 [00]... done

170428 13:11:22 completed OK!

[root@localhost] # innobackupex-- apply-log-- use-memory=4 / hotbackup/full/-- incremental-dir=/hotbackup/increment-3/

.... ....

InnoDB: Setting file'. / ibtmp1' size to 12 MB. Physically writing the file full; Please wait...

InnoDB: File'. / ibtmp1' size is now 12 MB.

InnoDB: 96 redo rollback segment (s) found. 1 redo rollback segment (s) are active.

InnoDB: 32 non-redo rollback segment (s) are active.

InnoDB: 5.7.13 started; log sequence number 21898773

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 21898885

170428 13:13:23 completed OK!

[root@localhost] # innobackupex-- apply-log-- use-memory=4 / hotbackup/full/

InnoDB: 96 redo rollback segment (s) found. 1 redo rollback segment (s) are active.

InnoDB: 32 non-redo rollback segment (s) are active.

InnoDB: 5.7.13 started; log sequence number 21898904

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 21898923

170428 13:15:33 completed OK!

[root@localhost ~] # service mysql stop

Shutting down MySQL.. [OK]

[root@localhost ~] # mv / data/mysql/ / tmp/data_bak

[root@localhost] # innobackupex-- defaults-file=/usr/local/etc/my.cnf-- copy-back / hotbackup/full/

. .

170428 13:16:55 [01] Copying. / testdb/test.ibd to / data/mysql/testdb/test.ibd

170428 13:16:55 [01]... done

170428 13:16:55 [01] Copying. / testdb/t1.ibd to / data/mysql/testdb/t1.ibd

170428 13:16:55 [01]... done

170428 13:16:55 [01] Copying. / testdb/t2.ibd to / data/mysql/testdb/t2.ibd

170428 13:16:55 [01]... done

170428 13:16:55 completed OK!

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

[root@localhost ~] # service mysql start

Starting MySQL.170428 12:21:59 mysqld_safe Logging to'/ data/mysql/localhost.err'.

170428 12:21:59 mysqld_safe Starting mysqld daemon with databases from / data/mysql

[OK]

MariaDB [testdb] > select * from T3

+-+ +

| | id | name |

+-+ +

| | 1 | tom |

| | 2 | jerry |

| | 3 | lucy |

| | 4 | linda |

| | 5 | xiaofeng |

| | 6 | linchong |

+-+ +

6 rows in set (0.00 sec)

4. Incomplete recovery

Find the location file xtrabackup_binlog_info of the binlog that records the end time of the backup, and view the name and location of the binglogd at the end time:

[root@localhost ~] # cat / hotbackup/increment-3/xtrabackup_binlog_info

Mysql-bin.000001 2657 0-1-14

View the binglog file and location of the current database:

MariaDB [(none)] > show master logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000001 | 313 |

+-+ +

1 row in set (0.00 sec)

Restore the database from the full standby, restore the full database, and then start from the location of the binlog at the end of the hot backup to the binlog before 14:00 at the time of misoperation:

Mysqlbinlog-- start-position='2657'-- stop-datetime='2017-05-10 13 stop-datetime='2017 59'/ data/mysql/mysql-bin.000001 | mysql- uroot-ppancou

Skip the point of misoperation at the point of failure and apply the log:

Mysqlbinlog-- stop-datetime='2016-05-10 1414 data/mysql/mysql-bin.000001 01purl 00' / data/mysql/mysql-bin.000001 | mysql- uroot-ppancou

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.