MySQL误DROP表恢复,innodb_file_per_table=OFF

MySQL误DROP表恢复,innodb_file_per_table=OFF

原文https://twindb.com/recover-after-drop-table-innodb_file_per_table-is-off/

Undrop-for-innodb安装

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

介绍

误操作是不可避免的.错误的DROP DATABASE或DROP TABLE会摧毁重要的数据,更悲剧的是备份又不可用.
恢复方法取决于InnoDB是将所有数据保存在单个ibdata1中还是每个表都有自己的表空间.在这篇文章中,我们将考虑innodb_file_per_table = OFF的情况.此选项假定所有表都存储在通用文件中,通常位于/var/lib/mysql/ibdata1.

误删表恢复

这里使用sakila库,假设我们误删除了actor表

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
31
32
33
34
35
36
mysql> SELECT * FROM actor LIMIT 10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
| 4 | JENNIFER | DAVIS | 2006-02-15 04:34:33 |
| 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
| 6 | BETTE | NICHOLSON | 2006-02-15 04:34:33 |
| 7 | GRACE | MOSTEL | 2006-02-15 04:34:33 |
| 8 | MATTHEW | JOHANSSON | 2006-02-15 04:34:33 |
| 9 | JOE | SWANK | 2006-02-15 04:34:33 |
| 10 | CHRISTIAN | GABLE | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.02 sec)
查看一下checksum
mysql> CHECKSUM TABLE actor;
+--------------+------------+
| Table | Checksum |
+--------------+------------+
| sakila.actor | 1702520518 |
+--------------+------------+
1 row in set (0.01 sec)
mysql> select count(*) from actor;
+----------+
| count(*) |
+----------+
| 200 |
+----------+
1 row in set (0.01 sec)
mysql> DROP TABLE actor;
Query OK, 0 rows affected (0.03 sec)

阅读全文

恢复表结构

恢复表结构

此文档适用于.frm文件丢失,并且没有备份情况下恢复出建表语句

恢复InnoDB字典表

首先创建用于恢复的字典辅助表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> create database sakila_recovered;
Query OK, 1 row affected (0.01 sec)
[mysql@master undrop-for-innodb]$ cat dictionary/SYS_* | mysql -umysql -pmysql -S /data/mysqldata/3307/mysql.sock sakila_recovered
Warning: Using a password on the command line interface can be insecure.
mysql> show tables;
+----------------------------+
| Tables_in_sakila_recovered |
+----------------------------+
| SYS_COLUMNS |
| SYS_FIELDS |
| SYS_INDEXES |
| SYS_TABLES |
+----------------------------+
4 rows in set (0.00 sec)

字典表存储在ibdata1中,所以要解析它

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
[mysql@master undrop-for-innodb]$ ./stream_parser -f /data/mysqldata/3307/data/ibdata1
Opening file: /data/mysqldata/3307/data/ibdata1
File information:
ID of device containing file: 64768
inode number: 104900755
protection: 100660 (regular file)
number of hard links: 1
user ID of owner: 27
group ID of owner: 27
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 4194304
time of last access: 1482819237 Tue Dec 27 14:13:57 2016
time of last modification: 1482819237 Tue Dec 27 14:13:57 2016
time of last status change: 1482819237 Tue Dec 27 14:13:57 2016
total size, in bytes: 2147483648 (2.000 GiB)
Size to process: 2147483648 (2.000 GiB)
Worker(0): 5.47% done. 2016-12-27 14:24:21 ETA(in 00:00:18). Processing speed: 104.000 MiB/sec
Worker(0): 13.67% done. 2016-12-27 14:24:14 ETA(in 00:00:10). Processing speed: 168.000 MiB/sec
Worker(0): 22.66% done. 2016-12-27 14:24:13 ETA(in 00:00:08). Processing speed: 184.000 MiB/sec
Worker(0): 28.52% done. 2016-12-27 14:24:18 ETA(in 00:00:12). Processing speed: 120.000 MiB/sec
Worker(0): 35.94% done. 2016-12-27 14:24:15 ETA(in 00:00:08). Processing speed: 152.000 MiB/sec
Worker(0): 43.75% done. 2016-12-27 14:24:15 ETA(in 00:00:07). Processing speed: 160.000 MiB/sec
Worker(0): 51.17% done. 2016-12-27 14:24:15 ETA(in 00:00:06). Processing speed: 152.000 MiB/sec
Worker(0): 57.81% done. 2016-12-27 14:24:16 ETA(in 00:00:06). Processing speed: 136.000 MiB/sec
Worker(0): 64.45% done. 2016-12-27 14:24:16 ETA(in 00:00:05). Processing speed: 136.000 MiB/sec
Worker(0): 71.48% done. 2016-12-27 14:24:16 ETA(in 00:00:04). Processing speed: 144.000 MiB/sec
Worker(0): 78.91% done. 2016-12-27 14:24:15 ETA(in 00:00:02). Processing speed: 152.000 MiB/sec
Worker(0): 85.16% done. 2016-12-27 14:24:16 ETA(in 00:00:02). Processing speed: 128.000 MiB/sec
Worker(0): 92.19% done. 2016-12-27 14:24:16 ETA(in 00:00:01). Processing speed: 144.000 MiB/sec
All workers finished in 13 sec
Data from database pages is saved by the stream_parser to folder pages-ibdata1:
[mysql@master undrop-for-innodb]$ tree pages-ibdata1/
pages-ibdata1/
├── FIL_PAGE_INDEX
│ ├── 0000000000000001.page
│ ├── 0000000000000002.page
│ ├── 0000000000000003.page
│ ├── 0000000000000004.page
│ ├── 0000000000000005.page
│ ├── 0000000000000011.page
│ ├── 0000000000000012.page
│ ├── 0000000000000013.page
│ ├── 0000000000000014.page
│ ├── 0000000000000015.page
│ ├── 0000000000000016.page
│ ├── 0000000000000017.page
│ ├── 0000000000000018.page
│ ├── 0000000000000019.page
│ ├── 0000000000000020.page
│ ├── 0000000000000021.page
│ ├── 0000000000000022.page
│ ├── 0000000000000023.page
│ └── 18446744069414584320.page
└── FIL_PAGE_TYPE_BLOB
2 directories, 19 files

阅读全文

MySQL误DROP表恢复,innodb_file_per_table=ON

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)

接下来删除country表

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

阅读全文

Auto_increment 产生 GAP的原因

原文
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
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锁和AUTO_INCREMENT在InnoDB中处理方式

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事务隔离级别

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 中插入新的记录

阅读全文


Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2018 Fan() All Rights Reserved.

访客数 : | 访问量 :

#