金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > innodb可传输表空间,ibdata1文件损坏时恢复InnoDB单

innodb可传输表空间,ibdata1文件损坏时恢复InnoDB单

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-09-14 06:55

 

 

 

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数据库里的InnoDB表。从5.6开始,包括在MySQL数据库里的有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数据库里的*.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 DIRECTORY = 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操作,所以多个表上的写操作就无法合并成一个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会重新创建表(ALGORITHM=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数据使用,但不会被操作系统回收使用。当要把很多的表从系统表空间里移除的时候要注意磁盘空间的限制,你最好是开启 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 DIRECTORY = 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 DIRECTORY子句。你指定的目录可以是 另一个有特别性能优势或者存储能力的磁盘,例如更快的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 DATADIR目录下面的数据库目录里,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 DIRECTORY子句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.

  • 分区表不支持DISCARD TABLESPACE意味着表空间传输也不支持。如果你在一个分区表上运行ALTER TABLE ... DISCARD TABLESPACE,会报下面的错误:ERROR 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)关系的表是不支持DISCARD 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.

  • ALTER 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:

  • ALTER 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 ... FOR 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.

ALTER 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:

当在远端实例运行ALTER TABLE ... DISCARD 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 ... FOR 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:

当在远端实例运行ALTER 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.

决定保存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实例。使用和生产环境相似的数据负载量,或者从现有的数据库里使用表空间传输特性把表复制到新配置的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手机下载,转载请注明出处:innodb可传输表空间,ibdata1文件损坏时恢复InnoDB单

关键词:

上一篇:没有了

下一篇:没有了