MySQL误DROP表恢复,innodb_file_per_table=ON

参考
https://twindb.com/recover-after-drop-table-innodb_file_per_table-is-on/

Undrop-for-innodb安装

详见https://github.com/chhabhaiya/undrop-for-innodb

删除Country表并恢复

使用sakila示例数据库作为例子,sakila相当于Oracle的SCOTT
innodb每个表包含两个文件.frm文件包含建表语句.ibd文件包含数据

1
2
3

[mysql@master sakila]$ ll country.*
-rw-r----- 1 mysql mysql  8652 Aug 26 14:25 country.frm
-rw-r----- 1 mysql mysql 98304 Aug 26 14:25 country.ibd

接下来删除country表,删之前看一下checksum

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SESSION_A> use sakila
Database changed
SESSION_A> select count(*) from country;
+----------+
| count(*) |
+----------+
|      109 |
+----------+
1 row in set (0.01 sec)

SESSION_A>checksum table country;
+---------------------+------------+
| Table               | Checksum   |
+---------------------+------------+
| sakila.country      | 2039770088 |
+---------------------+------------+
1 row in set (0.00 sec)

1
2

SESSION_A>drop table country;
Query OK, 0 rows affected (0.01 sec)

drop table后,.frm和.ibd文件也被删除了

1
2

[mysql@master sakila]$ ll country*
ls: cannot access country*: No such file or directory

原文
http://thenoyes.com/littlenoise/?p=187

Auto_increment 产生 GAP的原因

Why are there gaps in my auto_increment sequence, even if there are no deletes or rolled back transactions?
Is it a bug?
The manual says, “For lock modes 1 or 2, gaps may occur between successive statements because for bulk inserts the exact number of auto-increment values required by each statement may not be known and overestimation is possible.”
Where does that overestimation come from?
An example to illustrate:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

DROP TABLE IF EXISTS t;
CREATE TABLE t (a bigint unsigned auto_increment primary key) ENGINE=InnoDB SELECT NULL AS a;
/* #1 */ INSERT INTO t SELECT NULL FROM t;
/* #2 */ INSERT INTO t SELECT NULL FROM t;
/* #3 */ INSERT INTO t SELECT NULL FROM t;
/* #4 */ INSERT INTO t SELECT NULL FROM t;
SELECT * FROM t;

+----+
| a |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
| 7 |
| 8 |
| 9 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
+----+
16 rows in set (0.02 sec)

AUTO-INC Locks

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

AUTO-INC锁是当向使用含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁
在最简单的情况下，如果一个事务正在向表中插入值，则任何其他事务必须等待对该表执行自己的插入操作，以便第一个事务插入的行的值是连续的。
innodb_autoinc_lock_mode配置选项控制用于自动增量锁定的算法。 它允许您选择如何在可预测的自动递增值序列和插入操作的最大并发性之间进行权衡。

AUTO_INCREMENT Handling in InnoDB

InnoDB提供了一个可配置的锁定机制，可以显着提高使用AUTO_INCREMENT列向表中添加行的SQL语句的可伸缩性和性能。 要对InnoDB表使用AUTO_INCREMENT机制，必须将AUTO_INCREMENT列定义为索引的一部分，以便可以对表执行相当于索引的SELECT MAX（ai_col）查找以获取最大列值。 通常，这是通过使列成为某些表索引的第一列来实现的。

本节介绍AUTO_INCREMENT锁定模式的行为，对不同AUTO_INCREMENT锁定模式设置的使用含义，以及InnoDB如何初始化AUTO_INCREMENT计数器。

• InnoDB AUTO_INCREMENT锁定模式

• InnoDB AUTO_INCREMENT锁定模式使用含义

• InnoDB AUTO_INCREMENT计数器初始化

Innodb事务隔离级别

REPEATABLE READ

This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.

这是 InnoDB 默认的事务隔离级别,同一事物通过第一次创建的快照来构造一致性读.这意味着如果你在同一会话执行多次查询(非锁定读),那么每次获取的结果都是彼此相等的

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.

