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

Kernel aspect:

$cat / etc/centos-release

CentOS Linux release 7.4.1708 (Core)

$uname-r

3.10.0-693.el7.x86_64

Download and install xtrabackup:

$wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.12-r170eb8c-el7-x86_64-bundle.tar

$tar-xf Percona-XtraBackup-2.4.12-r170eb8c-el7-x86_64-bundle.tar

$yum-y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

$which xtrabackup

/ usr/bin/xtrabackup

$innobackupex-v

Xtrabackup: recognized server arguments:-innodb_buffer_pool_size=1024M-datadir=/data/mysql/data-server-id=1

Create test data:

Mysql > mysql-uroot-p

Mysql > create database opop charset='utf8mb4'

Mysql > use opop

Mysql > create table user (id int unsigned primary key auto_increment,name varchar (30))

Mysql > show tables

Mysql > insert into user (name) values ('jack'), (' tom'), ('lily'), (' lucy')

Mysql > select from user

Mysql > commit

Mysql > select from user

Full backup and recovery of Xtrabackup

$innobackupex-defaults-file=/etc/my.cnf-user=sstuser-password= "123456"-backup. # the last point is the current directory

Xtrabackup: recognized server arguments:-innodb_buffer_pool_size=1024M-datadir=/data/mysql/data-server-id=1

Xtrabackup: recognized client arguments:-innodb_buffer_pool_size=1024M-- datadir=/data/mysql/data-- server-id=1-- backup=1

181025 08:27:43 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!".

181025 08:27:43 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as' sstuser' (using password: YES).

181025 08:27:43 version_check Connected to MySQL server

181025 08:27:43 version_check Executing a version check against the server...

181025 08:27:43 version_check Done.

181025 08:27:43 Connecting to MySQL server host: localhost, user: sstuser, password: set, port: not set, socket: not set

Using server version 5.7.23-23-57

Innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86x64) (revision id: 170eb8c)

Xtrabackup: uses posix_fadvise ().

Xtrabackup: cd to / data/mysql/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:12M:autoextend

Xtrabackup: innodb_log_group_home_dir =. /

Xtrabackup: innodb_log_files_in_group = 2

Xtrabackup: innodb_log_file_size = 50331648

InnoDB: Number of pools: 1

181025 08:27:43 > > log scanned up to (2605686)

Xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0

181025 08:27:44 [01] Copying. / ibdata1 to / root/2018-10-25 October 08-27-43/ibdata1

181025 08:27:44 [01]... done

....

....

181025 08:27:46 Executing LOCK TABLES FOR BACKUP... # Translation: execute locking table for backup

181025 08:27:46 Starting to backup non-InnoDB tables and files

181025 08:27:46 [01] Copying. / mysql/db.opt to / root/2018-10-25 October 08-27-43/mysql/db.opt

181025 08:27:46 [01]... done

181025 08:27:46 [01] Copying. / mysql/db.frm to / root/2018-10-25 October 08-27-43/mysql/db.frm

181025 08:27:46 [01]... done

....

....

181025 08:27:47 [01] Copying. / opop/user.frm to / root/2018-10-25 October 08-27-43/opop/user.frm

181025 08:27:47 [01]... done

181025 08:27:47 Finished backing up non-InnoDB tables and files

181025 08:27:47 Executing LOCK BINLOG FOR BACKUP... # execute lock binaries to start backup

181025 08:27:47 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... # execute flush no_write_to_binlog engine log

Xtrabackup: The latest check point (for incremental): '2605677'

Xtrabackup: Stopping log copying thread. # stop log replication thread.

.181025 08:27:47 > > log scanned up to (2605686) # Log scan to (2605686)

181025 08:27:47 Executing UNLOCK BINLOG # execute unlock binlog

181025 08:27:47 Executing UNLOCK TABLES # execute unlock table

181025 08:27:47 All tables unlocked # all tables are unlocked

181025 08:27:47 [00] Copying ib_buffer_pool to / root/2018-10-258-27-43/ib_buffer_pool

