基于Xtrabackup恢复单个innodb表,ibdata1文件损坏时恢

 

 

 

14.5 InnoDB Tablespace Management

Preface

Preface

Preface

 

 

 

 

14.5.1 Resizing the InnoDB System Tablespace

    ibdata1 file is a shared system tablespace of innodb engine.Although we always set variable of "innodb_file_per_table" to "on" which means put the data of table into their individual ibd files.The ibdata file will still store undo log of our innodb tables.As the ibdata1 file is inevitable to make the MySQL database running properly.Today,I'm gonna demonstrate a way to rescue the table in an instance whose ibdata file is destroied by a certain way such as write several unmeaningful characters into it.

    We all know that Xtrabackup is a backup tool of percona for innodb or Xtradb.It's usually used to back up whole databases physically.But how to restore a single innodb table from a full Xtrabackup after dropping it by accident?We can also make use of the feature of transportable tablespace to accomplish it.

    There're many ways in backing up or migrating data from one server to another one.Logically,we can use mysqldump,mydumper,mypump to do that kind of job.Physically,we can use Xtrabackup even cold copy way.What I'm gonna introduce is a special method to transmit data between MySQL servers which called “transportable tablespace”.

14.5.2 Changing the Number or Size of InnoDB Redo Log Files

 

    I've demonstrated how to restore a table on a server to another using transportable tablespace in my yesterday's blog.Today,we'll use another tool called "mysqlfrm" to fetch the structure of table in .frm files.

 

14.5.3 Using Raw Disk Partitions for the System Tablespace

Porcedure

 

Introduction

14.5.4 InnoDB File-Per-Table Tablespaces

 

Introduce

 

14.5.5 Creating a File-Per-Table Tablespace Outside the Data Directory

Backup the instance using Xtrabackup first.

 

    What's transportable tablespace?It is supported only on innodb engine and based on export/import grammer of "alter table ... ;" clause since MySQL 5.6.6 version.As we all know,innodb supports putting data of tables in their own tablespaces instead of shared system tablespace by setting parameter "innodb_file_per_table=1".It's different from the conception of oracle database.Business tables in oracle can be stored togerther with each other in the same tablespace while MySQL oblying the rule of "one table one ibd".That is,these ".ibd" files is what we really need to transport.**

