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

In this issue, the editor will bring you about how to restore the designated table of Percona Xtrabackup 2.4. the article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

In service versions prior to 5.6, it was not possible to copy tables by copying table files in different MySQL services, even if innodb_file_per_table was enabled. However, with Percona XtraBackup, you can export specified tables from any InnoDB database and import them into a Percona service that uses XtraDB or MySQL 5.6. This is only valid for .ibd files.

Create a test table

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 |

+-+

| | test |

+-+

1 row in set (0.00 sec)

Mysql > CREATE TABLE export_test (

-> an int (11) DEFAULT NULL

->) ENGINE=InnoDB DEFAULT CHARSET=latin1

Query OK, 0 rows affected (0.31 sec)

Mysql > insert into export_test values

Query OK, 2 rows affected (0.09 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from export_test

+-+

| | a |

+-+

| | 100 |

| | 200 |

+-+

2 rows in set (0.03 sec)

Export tabl

Mysql > show variables like 'innodb_file_per_table'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_file_per_table | ON |

+-+ +

1 row in set (0.00 sec)

-- perform backup

[root@localhost mysql] # / install/percona-xtrabackup-2.4.4-Linux-x86_64/bin/xtrabackup-defaults-file=/etc/my.cnf-- backup--datadir=/var/lib/mysql/-- target-dir=/backup/20160810-- user root-- password 'root'

The exported table must be created in innodb_file_per_table format and exist in .bd file format in the backup directory.

[root@localhost /] # find / backup/20160810-name export_test.*

/ backup/20160810/test/export_test.frm

/ backup/20160810/test/export_test.ibd

When preparing for backup, add the xtrabackup-- export parameter to the command.

[root@localhost mysql] # / install/percona-xtrabackup-2.4.4-Linux-x86_64/bin/xtrabackup-prepare-export-target-dir=/backup/20160810/

/ install/percona-xtrabackup-2.4.4-Linux-x86_64/bin/xtrabackup version 2.4.4 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: df58cf2)

Xtrabackup: auto-enabling-innodb-file-per-table due to the-export option

Xtrabackup: cd to / backup/20160810

Xtrabackup: This target seems to be not prepared yet.

InnoDB: Number of pools: 1

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

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 not 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: The log sequence number 1633851 in the system tablespace does not match the log sequence number 1639441 in the ib_logfiles!

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

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

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.13 started; log sequence number 1639441

Xtrabackup: export option is specified.

Xtrabackup: export metadata of table 'mysql/innodb_index_stats' to file `. / mysql/innodb_index_ stats.exp` (1 indexes)

Xtrabackup: name=PRIMARY, id.low=18, page=3

Xtrabackup: export metadata of table 'mysql/innodb_table_stats' to file `. / mysql/innodb_table_ stats.exp` (1 indexes)

Xtrabackup: name=PRIMARY, id.low=17, page=3

Xtrabackup: export metadata of table 'mysql/slave_worker_info' to file `. / mysql/slave_worker_ info.exp` (1 indexes)

Xtrabackup: name=PRIMARY, id.low=21, page=3

Xtrabackup: export metadata of table 'mysql/slave_relay_log_info' to file `. / mysql/slave_relay_log_ info.exp` (1 indexes)

Xtrabackup: name=PRIMARY, id.low=19, page=3

Xtrabackup: export metadata of table 'mysql/slave_master_info' to file `. / mysql/slave_master_ info.exp` (1 indexes)

Xtrabackup: name=PRIMARY, id.low=20, page=3

Xtrabackup: export metadata of table 'test/export_test' to file `. / test/export_ test.exp` (1 indexes)

Xtrabackup: name=GEN_CLUST_INDEX, id.low=23, page=3

Xtrabackup: export metadata of table 'test/test' to file `. / test/ test.exp` (1 indexes)

Xtrabackup: name=GEN_CLUST_INDEX, id.low=22, page=3

Xtrabackup: starting shutdown with innodb_fast_shutdown = 0

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1639460

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 not 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=1639460

InnoDB: Highest supported file format is Barracuda.

InnoDB: Log scan progressed past the checkpoint lsn 1639948

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

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

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.13 started; log sequence number 1639957

Xtrabackup: starting shutdown with innodb_fast_shutdown = 0

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1639976

160810 15:14:42 completed OK!

In the target directory, you can see the .exp file

[root@localhost ~] # cd / backup/20160810/test/

[root@localhost test] # ls-trl

Total 256

-rw-r-. 1 root root 98304 Aug 10 15:06 export_test.ibd

-rw-r-. 1 root root 98304 Aug 10 15:06 test.ibd

-rw-r-. 1 root root 8554 Aug 10 15:06 export_test.frm

-rw-r-. 1 root root 8556 Aug 10 15:06 test.frm

-rw-r-. 1 root root 16384 Aug 10 15:14 export_test.exp

