mysql 5.5 索引建设（表rebuild）过程中的系统崩溃的恢复

最新推荐文章于 2023-01-07 19:22:25 发布

cudi7618

最新推荐文章于 2023-01-07 19:22:25 发布

阅读量345

点赞数

文章标签：数据库

mysql 5.5 实现了 fast index creation

对于主键索引（ cluster key）还是没有发办法快速建立索引，还是要copy 表的数据到临时表

对于二级索引的建立，就可以实现快速建立索引了，实现机制，主要是mysql 对二级索引的建立过程中，
也会做全表扫描，只是不会马上的把记录对应的索引项写入索引，而是先在内存里排序，然后写入索引
这样就避免了大量的随机i/o。从而提高了速度，这不是我们本次讨论的目的。

主要谈崩溃的处理。

对于二级索引的建立过程中，数据库崩溃， 不会造成数据丢失，数据库在重启的自动回复过程中，会清理掉系统中留下的垃圾数据，
系统恢复后，需要重新建立二级索引就可以了。

对于主键（cluster key）的建立过程中如果系统崩溃，也不会造成数据的丢失，
主键的建立过程，跟我们目前的mysql5.1版本的二级索引的建立机制是一样的，
都要重建表，索引这个恢复机制，在mysql5.1的版本里都是适用，当然也适用于由于alter table 命令需要生成临时表的修改操作的回复。

但是数据库的回复过程，需要根据主键建立过程中到达的不同阶段，采取不同的措施
www.innodb.com 的官网上给出了一个指导原则，就把原文贴这里吧。

http://www.innodb.com/wp/tips/fic-recovery/

Processing CREATE INDEXConsider an example. If you use the ALTER TABLE command to redefine the primary key of table T (or otherwise cause the clustered index to be recreated, for example by compressing an uncompressed table), the InnoDB Plugin will perform the following sequence of steps:

Lock table T in exclusive mode.
Create a new temporary table T1, with the new index structure.
Copy data from table T to temporary table T1.
Rename the original table T to temporary table T2.
Rename table T1 (the new table) to T (the original table name).
Drop table T2 (the original table, with the old index structure).

Recovery ScenariosSince the InnoDB plugin does not drop the temporary table containing the original data (T2 above) until the very last step, there will never be a loss of data. Furthermore, until the original table is dropped, it is always safe to revert back to that table, with its original index structure. However, if the process of copying the data (step 3) is complete, you can chose to use the new table by appropriately renaming or dropping the temporary table(s).
Until the last step is completed, one or both of the temporary tables may exist following a crash during ALTER TABLE. It is up to the user to complete the recovery process using these temporary tables. Following a crash during ALTER TABLE, you should first determine which steps of the above process have been completed. There are the following possibilities and remedial steps.
Case A: Processing was interrupted after step 2, and during step 3 above. Some, but not all of the data from original table T has been copied to temporary table T1.
In this case, table T will still exist and may be used by the application. You should drop temporary table T1.
Case B: Processing was interrupted after step 3 above completed, but before step 4. All of the original data has been copied to temporary table T1, but the original table T still exists by that name. There will be no temporary table named T2.
Because step 4 has not been completed, you cannot tell that all the data has been copied. Therefore, this is equivalent to case A, and you should revert to the original table T, and drop temporary table T1.
Case C: Processing was interrupted after step 4 above, but before step 5. All of the data from original table T has been copied, and table T has now been renamed to temporary table T2. The new table is complete, but is still named temporary table T1.
In this case, as with case B, you may choose to use the original table T, by renaming temporary table T2 to T, and dropping temporary table T1. Or, you may decide to use the new table, renaming temporary table T1 to T, and dropping temporary table T2.
Case D: Processing was interrupted after step 5 above, but before step 6. Table T now has the new structure, and the original table is present as temporary table T2.
In this case, you can simply complete the processing of the ALTER TABLE command by dropping temporary table T2, and the application can use the newly-indexed table. T. Or, if you wish, you can go back to using the original index structure, dropping table T, and renaming temporary table T2 (which is the original table) back to T.
The InnoDB and MySQL Data DictionariesThe recovery process outlined above is a bit tricky, because there are two data dictionaries involved: the SYS_TABLES table in the InnoDB system tablespace (which is not directly accessible from MySQL), and the .frm files in the MySQL database directories.
The temporary tables T1 and T2 in the example above will be created within the InnoDB data dictionary only, and not as MySQL .frm files. Furthermore, the creation or deletion of .frm files is not transactional, and InnoDB crash recovery does not cover .frm files. If the operating system crashes immediately after the InnoDB transaction has been committed, it is possible that the .frm file for the modified table will be lost, in which case the internal InnoDB data dictionary and that of MySQL will be out of sync.
After a crash that occurs while a new clustered index is being created, the table may not appear to exist, and the following error will be returned: ERROR 1146 (42S02): Table ‘test.T‘ doesn’t exist. This may be due to the file test/T.frm missing from the MySQL data directory, but it can also be due to InnoDB.
To diagnose the error condition, first examine the error log to see if InnoDB has reported some error. For further analysis, have InnoDB dump the internal data dictionary, as follows:
CREATE TABLE innodb_table_monitor (a INT) ENGINE=InnoDB;
DROP TABLE innodb_table_monitor;