14.5.6 Copying File-Per-Table Tablespaces to Another Server

 1 [root@zlm1 13:46:27 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup
 3 180805 13:46:40 innobackupex: Starting the backup operation
 4 ... //Omitted.
 5 
 6 [root@zlm1 13:47:14 /data/backup]
 7 #ls -l
 8 total 4
 9 drwxr-x--- 7 root root 4096 Aug  5 13:47 2018-08-05_13-46-40
10 
11 [root@zlm1 13:47:17 /data/backup]

    mysqlfrm is a tool designed to diagnose information in .frm file when in recovery scenario.mysqlfrm provides two modes of operatins.By default,it creates a new instance referencing the base directory using "--basedir" it also need a port specified by "--port" which ought to be diffrent from the one used in the original instance.The other mode is connecting to the already exist instance using "--server".The new instance will be shutdown and all temperary files will be deleted after it reads data in .frm files.Further more,there're two exclusions when using mysqlfrm,one is foreign key constraints,the other one is auto increment number sequences.

 

14.5.7 Storing InnoDB Undo Logs in Separate Tablespaces

 

 

Scenarios

 

Check the target table which we want to rescue in plan.

Example

  • Transport a single table to report server without influencing loads on product.
  • Transport a single table to slave server for correcting the replication errors about the table.
  • Transport a single table to better storages such as ssd device for special purpose.
  • Restore a big table efficiently and swiftly as mysqldump needs to reinsert data and rebuild indexes.

14.5.1 Resizing the InnoDB System Tablespace

 1 zlm@192.168.56.100:3306 [sysbench]>show tables;
 2  -------------------- 
 3 | Tables_in_sysbench |
 4  -------------------- 
 5 | sbtest1            |
 6 | sbtest2            |
 7 | sbtest3            |
 8 | sbtest4            |
 9  -------------------- 
10 4 rows in set (0.00 sec)
11 
12 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest4;
13  ---------- 
14 | count(*) |
15  ---------- 
16 |    10000 |
17  ---------- 
18 1 row in set (0.05 sec)
19 
20 zlm@192.168.56.100:3306 [sysbench]>delete from sbtest4 limit 1000;
21 Query OK, 1000 rows affected (0.17 sec)
22 
23 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest4;
24  ---------- 
25 | count(*) |
26  ---------- 
27 |     9000 |
28  ---------- 
29 1 row in set (0.00 sec)

 

 

 

 

Install mysqlfrm tool.

Limitations

This section describes how to increase or decrease the size of the InnoDB system tablespace.

Destroy the ibdata1 file with "dd" command.

 1 [root@zlm1 10:03:25 ~]
 2 #yum install mysql-utilities
 3 
 4 Installed:
 5   mysql-utilities.noarch 0:1.3.6-1.el7                                                                                                               
 6 
 7 Dependency Installed:
 8   mysql-connector-python.noarch 0:1.1.6-1.el7                                                                                                        
 9 
10 Complete!
  • "innodb_file_per_table" should be set to "on"(the same to slave server if in replication structure).
  • Page size on instance of target server should be same as the one on source server.
  • It doesn't support partition table and tables which contains fulltext indexes.
  • "foreign_key_checks" should be set to "0" if there's a paraent-child relationship in a table.
  • It doesn't check the foreign key constraints when importing,so all relevant tables should be exported at the same time.
  • Target instance must has the same version of series with the source instance.
  • it's recommended to set "lower_case_table" to "1" to avoid import problems.

这一章节陈说了哪些扩大降低InnoDB系统表空间的高低。

 1 [root@zlm1 13:57:01 ~]
 2 #cd /data/mysql/mysql3306/data
 3 
 4 [root@zlm1 13:59:35 /data/mysql/mysql3306/data]
 5 #ls -l
 6 total 433892
 7 -rw-r----- 1 mysql mysql        56 Mar 18 15:10 auto.cnf
 8 -rw-r----- 1 mysql mysql     81490 Aug  5 13:50 error.log
 9 -rw-r----- 1 mysql mysql       882 Jul 31 16:36 ib_buffer_pool
10 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ibdata1
11 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ib_logfile0
12 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ib_logfile1
13 -rw-r----- 1 mysql mysql 104857600 Jul  1 14:32 ib_logfile2
14 -rw-r----- 1 mysql mysql  12582912 Aug  5 13:45 ibtmp1
15 -rw-r----- 1 mysql mysql      3924 Aug  5 13:53 innodb_status.3799
16 -rw-r----- 1 mysql mysql         0 Jul 14 02:52 innodb_status.3828
17 -rw-r----- 1 mysql mysql      4008 Jun  1 21:38 innodb_status.4131
18 drwxr-x--- 2 mysql mysql      4096 Jul  7 09:57 mrbs
19 drwxr-x--- 2 mysql mysql      4096 Mar 18 15:10 mysql
20 -rw-r----- 1 mysql mysql         5 Aug  5 13:45 mysql.pid
21 drwxr-x--- 2 mysql mysql      8192 Mar 18 15:10 performance_schema
22 -rw-r----- 1 mysql mysql       177 Jun  4 16:48 relay-bin.000001
23 -rw-r----- 1 mysql mysql        19 Jun  4 16:48 relay-bin.index
24 -rw-r----- 1 mysql mysql    526773 Aug  5 13:45 slow.log
25 drwxr-x--- 2 mysql mysql      8192 Mar 18 15:10 sys
26 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:35 sysbench
27 -rw-r----- 1 mysql mysql  11610419 Jul 29 09:52 zlm1.log
28 
29 [root@zlm1 13:59:38 /data/mysql/mysql3306/data]
30 #dd if=/dev/zero of=./ibdata1 bs=1024 count=262144
31 262144 0 records in
32 262144 0 records out
33 268435456 bytes (268 MB) copied, 1.61997 s, 166 MB/s
34 
35 [root@zlm1 14:02:34 /data/mysql/mysql3306/data]
36 #ls -l|grep ibdata1
37 -rw-r----- 1 mysql mysql 268435456 Aug  5 14:06 ibdata1 //The ibdata1 turned out to be 256M and filled with zero.

 

Example

 

 

**Generate a Xtrabackup backup.**

 

Increasing the Size of the InnoDB System Tablespace

Restart the MySQL instance.

 1 [root@zlm1 10:07:36 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --host=localhost --user=root --password=Passw0rd --sock=/tmp/mysql3306.sock /data/backup
 3 180706 10:09:22 innobackupex: Starting the backup operation
 4 
 5 IMPORTANT: Please check that the backup run completes successfully.
 6            At the end of a successful backup run innobackupex
 7            prints "completed OK!".
 8 
 9 //Omitts the intermedia output.
10 
11 180706 10:10:27 Finished backing up non-InnoDB tables and files
12 180706 10:10:27 [00] Writing xtrabackup_binlog_info
13 180706 10:10:27 [00]        ...done
14 180706 10:10:27 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
15 xtrabackup: The latest check point (for incremental): '1703733455'
16 xtrabackup: Stopping log copying thread.
17 .180706 10:10:27 >> log scanned up to (1703733464)
18 
19 180706 10:10:27 Executing UNLOCK TABLES
20 180706 10:10:27 All tables unlocked
21 180706 10:10:27 [00] Copying ib_buffer_pool to /data/backup/2018-07-06_10-09-22/ib_buffer_pool
22 180706 10:10:27 [00]        ...done
23 180706 10:10:27 Backup created in directory '/data/backup/2018-07-06_10-09-22'
24 MySQL binlog position: filename 'mysql-bin.000071', position '194', GTID of the last change '2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715494'
25 180706 10:10:27 [00] Writing backup-my.cnf
26 180706 10:10:27 [00]        ...done
27 180706 10:10:27 [00] Writing xtrabackup_info
28 180706 10:10:27 [00]        ...done
29 xtrabackup: Transaction log of lsn (1703733455) to (1703733464) was copied.
30 180706 10:10:27 completed OK!
31 
32 [root@zlm1 10:10:27 ~]
33 #cd /data/backup
34 
35 [root@zlm1 10:13:14 /data/backup]
36 #ls -l
37 total 4
38 drwxr-x--- 9 root root 4096 Jul  6 10:10 2018-07-06_10-09-22
39 
40 [root@zlm1 10:13:15 /data/backup]
41 #cd 2018-07-06_10-09-22/
42 
43 [root@zlm1 10:13:19 /data/backup/2018-07-06_10-09-22]
44 #ls -l
45 total 102468
46 drwxr-x--- 2 root root        51 Jul  6 10:10 aaron8219
47 -rw-r----- 1 root root       433 Jul  6 10:10 backup-my.cnf
48 drwxr-x--- 2 root root        19 Jul  6 10:10 -help
49 -rw-r----- 1 root root      9492 Jul  6 10:10 ib_buffer_pool
50 -rw-r----- 1 root root 104857600 Jul  6 10:09 ibdata1
51 drwxr-x--- 2 root root      4096 Jul  6 10:10 mysql
52 drwxr-x--- 2 root root      8192 Jul  6 10:10 performance_schema
53 drwxr-x--- 2 root root      8192 Jul  6 10:10 sys
54 drwxr-x--- 2 root root      4096 Jul  6 10:10 sysbench
55 -rw-r----- 1 root root        69 Jul  6 10:10 xtrabackup_binlog_info
56 -rw-r----- 1 root root       119 Jul  6 10:10 xtrabackup_checkpoints
57 -rw-r----- 1 root root       639 Jul  6 10:10 xtrabackup_info
58 -rw-r----- 1 root root      2560 Jul  6 10:10 xtrabackup_logfile
59 drwxr-x--- 2 root root      4096 Jul  6 10:10 zlm

Check the table which you want to transport first(eg. ”sbtest2” in database "sysbench" here).

 

 1 zlm@192.168.56.100:3306 [sysbench]>exit
 2 Bye
 3 
 4 [root@zlm1 14:13:06 ~]
 5 #mysqladmin shutdown
 6 
 7 [root@zlm1 14:13:10 ~]
 8 #ps aux|grep mysqld
 9 root      4002  0.0  0.0 112640   960 pts/0    R    14:13   0:00 grep --color=auto mysqld
10 
11 [root@zlm1 14:13:15 ~]
12 #./mysqld.sh
13 
14 [root@zlm1 14:13:53 ~]
15 #mysql
16 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.100' (111)
17 
18 [root@zlm1 14:13:56 ~]
19 #mysql
20 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.100' (111)
21 
22 [root@zlm1 14:13:58 ~]
23 #cd /data/mysql/mysql3306/data
24 
25 [root@zlm1 14:14:07 /data/mysql/mysql3306/data]
26 #tail error.log
27 ... /Omitted.
28 2018-08-05T12:13:53.242723Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
29 2018-08-05T12:13:53.242806Z 0 [Note] mysqld (mysqld 5.7.21-log) starting as process 4008 ...
30 2018-08-05T12:13:53.249168Z 0 [Note] InnoDB: PUNCH HOLE support available
31 2018-08-05T12:13:53.249207Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
32 2018-08-05T12:13:53.249213Z 0 [Note] InnoDB: Uses event mutexes
33 2018-08-05T12:13:53.249218Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
34 2018-08-05T12:13:53.249222Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
35 2018-08-05T12:13:53.249227Z 0 [Note] InnoDB: Using Linux native AIO
36 2018-08-05T12:13:53.249426Z 0 [Note] InnoDB: Number of pools: 1
37 2018-08-05T12:13:53.249507Z 0 [Note] InnoDB: Using CPU crc32 instructions
38 2018-08-05T12:13:53.251488Z 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
39 2018-08-05T12:13:53.256630Z 0 [Note] InnoDB: Completed initialization of buffer pool
40 2018-08-05T12:13:53.257913Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
41 2018-08-05T12:13:53.280321Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
42 12:13:53 UTC - mysqld got signal 11 ;
43 This could be because you hit a bug. It is also possible that this binary
44 or one of the libraries it was linked against is corrupt, improperly built,
45 or misconfigured. This error can also be caused by malfunctioning hardware.
46 Attempting to collect some information that could help diagnose the problem.
47 As this is a crash and something is definitely wrong, the information
48 collection process might fail.
49 
50 key_buffer_size=8388608
51 read_buffer_size=2097152
52 max_used_connections=0
53 max_threads=100
54 thread_count=0
55 connection_count=0
56 It is possible that mysqld could use up to 
57 key_buffer_size   (read_buffer_size   sort_buffer_size)*max_threads = 227127 K  bytes of memory
58 Hope that's ok; if not, decrease some variables in the equation.
59 
60 Thread pointer: 0x0
61 Attempting backtrace. You can use the following information to find out
62 where mysqld died. If you see no messages after this, something went
63 terribly wrong...
64 stack_bottom = 0 thread_stack 0x30000
65 mysqld(my_print_stacktrace 0x35)[0xf4a495]
66 mysqld(handle_fatal_signal 0x4a4)[0x7ce2f4]
67 /lib64/libpthread.so.0( 0xf130)[0x7f6bbee76130]
68 mysqld(_Z26page_cur_search_with_matchPK11buf_block_tPK12dict_index_tPK8dtuple_t15page_cur_mode_tPmS9_P10page_cur_tP8rtr_info 0x148)[0x1074478]
69 mysqld(_Z27btr_cur_search_to_nth_levelP12dict_index_tmPK8dtuple_t15page_cur_mode_tmP9btr_cur_tmPKcmP5mtr_t 0x1598)[0x11806d8]
70 mysqld(_Z30btr_pcur_open_on_user_rec_funcP12dict_index_tPK8dtuple_t15page_cur_mode_tmP10btr_pcur_tPKcmP5mtr_t 0x212)[0x1184b62]
71 mysqld[0x11df28d]
72 mysqld(_Z19dict_load_sys_tableP12dict_table_t 0x69)[0x11e0609]
73 mysqld(_Z9dict_bootv 0xdfb)[0x11bf48b]
74 mysqld(_Z34innobase_start_or_create_for_mysqlv 0x3212)[0x11150a2]
75 mysqld[0x100023a]
76 mysqld(_Z24ha_initialize_handlertonP13st_plugin_int 0x51)[0x819721]
77 mysqld[0xd39226]
78 mysqld(_Z40plugin_register_builtin_and_init_core_sePiPPc 0x3e4)[0xd397a4]
79 mysqld[0x7c48f7]
80 mysqld(_Z11mysqld_mainiPPc 0x92f)[0x7c7e9f]
81 /lib64/libc.so.6(__libc_start_main 0xf5)[0x7f6bbd857af5]
82 mysqld[0x7be479]
83 The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
84 information that should help you find out what is causing the crash.
85 
86 [root@zlm1 14:14:36 /data/mysql/mysql3306/data]
87 
88 //Okay,becasue of the lack of normal ibdata1 file in datadir,the instance cannot be started again.
89 //Then,how can we resuce the data in target table 'sbtest4'?
90 //First of all,let's get the ibd and frm file of it from the crashed server.

 

 1 (root@localhost mysql3306.sock)[sysbench]>show tables;
 2  -------------------- 
 3 | Tables_in_sysbench |
 4  -------------------- 
 5 | sbtest1            |
 6 | sbtest10           |
 7 | sbtest2            |
 8 | sbtest3            |
 9 | sbtest4            |
10 | sbtest5            |
11 | sbtest6            |
12 | sbtest7            |
13 | sbtest8            |
14 | sbtest9            |
15  -------------------- 
16 10 rows in set (0.00 sec)
17 
18 (root@localhost mysql3306.sock)[sysbench]>show create table sbtest2G
19 *************************** 1. row ***************************
20        Table: sbtest2
21 Create Table: CREATE TABLE `sbtest2` (
22   `id` int(11) NOT NULL AUTO_INCREMENT,
23   `k` int(11) NOT NULL DEFAULT '0',
24   `c` char(120) NOT NULL DEFAULT '',
25   `pad` char(60) NOT NULL DEFAULT '',
26   PRIMARY KEY (`id`),
27   KEY `k_2` (`k`)
28 ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
29 1 row in set (0.00 sec)
30 
31 (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest2;
32  ---------- 
33 | count(*) |
34  ---------- 
35 |   100000 |
36  ---------- 
37 1 row in set (0.07 sec)
38 
39 (root@localhost mysql3306.sock)[sysbench]>show variables like '%innodb_file_per_table%';
40  ----------------------- ------- 
41 | Variable_name         | Value |
42  ----------------------- ------- 
43 | innodb_file_per_table | ON    |
44  ----------------------- ------- 
45 1 row in set (0.00 sec)

The easiest way to increase the size of the InnoDB system tablespace is to configure it from the beginning to be auto-extending. Specify the autoextend attribute for the last data file in the tablespace definition. Then InnoDB increases the size of that file automatically in 8MB increments when it runs out of space. The increment size can be changed by setting the value of the innodb_autoextend_increment system variable, which is measured in megabytes.

 

***Prepare the backup.***

 

追加InnoDB系统表空间尺寸最简便易行的诀尽管在一上马的时候就把它配置成都电子通信工程高校动扩张的。在表空间的定义中钦命最后数据文件自动增添,当空间满的时候InnoDB会自动为特别文件扩张8MB。拉长的值能够因而innodb_autoextend_increment系统参数设定,单位是M。

Copy ibd and frm file of table "sbtest4" to another newly initialized instance on zlm2.

 1 [root@zlm1 10:17:32 /data/backup/2018-07-06_10-09-22]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --host=localhost --user=root --password=Passw0rd --sock=/tmp/mysql3306.sock --apply-log /data/backup/2018-07-06_10-09-22/
 3 180706 10:18:21 innobackupex: Starting the apply-log operation
 4 
 5 IMPORTANT: Please check that the apply-log run completes successfully.
 6            At the end of a successful apply-log run innobackupex
 7            prints "completed OK!".
 8            
 9 //Omitts the intermedia output.
10 
11 InnoDB: Database was not shutdown normally!
12 InnoDB: Starting crash recovery.
13 InnoDB: xtrabackup: Last MySQL binlog file position 139807334, file name mysql-bin.000069
14 InnoDB: Removed temporary tablespace data file: "ibtmp1"
15 InnoDB: Creating shared tablespace for temporary tables
16 InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
17 InnoDB: File './ibtmp1' size is now 12 MB.
18 InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
19 InnoDB: 32 non-redo rollback segment(s) are active.
20 InnoDB: Waiting for purge to start
21 InnoDB: 5.7.13 started; log sequence number 1703733781
22 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
23 InnoDB: page_cleaner: 1000ms intended loop took 10865ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
24 InnoDB: FTS optimize thread exiting.
25 InnoDB: Starting shutdown...
26 InnoDB: Shutdown completed; log sequence number 1703733800
27 180706 10:18:36 completed OK!

**Create the structure of table sbtest2 in database "tt” of target instance.**

 

1 [root@zlm1 14:20:39 /data/mysql/mysql3306/data/sysbench]
2 #scp sbtest4.* zlm2:/data/backup
3 sbtest4.frm                                                                                                        100% 8632     8.4KB/s   00:00    
4 sbtest4.ibd                                                                                                        100%   10MB  10.0MB/s   00:01

 

 1 (root@localhost mysql3306.sock)[tt]>CREATE TABLE `sbtest2` (
 2     ->   `id` int(11) NOT NULL AUTO_INCREMENT,
 3     ->   `k` int(11) NOT NULL DEFAULT '0',
 4     ->   `c` char(120) NOT NULL DEFAULT '',
 5     ->   `pad` char(60) NOT NULL DEFAULT '',
 6     ->   PRIMARY KEY (`id`),
 7     ->   KEY `k_2` (`k`)
 8     -> ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8;
 9 Query OK, 0 rows affected (0.02 sec)
10 
11 (root@localhost mysql3306.sock)[tt]>select count(*) from sbtest2;
12  ---------- 
13 | count(*) |
14  ---------- 
15 |        0 |
16  ---------- 
17 1 row in set (0.00 sec)
18 
19 (root@localhost mysql3306.sock)[tt]>show variables like '%innodb_file_per_table%';
20  ----------------------- ------- 
21 | Variable_name         | Value |
22  ----------------------- ------- 
23 | innodb_file_per_table | ON    |
24  ----------------------- ------- 
25 1 row in set (0.00 sec)

You can expand the system tablespace by a defined amount by adding another data file:

 

**Drop two tables to mimic misoperation.**

 

你能够透过扩张另一个数据文件来扩充系统表空间:

Install mysql-utilities package on zlm2.

 1 root@localhost:mysql3306.sock [(none)]>show tables from zlm;
 2  ---------------- 
 3 | Tables_in_zlm  |
 4  ---------------- 
 5 | customer       |
 6 | goods          |
 7 | semi_sync_test |
 8 | test_flashbk   |
 9 | test_myisam    |
10  ---------------- 
11 5 rows in set (0.00 sec)
12 
13 root@localhost:mysql3306.sock [(none)]>show tables from sysbench;
14  -------------------- 
15 | Tables_in_sysbench |
16  -------------------- 
17 | sbtest1            |
18 | sbtest10           |
19 | sbtest2            |
20 | sbtest3            |
21 | sbtest4            |
22 | sbtest5            |
23 | sbtest6            |
24 | sbtest7            |
25 | sbtest8            |
26 | sbtest9            |
27  -------------------- 
28 10 rows in set (0.00 sec)
29 
30 root@localhost:mysql3306.sock [(none)]>drop table zlm.test_flashbk,sysbench.sbtest1;
31 Query OK, 0 rows affected (0.11 sec)

**Detach the tablespace of table "sbtest2"  on target.**

 

 1 [root@zlm2 14:23:13 ~]
 2 #mysqlfrm --help
 3 -bash: mysqlfrm: command not found
 4 
 5 [root@zlm2 14:23:17 ~]
 6 #yum install mysql-utilities
 7 Loaded plugins: fastestmirror
 8 base                                                                                                                          | 3.6 kB  00:00:00     
 9 epel/x86_64/metalink                                                                                                          | 7.0 kB  00:00:00     
10 epel                                                                                                                          | 3.2 kB  00:00:00     
11 extras                                                                                                                        | 3.4 kB  00:00:00     
12 updates                                                                                                                       | 3.4 kB  00:00:00     
13 (1/5): epel/x86_64/group_gz                                                                                                   |  88 kB  00:00:00     
14 (2/5): epel/x86_64/updateinfo                                                                                                 | 932 kB  00:00:00     
15 (3/5): extras/7/x86_64/primary_db                                                                                             | 173 kB  00:00:00     
16 (4/5): epel/x86_64/primary                                                                                                    | 3.6 MB  00:00:01     
17 (5/5): updates/7/x86_64/primary_db                                                                                            | 4.3 MB  00:00:04     
18 Loading mirror speeds from cached hostfile
19  * base: mirrors.shu.edu.cn
20  * epel: mirrors.tongji.edu.cn
21  * extras: mirrors.163.com
22  * updates: mirrors.163.com
23 epel                                                                                                                                     12629/12629
24 Resolving Dependencies
25 --> Running transaction check
26 ---> Package mysql-utilities.noarch 0:1.3.6-1.el7 will be installed
27 --> Processing Dependency: mysql-connector-python for package: mysql-utilities-1.3.6-1.el7.noarch
28 --> Running transaction check
29 ---> Package mysql-connector-python.noarch 0:1.1.6-1.el7 will be installed
30 --> Finished Dependency Resolution
31 
32 ... //Omitted.
33 
34 Installed:
35   mysql-utilities.noarch 0:1.3.6-1.el7                                                                                                               
36 
37 Dependency Installed:
38   mysql-connector-python.noarch 0:1.1.6-1.el7                                                                                                        
39 
40 Complete!

 

1 (root@localhost mysql3306.sock)[zlm]>alter table sbtest2 discard tablespace;
2 Query OK, 0 rows affected (0.00 sec)
3 
4 [root@zlm3 10:30:44 /data/mysql/mysql3306/data/tt]
5 #ls -l
6 total 16
7 -rw-r----- 1 mysql mysql   61 Jul  5 10:13 db.opt
8 -rw-r----- 1 mysql mysql 8632 Jul  5 10:31 sbtest2.frm //The sbtest2.ibd file has been deleted.

1.Shut down the MySQL server.

 

***Diagnose .frm file from Xtrabackup using mysqlfrm.***

 

1.关闭MySQL实例。

Restore the table structure by mysqlfrm.

 1 [root@zlm1 10:35:56 /data/backup/2018-07-06_10-09-22]
 2 #mysqlfrm --basedir=/usr/local/mysql --port=8219 --diagnostic /data/backup/2018-07-06_10-09-22/sysbench/sbtest1.frm /data/backup/2018-07-06_10-09-22/zlm/zlm:test_flashbk.frmsbtest1.frm /data/backup/2018-07-06_10-09-22/zlm/zlm:tes
 3 # WARNING The --port option is not used in the --diagnostic mode.
 4 # WARNING: Cannot generate character set or collation names without the --server option.
 5 # CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
 6 # Reading .frm file for /data/backup/2018-07-06_10-09-22/sysbench/sbtest1.frm:
 7 # The .frm file is a TABLE.
 8 # CREATE TABLE Statement:
 9 
10 CREATE TABLE `sysbench`.`sbtest1` (
11   `id` int(11) NOT NULL AUTO_INCREMENT, 
12   `k` int(11) NOT NULL, 
13   `c` char(360) NOT NULL, 
14   `pad` char(180) NOT NULL, 
15 PRIMARY KEY `PRIMARY` (`id`),
16 KEY `k_1` (`k`)
17 ) ENGINE=InnoDB;
18 
19 # Reading .frm file for /data/backup/2018-07-06_10-09-22/zlm/test_flashbk.frm:
20 # The .frm file is a TABLE.
21 # CREATE TABLE Statement:
22 
23 CREATE TABLE `zlm`.`test_flashbk` (
24   `id` bigint(20) NOT NULL AUTO_INCREMENT, 
25   `name` varchar(80) NOT NULL, 
26 PRIMARY KEY `PRIMARY` (`id`)
27 ) ENGINE=InnoDB;
28 
29 #...done.
30 
31 //If you want to get the information of character set,"--server" is indispensable.
32 //you can either use <dbname>:<tablename>.frm or just <tablename>.frm.
33 //"--port" can be omitted.

**Flush the "sbtest2" table on source.**

 

 1 [root@zlm2 14:30:41 ~]
 2 #cd /data/backup/
 3 
 4 [root@zlm2 14:30:48 /data/backup]
 5 #ls -l|grep sbtest4
 6 -rw-r----- 1 root  root      8632 Aug  5 14:30 sbtest4.frm
 7 -rw-r----- 1 root  root  10485760 Aug  5 14:30 sbtest4.ibd
 8 
 9 [root@zlm2 14:31:25 /data/backup]
10 #mysqlfrm --basedir=/usr/local/mysql --port=8219 --diagnostic ./sbtest4.frm
11 # WARNING The --port option is not used in the --diagnostic mode.
12 # WARNING: Cannot generate character set or collation names without the --server option.
13 # CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
14 # Reading .frm file for ./sbtest4.frm:
15 # The .frm file is a TABLE.
16 # CREATE TABLE Statement:
17 
18 CREATE TABLE `sbtest4` (
19   `id` int(11) NOT NULL AUTO_INCREMENT, 
20   `k` int(11) NOT NULL, 
21   `c` char(360) NOT NULL, 
22   `pad` char(180) NOT NULL, 
23 PRIMARY KEY `PRIMARY` (`id`),
24 KEY `k_4` (`k`)
25 ) ENGINE=InnoDB;
26 
27 #...done.

 

 1 (root@localhost mysql3306.sock)[sysbench]>flush table sbtest2 for export;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 [root@zlm2 10:36:01 /data/mysql/mysql3306/data/sysbench]
 5 #ls -l|grep sbtest2
 6 -rw-r----- 1 mysql mysql      559 Jul  5 10:35 sbtest2.cfg
 7 -rw-r----- 1 mysql mysql     8632 Jul  4 09:26 sbtest2.frm
 8 -rw-r----- 1 mysql mysql 30408704 Jul  4 09:26 sbtest2.ibd //A .cfg file has been created now.
 9 
10 2018-07-05T08:35:03.515902Z 9 [Note] InnoDB: Sync to disk of `sysbench`.`sbtest2` started.
11 2018-07-05T08:35:03.515929Z 9 [Note] InnoDB: Stopping purge
12 2018-07-05T08:35:03.516147Z 9 [Note] InnoDB: Writing table metadata to './sysbench/sbtest2.cfg'
13 2018-07-05T08:35:03.516276Z 9 [Note] InnoDB: Table `sysbench`.`sbtest2` flushed to disk
14 
15 //error log shows the information after flush operation.
16 //table metadata has been written into the .cfg file.

2.If the previous last data file is defined with the keyword autoextend, change its definition to use a fixed size, based on how large it has actually grown. Check the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify this rounded size explicitly in innodb_data_file_path.

 

**Create vacant table using above create statement.**

 

2.固然此前的尾声贰个文本已经定义自动扩展了,那就依照表空间的实在增加景况修改拉长值。检查数据文件的轻重缓急, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify this rounded size explicitly in innodb_data_file_path.

Create a same table structure in the new instance using the restored "create table" statement.

 1 root@localhost:mysql3306.sock [(none)]>CREATE TABLE `sysbench`.`sbtest1` (
 2     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
 3     ->   `k` int(11) NOT NULL, 
 4     ->   `c` char(360) NOT NULL, 
 5     ->   `pad` char(180) NOT NULL, 
 6     -> PRIMARY KEY `PRIMARY` (`id`),
 7     -> KEY `k_1` (`k`)
 8     -> ) ENGINE=InnoDB;
 9 ERROR 1074 (42000): Column length too big for column 'c' (max = 255); use BLOB or TEXT instead
10 root@localhost:mysql3306.sock [(none)]>CREATE TABLE `zlm`.`test_flashbk` (
11     ->   `id` bigint(20) NOT NULL AUTO_INCREMENT, 
12     ->   `name` varchar(80) NOT NULL, 
13     -> PRIMARY KEY `PRIMARY` (`id`)
14     -> ) ENGINE=InnoDB;
15 Query OK, 0 rows affected (0.02 sec)
16 
17 //We get an error when creating table sysbench.sbtest1 beause of the overload value of char.

**Copy .ibd & .cfg file to target.**

 

 1 [root@zlm2 14:39:02 /data/backup]
 2 #mysql
 3 Welcome to the MySQL monitor.  Commands end with ; or g.
 4 Your MySQL connection id is 7
 5 Server version: 5.7.21-log MySQL Community Server (GPL)
 6 
 7 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
14 
15 zlm@192.168.56.101:3306 [(none)]>show databases;
16  -------------------- 
17 | Database           |
18  -------------------- 
19 | information_schema |
20 | mrbs               |
21 | mysql              |
22 | performance_schema |
23 | sys                |
24  -------------------- 
25 5 rows in set (0.00 sec)
26 
27 zlm@192.168.56.101:3306 [(none)]>create database sysbench;
28 Query OK, 1 row affected (0.00 sec)
29 
30 zlm@192.168.56.101:3306 [(none)]>use sysbench
31 Database changed
32 zlm@192.168.56.101:3306 [sysbench]>show tables;
33 Empty set (0.00 sec)
34 
35 zlm@192.168.56.101:3306 [sysbench]>CREATE TABLE `sbtest4` (
36     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
37     ->   `k` int(11) NOT NULL, 
38     ->   `c` char(360) NOT NULL, 
39     ->   `pad` char(180) NOT NULL, 
40     -> PRIMARY KEY `PRIMARY` (`id`),
41     -> KEY `k_4` (`k`)
42     -> ) ENGINE=InnoDB;
43 ERROR 1074 (42000): Column length too big for column 'c' (max = 255); use BLOB or TEXT instead
44 zlm@192.168.56.101:3306 [sysbench]>CREATE TABLE `sbtest4` (
45     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
46     ->   `k` int(11) NOT NULL, 
47     ->   `c` char(120) NOT NULL, 
48     ->   `pad` char(60) NOT NULL, 
49     -> PRIMARY KEY `PRIMARY` (`id`),
50     -> KEY `k_4` (`k`)
51     -> ) ENGINE=InnoDB;
52 ERROR 1146 (42S02): Table 'sysbench.sbtest4' doesn't exist
53 zlm@192.168.56.101:3306 [sysbench]>CREATE TABLE `sbtest4_bak` (
54     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
55     ->   `k` int(11) NOT NULL, 
56     ->   `c` char(120) NOT NULL, 
57     ->   `pad` char(60) NOT NULL, 
58     -> PRIMARY KEY `PRIMARY` (`id`),
59     -> KEY `k_4` (`k`)
60     -> ) ENGINE=InnoDB;
61 Query OK, 0 rows affected (0.01 sec)
62 
63 
64 zlm@192.168.56.101:3306 [sysbench]>rename table sbtest4_bak to sbtest4;
65 Query OK, 0 rows affected (0.00 sec)
66 
67 zlm@192.168.56.101:3306 [sysbench]>show tables;
68  -------------------- 
69 | Tables_in_sysbench |
70  -------------------- 
71 | sbtest4            |
72  -------------------- 
73 1 row in set (0.00 sec)
74 
75 zlm@192.168.56.101:3306 [sysbench]>show create table sbtest4G
76 *************************** 1. row ***************************
77        Table: sbtest4
78 Create Table: CREATE TABLE `sbtest4` (
79   `id` int(11) NOT NULL AUTO_INCREMENT,
80   `k` int(11) NOT NULL,
81   `c` char(120) NOT NULL,
82   `pad` char(60) NOT NULL,
83   PRIMARY KEY (`id`),
84   KEY `k_4` (`k`)
85 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
86 1 row in set (0.00 sec)
87 
88 zlm@192.168.56.101:3306 [sysbench]>select count(*) from sbtest4;
89  ---------- 
90 | count(*) |
91  ---------- 
92 |        0 |
93  ---------- 
94 1 row in set (0.00 sec)

 

1 [root@zlm2 10:36:03 /data/mysql/mysql3306/data/sysbench]
2 #scp sbtest2.{ibd,cfg} zlm3:/data/mysql/mysql3306/data/tt/
3 root@zlm3's password: 
4 sbtest2.ibd                                                                                                        100%   29MB  29.0MB/s   00:00    
5 sbtest2.cfg                                                                                                        100%  559     0.6KB/s   00:00

3.Add a new data file to the end of innodb_data_file_path, optionally making that file auto-extending. Only the last data file in the innodb_data_file_path can be specified as auto-extending.

 

**Check the structure of  sbtest2 table in sysbench.**

 

3.在innodb_data_file_path的尾巴加多叁个新的文本,并钦点自动扩展。只有innodb_data_file_path里倒数数据文件能够被钦赐为自行扩张。

Discard the tablesapce of new table "sbtest4".

 1 root@localhost:mysql3306.sock [(none)]>show create table sysbench.sbtest2G
 2 *************************** 1. row ***************************
 3        Table: sbtest2
 4 Create Table: CREATE TABLE `sbtest2` (
 5   `id` int(11) NOT NULL AUTO_INCREMENT,
 6   `k` int(11) NOT NULL DEFAULT '0',
 7   `c` char(120) NOT NULL DEFAULT '', //In the counterpart table,the value is 120.
 8   `pad` char(60) NOT NULL DEFAULT '', //In the counterpart table,the value is 60.
 9   PRIMARY KEY (`id`),
10   KEY `k_2` (`k`)
11 ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
12 1 row in set (0.00 sec)

**Release the lock resources on source instance.**

 

 1 zlm@192.168.56.101:3306 [sysbench]>alter table sbtest4 discard tablespace;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 [root@zlm2 15:01:45 /data/mysql/mysql3306/data/sysbench]
 5 #ls -l
 6 total 16
 7 -rw-r----- 1 mysql mysql   61 Aug  5 14:39 db.opt
 8 -rw-r----- 1 mysql mysql 8632 Aug  5 14:54 sbtest4.frm
 9 
10 //After the discard operation,the new ibd file has gone.

 

 1 (root@localhost mysql3306.sock)[sysbench]>unlock tables;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 [root@zlm2 10:37:57 /data/mysql/mysql3306/data/sysbench]
 5 #ls -l|grep sbtest2
 6 -rw-r----- 1 mysql mysql     8632 Jul  4 09:26 sbtest2.frm
 7 -rw-r----- 1 mysql mysql 30408704 Jul  4 09:26 sbtest2.ibd
 8 
 9 2018-07-05T08:38:09.256442Z 9 [Note] InnoDB: Deleting the meta-data file './sysbench/sbtest2.cfg'
10 2018-07-05T08:38:09.256458Z 9 [Note] InnoDB: Resuming purge
11 
12 //The .cfg file will be deleted after execute "unlock tables;"

4.Start the MySQL server again.

 

***Change the create statement reference to the value in sbtest2.***

 

4.重新启航MySQL实例。

Copy the ibd file of table "sbtest4" to the sysbench directory in datadir(notice the owner of file).

 1 when creating table sysbench.sbtest1 beause of the overload value of char.
 2 
 3 
 4 Check the structure of  sbtest2 table in sysbench.
 5  1 root@localhost:mysql3306.sock [(none)]>show create table sysbench.sbtest2G
 6  2 *************************** 1. row ***************************
 7  3        Table: sbtest2
 8  4 Create Table: CREATE TABLE `sbtest2` (
 9  5   `id` int(11) NOT NULL AUTO_INCREMENT,
10  6   `k` int(11) NO

**Check the files "sbtest2" table needs and give it the mysql privileges.**

 

 1 [root@zlm2 15:05:42 /data/backup]
 2 #cp sbtest4.ibd /data/mysql/mysql3306/data/sysbench/
 3 
 4 [root@zlm2 15:05:54 /data/backup]
 5 #ll /data/mysql/mysql3306/data/sysbench/ |grep sbtest4
 6 -rw-r----- 1 mysql mysql     8632 Aug  5 14:54 sbtest4.frm
 7 -rw-r----- 1 root  root  10485760 Aug  5 15:05 sbtest4.ibd
 8 
 9 [root@zlm2 15:06:12 /data/backup]
10 #chown mysql.mysql /data/mysql/mysql3306/data/sysbench/sbtest4.ibd
11 
12 [root@zlm2 15:06:39 /data/backup]
13 #ll /data/mysql/mysql3306/data/sysbench/ |grep sbtest4
14 -rw-r----- 1 mysql mysql     8632 Aug  5 14:54 sbtest4.frm
15 -rw-r----- 1 mysql mysql 10485760 Aug  5 15:05 sbtest4.ibd

 

 1 [root@zlm3 10:39:13 /data/mysql/mysql3306/data/tt]
 2 #ls -l
 3 total 29716
 4 -rw-r----- 1 mysql mysql       61 Jul  5 10:13 db.opt
 5 -rw-r----- 1 root  root       559 Jul  5 10:36 sbtest2.cfg
 6 -rw-r----- 1 mysql mysql     8632 Jul  5 10:31 sbtest2.frm
 7 -rw-r----- 1 root  root  30408704 Jul  5 10:36 sbtest2.ibd
 8 
 9 //change the root.root to mysql.mysql
10 
11 [root@zlm3 10:39:30 /data/mysql/mysql3306/data/tt]
12 #chown mysql.mysql sbtest2.*
13 
14 [root@zlm3 10:39:41 /data/mysql/mysql3306/data/tt]
15 #ls -l
16 total 29716
17 -rw-r----- 1 mysql mysql       61 Jul  5 10:13 db.opt
18 -rw-r----- 1 mysql mysql      559 Jul  5 10:36 sbtest2.cfg
19 -rw-r----- 1 mysql mysql     8632 Jul  5 10:31 sbtest2.frm
20 -rw-r----- 1 mysql mysql 30408704 Jul  5 10:36 sbtest2.ibd

For example, this tablespace has just one auto-extending data file ibdata1:

 

**Add a write lock on these two tables.**

 

诸如,这么些表空间独有一个活动扩充数据文件ibdata1:

Import the original tablespace of table "sbtest4".

 1 root@localhost:mysql3306.sock [(none)]>lock tables sysbench.sbtest1 write;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 root@localhost:mysql3306.sock [(none)]>lock tables zlm.test_flashbk write;
 5 Query OK, 0 rows affected (0.00 sec)
 6 
 7 root@localhost:mysql3306.sock [(none)]>alter table sysbench.sbtest1 discard tablespace;
 8 ERROR 1100 (HY000): Table 'sbtest1' was not locked with LOCK TABLES //when locks another table,the lock on previous table will be released.
 9 root@localhost:mysql3306.sock [(none)]>lock tables sysbench.sbtest1 write; //This time,lock one and discard one in order.
10 Query OK, 0 rows affected (0.00 sec)
11 
12 root@localhost:mysql3306.sock [(none)]>alter table sysbench.sbtest1 discard tablespace;
13 Query OK, 0 rows affected (0.00 sec)
14 
15 root@localhost:mysql3306.sock [(none)]>lock tables zlm.test_flashbk write;
16 Query OK, 0 rows affected (0.00 sec)
17 
18 root@localhost:mysql3306.sock [(none)]>alter table zlm.test_flashbk discard tablespace;
19 Query OK, 0 rows affected (0.00 sec)

**Import the tablespace of "sbtest2" table.**

 

 1 zlm@192.168.56.101:3306 [sysbench]>alter table sbtest4 import tablespace;
 2 Query OK, 0 rows affected, 1 warning (1.77 sec)
 3 
 4 zlm@192.168.56.101:3306 [sysbench]>select count(*) from sbtest4;
 5  ---------- 
 6 | count(*) |
 7  ---------- 
 8 |     9000 |
 9  ---------- 
10 1 row in set (0.01 sec)
11 
12 //Now the table has been rescued.
13 //Because of the destroying of ibdata1 in the original instance,it should be restored by Xtrabackup again.

 

 1 (root@localhost mysql3306.sock)[tt]>alter table sbtest2 import tablespace;
 2 Query OK, 0 rows affected, 1 warning (2.68 sec)
 3 
 4 (root@localhost mysql3306.sock)[tt]>show tables;
 5  -------------- 
 6 | Tables_in_tt |
 7  -------------- 
 8 | sbtest2      |
 9  -------------- 
10 1 row in set (0.00 sec)
11 
12 (root@localhost mysql3306.sock)[tt]>select count(*) from sbtest2;
13  ---------- 
14 | count(*) |
15  ---------- 
16 |   100000 |
17  ---------- 
18 1 row in set (0.06 sec)
19 
20 2018-07-05T08:40:03.820441Z 10 [Note] InnoDB: Importing tablespace for table 'sysbench/sbtest2' that was exported from host 'zlm2'
21 2018-07-05T08:40:03.820441Z 10 [Note] InnoDB: Phase I - Update all pages
22 2018-07-05T08:40:03.859485Z 10 [Note] InnoDB: Sync to disk
23 2018-07-05T08:40:04.936351Z 10 [Note] InnoDB: Sync to disk - done!
24 2018-07-05T08:40:04.962775Z 10 [Note] InnoDB: Phase III - Flush changes to disk
25 2018-07-05T08:40:04.975519Z 10 [Note] InnoDB: Phase IV - Flush complete
26 2018-07-05T08:40:04.975722Z 10 [Note] InnoDB: `tt`.`sbtest2` autoinc value set to 100001
27 
28 //The error log shows details of this import operation.

innodb_data_home_dir =

 

**Copy .ibd files from Xtrabackup and change privilege.**

 

innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

Summary

 1 [root@zlm1 11:06:18 /data/backup/2018-07-06_10-09-22]
 2 #cp sysbench/sbtest1.ibd /data/mysql/mysql3306/data/sysbench
 3 
 4 [root@zlm1 11:07:50 /data/backup/2018-07-06_10-09-22]
 5 #cp zlm/test_flashbk.ibd /data/mysql/mysql3306/data/zlm
 6 
 7 [root@zlm1 11:08:05 /data/backup/2018-07-06_10-09-22]
 8 #chown -R mysql.mysql /data/mysql/mysql3306/data
 9 
10 [root@zlm1 11:11:25 /data/backup/2018-07-06_10-09-22]
11 #ls -l /data/mysql/mysql3306/data/sysbench | grep sbtest1.ibd
12 -rw-r----- 1 mysql mysql 33554432 Jul  6 11:07 sbtest1.ibd
13 
14 [root@zlm1 11:12:39 /data/backup/2018-07-06_10-09-22]
15 #ls -l /data/mysql/mysql3306/data/zlm | grep test_flashbk.ibd
16 -rw-r----- 1 mysql mysql  12582912 Jul  6 11:08 test_flashbk.ibd

**If you detach an inexistent tablespace,it will show below errors.**

 

  • This method is only used to resuce a single table without backup when MySQL instance cannot startup beause of the destoryed ibdata file.
  • mysqlfrm is a tool which can load table structure from .frm files.We need to install the mysql-utilities package first.
  • If we don't have a properly full Xtrabackup and binlog,the .ibd file may lose the undo information on target table.In this situation,it's an incompletely recovery.

 

 1 (root@localhost mysql3306.sock)[tt]>alter table sbtest2 discard tablespace;
 2 Query OK, 0 rows affected (0.01 sec)
 3 
 4 (root@localhost mysql3306.sock)[tt]>alter table sbtest2 discard tablespace;
 5 Query OK, 0 rows affected, 1 warning (0.00 sec)
 6 
 7 (root@localhost mysql3306.sock)[tt]>show warnings;
 8  --------- ------ ----------------------------------------------------- 
 9 | Level   | Code | Message                                             |
10  --------- ------ ----------------------------------------------------- 
11 | Warning | 1812 | InnoDB: Tablespace is missing for table tt/sbtest2. |
12  --------- ------ ----------------------------------------------------- 
13 1 row in set (0.00 sec)
14 
15 2018-07-05T08:52:55.055225Z 11 [ERROR] InnoDB: Cannot delete tablespace 494 because it is not found in the tablespace memory cache.
16 2018-07-05T08:52:55.055226Z 11 [Warning] InnoDB: Cannot delete tablespace 494 in DISCARD TABLESPACE: Tablespace not found
17 
18 //error log shows the ERROR & Warning because of the .ibd file has been deleted in first discard operation.

Suppose that this data file, over time, has grown to 988MB. Here is the configuration line after modifying the original data file to use a fixed size and adding a new auto-extending data file:

 

**Import tablespaces and check data of tables.**

 

比如那些数据文件,随着时间已经增进到988MB。这里就配置修改了原本数据利用四个固定的深浅并加上二个新的电动扩大的数据文件:

 1 root@localhost:mysql3306.sock [(none)]>alter table sysbench.sbtest1 import tablespace;
 2 Query OK, 0 rows affected, 1 warning (1.73 sec)
 3 
 4 root@localhost:mysql3306.sock [(none)]>show warnings;
 5  --------- ------ -------------------------------------------------------------------------------------------------------------------------------------------------- 
 6 | Level   | Code | Message                                                                                                                                          |
 7  --------- ------ -------------------------------------------------------------------------------------------------------------------------------------------------- 
 8 | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './sysbench/sbtest1.cfg', will attempt to import without schema verification |
 9  --------- ------ -------------------------------------------------------------------------------------------------------------------------------------------------- 
10 1 row in set (0.00 sec)
11 
12 root@localhost:mysql3306.sock [(none)]>alter table zlm.test_flashbk import tablespace;
13 Query OK, 0 rows affected, 1 warning (1.01 sec)
14 
15 root@localhost:mysql3306.sock [(none)]>show warnings;
16  --------- ------ -------------------------------------------------------------------------------------------------------------------------------------------------- 
17 | Level   | Code | Message                                                                                                                                          |
18  --------- ------ -------------------------------------------------------------------------------------------------------------------------------------------------- 
19 | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './zlm/test_flashbk.cfg', will attempt to import without schema verification |
20  --------- ------ -------------------------------------------------------------------------------------------------------------------------------------------------- 
21 1 row in set (0.00 sec)
22 
23 root@localhost:mysql3306.sock [(none)]>select count(*) from sysbench.sbtest1;
24  ---------- 
25 | count(*) |
26  ---------- 
27 |   100000 |
28  ---------- 
29 1 row in set (0.25 sec)
30 
31 root@localhost:mysql3306.sock [(none)]>select count(*) from zlm.test_flashbk;
32  ---------- 
33 | count(*) |
34  ---------- 
35 |   100000 |
36  ---------- 
37 1 row in set (0.10 sec)
38 
39 //The warnings show that tthe message about missing of .cfg file what rally doesn't matter.
40 //The .cfg file is usually create by executing "flush table ... for export;"
41 //We can benifit in crash recover scenario with the support of ignoring the missing of .cfg in transportable tablespace feature.

**Copy those files to target again.**

 

 

1 [root@zlm2 11:00:05 /data/mysql/mysql3306/data/sysbench]
2 #scp sbtest2.{ibd,cfg} zlm3:/data/mysql/mysql3306/data/tt/
3 root@zlm3's password: 
4 sbtest2.ibd                                                                                                        100%   29MB  29.0MB/s   00:01    
5 sbtest2.cfg: No such file or directory
6 
7 //Because of "unlock tables" operation,the .cfg file has gone now.

innodb_data_home_dir =

*Summary*

 

innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

  • *mysqlfrm is a tool of mysql-utilities which is specialized in analyzing .frm files in order to gain the missing structure of tables.*
  • mysqlfrm provides two modes of operation:1. connecting to server with "--server"(defaut mode);2. create a new instance with "--basedir".
  • With the help of parameter "--diagnostic",we can even get information from a .frm file without installing a MySQL server on the host.
  • We cannot get character set and collation information on tables if we forget to use "--server" option.
  • Of course,in order to use transportable tablespace properly,the parameter "innodb_file_per_table=1" is necessary.
  • In my case above,the structure of table about char datatype changed accidently which I'm still baffled with.
  • Also,we can restore these table to any other server like transportable tablespace does.

**Import the "sbtest2" tablespace again.**

 

 

 1 (root@localhost mysql3306.sock)[tt]>alter table sbtest2 import tablespace;
 2 Query OK, 0 rows affected, 1 warning (2.34 sec)
 3 
 4 (root@localhost mysql3306.sock)[tt]>show warnings;
 5  --------- ------ -------------------------------------------------------------------------------------------------------------------------------------------- 
 6 | Level   | Code | Message                                                                                                                                    |
 7  --------- ------ -------------------------------------------------------------------------------------------------------------------------------------------- 
 8 | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './tt/sbtest2.cfg', will attempt to import without schema verification |
 9  --------- ------ -------------------------------------------------------------------------------------------------------------------------------------------- 
10 1 row in set (0.00 sec)
11 
12 //There's a warning about importing without .cfg file which won't impact the result.

When you add a new data file to the system tablespace configuration, make sure that the filename does not refer to an existing file. InnoDB creates and initializes the file when you restart the server.

  

当你要像系统表空间增加八个新的数据文件的时候,要保管文件名不会引用到存活的文书。InnoDB会在您重启实例的时候创设并开头化这么些文件。

Summary

 

  • ***Transportable Tablespace(TT) of innodb provids a different way in backing up and restoring a single table between servers.
  • ***TT merely supports innodb engine which can store data in tablespaces of their own by setting "innodb_file_per_table=1".
  • TT supports importing tablespace without .cfg file what brings about us much convenience in crash recovery.***
  • Notice that there will be shared read locks on the tables after execute "flush table ... for export;" what really influences the tables need to be write.

Decreasing the Size of the InnoDB System Tablespace

 

 

You cannot remove a data file from the system tablespace. To decrease the system tablespace size, use this procedure:

您无法重系统表空间里移除数据文件。要减小系统表空间尺寸的话能够拓宽如下的操作:

 

1.Use mysqldump to dump all your InnoDB tables, including InnoDB tables located in the MySQL database. As of 5.6, there are five InnoDB tables included in the MySQL database:

1.用到mysqldump导出您具有的InnoDB表,包罗MySQL数据Curry的InnoDB表。从5.6方始,包蕴在MySQL数据Curry的有5个InnoDB表:

 

mysql> select table_name from information_schema.tables where table_schema='mysql' and engine='InnoDB';

----------------------

| table_name |

----------------------

| innodb_index_stats |

| innodb_table_stats |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

----------------------

5 rows in set (0.00 sec)

 

 

2.Stop the server.

2.关门实例。

 

3.Remove all the existing tablespace files (*.ibd), including the ibdata and ib_log files. Do not forget to remove *.ibd files for tables located in the MySQL database.

3.移除全数现成的表空间文件(*.ibd),包括ibdata 和ib_log文件。还只怕有要不忘怀移除MySQL数据Curry的*.ibd文件。

 

4.Remove any .frm files for InnoDB tables.

4.移除InnoDB表的.frm文件。

 

5.Configure a new tablespace.

5.布署三个新的表空间。

 

6.Restart the server.

6.重启实例。

 

7.Import the dump files.

7.导入dump文件。

 

Note

 

If your databases only use the InnoDB engine, it may be simpler to dump all databases, stop the server, remove all databases and InnoDB log files, restart the server, and import the dump files.

一旦您的数据库只利用了InnoDB引擎,那么最简易的办法是导出全部的数据库,甘休实例,移除全数的数据库和InnoDB日志文件,重启实例,再导入dump文件。

 

14.5.2 Changing the Number or Size of InnoDB Redo Log Files

 

To change the number or size of InnoDB redo log files in MySQL 5.6.7 or earlier, perform the following steps:

在MySQL5.6.7照旧更早的本子退换InnoDB redo log文件的数量或大小,要实施上边包车型客车手续:

 

1.If innodb_fast_shutdown is set to 2, set innodb_fast_shutdown to 1:

1.如果innodb_fast_shutdown的设置成2,那么改成1:

 

mysql> SET GLOBAL innodb_fast_shutdown = 1;

 

2.After ensuring that innodb_fast_shutdown is not set to 2, stop the MySQL server and make sure that it shuts down without errors (to ensure that there is no information for outstanding transactions in the log).

2.在确保innodb_fast_shutdown设置的不是2之后,停止MySQL实例并保管没错误(确定保证在log里面未有未形成业务的音信)。

 

3.Copy the old log files into a safe place in case something went wrong during the shutdown and you need them to recover the tablespace.

3.把老的log文件复制到一个白山的地点,以幸免关闭报错的时候用来recover表空间。

 

4.Delete the old log files from the log file directory.

4.在日记文件目录删除老的日志文件。

 

5.Edit my.cnf to change the log file configuration.

5.编辑my.cnf修改日志文件配置。

 

6.Start the MySQL server again. mysqld sees that no InnoDB log files exist at startup and creates new ones.

6.初叶MySQL实例。mysqld的在开发银行的时候见到未有InnoDB日志文件会创设二个新的。

 

As of MySQL 5.6.8, the innodb_fast_shutdown setting is no longer relevant when changing the number or the size of InnoDB log files. Additionally, you are no longer required remove old log files, although you may still want to copy the old log files to a safe place, as a backup. To change the number or size of InnoDB log files, perform the following steps:

从MySQL5.6.8开始,innodb_fast_shutdown设置已经和退换InnoDB日志文件的数量或大小未有涉及了。其余,你也不再需求移除老的日记文件,当然你还是能把老的日志文件复制到二个康宁的地点作为备份。要修改InnoDB日志文件的多少或大小能够施行以下的手续:

 

1.Stop the MySQL server and make sure that it shuts down without errors.

1.关闭MySQL实例并确定保证未有报错。

 

2.Edit my.cnf to change the log file configuration. To change the log file size, configure innodb_log_file_size. To increase the number of log files, configure innodb_log_files_in_group.

2.编写翻译my.cnf文件修改日志文件配置。innodb_log_file_size配置日志文件的轻重。innodb_log_files_in_group配置日志文件的数目。

 

3.Start the MySQL server again.

3.启动MySQL实例。

 

If InnoDB detects that the innodb_log_file_size differs from the redo log file size, it will write a log checkpoint, close and remove the old log files, create new log files at the requested size, and open the new log files.

如果InnoDB发现innodb_log_file_size的值不一致于redo log文件的尺寸,它会写下日志的checkpoint,关闭并删除老的日记文件,再次创下设一个合乎大小要求的新的日志文件,最终张开新的日志文件。

 

14.5.3 Using Raw Disk Partitions for the System Tablespace

 

You can use raw disk partitions as data files in the InnoDB system tablespace. This technique enables nonbuffered I/O on Windows and on some Linux and Unix systems without file system overhead. Perform tests with and without raw partitions to verify whether this change actually improves performance on your system.

您能够利用raw磁盘分区来作为InnoDB系统表空间的数据文件。这种格局能够使得在Windows以及一些Linux平台上利用未有buffer的I/O,这样就减少了文件系统的消耗。分别对应用或没动用raw分区的事态打开测量试验,经验证那样的修改对于的您系统是或不是有实际的属性提高。

 

When you use a raw disk partition, ensure that the user ID that runs the MySQL server has read and write privileges for that partition. For example, if you run the server as the mysql user, the partition must be readable and writeable by mysql. If you run the server with the --memlock option, the server must be run as root, so the partition must be readable and writeable by root.

当您使用了raw磁盘分区,要确认保证运维MySQL实例的客户ID有读写分区的权杖。举例,你以mysql客户来运行实例的,那么mysql顾客对于分区必要求能读能写。假如您想要以--memlock的参数运营的实例,那应当要以root来运营,那么分区对于root也必需是要可读可写的。

 

The procedures described below involve option file modification. For additional information, see Section 4.2.6, "Using Option Files".

上面涉及的布局文件修改的连锁新闻能够查阅Section 4.2.6, "Using Option Files"。

 

Allocating a Raw Disk Partition on Linux and Unix Systems

 

1.When you create a new data file, specify the keyword newraw immediately after the data file size for the innodb_data_file_path option. The partition must be at least as large as the size that you specify. Note that 1MB in InnoDB is 1024 × 1024 bytes, whereas 1MB in disk specifications usually means 1,000,000 bytes.

1.当您创制多少个新数据文件的时候,在innodb_data_file_path文件大小的背后钦命关键字newraw。分区也不能够不至少到达你钦定的尺寸。还应该有要专一的是在InnoDB里面1MB等于1024 × 1024 bytes,然则在磁盘规格上1MB平时是1,000,000 bytes。

 

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

 

2.Restart the server. InnoDB notices the newraw keyword and initializes the new partition. However, do not create or change any InnoDB tables yet. Otherwise, when you next restart the server, InnoDB reinitializes the partition and your changes are lost. (As a safety measure InnoDB prevents users from modifying data when any partition with newraw is specified.)

2.重启实例。InnoDB注意到newraw关键字会早先化那个新的分区。可是,不会创立或涂改任何的InnoDB表。要不然,当您再一次重启实例的时候,InnoDB会再次起始化分区,你的修改也会放任。(作为一种安全的艺术当别的分区内定了newraw,InnoDB会阻止客商修改数据。)

 

3.After InnoDB has initialized the new partition, stop the server, change newraw in the data file specification to raw:

3.在InnoDB初始完新的分区之后,甘休实例,为任何的raw数据文件钦命newraw :

 

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw

 

4.Restart the server. InnoDB now permits changes to be made.

4.重启实例。现在InnoDB允许能够修改了。

 

Allocating a Raw Disk Partition on Windows

 

On Windows systems, the same steps and accompanying guidelines described for Linux and Unix systems apply except that the innodb_data_file_path setting differs slightly on Windows.

在Windows系统上,除了innodb_data_file_path的安排有微微例外,别的的步子和Linux平台的等同。

 

1.When you create a new data file, specify the keyword newraw immediately after the data file size for the innodb_data_file_path option:

1.当您创立多少个新数据文件的时候,在innodb_data_file_path的数据文件的分寸前面钦点keyword 关键字:

 

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=//./D::10Gnewraw

 

The //./ corresponds to the Windows syntax of \. for accessing physical drives. In the example above, D: is the drive letter of the partition.

//./对应Windows的\.。在地方的事例里,D: 是分区的驱动字符。

 

2.Restart the server. InnoDB notices the newraw keyword and initializes the new partition.

2.重启实例。InnoDB注意到newraw 关键字会初阶化新的分区。

 

3.After InnoDB has initialized the new partition, stop the server, change newraw in the data file specification to raw:

3.在InnoDB初阶完新的分区之后,截止实例,为另外的raw数据文件钦赐newraw:

 

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=//./D::10Graw

 

4.Restart the server. InnoDB now permits changes to be made.

4.重启实例。InnoDB未来同意修改。

 

14.5.4 InnoDB File-Per-Table Tablespaces

 

14.5.4.1 Enabling and Disabling File-Per-Table Tablespaces

 

Historically, all InnoDB tables and indexes were stored in the system tablespace. This monolithic approach was targeted at machines dedicated entirely to database processing, with carefully planned data growth, where any disk storage allocated to MySQL would never be needed for other purposes. InnoDB's file-per-table tablespace feature provides a more flexible alternative, where each InnoDB table and its indexes are stored in a separate .ibd data file. Each such .ibd data file represents an individual tablespace. This feature is controlled by the innodb_file_per_table configuration option, which is enabled by default in MySQL 5.6.6 and higher.

在原先,全体的InnoDB表和目录都以积累在系统表空间里的。这种单片的主意是对准于全部机器都以用来数据库管理的,那样可紧凑安插多少的加强,MySQL分配的任何磁盘存款和储蓄也不会用于其余的目标。InnoDB file-per-table 表空间特点提供了一种越来越灵敏的不二等秘书诀,各个InnoDB的表和索引都存储在各自.ibd数据文件里。每一种.ibd数据文件都意味着了一个独立的表空间。这种特点是由innodb_file_per_table配置参数调控的,在MySQL5.6.6及更高的本子里暗中认可是敞开的。

 

Advantages of File-Per-Table Tablespaces

 

  • You can reclaim disk space when truncating or dropping a table stored in a file-per-table tablepace. Truncating or dropping tables stored in the system tablespace creates free space internally in the system tablespace data files (ibdata files) which can only be used for new InnoDB data.

  • 当truncate或许drop存储在file-per-table表空间里的表的时候,能够回收磁盘空间。而truncate或然drop存款和储蓄在系统表空间里的表时回收的半空中只可以给新的InnoDB数据应用。

 

  • The TRUNCATE TABLE operation is faster when run on tables stored in file-per-table tablepaces.

  • 表存款和储蓄在file-per-table表空间上,运转TRUNCATE TABLE操作速度更加快。

 

  • You can store specific tables on separate storage devices, for I/O optimization, space management, or backup purposes. In previous releases, you had to move entire database directories to other drives and create symbolic links in the MySQL data directory, as described in Section 8.12.4, "Using Symbolic Links". In MySQL 5.6.6 and higher, you can specify the location of each table using the syntax CREATE TABLE ... DATA DIRECTORY = absolute_path_to_directory, as explained in Section 14.5.5, "Creating a File-Per-Table Tablespace Outside the Data Directory".

  • 您可以为I/O优化,空间管理,只怕备份指标,把钦命的表存储在不一致的贮存磁盘上。在此前的本子,你则要把整个数据目录移动到其余的磁盘上,在成立四个链接指向到MySQL的数额目录,如Section 8.12.4, "Using Symbolic Links"所叙述的。在MySQL5.6.6及越来越高的本子里,你能够通过CREATE TABLE ... DATA DIRECTO昂科拉Y = absolute_path_to_directory来钦命种种表地方,如Section 14.5.5, "Creating a File-Per-Table Tablespace Outside the Data Directory"锁描述的。

 

  • You can run OPTIMIZE TABLE to compact or recreate a file-per-table tablespace. When you run an OPTIMIZE TABLE, InnoDB creates a new .ibd file with a temporary name, using only the space required to store actual data. When the optimization is complete, InnoDB removes the old .ibd file and replaces it with the new one. If the previous .ibd file grew significantly but the actual data only accounted for a portion of its size, running OPTIMIZE TABLE can reclaim the unused space.

  • 您能够运作OPTIMIZE TABLE来压缩可能重新建立贰个file-per-table表空间。当你运转了OPTIMIZE TABLE,InnoDB会采纳一个一时半刻的名字创办二个新的.ibd文件,这么些文件仅攻陷实际多少的大大小小。当OPTIMIZE TABLE操作实现了,InnoDB会删除旧的.ibd文件并用新的来替换它。要是原先的.ibd文件拉长得十分的大而是实际多少只占用了一片段,那么运营OPTIMIZE TABLE可以回收未使用的空中。

 

  • You can move individual InnoDB tables rather than entire databases.

  • 您能够运动个别表而无需动整个数据库。

 

  • You can copy individual InnoDB tables from one MySQL instance to another (known as the transportable tablespace feature).

  • 您能够从四个MySQL实例之中复制个别表到另二个实例之中(表空间传输本性the transportable tablespace feature)。

 

  • Tables created in file-per-table tablespaces use the Barracuda file format. The Barracuda file format enables features such as compressed and dynamic row formats. Tables created in the system tablespace cannot use these features. To take advantage of these features for an existing table, enable the innodb_file_per_table setting and run ALTER TABLE t ENGINE=INNODB to place the table in a file-per-table tablespace. Before converting tables, refer to Section 14.6.4, "Converting Tables from MyISAM to InnoDB".

  • 创造在file-per-table表空间里的表使用了Barracuda的文件格式。Barracuda文件格式具备减弱和动态的行格式。而创办在系统表空间里的表是力不从心使用这么些特征的。现存的表要使用这个特征优势,要起来innodb_file_per_table设定并运营ALTER TABLE t ENGINE=INNODB把表放置到filer-per-table表空间里。在转移以前,请先参谋Section 14.6.4, "Converting Tables from MyISAM to InnoDB"。

 

  • You can enable more efficient storage for tables with large BLOB or TEXT columns using the dynamic row format.

  • 动用动态行格式(dynamic row format)能够使得有众多BLOB或许TEXT列的表在积累上更有效用。

 

  • File-per-table tablespaces may improve chances for a successful recovery and save time when a corruption occurs, when a server cannot be restarted, or when backup and binary logs are unavailable.

  • 当发生故障实例不也许重启的时候,或然备份和二进制日志(binary logs)不能够利用的时候,file-per-table表空间能够提升recovery的功成名就几率并节约一定的年华。

 

  • You can back up or restore individual tables quickly using the MySQL Enterprise Backup product, without interrupting the use of other InnoDB tables. This is beneficial if you have tables that require backup less frequently or on a different backup schedule. See Partial Backup and Restore Options for details.

  • 您能够选拔MySQL Enterprise Backup来急忙备份大概复苏别个的表,而不会搁浅别的InnoDB表的运用。那在这种渴求备份少许一再使用或许差异备份战略的表时就突显煞是有助于了。相关部分能够查阅Partial Backup and Restore Options。

 

  • File-per-table tablespaces are convenient for per-table status reporting when copying or backing up tables.

  • 当复制或许备份表的时候,file-per-table表空间为种种表告诉情形则更低价。

 

  • You can monitor table size at a file system level, without accessing MySQL.

  • 您能够在文件系统层面监察和控制表的分寸,而没有要求走访MySQL。

 

  • Common Linux file systems do not permit concurrent writes to a single file when innodb_flush_method is set to O_DIRECT. As a result, there are possible performance improvements when using file-per-table tablespaces in conjunction with innodb_flush_method.

  • 当innodb_flush_method被设置成O_DIRECT的时候常见的Linux文件系统是差异意对单个文件实行并发写的。由此,当同一时候选用file-per-table表空间以及innodb_flush_method的时候能够提高质量。

 

  • The system tablespace stores the data dictionary and undo logs, and has a 64TB size limit. By comparison, each file-per-table tablespace has a 64TB size limit, which provides you with room for growth. See Section C.10.3, "Limits on Table Size" for related information.

  • 仓库储存数据目录和undo log的体系表空间是有64TB的大大小小限制的。相相比之下,每种表空间各有64TB的限制,那就为您提供了越来越多的拉长空间。详见Section C.10.3, "Limits on Table Size" for related information。

 

Potential Disadvantages of File-Per-Table Tablespaces

 

  • With file-per-table tablespaces, each table may have unused space, which can only be utilized by rows of the same table. This could lead to wasted space if not properly managed.

  • 采用file-per-table表空间,每个表都也有未采纳的上空,而这部分上空只好被它本中国人民银行使。假设管理不力的话就只怕引致空中的浪费。

 

  • fsync operations must run on each open table rather than on a single file. Because there is a separate fsync operation for each file, write operations on multiple tables cannot be combined into a single I/O operation. This may require InnoDB to perform a higher total number of fsync operations.

  • fsync操作必得运转在每一个展开的表上,并非一个独自的文件。因为各类文件分别都有八个fsync操作,所以三个表上的写操作就不能统10%三个I/O操作。那可能会使得InnoDB实行越多的fsync操作。

 

  • mysqld must keep one open file handle per table, which may impact performance if you have numerous tables in file-per-table tablespaces.

  • mysqld必需为各样表保持一个open file handle,倘让你有那个的file-per-table表空间的表就只怕会潜濡默化属性。

 

  • More file descriptors are used.

  • 运用越来越多的file descriptors。

 

  • innodb_file_per_table is enabled by default in MySQL 5.6.6 and higher. You may consider disabling it if backward compatibility with MySQL 5.5 or 5.1 is a concern. Disabling innodb_file_per_table prevents ALTER TABLE from moving an InnoDB table from the system tablespace to an individual .ibd file in cases where ALTER TABLE recreates the table (ALGORITHM=COPY).

  • 在MySQL5.6.6及越来越高版本里innodb_file_per_table私下认可是敞开的。要是要回降到MySQL5.5依旧5.1的话就要思索关闭它。关闭innodb_file_per_table能够阻止ALTER TABLE把一个InnoDB表从系统表空间移动到个别.ibd文件。这种景色下ALTER TABLE会重新创立表(ALGO奇骏ITHM=COPY)。

 

For example, when restructuring the clustered index for an InnoDB table, the table is re-created using the current setting for innodb_file_per_table. This behavior does not apply when adding or dropping InnoDB secondary indexes. When a secondary index is created without rebuilding the table, the index is stored in the same file as the table data, regardless of the current innodb_file_per_table setting.

举例,当为一个InnoDB表重新创设clustered index的时候,使用当前的innodb_file_per_table设定表会重新建立。不过增多或删除InnoDB secondary index的时候不是那般的。当创立二个secondary index而不重新创设表的时候,索引会累积在表数据的同样的文书里,而不管innodb_file_per_table的设定是何许。

 

  • If many tables are growing there is potential for more fragmentation which can impede DROP TABLE and table scan performance. However, when fragmentation is managed, having files in their own tablespace can improve performance.

  • 一经过多的表拉长得有了过多的零碎,那就能妨碍了DROP TABLE和表扫描的习性。可是,碎片能够使得管理的话,file-per-table表空间还能够晋升质量的。

 

  • The buffer pool is scanned when dropping a file-per-table tablespace, which can take several seconds for buffer pools that are tens of gigabytes in size. The scan is performed with a broad internal lock, which may delay other operations. Tables in the system tablespace are not affected.

  • 当删除一个file-per-table表空间的时候是要扫描buffer pool的,那对于有几十二个G的buffer pool也是要开支十分多时辰(several seconds)的。这种扫描使用的是一种广泛的里边锁,那会延迟其余的操作。而在系统表空间里的表则不会有如此的震慑。

 

  • The innodb_autoextend_increment variable, which defines increment size (in MB) for extending the size of an auto-extending shared tablespace file when it becomes full, does not apply to file-per-table tablespace files, which are auto-extending regardless of the innodb_autoextend_increment setting. The initial extensions are by small amounts, after which extensions occur in increments of 4MB.

  • innodb_autoextend_increment变量定义了自行扩展的分享表空间满的时候扩大的尺寸(in MB),但那不适用于file-per-table表空间文件。file-per-table表空间的电动扩大与innodb_autoextend_increment的设定非亲非故。最先的扩展是微量的,那今后会巩固到4MB。

 

14.5.4.1 Enabling and Disabling File-Per-Table Tablespaces

 

The innodb_file_per_table option is enabled by default as of MySQL 5.6.6.

innodb_file_per_table参数在MySQL5.6.6里面私下认可是翻开的。

 

To set the innodb_file_per_table option at startup, start the server with the --innodb_file_per_table command-line option, or add this line to the [mysqld] section of my.cnf:

要在开发银行的时候设定innodb_file_per_table参数,能够在运转的吩咐行里使用--innodb_file_per_table ,或者在my.cnf的 [mysqld]在那之中增加上边包车型客车原委:

 

[mysqld]

innodb_file_per_table=1

 

You can also set innodb_file_per_table dynamically, while the server is running:

您还足以在实例运营的时候动态设定innodb_file_per_table:

 

SET GLOBAL innodb_file_per_table=1;

 

With innodb_file_per_table enabled, you can store InnoDB tables in a tbl_name.ibd file. Unlike the MyISAM storage engine, with its separate tbl_name.MYD and tbl_name.MYI files for indexes and data, InnoDB stores the data and the indexes together in a single .ibd file. The tbl_name.frm file is still created as usual.

在innodb_file_per_table开启的时候,你能够把InnoDB表存储到一个tbl_name.ibd文件里。不像MyISAM存储引擎,分别在tbl_name.MYD and tbl_name.MYI文件里积累索引和数量,InnoDB会把数据和目录都存款和储蓄在一个.ibd文件里。tbl_name.frm平时情状下也依然会被创设的。

 

If you disable innodb_file_per_table in your startup options and restart the server, or disable it with the SET GLOBAL command, InnoDB creates new tables inside the system tablespace.

若果您在运转和重启的时候关闭了innodb_file_per_table,又恐怕选择SET GLOBAL动态关闭了它,InnoDB会把新的表创设到系统表空间里。

 

You can always read and write any InnoDB tables, regardless of the file-per-table setting.

你还能够读写任何的InnoDB表,而不用去管file-per-table的设定。

 

To move a table from the system tablespace to its own tablespace, change the innodb_file_per_table setting and rebuild the table:

要把多个表从系统表空间活动到它本身的表空间里,能够修改innodb_file_per_table的设定一碗水端平建表:

 

SET GLOBAL innodb_file_per_table=1;

ALTER TABLE table_name ENGINE=InnoDB;

 

Note

 

InnoDB always needs the system tablespace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate.

InnoDB如故依然供给系统表空间的,因为中间寄存了里面的数额字典以及undo log。单纯的.ibd文件对此InnoDB的操作还是远远不足的。

 

When a table is moved out of the system tablespace into its own .ibd file, the data files that make up the system tablespace remain the same size. The space formerly occupied by the table can be reused for new InnoDB data, but is not reclaimed for use by the operating system. When moving large InnoDB tables out of the system tablespace, where disk space is limited, you may prefer to enable innodb_file_per_table and recreate the entire instance using the mysqldump command.

当三个表从系统表空间活动到它自身的表空间的时候,系统表空间的轻重缓急不会降价扣。那一个表在此之前占用的半空中能够给新的InnoDB数据接纳,但不会被操作系统回收利用。当要把不知凡几的表从系统表空间里移除的时候要留心磁盘空间的限定,你Infiniti是翻开 innodb_file_per_table在行使mysqldump重城建总公司体实例。

 

14.5.5 Creating a File-Per-Table Tablespace Outside the Data Directory

 

To create a new InnoDB file-per-table tablespace in a specific location outside the MySQL data directory, use the DATA DIRECTORY = absolute_path_to_directory clause of the CREATE TABLE statement.

要在MySQL数据目录之外的地点成立二个新的InnoDB file-per-table表空间,能够在CREATE TABLE 的时候使用DATA DIRECTO福特ExplorerY = absolute_path_to_directory子句。

 

Plan the location in advance, because you cannot use the DATA DIRECTORY clause with the ALTER TABLE statement. The directory you specify could be on another storage device with particular performance or capacity characteristics, such as a fast SSD or a high-capacity HDD.

要事先规划数据寄存的职分,因为您不可能在ALTER TABLE的时候使用DATA DIRECTO奥迪Q5Y子句。你内定的目录能够是 另一个有特意质量优势依旧存款和储蓄工夫的磁盘,举个例子更快的SSD也许更牢靠的HDD。

 

Within the destination directory, MySQL creates a subdirectory corresponding to the database name, and within that a .ibd file for the new table. In the database directory beneath the MySQL DATADIR directory, MySQL creates a table_name.isl file containing the path name for the table. The .isl file is treated by MySQL like a symbolic link. (Using actual symbolic links has never been supported for InnoDB tables.)

在目的目录里,MySQL会创设三个和数据库名相关的子目录,并在其间贮存新建表的ibd文件。MySQL DATADI安德拉目录上边包车型大巴数据库目录里,MySQL会创立二个包括表路线名的 table_name.isl文件。那个.isl文件对MySQL就好似二个symbolic link同样。(InnoDB表从未协理过symbolic links。)

 

The following example demonstrates creating a file-per-table tablespace outside the MySQL data directory. It shows the .ibd created in the specified directory, and the .isl created in the database directory beneath the MySQL data directory.

上边包车型大巴例证示范了在MySQL数据目录之外创制三个file-per-table表空间。这里显得了.ibd钦赐的始建目录,以及MySQL数据目录下的数据库目录里面创制的.isl。

 

mysql> USE test;

Database changed

 

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';

----------------------- -------

| Variable_name | Value |

----------------------- -------

| innodb_file_per_table | ON |

----------------------- -------

1 row in set (0.00 sec)

 

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/alternative/directory';

Query OK, 0 rows affected (0.03 sec)

 

# MySQL creates a .ibd file for the new table in a subdirectory that corresponding

# to the database name

 

db_user@ubuntu:~/alternative/directory/test$ ls

t1.ibd

 

# MySQL creates a .isl file containing the path name for the table in a directory

# beneath the MySQL data directory

 

db_user@ubuntu:~/mysql/data/test$ ls

db.opt t1.frm t1.isl

 

Usage Notes:

 

  • MySQL initially holds the .ibd file open, preventing you from dismounting the device, but might eventually close the table if the server is busy. Be careful not to accidentally dismount an external device while MySQL is running, or to start MySQL while the device is disconnected. Attempting to access a table when the associated .ibd file is missing causes a serious error that requires a server restart.

  • MySQL最早为保持.ibd文件的张开,已幸免你卸载磁盘,但实例繁忙的时候照旧会关闭表的。在MySQL正在运转的时候要警惕不要卸载外界磁盘,有只怕是在未连接磁盘的动静下运维MySQL。尝试去做客那个.idb文件遗失的表会产生惨恻的荒谬并促成实例重启。

 

A server restart might fail if the .ibd file is still not at the expected path. In this case, manually remove the table_name.isl file in the database directory, and after restarting perform a DROP TABLE to delete the .frm file and remove the information about the table from the data dictionary.

假如.idb文件不在其理应在的路径的话,实例重启或然会停业。在这种气象下,在数据库目录里面手动删除table_name.isl文件,在重启之后试行DROP TABLE删除.frm文件同等对待数据目录里面删除那个表的连锁消息。

 

  • Do not put MySQL tables on an NFS-mounted volume. NFS uses a message-passing protocol to write to files, which could cause data inconsistency if network messages are lost or received out of order.

  • 永不把MySQL表放在NFS的卷上。NFS使用的是一种音信传递的商事写文件,即使互联网消息错过大概收受的音信不对大概会促成数据分化样。

 

  • If you use an LVM snapshot, file copy, or other file-based mechanism to back up the .ibd file, always use the FLUSH TABLES ... FOR EXPORT statement first to make sure all changes that were buffered in memory are flushed to disk before the backup occurs.

  • 万一您使用了LVM的快速照相,文件拷贝,或许别的遵照文件的法规来备份.ibd文件,记住在那前面要选拔FLUSH TABLES ... FOR EXPORT语句首先保险全数内部存款和储蓄器里缓存的修改都早就刷新的到磁盘上。

 

  • The DATA DIRECTORY clause is a supported alternative to using symbolic links, which has always been problematic and was never supported for individual InnoDB tables.

  • 周旋于采取symbolic links,DATA DIRECTOEvoqueY子句InnoDB是永葆的。而symbolic links则直接是个难点,并且对于单个的InnoDB表是未曾协理过的。

 

14.5.6 Copying File-Per-Table Tablespaces to Another Server

 

This section describes how to copy file-per-table tablespaces from one database server to another, otherwise known as the Transportable Tablespaces feature.

这一章节呈报了表空间传输(Transportable Tablespaces)的特点,把一个file-per-table表空间从一个数据库实例复制到到另三个。

 

For information about other InnoDB table copying methods, see Section 14.6.2, "Moving or Copying InnoDB Tables to Another Machine".

InnoDB表复制的的别的方法可知Section 14.6.2, "Moving or Copying InnoDB Tables to Another Machine"。

 

There are many reasons why you might copy an InnoDB file-per-table tablespace to a different database server:

上边是描述的是为啥要把贰个InnoDB file-per-table表空间复制到另三个实例上:

 

  • To run reports without putting extra load on a production server.

  • 在生养实例上在不开展额外的多寡加载的状态下运营报表。

 

  • To set up identical data for a table on a new slave server.

  • 在一个新的slave实例上创建一份一样的表数据。

 

  • To restore a backed-up version of a table after a problem or mistake.

  • 出现难题也许人工错误之后苏醒表的贰个备份版本。

 

  • As a faster way of moving data around than importing the results of a mysqldump command. The data is available immediately, rather than having to be re-inserted and the indexes rebuilt.

  • 比较mysqldump的导入那是贰个更是火速的数额迁移方式。因为数量是及时可知的,无需再一次insert和确立目录。

 

  • To move a file-per-table tablespace to a server with storage medium that better suits system requirements. For example, you may want to have busy tables on an SSD device, or large tables on a high-capacity HDD device.

  • 透过存款和储蓄媒介把转变file-per-table表空间更符合系统供给。比方,你想把繁忙的表放到SSD磁盘上,恐怕把大的表发到大容积的HDD磁盘上。

 

Limitations and Usage Notes

 

  • The tablespace copy procedure is only possible when innodb_file_per_table is set to ON, which is the default setting as of MySQL 5.6.6. Tables residing in the shared system tablespace cannot be quiesced.

  • 只有innodb_file_per_table被设置成ON的时候表空间复制才是恐怕的。从MySQL5.6.6起头私下认可都以开垦的。在分享的体系表空间里的表是不扶助的(cannot be quiesced)。

 

  • When a table is quiesced, only read-only transactions are allowed on the affected table.

  • 当表是静默的(quiesced),那么这些表值允许只读事务。

 

  • When importing a tablespace, the page size must match the page size of the importing instance.

  • 当导入多个表空间的时候,页的轻重必须要和导入的实例相称。

 

  • DISCARD TABLESPACE is not supported for partitioned tables meaning that transportable tablespaces is also unsupported. If you run ALTER TABLE ... DISCARD TABLESPACE on a partitioned table, the following error is returned: ERROR 1031 (HY000): Table storage engine for 'part' doesn't have this option.

  • 分区表不支持DISCA大切诺基D TABLESPACE意味着表空间传输也不支持。倘若你在二个分区表上运转ALTER TABLE ... DISCA奥迪Q7D TABLESPACE,会报上面包车型大巴荒唐:E大切诺基RO中华V 1031 (HY000): Table storage engine for 'part' doesn't have this option。

 

  • DISCARD TABLESPACE is not supported for tablespaces with a parent-child (primary key-foreign key) relationship when foreign_key_checks is set to 1. Before discarding a tablespace for parent-child tables, set foreign_key_checks=0.

  • 当oreign_key_checks被设置成1的时候有老爹和儿子(primary key-foreign key)关系的表是不支持DISCA途乐D TABLESPACE的。在discard一个有父子关系的表在此以前,要安装foreign_key_checks=0。

 

  • ALTER TABLE ... IMPORT TABLESPACE does not enforce foreign key constraints on imported data. If there are foreign key constraints between tables, all tables should be exported at the same (logical) point in time.

  • ALTE大切诺基 TABLE ... IMPORT TABLESPACE不能够在导入的数量上进行外键约束。即使表上有外键,那么就亟须在同二个日子点一回导出全数的表。

 

  • ALTER TABLE ... IMPORT TABLESPACE does not require a .cfg metadata file to import a tablespace. However, metadata checks are not performed when importing without a .cfg file, and a warning similar to the following will be issued:

  • ALTE福睿斯 TABLE ... IMPORT TABLESPACE无需把.cfg元数据文件导入到表空间。然则,不导入.cfg文件元数据检查也不会实施,也就可以博得上边包车型地铁警告:

 

Message: InnoDB: IO Read error: (2, No such file or directory) Error opening '.

testt.cfg', will attempt to import without schema verification

1 row in set (0.00 sec)

 

The ability to import without a .cfg file may be more convenient when no schema mismatches are expected. Additionally, the ability to import without a .cfg file could be useful in crash recovery scenarios in which metadata cannot be collected from an .ibd file.

当预期未有schema不相称的时候,不导入.cfg文件大概会更利于。其他,在崩溃苏醒的场地中不导入.cfg会更有用处,因为.idb文件是不会征集元数据的。

 

  • In MySQL 5.6 or later, importing a tablespace file from another server works if both servers have GA (General Availability) status and their versions are within the same series. Otherwise, the file must have been created on the server into which it is imported.

  • 在MySQL5.6及越来越高的版本里,即使实例都是GA(General Availability)状态,而且版本同样,就足以从另贰个实例之中程导弹入表空间文件。不然,被导入的实例则就务须创制文件。

 

  • In replication scenarios, innodb_file_per_table must be set to ON on both the master and slave.

  • 在复制场景中,主从都不可能不把innodb_file_per_table设置成ON。

 

  • On Windows, InnoDB stores database, tablespace, and table names internally in lowercase. To avoid import problems on case-sensitive operating systems such as Linux and UNIX, create all databases, tablespaces, and tables using lowercase names. A convenient way to accomplish this is to add the following line to the [mysqld] section of your my.cnf or my.ini file before creating databases, tablespaces, or tables:

  • 在Windows平台上,InnoDB的多少库名,表空间名,和表名都以以小写的方式积攒的。为了防止在Linux和Unix这种差别轻重缓急写平台上的导入难题,最棒全体成立的数目库名,表空间名,和表名都用小写。还也是有一种有益的艺术在创制数据库,表空间和表在此之前能够完成那样的功用,正是是my.cnf可能my.ini配置文件的[mysqld]里投入上边包车型大巴内容:

 

[mysqld]

lower_case_table_names=1

 

Example: Copying a File-Per-Table Tablespace From One Server To Another

 

This procedure demonstrates how to copy a table stored in a file-per-table tablespace from a running MySQL server instance to another running instance. The same procedure with minor adjustments can be used to perform a full table restore on the same instance.

下边包车型客车进度演示了哪些从三个正值运行的实例里把一个积累在file-per-table表空间里的表复制到另多少个正值运维的实例上。对于在同一个实例上海展览中心开全表的大张旗鼓只需求对那么些历程进展微小的调整。

 

1.On the source server, create a table if one does not already exist:

在源端创立以二个例外名的新表:

 

mysql> use test;

mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

 

2.On the destination server, create a table if one does not exist:

在远端也创制同样的叁个表:

 

mysql> use test;

mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

 

3.On the destination server, discard the existing tablespace. (Before a tablespace can be imported, InnoDB must discard the tablespace that is attached to the receiving table.)

在远端,discard现存的表空间。(在表空间导入在此之前,InnoDB必得discard表空间再黏附收到的表。)

 

mysql> ALTER TABLE t DISCARD TABLESPACE;

 

4.On the source server, run FLUSH TABLES ... FOR EXPORT to quiesce the table and create the .cfg metadata file:

在源端,运维FLUSH TABLES ... FOR EXPORT静默(quiesce )表并创设.cfg元数据文件:

 

mysql> use test;

mysql> FLUSH TABLES t FOR EXPORT;

 

The metadata (.cfg) file is created in the InnoDB data directory.

元数据(.cfg)文件创设在InnoDB的多少目录里。

 

Note

 

FLUSH TABLES ... FOR EXPORT is available as of MySQL 5.6.6. The statement ensures that changes to the named tables have been flushed to disk so that binary table copies can be made while the server is running. When FLUSH TABLES ... FOR EXPORT is run, InnoDB produces a .cfg file in the same database directory as the table. The .cfg file contains metadata used for schema verification when importing the tablespace file.

FLUSH TABLES ... FOR EXPORT是从MySQL5.6.6才起来可用的。那几个讲话会确认保障表的翻新已经被刷新到了磁盘上,那样二进制的表文件本事在实例正在周转的情形下展开复制。当运营FLUSH TABLES ... FOR EXPORT的时候,InnoDB会在表一样的数据库目录上面产生多个.cfg文件。那个.cfg文件包涵了在导入表空间文件时用于schema验证的元数据。

 

5.Copy the .ibd file and .cfg metadata file from the source server to the destination server. For example:

从源端复制.ibd文件以及.cfg元数据文件到远端:

 

shell> scp /path/to/datadir/test/t.{ibd,cfg} destination-server:/path/to/datadir/test

 

Note

 

The .ibd file and .cfg file must be copied before releasing the shared locks, as described in the next step.

.idb文件和.cfg文件必需在出狱分享锁以前复制,正如下一步所陈述的。

 

6.On the source server, use UNLOCK TABLES to release the locks acquired by FLUSH TABLES ... FOR EXPORT:

在源端,使用UNLOCK TABLES释放具备FLUSH TABLES ... FO奥德赛 EXPORT获取的锁:

 

mysql> use test;

mysql> UNLOCK TABLES;

 

7.On the destination server, import the tablespace:

在远端,导入表空间:

 

mysql> use test;

mysql> ALTER TABLE t IMPORT TABLESPACE;

 

Note

 

The ALTER TABLE ... IMPORT TABLESPACE feature does not enforce foreign key constraints on imported data. If there are foreign key constraints between tables, all tables should be exported at the same (logical) point in time. In this case you would stop updating the tables, commit all transactions, acquire shared locks on the tables, and then perform the export operation.

ALTE奇骏 TABLE ... IMPORT TABLESPACE性子不会在导入的多寡上举行外键约束。若是表上并未有外键约束,全部的表必得在同三个时刻点上一回都导出。这种景观下您要停下对表的换代,提交全体的业务,在表上施加分享锁,之后能力实践导出操作。

 

Transportable Tablespace Internals

 

The following information describes internals and error log messaging for the transportable tablespaces copy procedure.

上面包车型地铁源委陈述了表空间传输进程中的内部原理和错误日志音讯。

 

When ALTER TABLE ... DISCARD TABLESPACE is run on the destination instance:

当在远端实例运维ALTEEscort TABLE ... DISCA凯雷德D TABLESPACE的时候:

 

  • The table is locked in X mode.

  • 表会加X锁。

 

  • The tablespace is detached from the table.

  • 表空间会从表上分别。

 

When FLUSH TABLES ... FOR EXPORT is run on the source instance:

当在源端运维FLUSH TABLES ... FOEscort EXPORT的时候:

 

  • The table being flushed for export is locked in shared mode.

  • 要导出的表会被刷新的磁盘上,并加分享锁。

 

  • The purge coordinator thread is stopped.

  • purge和睦线程会结束。

 

  • Dirty pages are synchronized to disk.

  • 脏数据页会同步上磁盘上。

 

  • Table metadata is written to the binary .cfg file.

  • 表的元数据会写入到二进制的.cfg文件里。

 

Expected error log messages for this operation:

其一操作在错误日志里预期的音讯如下:

 

2013-07-18 14:47:31 34471 [Note] InnoDB: Sync to disk of '"test"."t"' started.

2013-07-18 14:47:31 34471 [Note] InnoDB: Stopping purge

2013-07-18 14:47:31 34471 [Note] InnoDB: Writing table metadata to './test/t.cfg'

2013-07-18 14:47:31 34471 [Note] InnoDB: Table '"test"."t"' flushed to disk

 

When UNLOCK TABLES is run on the source instance:

当在源端运转UNLOCK TABLES的时候:

 

  • The binary .cfg file is deleted.

  • 二进制的.cfg文件被删去。

 

  • The shared lock on the table or tables being imported is released and the purge coordinator thread is restarted.

  • 表上的分享锁被放走,重启purge和煦线程。

 

Expected error log messages for this operation:

这几个操作在错误日志里预期的信息如下:

 

2013-07-18 15:01:40 34471 [Note] InnoDB: Deleting the meta-data file './test/t.cfg'

2013-07-18 15:01:40 34471 [Note] InnoDB: Resuming purge

 

When ALTER TABLE ... IMPORT TABLESPACE is run on the destination instance, the import algorithm performs the following operations for each tablespace being imported:

当在远端实例运维ALTE牧马人 TABLE ... IMPORT TABLESPACE的时候,导入算法会为每种导入的表空间进行下边包车型客车操作:

 

  • Each tablespace page is checked for corruption.

  • 各种表空间的数据页都会进展破坏检查。

 

  • The space ID and log sequence numbers (LSNs) on each page are updated

  • 每一个数据页上的space ID and log sequence numbers (LSNs)都会被更新。

 

  • Flags are validated and LSN updated for the header page.

  • 多少页头的确定标记和LSN会被更新。

 

  • Btree pages are updated.

  • 更新Btree数据页。

 

  • The page state is set to dirty so that it will be written to disk.

  • 数据页的图景被设置成dirty以便能够被写入磁盘。

 

Expected error log messages for this operation:

这一个操作在错误日志里预期的新闻如下:

 

2013-07-18 15:15:01 34960 [Note] InnoDB: Importing tablespace for table 'test/t' that was exported from host 'ubuntu'

2013-07-18 15:15:01 34960 [Note] InnoDB: Phase I - Update all pages

2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk

2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk - done!

2013-07-18 15:15:01 34960 [Note] InnoDB: Phase III - Flush changes to disk

2013-07-18 15:15:01 34960 [Note] InnoDB: Phase IV - Flush complete

 

Note

 

You may also receive a warning that a tablespace is discarded (if you discarded the tablespace for the destination table) and a message stating that statistics could not be calculated due to a missing .ibd file:

表空被分开的时候你也大概会收到二个告诫(假令你是为远端的表举办表空间的分开):由于错过了.ibd文件不能够测算总括新闻:

 

2013-07-18 15:14:38 34960 [Warning] InnoDB: Table "test"."t" tablespace is set as discarded.

2013-07-18 15:14:38 7f34d9a37700 InnoDB: cannot calculate statistics for table "test"."t" because the .ibd file is missing. For help, please refer to

 

14.5.7 Storing InnoDB Undo Logs in Separate Tablespaces

 

As of MySQL 5.6.3, you can store InnoDB undo logs in one or more separate undo tablespaces outside of the system tablespace. This layout is different from the default configuration where the undo log is part of the system tablespace. The I/O patterns for the undo log make these tablespaces good candidates to move to SSD storage, while keeping the system tablespace on hard disk storage. Users cannot drop the separate tablespaces created to hold InnoDB undo logs, or the individual segments inside those tablespaces.

从MySQL5.6.3初始,你可以把InnoDB的undo log存款和储蓄在三个要么四个分别在系统表空间之外的undo表空间里。这种布局不是暗中同意的布局,私下认可的是undo log是被停放在系统表空间里的。undo log的I/O形式使得最棒能够把这么些undo表空间活动到SSD存款和储蓄上,而把系统表空间位于平日的硬盘上。客户是不可能删除用于寄存InnoDB undo log的表空间的,又或然是这个表空间里的各自segment。

 

Because these files handle I/O operations formerly done inside the system tablespace, we broaden the definition of system tablespace to include these new files.

因为过去这么些文件都是在系统表空间里管理I/O操作的,大家放松的种类表空间定义也囊括这几个新的文本。

 

Undo logs are also referred to as rollback segments.

undo log依然被叫作回滚段。

 

This feature involves the following new or renamed configuration options:

新的性情涉及了下面新的依旧重命令的布置参数:

 

  • innodb_undo_tablespaces

 

  • innodb_undo_directory

 

  • innodb_rollback_segments becomes innodb_undo_logs. The old name is still available for compatibility.

  • innodb_rollback_segments变成了innodb_undo_logs。因为包容性难点老的名字照旧可用。

 

Because the InnoDB undo log feature involves setting two non-dynamic startup variables (innodb_undo_tablespaces and innodb_undo_directory), this feature can only be enabled when initializing a MySQL instance.

因为InnoDB undo log的特点涉及了设置五个非动态的运营变量(innodb_undo_tablespaces and innodb_undo_directory),所以唯有在起先化MySQL实例的时候本事开发银行新的性状。

 

Usage Notes

 

To use this feature, follow these steps:

使用新的特点要追随上边包车型地铁步调:

 

1.Decide on a path to hold the undo logs. You will specify that path as the argument to the innodb_undo_directory option in your MySQL configuration file or startup script. For embedded MySQL installations, an absolute path must be specified.

垄断(monopoly)保存undo log的路子。钦定那一个路子作为MySQL配置文件恐怕运营脚本里innodb_undo_directory参数的值。对于嵌入式的MySQL,则内定的必得是相对路线。

 

2.Decide on a starting value for the innodb_undo_logs option. You can start with a relatively low value and increase it over time to examine the effect on performance.

决定innodb_undo_logs参数的起初值。开始的时候你可以安装一个针锋相对不大的值,随着时光测量检验对质量的熏陶来稳步扩充。

 

3.Decide on a non-zero value for the innodb_undo_tablespaces option. The multiple undo logs specified by the innodb_undo_logs value are divided between this number of separate tablespaces (represented by .ibd files). This value is fixed for the life of the MySQL instance, so if you are uncertain about the optimal value, estimate on the high side.

为innodb_undo_tablespaces参数决定一个非0的值。innodb_undo_logs的值钦赐了细分三个undo log表空间(代表.ibd文件)。那些值对MySQL实例一初阶正是确定地点好的,所以只要您不确认那么些参数的值,那就预估三个针锋相对十分的大。

 

4.Create a new MySQL instance, using the values you chose in the configuration file or in your MySQL startup script. Use a realistic workload with data volume similar to your production servers. Alternatively, use the transportable tablespaces feature to copy existing database tables to your newly configured MySQL instance. See Section 14.5.6, "Copying File-Per-Table Tablespaces to Another Server" for more information.

你能够在安插文件大概MySQL的启航脚本里面使用你选拔的值来创立二个新的MySQL实例。使用和生育情状一般的数据负载量,也许从现成的数据Curry使用表空间传输个性把表复制到新配置的MySQL实例之中,详见Section 14.5.6, "Copying File-Per-Table Tablespaces to Another Server"。

 

5.Benchmark the performance of I/O intensive workloads.

进展密集型I/O负载的习性基准测量试验。

 

  • 6.Periodically increase the value of innodb_undo_logs and rerun performance tests. Find the value where you stop experiencing gains in I/O performance.

  • 限时扩张innodb_undo_logs的值并再度实行品质测量试验,直到找到最适合你的I/O品质的值。

 

7.Deploy a new production instance using the ideal settings for these options. Set it up as a slave server in a replication configuration, or transfer data from an earlier production instance.

对那个参数使用可以的设定来布局贰个新的生产实例。在主导配置中把它设置成slave,只怕从以前的生育实例上把数据传输过来。

 

Performance and Scalability Considerations

 

Keeping the undo logs in separate files allows the MySQL team to implement I/O and memory optimizations related to this transactional data. For example, because the undo data is written to disk and then rarely used (only in case of crash recovery), it does not need to be kept in the filesystem memory cache, in turn allowing a higher percentage of system memory to be devoted to the InnoDB buffer pool.

把undo log从系统表空间分离出来能够使得MySQL革新和事情数占领关的I/O和内存的优化。比方,因为undo数据写入到磁盘后就非常少使用了(除非是崩溃复苏的景观),那也就无需保障在文件系统的内部存款和储蓄器cache里,反过来运营也就同意越多的系统内部存款和储蓄器可用于InnoDB的buffer pool。

 

The typical SSD best practice of keeping the InnoDB system tablespace on a hard drive and moving the per-table tablespaces to SSD, is assisted by moving the undo information into separate tablespace files.

至上的施行是把InnoDB的体系表空间位居普通的硬盘上,而把undo的表空间放到SSD的磁盘上。

 

Internals

 

The physical tablespace files are named undoN, where N is the space ID, including leading zeros.

大要的表空间文件命名叫undoN,N表示的是space ID,including leading zeros。

 

MySQL instances containing separate undo tablespaces cannot be downgraded to earlier releases such as MySQL 5.5 or 5.1.

含有多少个undo表空间的实例是不能够降级到在此以前的本子的,比如MySQL5.5也许5.1。

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:基于Xtrabackup恢复单个innodb表,ibdata1文件损坏时恢

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。