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

I. Software version

Click (here) to collapse or open

Platform: Centos 7

Database version: MySQL 5.7

Xtrabackup version: xtrabackup version 2.4.8 II. Installation: binary decompression installation

Click (here) to collapse or open

[root@my01 xtrabackup] # tar zxvf percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz

[root@my01 xtrabackup] # mv percona-xtrabackup-2.4.8-Linux-x86_64 / usr/local/xtrabackup add environment variable export PATH=$PATH:/usr/local/xtrabackup/bin

Third, create backup users

Click (here) to collapse or open

Mysql > create user xtbakup@'localhost' identified by 'oracle'

Mysql > grant reload,process,lock tables,replication client on *. * to xtbakup@localhost; IV. Perform a full library backup

Click (here) to collapse or open

[root@my01 ~] # innobackupex-- defaults-file=/etc/my.cnf-- user=xtbakup-- password=oracle-- socket=/data/db/mysql/1221/mysql.sock / data/db/ xtbakup V, simulated deletion and recovery

Click (here) to collapse or open

[root@my01 ~] # service mysql stop

Shutting down MySQL.. [root@my01 db] # ls

Mysql xtbakup

[root@my01 db] # mv mysql/ mysql_bak/

[root@my01 db] # ls

Mysql_bak xtbakup

View database status

[root@my01 ~] # service mysql status

MySQL is not running

[root@my01 ~] # service mysql start

Starting MySQL.Logging to'/ data/db/mysql/1221/mariadb.log'.

2018-03-20T03:26:56.919210Z mysqld_safe Directory'/ data/db/mysql/1221' for UNIX socket file don't exists.

ERROR! The server quit without updating PID file (/ data/db/mysql/1221/my01.pid).

Application log

[root@my01 db] # innobackupex-- apply-log / data/db/xtbakup/2018-03-20 16-02-00 /

180320 16:01:55 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.8 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 97330f7)

Xtrabackup: cd to / data/db/xtbakup/2018-03-20 16-02-00 /

Xtrabackup: This target seems to be not prepared yet.

InnoDB: Number of pools: 1

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

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 _ _ sync_synchronize () is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.3

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 9239084

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

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

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008

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: 5.7.13 started; log sequence number 9239093

InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 9239112

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 _ _ sync_synchronize () is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.3

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=9239112

InnoDB: Highest supported file format is Barracuda.

InnoDB: Log scan progressed past the checkpoint lsn 9239564

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

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

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008

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

Restore

[root@my01 2018-03-20 16-02-00] # innobackupex-- defaults-file=/etc/my.cnf-- copy-back-- rsync / data/db/xtbakup/2018-03-20 years 16-02-00 /

[root@my01 ~] # cd / data/

[root@my01 data] # ls

Db kafka-logs zookeeper

[root@my01 data] # cd db/

[root@my01 db] # ls

Mysql mysql_bak xtbakup

[root@my01 db] # chown-R mysql.mysql mysql

[root@my01 db] # ll

Total 0

Drwxr-x---. 3 mysql mysql 18 Mar 20 11:29 mysql

Drwxr-xr-x. 3 mysql mysql 18 Aug 18 2017 mysql_bak

Drwxr-xr-x. 3 root root 33 Mar 20 11:03 xtbakup

[root@my01 db] # service mysql start

Starting MySQL.Logging to'/ data/db/mysql/1221/mariadb.log'.

SUCCESS!

[root@my01] # mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 3

Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql >

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | t |

| | test |

+-+

2 rows in set (0.00 sec)

Mysql > select count (*) from test

+-+

| | count (*) |

+-+

| | 81920 |

+-+

1 row in set (0.11 sec)

Create a table to insert data

Click (here) to collapse or open

Mysql > create table T1 (id int,name varchar (40))

Query OK, 0 rows affected (0.04 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | t |

| | T1 |

| | test |

+-+

3 rows in set (0.00 sec)

Mysql > insert into T1 values (1), (2)), (3)), (4))