-rw-r--r--. 1 root root 374 Aug 10 15:14 export_test.cfg

-rw-r-. 1 root root 16384 Aug 10 15:14 test.exp

-rw-r--r--. 1 root root 369 Aug 10 15:14 test.cfg

The three files .exp, .ibd and .cfg are used in database import

Import tabl

Delete tabl

Mysql > drop table export_test

Query OK, 0 rows affected (1.45 sec)

On the target MySQL server, create an empty table with the same structure.

Mysql > CREATE TABLE export_test (

-> an int (11) DEFAULT NULL

->) ENGINE=InnoDB DEFAULT CHARSET=latin1

Query OK, 0 rows affected (0.06 sec)

Mysql > ALTER TABLE test.export_test DISCARD TABLESPACE

Query OK, 0 rows affected (0.10 sec)

Copy the export file to the data directory

[root@localhost test] # cp export_test.ibd export_test.exp export_test.cfg / var/lib/mysql/test

Mysql > ALTER TABLE test.export_test IMPORT TABLESPACE

ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table'"test". "export_test"': Tablespace not found

Change file permissions to mysql

[root@localhost ~] # cd / var/lib/mysql/test/

[root@localhost test] # ls

Export_test.frm test.frm test.ibd

[root@localhost test] # ls

Export_test.cfg export_test.exp export_test.frm export_test.ibd test.frm test.ibd

[root@localhost test] # ls-trl

Total 236

-rw-r-. 1 mysql mysql 8556 Aug 8 17:17 test.frm

-rw-r-. 1 mysql mysql 98304 Aug 8 17:17 test.ibd

-rw-rw----. 1 mysql mysql 8554 Aug 10 15:30 export_test.frm

-rw-r-. 1 root root 98304 Aug 10 15:34 export_test.ibd

-rw-r-. 1 root root 16384 Aug 10 15:34 export_test.exp

-rw-r--r--. 1 root root 374 Aug 10 15:34 export_test.cfg

[root@localhost test] # chown-R mysql:mysql.

[root@localhost test] # ls-trl

Total 236

-rw-r-. 1 mysql mysql 8556 Aug 8 17:17 test.frm

-rw-r-. 1 mysql mysql 98304 Aug 8 17:17 test.ibd

-rw-rw----. 1 mysql mysql 8554 Aug 10 15:30 export_test.frm

-rw-r-. 1 mysql mysql 98304 Aug 10 15:34 export_test.ibd

-rw-r-. 1 mysql mysql 16384 Aug 10 15:34 export_test.exp

-rw-r--r--. 1 mysql mysql 374 Aug 10 15:34 export_test.cfg

Mysql > ALTER TABLE test.export_test IMPORT TABLESPACE

Query OK, 0 rows affected (0.11 sec)

Verify the data in the table

Mysql > select * from export_test

+-+

| | a |

+-+

| | 100 |

| | 200 |

+-+

2 rows in set (0.00 sec)

It should be noted that after importing the table, the permanent statistics of the table are empty and need to be collected again.

Mysql > select * from innodb_index_stats where table_name='export_test'

+- -- +

| | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |

+- -- +

| | test | export_test | GEN_CLUST_INDEX | 2016-08-10 15:36:50 | n_diff_pfx01 | 2 | 1 | DB_ROW_ID |

| | test | export_test | GEN_CLUST_INDEX | 2016-08-10 15:36:50 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |

| | test | export_test | GEN_CLUST_INDEX | 2016-08-10 15:36:50 | size | 1 | NULL | Number of pages in the index |

+- -- +

3 rows in set (0.00 sec)

Mysql > select * from innodb_table_stats where table_name='export_test'

+-+

| | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | |

+-+

| | test | export_test | 2016-08-10 15:36:50 | 2 | 1 | 0 |

+-+

1 row in set (0.00 sec)

Mysql > analyze table test.export_test

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | test.export_test | analyze | status | OK | |

+-+

1 row in set (0.01 sec)

Mysql > select * from innodb_index_stats where table_name='export_test'

+- -- +

| | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |

+- -- +

| | test | export_test | GEN_CLUST_INDEX | 2016-08-10 15:48:32 | n_diff_pfx01 | 2 | 1 | DB_ROW_ID |

| | test | export_test | GEN_CLUST_INDEX | 2016-08-10 15:48:32 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |

| | test | export_test | GEN_CLUST_INDEX | 2016-08-10 15:48:32 | size | 1 | NULL | Number of pages in the index |

+- -- +

3 rows in set (0.00 sec)

Mysql > select * from innodb_table_stats where table_name='export_test'

+-+

| | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | |

+-+

| | test | export_test | 2016-08-10 15:48:32 | 2 | 1 | 0 |

+-+

1 row in set (0.00 sec)

The above is the editor for you to share the Percona Xtrabackup 2.4 how to restore the designated table, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.