Between the two commands, monitor the MySQL error log for up to one minute, until a section labeled INNODB TABLE MONITOR OUTPUT appears. In the dumped table names, the table name suffixes will be shown as something like @00231 or @00232.
The following sections describe how to recover under two scenarios: and a missing .frm file, and mis-match in tablespace IDs.
Recovering a Missing .frm File
Before creating an index, the MySQL server writes the data dictionary information of the table in a temporary file that is named like #sql-110b_1.frm. The file should have a recent timestamp. If the file T.frm is missing, you may try renaming it from the temporary file.
The MySQL server cannot drop a table unless there is a .frm file for it. You can create the file by creating a table under a different name and copying the file, say, to T@00231.frm to “create” the table T.
Recovering from a Tablespace ID Mismatch
If InnoDB reports a tablespace ID mismatch in the error log for table T, the situation should be like this:

Table T in SYS_TABLES points to the table definition as it was before creating the index.
Table T@00231 in SYS_TABLES points to table definition as it is after creating the index.
There is no T@00232 in SYS_TABLES.
The file T.frm points to the table definition as it was before creating the index.
The file T@00232.ibd points to the table definition as it was before creating the index. This file may or may not exist.
The file T@00231.ibd may or may not exist.
The file T.ibd points to the table definition as it was after creating the index.To correct this situation, do the following:
- Perform a “slow” shutdown of the MySQL server (SET GLOBAL innodb_fast_shutdown=0 followed by a clean shutdown).
- Back up the MySQL data directory.
- If the file T@00232.ibd exists, restore the situation as it was before creating the table:
  - Remove the files T@00231.ibd (if it exists) and T.ibd.
  - Rename the file T@00232.ibd to T.ibd.
  - Copy the file T.frm to T@00231.frm.
  - Start the MySQL server.
  - DROP TABLE `T1`;
  - There may be an orphan file #sql-110b_1.frm that you may want to delete. It could contain the table definition after creating the index.
  If the file T@00232.ibd does not exist, try to restore the situation as it was after creating the table:
  - Look for an orphan file like #sql-110b_1.frm with a recent timestamp. It should contain the table definition after creating the index. Rename the file to T@00231.frm.
  - Rename the file T.ibd to T@00231.ibd.
  - Start the MySQL server.
  - DROP TABLE T;
  - RENAME TABLE `T#1` TO T;
- Ensure with SHOW CREATE TABLE T and SELECT * FROM T LIMIT 10 that the table definition is correct. Compare the output of SHOW CREATE TABLE with the innodb_table_monitor dump of the table.