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

This test uses XtraBackup to back up the MySQL database

Version: XtraBackup2.4.5+MySQL5.7.16

Download address: https://www.percona.com/downloads/XtraBackup/

1. Install XtraBackup

This time, for convenience, use the decompressed version to install, directly decompress can be used.

In the process of using, you may encounter the problem of lack of perl dependency package. My method is to directly install all the perl in the image package, yum install-y perl*.

2. Introduction

XtraBackup mainly includes two backup tools, xtrabackup and innobackupex. Innobackupex encapsulates xtrabackup. This time, only innobackupex backup MySQL is introduced.

3. The operation method of full equipment

1) create a backup

# create a directory to store backup data [root@rhel7 ~] # mkdir / mysqlbackup# start backup [root@rhel7 ~] # innobackupex-- user=root-- password=123456 / mysqlbackup161213 12:27:13 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". 161213 12:27:13 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as' root' (using password: YES). Failed to connect to MySQL server: DBI connect ('; mysql_read_default_group=xtrabackup','root',...) Failed: Can't connect to local MySQL server through socket'/ var/lib/mysql/mysql.sock' (2) at-line 1314.161213 12:27:13 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: (null) Using server version 5.7.16innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (revision id: e41c0be) xtrabackup: uses posix_fadvise (). Xtrabackup: cd to / usr/local/mysql/dataxtrabackup: open files limit requested 0 Set to 1024xtrabackup: using the following InnoDB configuration:xtrabackup: innodb_data_home_dir = .xtrabackup: innodb_data_file_path = ibdata1:12M:autoextendxtrabackup: innodb_log_group_home_dir =. / xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 50331648InnoDB: Number of pools: 1161213 12:27:13 > > log scanned up to (2671643) xtrabackup: Generating a list of tablespacesInnoDB: Allocated tablespace ID 2 for mysql/plugin Old maximum was 0161213 12:27:13 [01] Copying. / ibdata1 to / mysqlbackup/2016-12-13 December 12-27-13/ibdata1161213 12:27:14 > > log scanned up to (2671643) 161213 12:27:15 [01]. Done161213 12:27:15 > > log scanned up to (2671643) 161213 12:27:15 [01] Copying. / mysql/plugin.ibd to / mysqlbackup/2016-12-13 12-27-13/mysql/plugin.ibd161213 12:27:15 [ 01]. Done.161213 12:27:16 [01] Copying. / wl/zx.ibd to / mysqlbackup/2016-12-13 December 12-27-13/wl/zx.ibd161213 12:27:16 [01]. Done161213 12:27:16 > > log scanned up to (2671643) 161213 12:27:16 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...161213 12:27:16 Executing FLUSH TABLES WITH READ LOCK...161213 12: 27:16 Starting to backup non-InnoDB tables and files161213 12:27:16 [01] Copying. / mysql/db.opt to / mysqlbackup/2016-12-13 12-27-13/mysql/db.opt161213 12:27:16 [01]. Done.161213 12:27:18 [01] Copying. / wl/wl.frm to / mysqlbackup/2016-12-13 12-27-13/wl/wl.frm161213 12:27:18 [01] ... done161213 12:27:18 [01] Copying. / wl/zx.frm to / mysqlbackup/2016-12-13 December 12-27-13/wl/zx.frm161213 12:27:18 [01]... done161213 12:27:18 Finished backing up non-InnoDB tables and files161213 12:27:18 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...xtrabackup: The latest check point (for incremental): '2671634'xtrabackup: Stopping log copying thread..161213 12:27:18 > log scanned up to (2671643) 161213 12:27:18 Executing UNLOCK TABLES161213 12:27:18 All tables unlocked161213 12:27:18 [00] Copying ib_buffer_pool to / mysqlbackup/2016-12-13 12-27-13/ib_buffer_pool161213 12:27:18 [00]. Done161213 12:27:18 Backup created in directory'/ mysqlbackup/2016-12-13 12-27-13 16 1213 12:27:18 [00] Writing backup-my.cnf161213 12:27: 18 [00]... done161213 12:27:18 [00] Writing xtrabackup_info161213 12:27:18 [00]... donextrabackup: Transaction log of lsn (2671634) to (2671643) was copied.161213 12:27:19 completed OKTV # generated a time directory [root@rhel7 ~] # ls-l / mysqlbackup/total 4drwxrMuk. 7 root root 4096 Dec 13 12:27 2016-12-13 12-27-13 [root@rhel7] # du-sm / mysqlbackup/*90 / mysqlbackup/2016-12-13 12-27-13