对于锁定读,(SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, 和 DELETE语句,是否锁定取决于此次查询(UPDATE DELETE 也是一种查询)是否通过唯一条件或范围条件查询使用唯一索引

For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.

• For a unique index with a unique search condition,InnoDB只锁索引记录,不锁 gap
  • For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range

READ COMMITTED

Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. For information about consistent reads

read view在 innodb 如何避免幻读中已经介绍过

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking.

locking reads(SELECT with FOR UPDATE or LOCK IN SHARE MODE),UPDATE 语句,DELETE语句,InnoDB 都只会锁index records(也就是 record lock),不会产生 gap lock,因此允许在 gap 中插入新的 records.Gap locking只在外键约束检查和重复值检查时产生

Because gap locking is disabled, phantom problems may occur, as other sessions can insert new rows into the gaps

因为gap locking被 disabled,幻读问题可能会产生,其他会话可以在 gap 中插入新的记录

幻读Phantom Rows

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

幻读问题是指一个事务的两次不同时间的相同查询返回了不同的的结果集。例如:一个 select 语句执行了两次，但是在第二次返回了第一次没有返回的行,那么这些行就是“phantom” row.

read view(或者说 MVCC)实现了一致性不锁定读(Consistent Nonlocking Reads)，从而避免了幻读

实验1:

开两个窗口设置

1
2
3
4
5

set session tx_isolation='REPEATABLE-READ';
select @@session.autocommit;select @@global.tx_isolation,@@session.tx_isolation;

create table read_view(text varchar(50));
insert into read_view values('init');

两个会话开始事务

1
2
3
4
5

SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_A执行一个查询，这个查询可以访问任何表，这个查询的目的是创建一个当前时间点的快照
START TRANSACTION WITH CONSISTENT SNAPSHOT;也可以达到同样的效果

1
2
3
4
5
6
7
8
9
10

SESSION_A>select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

SESSION_B 插入一条记录并提交

1
2
3
4
5

SESSION_B>insert into read_view values('after session A select');
Query OK, 1 row affected (0.01 sec)

SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SESSION_A>select * from read_view;
+------+
| text |
+------+
| init |
+------+
1 row in set (0.00 sec)

SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from read_view;
+------------------------+
| text                   |
+------------------------+
| init                   |
| after session A select |
+------------------------+
2 rows in set (0.00 sec)

Auto_increment

Mysql AUTO_INCREMENT

1.Innodb表的自动增长列可以手工插入，但是插入的值如果是空或者0，则实际插入的将是自动增长后的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

mysql> create table t1(id int not null auto_increment primary key,name varchar(10));
Query OK, 0 rows affected (0.06 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into t1 values(0,'fanboshi');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(null,'duyalan');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+----------+
| id | name     |
+----+----------+
|  1 | fanboshi |
|  2 | duyalan  |
+----+----------+
2 rows in set (0.00 sec)

2.可以通过alter table t1 auto_incremenrt=n 语句强制设置自动增长列的初始值，默认从1开始，但是该强制的默认值是保留在内存中的，如果该值在使用之前数据库重新启动，那么这个强制的默认值就会丢失，就需要数据库启动后重新设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

mysql> alter table t1 auto_increment=5;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t1 values(null,'handudu');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+----------+
| id | name     |
+----+----------+
|  1 | fanboshi |
|  2 | duyalan  |
|  5 | handudu  |
+----+----------+
3 rows in set (0.00 sec)

3.可以是用last_insert_id()查询当前线程最后插入记录使用的值。如果一次插入多条记录，那么返回的是第一条记录使用的自动增长值。

Welcome to Hexo! This is your very first post. Check documentation for more info. If you get any problems when using Hexo, you can find the answer in troubleshooting or you can ask me on GitHub.

Quick Start

Create a new post

1

$ hexo new "My New Post"

More info: Writing

Run server

1

$ hexo server

More info: Server

Generate static files

1

$ hexo generate

More info: Generating

Deploy to remote sites

1

$ hexo deploy

More info: Deployment