Query OK, 4 rows affected (0.01sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 1 | aaa |

| | 2 | bbb |

| | 3 | ccc |

| | 4 | ddd |

+-+ +

4 rows in set (0.00 sec)

VII. The first incremental backup

Click (here) to collapse or open

[root@my01 db] # ls

Incdata mysql mysql_bak xtbakup

[root@my01 incdata] # pwd

/ data/db/incdata

[root@my01 incdata] # innobackupex-- defaults-file=/etc/my.cnf-- user=xtbakup-- password=oracle-- socket=/data/db/mysql/1221/mysql.sock-- incremental / data/db/incdata-- incremental-basedir=/data/db/xtbakup/2018-03-20 16-02-00 /

[root@my01 incdata] # ls

2018-03-20 16-04-16

[root@my01 incdata] # cd 2018-03-20 16-04-16 /

[root@my01 2018-03-20 16-04-16] # ls

Backup-my.cnf ibdata1.delta mysql scott test xtrabackup_checkpoints xtrabackup_logfile

Ib_buffer_pool ibdata1.meta performance_schema sys xtrabackup_binlog_info xtrabackup_info

[root@my01 2018-03-20 16-04-16] # more xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 9251193

To_lsn = 9257319

Last_lsn = 9257328

Compact = 0

Recover_binlog_info = 0. 8. Second incremental backup

Click (here) to collapse or open

Mysql > insert into T1 values (101 values), (102)), (103)), (104)

Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 1 | aaa |

| | 2 | bbb |

| | 3 | ccc |

| | 4 | ddd |

| | 101 | aaa |

| | 102 | bbb |

| | 103 | ccc |

| | 104 | ddd |

+-+ +

8 rows in set (0.00 sec)

Incremental backup 2

[root@my01 db] # innobackupex-- defaults-file=/etc/my.cnf-- user=xtbakup-- password=oracle-- socket=/data/db/mysql/1221/mysql.sock-- incremental / data/db/incdata-- incremental-basedir=/data/db/incdata/2018-03-20 16-04-169. Analog data loss

Click (here) to collapse or open

Mysql > drop table T1

Query OK, 0 rows affected (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | t |

| | test |

+-+

2 rows in set (0.00 sec) 10. Restore data

Click (here) to collapse or open

Add the first incremental backup to the full backup

[root@my01 db] # innobackupex-- apply-log-- redo-only / data/db/xtbakup/2018-03-20 16-02-00 /-- incremental-dir=/data/db/incdata/2018-03-20 16-04-16

Add the second incremental backup to the full backup (note: do not add redo-only)

* * Note * * when doing incremental backup and restore No redo-only parameter is added when the last incremental backup is added to the full backup *

[root@my01 db] # innobackupex-- apply-log / data/db/xtbakup/2018-03-20 16-02-00 /-- incremental-dir=/data/db/incdata/2018-03-20 16-09-04

Apply-log all the backups together

[root@my01 xtbakup] # innobackupex-- apply-log / data/db/xtbakup/2018-03-20 16-02-00 /

180320 16:17:37 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.8 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 97330f7)

Xtrabackup: cd to / data/db/xtbakup/2018-03-20 16-02-00 /

Xtrabackup: This target seems to be already prepared.

InnoDB: Number of pools: 1

Xtrabackup: notice: xtrabackup_logfile was already used to'--prepare'.

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

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

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 _ _ sync_synchronize () is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.3

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: 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: 5.7.13 started; log sequence number 9262120

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

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 9262139

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 _ _ sync_synchronize () is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.3

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: 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: 5.7.13 started; log sequence number 9262139

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 9262158

180320 16:17:40 completed

Recover data

[root@my01 db] # innobackupex-- defaults-file=/etc/my.cnf-- copy-back-- rsync / data/db/xtbakup/2018-03-20 million 16-02-00 /

Validating data

Click (here) to collapse or open

[root@my01 ~] # service mysql status

ERROR! MySQL is not running

[root@my01 ~] # service mysql start

Starting MySQL.Logging to'/ data/db/mysql/1221/mariadb.log'.

SUCCESS!

[root@my01 ~] #

[root@my01 ~] #

[root@my01 ~] #

[root@my01] # mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 3

Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql >

Mysql >

Mysql >

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | scott |

| | sys |

| | test |

+-+

6 rows in set (0.01 sec)

Mysql > use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | t |

| | T1 |

| | test |

+-+

3 rows in set (0.00 sec)

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 1 | aaa |

| | 2 | bbb |

| | 3 | ccc |

| | 4 | ddd |

| | 101 | aaa |

| | 102 | bbb |

| | 103 | ccc |

| | 104 | ddd |

+-+ +

8 rows in set (0.00 sec)

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.