Note: innobackupex reads the [mysqld] and [xtrabackup] sections of the my.cnf file, or you can use-- defaults-file to specify the parameter file.

2) restore complete data

The backed-up data cannot be used directly, so you need to do prepare operation first (which can be understood as the process of applying log, transaction commit of commit, rollback of transactions without commit)

# use the-- apply-log application log, [root@rhel7 ~] # innobackupex-- apply-log / mysqlbackup/2016-12-13 apply-log 12-27-13 apply-log 12:36:24 innobackupex: Starting the apply-log operationIMPORTANT: 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.5 based on MySQL server 5.7.13 Linux (x86: 64) (revision id: e41c0be) xtrabackup: cd to / mysqlbackup/2016-12-13: 12-27-13/xtrabackup: This target seems to be not prepared yet.InnoDB: Number of pools: 1xtrabackup: xtrabackup_logfile detected: size=8388608 Start_lsn= (2671634) xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir = .xtrabackup: innodb_data_file_path = ibdata1:12M:autoextendxtrabackup: innodb_log_group_home_dir = .xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 8388608xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir = .xtrabackup: innodb_data_file_path = ibdata1:12M:autoextendxtrabackup: Innodb_log_group_home_dir = .xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 8388608xtrabackup: Starting InnoDB instance for recovery.xtrabackup: Using 104857600 bytes for buffer pool (set by-- use-memory parameter) InnoDB: PUNCH HOLE support not availableInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Uses event mutexesInnoDB: GCC builtin _ sync_synchronize () is used for memory barrierInnoDB: Compressed tables use zlib 1.2.3InnoDB: Number of pools: 1InnoDB: Not using CPU crc32 instructionsInnoDB: Initializing buffer pool Total size = 100m, instances = 1, chunk size = 100MInnoDB: Completed initialization of buffer poolInnoDB: page_cleaner coordinator priority:-20InnoDB: Highest supported file format is Barracuda.InnoDB: Log scan progressed past the checkpoint lsn 2671634InnoDB: Doing recovery: scanned up to log sequence number 2671643 InnoDB: Doing recovery: scanned up to log sequence number 2671643 InnoDB: Database was not shutdown normallyinnovative DB: Starting crash recovery.InnoDB: Creating shared tablespace for temporary tablesInnoDB: 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: Waiting for purge to startInnoDB: 5.7.13 started; log sequence number 2671643xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed Log sequence number 2671671InnoDB: Number of pools: 1xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir = .xtrabackup: innodb_data_file_path = ibdata1:12M:autoextendxtrabackup: innodb_log_group_home_dir = .xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 50331648InnoDB: PUNCH HOLE support not availableInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Uses event mutexesInnoDB: GCC builtin _ sync_synchronize () is used for memory barrierInnoDB : Compressed tables use zlib 1.2.3InnoDB: Number of pools: 1InnoDB: Not using CPU crc32 instructionsInnoDB: Initializing buffer pool Total size = 100m, instances = 1, chunk size = 100MInnoDB: Completed initialization of buffer poolInnoDB: page_cleaner coordinator priority:-20InnoDB: Setting logfile. / ib_logfile101 size to 48 MBInnoDB: Setting logfile. / ib_logfile1 size to 48 MBInnoDB: Renaming logfile. / ib_logfile101 to. / ib_logfile0InnoDB: New log files created LSN=2671671InnoDB: Highest supported file format is Barracuda.InnoDB: Log scan progressed past the checkpoint lsn 2672140InnoDB: Doing recovery: scanned up to log sequence number 2672149 InnoDB: Doing recovery: scanned up to log sequence number 2672149 InnoDB: Database was not shutdown normallyInnoDB: Starting crash recovery.InnoDB: Removed temporary tablespace data file: "ibtmp1" InnoDB: Creating shared tablespace for temporary tablesInnoDB: 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: Waiting for purge to startInnoDB: 5.7.13 started; log sequence number 2672149xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 2672168161213 12:36:30 completed OKlines # View the size of the data directory after prepare [root@rhel7 ~] # ls-l / mysqlbackup/total 4drwxr Murray. 7 root root 4096 Dec 13 12:36 2016-12-13 12-27-13 [root@rhel7] # du-sm / mysqlbackup/*206 / mysqlbackup/2016-12-13 12-27-13

Restore the data to the original MySQL data directory after prepare completion

Note: the data directory for MySQL must be empty and the MySQL service must be stopped, otherwise the restore will report an error (unless a partial backup is restored)