181025 08:27:47 [00]... done

181025 08:27:47 Backup created in directory'/ root/2018-10-25mm 08-27-43max'# create a backup in / root/2018-10-25mm 08-27-43 / directory

181025 08:27:47 [00] Writing / root/2018-10-258-27-43/backup-my.cnf

181025 08:27:47 [00]... done

181025 08:27:47 [00] Writing / root/2018-10-258-27-43/xtrabackup_info

181025 08:27:47 [00]... done

Xtrabackup: Transaction log of lsn (2605677) to (2605686) was copied. # the transaction log from lsn (2605677) to (2605686) has been replicated.

181025 08:27:48 completed OK! # done!

A directory with the name of the current time is generated under the current directory:

# this is the relevant backup file, and you can also see the name of the library we created

Use the following command to keep the relevant data files consistent

[root@localhost] # innobackupex-- apply-log / root/2018-10-258-27-43 /

Xtrabackup: recognized server arguments:-- innodb_checksum_algorithm=crc32-- innodb_log_checksum_algorithm=strict_crc32-- innodb_data_file_path=ibdata1:12M:autoextend-- innodb_log_files_in_group=2-- innodb_log_file_size=50331648-- innodb_fast_checksum=0-- innodb_page_size=16384-- innodb_log_block_size=512-- innodb_undo_directory=./-- innodb_undo_tablespaces=0-- server-id=1-- redo-log-version=1

Xtrabackup: recognized client arguments:-- innodb_checksum_algorithm=crc32-- innodb_log_checksum_algorithm=strict_crc32-- innodb_data_file_path=ibdata1:12M:autoextend-- innodb_log_files_in_group=2-- innodb_log_file_size=50331648-- innodb_fast_checksum=0-- innodb_page_size=16384-- innodb_log_block_size=512-- innodb_undo_directory=./-- innodb_undo_tablespaces=0-- server-id=1-- redo-log-version=1

181025 08:57: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!".

Innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86x64) (revision id: 170eb8c)

Xtrabackup: cd to / root/2018-10-25 08-27-43 /

Xtrabackup: This target seems to be not prepared yet.

InnoDB: Number of pools: 1

Xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn= (2605677)

Xtrabackup: using the following InnoDB configuration for recovery:

Xtrabackup: innodb_data_home_dir =.

Xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

Xtrabackup: innodb_log_group_home_dir =.

Xtrabackup: innodb_log_files_in_group = 1

Xtrabackup: innodb_log_file_size = 8388608

Xtrabackup: using the following InnoDB configuration for recovery:

Xtrabackup: innodb_data_home_dir =.

Xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

Xtrabackup: innodb_log_group_home_dir =.

Xtrabackup: innodb_log_files_in_group = 1

Xtrabackup: innodb_log_file_size = 8388608

Xtrabackup: Starting InnoDB instance for recovery.

Xtrabackup: Using 104857600 bytes for buffer pool (set by-use-memory parameter)

InnoDB: PUNCH HOLE support available

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Uses event mutexes

InnoDB: GCC builtin _ _ atomic_thread_fence () is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.7

InnoDB: Number of pools: 1

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, total size = 100m, instances = 1, chunk size = 100m

InnoDB: Completed initialization of buffer pool

InnoDB: page_cleaner coordinator priority:-20

InnoDB: Highest supported file format is Barracuda.

InnoDB: Log scan progressed past the checkpoint lsn 2605677

InnoDB: Doing recovery: scanned up to log sequence number 2605686

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

InnoDB: Creating shared tablespace for temporary tables

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: Waiting for purge to start

InnoDB: 5.7.19 started; log sequence number 2605686

Xtrabackup: Recovered WSREP position: bbe63ad7-d767-11e8-9ffc-073dc1824390:8

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 2605783

InnoDB: Number of pools: 1

Xtrabackup: using the following InnoDB configuration for recovery:

Xtrabackup: innodb_data_home_dir =.

Xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

Xtrabackup: innodb_log_group_home_dir =.

Xtrabackup: innodb_log_files_in_group = 2

Xtrabackup: innodb_log_file_size = 50331648

InnoDB: PUNCH HOLE support available

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Uses event mutexes

InnoDB: GCC builtin _ _ atomic_thread_fence () is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.7

InnoDB: Number of pools: 1

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, total size = 100m, instances = 1, chunk size = 100m

InnoDB: Completed initialization of buffer pool

InnoDB: page_cleaner coordinator priority:-20

InnoDB: Setting logfile. / ib_logfile101 size to 48 MB

InnoDB: Setting logfile. / ib_logfile1 size to 48 MB

InnoDB: Renaming logfile. / ib_logfile101 to. / ib_logfile0

InnoDB: New log files created, LSN=2605783

InnoDB: Highest supported file format is Barracuda.

InnoDB: Log scan progressed past the checkpoint lsn 2606092

InnoDB: Doing recovery: scanned up to log sequence number 2606101

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

InnoDB: Removed temporary tablespace data file: "ibtmp1"

InnoDB: Creating shared tablespace for temporary tables

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: Waiting for purge to start

InnoDB: 5.7.19 started; log sequence number 2606101

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 2606120

181025 08:57:48 completed OK!

Restore

Delete the corresponding table first

Note: it should be noted that the library is not deleted, because it will report an error when it is restored after deleting the library, because it cannot find the corresponding library. It is necessary to create a new library with the corresponding name and then restore it.

Mysql-uroot-p

Mysql > drop table opop.user

Mysql > select * from opop.user

ERROR 1146 (42S02): Table 'opop.user' doesn't exist

Mysql > use opop

Mysql > show tables

Mysql > exit

[root@localhost] # innobackupex-- defaults-file=/etc/my.cnf-- copy-back / root/2018-10-258-27-43 /

Note: you must remember to restart here, and the main node and other nodes in the Percona XtraDB cluster do not start and restart in the same way, because this document is connected to the previous document.

Log in to the database to check the recovery

Xtrabackup incremental backup and recovery

Incremental backup can only be applied to InooDB or XtraDB tables. For MyISAM tables, the increments are the same as complete. Continue to create an opop2 library on the basis of the above, and then create a table in the opop-2 library and insert data.

Mysql > create database opop2 charset='utf8mb4'

Mysql > use opop2

Mysql > create table user2 (id int unsigned primary key auto_increment,name varchar (30))

Mysql > insert into user2 (name) values ('zhangsan'), (' lisi'), ('wangwu'), (' maliu')

Mysql > commit

Mysql > select from user2

Backup begins

Omit the middle part.

#-incremental / backup/ specifies the directory where incremental backup files are backed up

#-incremental-basedir specifies the directory of the last full or incremental backup

Delete a piece of data to test incremental recovery (incremental backup data is deleted here)

Mysql-uroot-p

Mysql > use opop2

Mysql > show tables

Mysql > desc user2

Mysql > delete from user2 where name='lisi'

Mysql > select from user2; # confirm that lisi users have been deleted

Mysql > exit

The procedure for incremental recovery is as follows

[root@localhost ~] # innobackupex-- apply-log-- redo-only / root/2018-10-25 backup 08-27-43 / # first global backup data catalog

[root@localhost] # innobackupex-- apply-log-- redo-only / root/2018-10-25008-27-43 /-- incremental-dir=/root/2018-10-25011-00-25 / # the second incremental backup data directory

Note: this is equivalent to a merge action

Recover all data:

[root@localhost] # innobackupex-- defaults-file=/etc/my.cnf-- copy-back / root/2018-10-258-27-43 /

[root@localhost data] # systemctl restart mysql@bootstrap.service

# because the Percona XtraDB Cluster cluster is done in this experiment, use this command, if it is not a Percona XtraDB Cluster cluster, start or restart it using systemctl or service on centos

[root@localhost data] # mysql-uroot-p

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.