# create a test library zx [root@rhel7 ~] # mysql-uroot-p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 2Server version: 5.7.16 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | test | | wl | +-+ 6 rows in set (0.10 sec) mysql > create database zx Query OK, 1 row affected (0.00 sec) mysql > exitBye# stop the MySQL database and create a new data directory [root@rhel7 ~] # service mysqld stopShutting down MySQL.. SUCCESS! [root@rhel7 ~] # cd $MYSQL_ Home [root @ rhel7 mysql] # lsbin COPYING data docs include lib man mysql-test README share support-files [root@rhel7 mysql] # mv data data_ bak [root @ rhel7 mysql] # mkdir data# recovery data [root@rhel7 mysql] # innobackupex-- copy-back / mysqlbackup/2016-12-13 trees 12-27-13 root@rhel7 mysql 13:03:50 innobackupex: Starting the copy-back operationIMPORTANT: 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.5 based on MySQL server 5.7.13 Linux (revision id: e41c0be) 161213 13:03:50 [01] Copying ib_logfile0 to / usr/local/mysql/data/ib_logfile0161213 13:03:50 [01]... done161213 13:03:51 [01] Copying ib_logfile1 to / usr/local/mysql/data/ib_logfile1161213 13 : 03:51 [01]... done161213 13:03:52 [01] Copying ibdata1 to / usr/local/mysql/data/ibdata1161213 13:03:54 [01]... done161213 13:03:55 [01] Copying. / mysql/plugin.ibd to / usr/local/mysql/data/mysql/plugin.ibd161213 13:03:55 [01]... done161213 13:03:55 [01] Copying. / mysql/servers.ibd to / usr/local / mysql/data/mysql/servers.ibd161213 13:03:55 [01]... done161213 13:03:55 [01] Copying. / mysql/help_topic.ibd to / usr/local/mysql/data/mysql/help_topic.ibd161213 13:03:55 [01]... done161213 13:03:55 [01] Copying. / mysql/help_category.ibd to / usr/local/mysql/data/mysql/help_category.ibd161213 13:03:55 [01] .. done.161213 13:03:57 [01] Copying. / ibtmp1 to / usr/local/mysql/data/ibtmp1161213 13:03:57 [01]... done161213 13:03:57 completed Oklahs # modify the permissions under the data directory [root@rhel7 mysql] # chown mysql:mysql-R data# launch the MySQL database to check whether the restore is successful [root@rhel7 mysql] # service mysqld startStarting MySQL. SUCCESS! [root@rhel7 mysql] # mysql-uroot-p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 2Server version: 5.7.16 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | test | | wl | +-+ 6 rows in set (0.00 sec) # data recovery succeeded The test database zx is missing.

4. Steps for incremental backup

1) to create a backup, incremental backup needs to be based on a full backup, so make a full backup first and then do an incremental backup

# create full [root@rhel7 mysql] # innobackupex-- user=root-- password=123456 / mysqlbackup/ [root@rhel7 mysql] # ls-l / mysqlbackup/total 4drwxr Murray. 8 root root 4096 Dec 13 13:10 2016-12-13 13-10-4 delete a test library mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | test | | wl | | zx | +- -+ 7 rows in set (0.00 sec) mysql > drop database zx Query OK, 0 rows affected (0.07 sec) # create incremental backup (based on fulfillment) [root@rhel7 mysql] # innobackupex-- user=root-- password=123456-- incremental / mysqlbackup/-- incremental-basedir=/mysqlbackup/2016-12-13 sec 13-10-48 pm. [root@rhel7 mysql] # ls-l / mysqlbackup/total 8drwxrcombe. 8 root root 4096 Dec 13 13:10 2016-12-13 million 13-10-48 drwxr Murray Murray. 7 root root 4096 Dec 13 13:16 2016-12-13 13-16-17 [root@rhel7 mysql] # du-sm / mysqlbackup/*90 / mysqlbackup/2016-12-13 sm 13-10-483 / mysqlbackup/2016-12-13 13-16-delete a test library mysql > drop database wl Query OK, 0 rows affected (0.07 sec) # create an incremental backup (based on the last incremental backup) [root@rhel7 mysql] # innobackupex-- user=root-- password=123456-- incremental / mysqlbackup/-- incremental-basedir=/mysqlbackup/2016-12-13 sec 13-16-17 root@rhel7 mysql. [root@rhel7 mysql] # ls-l / mysqlbackup/total 12drwxrk. 8 root root 4096 Dec 13 13:10 2016-12-13 million 13-10-48 drwxr Murray Murray. 7 root root 4096 Dec 13 13:16 2016-12-1300 13-16-17drwxr Murray. 6 root root 4096 Dec 13 13:18 2016-12-13-13-18-30 [root@rhel7 mysql] # du-sm / mysqlbackup/*90 / mysqlbackup/2016-12-13-13-10-483 / mysqlbackup/2016-12-13-13-16-17-17 / mysqlbackup/2016-12-13-13-18-30

2) restore incremental backup

Like full backup, prepare is also required in recovery.

# prepare for full backup-- redo-only means transaction that only commits commit [root@rhel7 mysql] # innobackupex-- apply-log-- redo-only / mysqlbackup/2016-12-13 backup 13-10-48 rhel7 mysql # first incremental backup [root @ rhel7 mysql] # innobackupex-- apply-log-- redo-only / mysqlbackup/2016-12-13 backup 13-10-48 /-- incremental-dir=/mysqlbackup/2016-12-13 account 13-16-17 / # most The next incremental backup prepare no longer requires the redo-only parameter [root@rhel7 mysql] # innobackupex-- apply-log / mysqlbackup/2016-12-13cm 13-10-48 /-incremental-dir=/mysqlbackup/2016-12-13cm 13-18-30 MySQL # stop the MySQL service to create a new data directory [root@rhel7 mysql] # service mysqld stopShutting down MySQL.. SUCCESS! [root@rhel7 mysql] # rm-rf data [root@rhel7 mysql] # mkdir data# recovery data [root@rhel7 mysql] # innobackupex-copy-back / mysqlbackup/2016-12-13mm 13-10-48 data directory permissions [root@rhel7 mysql] # chown mysql:mysql-R data# start MySQL service and verify [root@rhel7 mysql] # service mysqld startStarting MySQL... SUCCESS! [root@rhel7 mysql] # mysql-uroot-p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 2Server version: 5.7.16 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | test | | wl | +-+ 6 rows in set (0.00 sec) mysql > show tables in wl Empty set (0.00 sec) # the test library zx is gone, the wl library is still there, but the tables in the library are gone.

The previous only provides a simple recovery method, if the database backup runs for a period of time, the database is down, if only restore the backup data, then part of the data will be lost. So how to recover this part of the data. The answer is to apply binlog.

In an binlog-enabled database, information about binlog is output in the log after the backup is completed, and there is also a xtrabackup_binlog_info file in the backup directory that stores the binlog location of the backup.

161214 10:24:34 Executing UNLOCK TABLES161214 10:24:34 All tables unlocked161214 10:24:34 [00] Copying ib_buffer_pool to / mysqlbackup//2016-12-14 "10-24-27/ib_buffer_pool161214 10:24:34 [00].. mysqlbackup//2016 161214 10:24:34 Backup created in directory'/ mysqlbackup//2016-12-14" 10-24-27'#binlog location MySQL binlog position: filename 'mysql-bin.000001' Position '1544th 161214 10:24:34 [00] Writing backup-my.cnf161214 10:24:34 [00]... done161214 10:24:34 [00] Writing xtrabackup_info161214 10:24:34 [00]... donextrabackup: Transaction log of lsn (2677865) to (2677874) was copied.161214 10:24:34 completed OK! [root@rhel7 2016-12-14 [10-24-27] # ls-ltotal 77876 Mustang. 1 root root 425 Dec 14 10:24 backup-my.cnf-rw-r-. 1 root root 307 Dec 14 10:24 ib_buffer_pool-rw-r-. 1 root root 79691776 Dec 14 10:24 ibdata1drwxr-x---. 2 root root 4096 Dec 14 10:24 mysqldrwxr-x---. 2 root root 8192 Dec 14 10:24 performance_schemadrwxr-x---. 2 root root 8192 Dec 14 10:24 sysdrwxr-x---. 2 root root 87 Dec 14 10:24 test-rw-r-. 1 root root 21 Dec 14 10:24 xtrabackup_binlog_info-rw-r-. 1 root root 113 Dec 14 10:24 xtrabackup_checkpoints-rw-r-. 1 root root 469 Dec 14 10:24 xtrabackup_info-rw-r-. 1 root root 2560 Dec 14 10:24 xtrabackup_logfile# record the file at the binlog location [root@rhel7 2016-12-14010-24-27] # cat xtrabackup_binlog_infomysql-bin.000001 154i

With this binlog log location, you can apply binlog to recover data backed up to downtime.

Mysqlbinlog-- start-position=154 mysql-bin.000001 | mysql- uroot-p123456

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.