Internals

MySQL special character rollback

August 16, 2017 Architect, Internals, MYSQL, replication No comments

Actually, Mysqlbinlog tools can’t miner mysql binlog exactly correct.

In this post ,a special environment we got may hexadecimal characters what we don’t need.

Read this PDF: Special-code-rollback.pdf

TokuDB benchmark on PCIe

October 21, 2015 Architect, Architecture, hardware, Internals, MYSQL, performance No comments

MariaDB TokuDB benchmark on FusionIO ,Compare TokuDB and InnoDB engines.

read: TokuDB_benchmark

MySQL lost “AUTO_INCREMENT” after a long time

October 8, 2015 Internals, MYSQL, rdbms No comments

Description:

I create a empty table and then alter this table's "AUTO_INCREMENT" starts from 10000000.After a long time ,MySQL lost "AUTO_INCREMENT" definition when I show this table again. MariaDB has the same problem but Percona Server is fine.

$mysql06

Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 141028725
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DROP TABLE IF EXISTS `test_bug` ;
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> CREATE TABLE test_bug (
    ->   `id` BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    ->   `consult_order_id` BIGINT ,
    ->   `pay_channel` INT COMMENT ,
    ->   `price` BIGINT ,
    ->   `trade_no` VARCHAR (64) ,
    ->   `right_no` VARCHAR (64) ,
    ->   `item_id` BIGINT NOT NULL,
    ->   `source_type` INT NOT NULL ,
    ->   `gmt_create` DATETIME DEFAULT CURRENT_TIMESTAMP ,
    ->   `gmt_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
    -> ) ENGINE = INNODB DEFAULT CHARSET = utf8 ;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> ALTER TABLE test_bug 
    ->   ADD INDEX index_consult_order (consult_order_id) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> ALTER TABLE test_bug 
    ->   ADD INDEX index_right_no (right_no) ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> ALTER TABLE test_bug 
    ->   AUTO_INCREMENT = 100000000 ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> show create table  test_bug;
  
   CREATE TABLE `test_bug` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `consult_order_id` bigint(20) DEFAULT NULL ,
  `pay_channel` int(11) DEFAULT NULL COMMENT ,
  `price` bigint(20) DEFAULT NULL COMMENT ,
  `trade_no` varchar(64) DEFAULT NULL COMMENT ,
  `right_no` varchar(64) DEFAULT NULL COMMENT ,
  `item_id` bigint(20) NOT NULL COMMENT ,
  `source_type` int(11) NOT NULL COMMENT ,
  `gmt_create` datetime DEFAULT CURRENT_TIMESTAMP ,
  `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  PRIMARY KEY (`id`),
  KEY `index_consult_order` (`consult_order_id`),
  KEY `index_right_no` (`right_no`)
) ENGINE=InnoDB AUTO_INCREMENT=100000000 DEFAULT CHARSET=utf8  


1 row in set (0.00 sec)


wait about 24 hours , then show create table :


mysql> show create table test_bug; 

   CREATE TABLE `test_bug` (
  `id` BIGINT (20) NOT NULL AUTO_INCREMENT,
  `consult_order_id` BIGINT (20) DEFAULT NULL,
  `pay_channel` INT (11) DEFAULT NULL,
  `price` BIGINT (20) DEFAULT NULL,
  `trade_no` VARCHAR (64) DEFAULT NULL,
  `right_no` VARCHAR (64) DEFAULT NULL,
  `item_id` BIGINT (20) NOT NULL,
  `source_type` INT (11) NOT NULL,
  `gmt_create` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `gmt_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `index_consult_order` (`consult_order_id`),
  KEY `index_right_no` (`right_no`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 

1 row in set (0.00 sec)  

AUTO_INCREMENT disappears .

Bug has been reported :https://bugs.mysql.com/bug.php?id=77743

NVMFS Supports Atomic Writes

September 6, 2015 Architect, hardware, Internals, MYSQL No comments

Benchmark for NVMFS (supports atomic writes ,so we can close double-write option on specific MySQL version )

tips: some Flash-based cards could support large block map , the main idea is to avoid fractured page writes.

http://www.vmcd.org/docs/nvmfs_benchmark.pdf

[Reprint]Injection MySQL – Function

August 24, 2015 Internals, linux, MYSQL, rdbms No comments

Reprint a PDF for MySQL Injection Test

MySQL Error Based SQL Injection Using EXP

How to configure Tcpdump system

July 24, 2015 Architect, Architecture, Internals, MYSQL, network, system No comments

MySQL Tcpdump system : use percona-toolkit to analyze network packages

We can identify problem SQLs with high execution frequency.

With DBMON system and AWR system we can find problem SQLs in a special time (high frequency, occurs over a period of time)

View this PDF:

http://www.vmcd.org/docs/MySQL_TCPDUMP.pdf

MariaDB : Bug when add index on Partition table

April 23, 2015 Internals, MYSQL, replication No comments

Version: 5.5.5-10.0.10-MariaDB-log MariaDB Server

When I use mariadb as multi-source slave DB I met a strange problem . Creating index on a vary large partition table , Mariadb do — copy to tmp table on the slave side. It takes a long time and still not finish over 9 hours.

Primary : MySQL 5.6.16 —

add index on a partition table:

CREATE TABLE `track_raw_wap211_log` (
 `table_id` varchar(100) DEFAULT NULL,
 `page_id` varchar(100) DEFAULT NULL,
 `banner_id` varchar(100) DEFAULT NULL,
 `button_id` varchar(100) DEFAULT NULL,
 `test_id` varchar(100) DEFAULT NULL,
 `classfication` varchar(100) DEFAULT NULL,
 `request_refer` varchar(100) DEFAULT NULL,
 `request_url` text,
 `title` varchar(100) DEFAULT NULL,
 `user_id` varchar(100) DEFAULT NULL,
 `language` varchar(100) DEFAULT NULL,
 `event` varchar(100) DEFAULT NULL,
 `event_desc` varchar(100) DEFAULT NULL,
 `event_type` varchar(100) DEFAULT NULL,
 `log_version` varchar(100) DEFAULT NULL,
 `project` varchar(100) DEFAULT NULL,
 `log_time` varchar(100) DEFAULT NULL,
 `unicookie` varchar(100) DEFAULT NULL,
 `session_id` varchar(100) DEFAULT NULL,
 `session_duration` varchar(100) DEFAULT NULL,
 `resolution` varchar(100) DEFAULT NULL,
 `channel` varchar(100) DEFAULT NULL,
 `refer` text,
 `user_agent` text,
 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `httplogtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `httpforwardip` varchar(100) DEFAULT NULL,
 `country` varchar(100) DEFAULT NULL,
 `province` varchar(100) DEFAULT NULL,
 `city` varchar(100) DEFAULT NULL,
 `area` varchar(100) DEFAULT NULL,
 `mapweidu` varchar(100) DEFAULT NULL,
 `mapjingdu` varchar(100) DEFAULT NULL,
 `sync_mysqlid` bigint(20) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`sync_mysqlid`,`create_time`),
 KEY `ix_create_time` (`create_time`),
 KEY `ix_httplogtime` (`httplogtime`),
 KEY `ix_httplogtime_userid` (`httplogtime`,`user_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=23217118 DEFAULT CHARSET=utf8
 /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(`create_time`))
 (PARTITION p20150318 VALUES LESS THAN (1426694400) ENGINE = InnoDB,
 PARTITION p20150319 VALUES LESS THAN (1426780800) ENGINE = InnoDB,
 PARTITION p20150320 VALUES LESS THAN (1426867200) ENGINE = InnoDB,
 PARTITION p20150321 VALUES LESS THAN (1426953600) ENGINE = InnoDB,
 PARTITION p20150322 VALUES LESS THAN (1427040000) ENGINE = InnoDB,
 PARTITION p20150323 VALUES LESS THAN (1427126400) ENGINE = InnoDB,
 PARTITION p20150324 VALUES LESS THAN (1427212800) ENGINE = InnoDB,
 PARTITION p20150325 VALUES LESS THAN (1427299200) ENGINE = InnoDB,
 PARTITION p20150326 VALUES LESS THAN (1427385600) ENGINE = InnoDB,
 PARTITION p20150327 VALUES LESS THAN (1427472000) ENGINE = InnoDB,
 PARTITION p20150328 VALUES LESS THAN (1427558400) ENGINE = InnoDB,
 PARTITION p20150329 VALUES LESS THAN (1427644800) ENGINE = InnoDB,
 PARTITION p20150330 VALUES LESS THAN (1427731200) ENGINE = InnoDB,
 PARTITION p20150331 VALUES LESS THAN (1427817600) ENGINE = InnoDB,
 PARTITION p20150401 VALUES LESS THAN (1427904000) ENGINE = InnoDB,
 PARTITION p20150402 VALUES LESS THAN (1427990400) ENGINE = InnoDB,
 PARTITION p20150403 VALUES LESS THAN (1428076800) ENGINE = InnoDB,
 PARTITION p20150404 VALUES LESS THAN (1428163200) ENGINE = InnoDB,
 PARTITION p20150405 VALUES LESS THAN (1428249600) ENGINE = InnoDB,
 PARTITION p20150406 VALUES LESS THAN (1428336000) ENGINE = InnoDB,
 PARTITION p20150407 VALUES LESS THAN (1428422400) ENGINE = InnoDB,
 PARTITION p20150408 VALUES LESS THAN (1428508800) ENGINE = InnoDB,
 PARTITION p20150409 VALUES LESS THAN (1428595200) ENGINE = InnoDB,
 PARTITION p20150410 VALUES LESS THAN (1428681600) ENGINE = InnoDB,
 PARTITION p20150411 VALUES LESS THAN (1428768000) ENGINE = InnoDB,
 PARTITION p20150412 VALUES LESS THAN (1428854400) ENGINE = InnoDB,
 PARTITION p20150413 VALUES LESS THAN (1428940800) ENGINE = InnoDB,
 PARTITION p20150414 VALUES LESS THAN (1429027200) ENGINE = InnoDB,
 PARTITION p20150415 VALUES LESS THAN (1429113600) ENGINE = InnoDB,
 PARTITION p20150416 VALUES LESS THAN (1429200000) ENGINE = InnoDB,
 PARTITION p20150417 VALUES LESS THAN (1429286400) ENGINE = InnoDB,
 PARTITION p20150418 VALUES LESS THAN (1429372800) ENGINE = InnoDB,
 PARTITION p20150419 VALUES LESS THAN (1429459200) ENGINE = InnoDB,
 PARTITION p20150420 VALUES LESS THAN (1429545600) ENGINE = InnoDB,
 PARTITION p20150421 VALUES LESS THAN (1429632000) ENGINE = InnoDB,
 PARTITION p20150422 VALUES LESS THAN (1429718400) ENGINE = InnoDB,
 PARTITION p20150423 VALUES LESS THAN (1429804800) ENGINE = InnoDB,
 PARTITION p20150424 VALUES LESS THAN (1429891200) ENGINE = InnoDB,
 PARTITION p20150425 VALUES LESS THAN (1429977600) ENGINE = InnoDB,
 PARTITION p20150426 VALUES LESS THAN (1430064000) ENGINE = InnoDB,
 PARTITION p20150427 VALUES LESS THAN (1430150400) ENGINE = InnoDB,
 PARTITION p20150428 VALUES LESS THAN (1430236800) ENGINE = InnoDB,
 PARTITION p20150429 VALUES LESS THAN (1430323200) ENGINE = InnoDB,
 PARTITION p20150430 VALUES LESS THAN (1430409600) ENGINE = InnoDB,
 PARTITION p20150501 VALUES LESS THAN (1430496000) ENGINE = InnoDB,
 PARTITION p20150502 VALUES LESS THAN (1430582400) ENGINE = InnoDB,
 PARTITION p20150503 VALUES LESS THAN (1430668800) ENGINE = InnoDB,
 PARTITION p20150504 VALUES LESS THAN (1430755200) ENGINE = InnoDB,
 PARTITION p20150505 VALUES LESS THAN (1430841600) ENGINE = InnoDB,
 PARTITION p20150506 VALUES LESS THAN (1430928000) ENGINE = InnoDB,
 PARTITION p20150507 VALUES LESS THAN (1431014400) ENGINE = InnoDB,
 PARTITION p20150508 VALUES LESS THAN (1431100800) ENGINE = InnoDB,
 PARTITION p20150509 VALUES LESS THAN (1431187200) ENGINE = InnoDB,
 PARTITION p20150510 VALUES LESS THAN (1431273600) ENGINE = InnoDB,
 PARTITION p20150511 VALUES LESS THAN (1431360000) ENGINE = InnoDB) */ 

create index idx_tmp on track_raw_wap211_log (log_time);

Slave: Mariadb – 5.5.5-10.0.15-MariaDB-log MariaDB Server

21407 | root | localhost | jkgj_log | Query | 34 | copy to tmp table | create index idx_tmp on track_raw_wap211_log (log_time) | 0.000 |  

drop index command still has this problem.

——————————————————————

When I repeat these steps on MariaDB – 10.0.16 everything is OK.

This bug has been reported to MariaDB:

https://mariadb.atlassian.net/browse/MDEV-8038

MySQL InnoDB的一些非常规恢复

July 9, 2013 Internals, MYSQL 2 comments

最近关于下厨房的数据恢复,讨论的很火热,乘机测试了把Percona Data Recovery Tool for InnoDB工具,并且事实证明这款工具对一些操作的恢复,还是比较困难的。

我们以一张小表为例做一个测试:

mysql> select * from  customer;
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
| customer_id | store_id | first_name | last_name | email             | address_id | active | create_date         | last_update         |
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
|           1 |        1 | liu        | yang      | www.yihaodian.com |        141 |      1 | 2013-07-09 15:34:23 | 2013-07-09 15:34:23 |
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> TRUNCATE TABLE customer;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
[root@db-42 tmp]# wget https://launchpad.net/percona-data-recovery-tool-for-innodb/trunk/release-0.5/+download/percona-data-recovery-tool-for-innodb-0.5.tar.gz

..


2013-07-09 15:41:03 (11.3 KB/s) - `percona-data-recovery-tool-for-innodb-0.5.tar.gz' saved [1960195/1960195]


[root@db-42 tmp]# tar -zxvf percona-data-recovery-tool-for-innodb-0.5.tar.gz

[root@db-42 tmp]# cd percona-data-recovery-tool-for-innodb-0.5/mysql-source
[root@db-42 mysql-source]# ./configure

编译生成page_parser和constraints_parser工具 (到根目录下直接make即可)这里注意create_defs.pl脚本需要依赖DBD,DBI,安装过程中可能出现如下错误:

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./create_defs.pl --host=localhost --user=root --password=yihaodian --db=test --table=customer  > include/table_defs.h
perl: symbol lookup error: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so: undefined symbol: mysql_init

通过如下方式解决:

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# mkdir /tmp/mysql-static
[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# cp /usr/lib64/mysql/*.a /tmp/mysql-static

删除DBD目录
重新解压
perl Makefile.PL –libs=”-L/tmp/mysqldbd-install -lmysqlclient”
重新编译即可解决

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# perl test.pl 
hello world

下面继续,获取这个表的定义:

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./create_defs.pl --host=localhost --user=root --password=yihaodian --db=test --table=customer  > include/table_defs.h
[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# cat include/table_defs.h
#ifndef table_defs_h
#define table_defs_h

// Table definitions
table_def_t table_definitions[] = {
	{
		name: "customer",
		{
			{ /* smallint(5) unsigned */
				name: "customer_id",
				type: FT_UINT,
				fixed_length: 2,

				has_limits: FALSE,
				limits: {
					can_be_null: FALSE,
					uint_min_val: 0,
					uint_max_val: 65535
				},

				can_be_null: FALSE
			},
			{ /*  */
				name: "DB_TRX_ID",
				type: FT_INTERNAL,
				fixed_length: 6,

				can_be_null: FALSE
			},
			{ /*  */
				name: "DB_ROLL_PTR",
				type: FT_INTERNAL,
				fixed_length: 7,

				can_be_null: FALSE
			},
			{ /* tinyint(3) unsigned */
				name: "store_id",
				type: FT_UINT,
				fixed_length: 1,

				has_limits: FALSE,
				limits: {
					can_be_null: FALSE,
					uint_min_val: 0,
					uint_max_val: 255
				},

				can_be_null: FALSE
			},
			{ /* varchar(45) */
				name: "first_name",
				type: FT_CHAR,
				min_length: 0,
				max_length: 135,

				has_limits: FALSE,
				limits: {
					can_be_null: FALSE,
					char_min_len: 0,
					char_max_len: 135,
					char_ascii_only: TRUE
				},

				can_be_null: FALSE
			},
			{ /* varchar(45) */
				name: "last_name",
				type: FT_CHAR,
				min_length: 0,
				max_length: 135,

				has_limits: FALSE,
				limits: {
					can_be_null: FALSE,
					char_min_len: 0,
					char_max_len: 135,
					char_ascii_only: TRUE
				},

				can_be_null: FALSE
			},
			{ /* varchar(50) */
				name: "email",
				type: FT_CHAR,
				min_length: 0,
				max_length: 150,

				has_limits: FALSE,
				limits: {
					can_be_null: TRUE,
					char_min_len: 0,
					char_max_len: 150,
					char_ascii_only: TRUE
				},

				can_be_null: TRUE
			},
			{ /* smallint(5) unsigned */
				name: "address_id",
				type: FT_UINT,
				fixed_length: 2,

				has_limits: FALSE,
				limits: {
					can_be_null: FALSE,
					uint_min_val: 0,
					uint_max_val: 65535
				},

				can_be_null: FALSE
			},
			{ /* tinyint(1) */
				name: "active",
				type: FT_INT,
				fixed_length: 1,

				has_limits: FALSE,
				limits: {
					can_be_null: FALSE,
					int_min_val: -128,
					int_max_val: 127
				},

				can_be_null: FALSE
			},
			{ /* datetime */
				name: "create_date",
				type: FT_DATETIME,
				fixed_length: 8,

				can_be_null: FALSE
			},
			{ /* timestamp */
				name: "last_update",
				type: FT_TIMESTAMP,
				fixed_length: 4,

				can_be_null: FALSE
			},
			{ type: FT_NONE }
		}
	},
};

提取需要的页

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# cp /var/lib/mysql/liuyang/customer.ibd  /tmp/
[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./page_parser -5 -f /tmp/customer.ibd 
Opening file: /tmp/customer.ibd:
2051		ID of device containing file
1798748		inode number
33184		protection
1		number of hard links
0		user ID of owner
0		group ID of owner
0		device ID (if special file)
147456		total size, in bytes
4096		blocksize for filesystem I/O
296		number of blocks allocated
1373360322	time of last access
1373360322	time of last modification
1373360322	time of last status change
147456	Size to process in bytes
104857600	Disk cache size in bytes

实际上对这个表并不能够挖掘出数据,也就是说无法挖掘出truncate过的数据,并且如果是drop操作在没有备份(冷备份orSlave)的情况下获取表的定义都成了问题。所以上面的操作可以获取表定义 但是无法unload出数据。

在使用innodb monitor之后发现并不能从21号子目录中抽取出数据:

TABLE: name liuyang/customer, id 20, flags 1, columns 12, indexes 4, appr.rows 2423
  COLUMNS: customer_id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 2; store_id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 1; first_name: DATA_VARMYSQL DATA_NOT_NULL len 135; last_name: DATA_VARMYSQL DATA_NOT_NULL len 135; email: DATA_VARMYSQL len 150; address_id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 2; active: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 1; create_date: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 8; last_update: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; 
  INDEX: name PRIMARY, id 21, fields 1/11, uniq 1, type 3  ----- extract from here
   root page 3, appr.key vals 2423, leaf pages 11, size pages 12
   FIELDS:  customer_id DB_TRX_ID DB_ROLL_PTR store_id first_name last_name email address_id active create_date last_update
  INDEX: name idx_fk_store_id, id 22, fields 1/2, uniq 2, type 0
   root page 4, appr.key vals 1, leaf pages 2, size pages 3
   FIELDS:  store_id customer_id
  INDEX: name idx_fk_address_id, id 23, fields 1/2, uniq 2, type 0
   root page 5, appr.key vals 1, leaf pages 2, size pages 3
   FIELDS:  address_id customer_id
  INDEX: name idx_last_name, id 24, fields 1/2, uniq 2, type 0
   root page 6, appr.key vals 1, leaf pages 4, size pages 5
   FIELDS:  last_name customer_id
[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser  -5 -f  pages-1373360911/FIL_PAGE_INDEX/0-21/0-00000003.page 
LOAD DATA INFILE '/tmp/percona-data-recovery-tool-for-innodb-0.5/dumps/default/customer' REPLACE INTO TABLE `customer` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'customer\t' (customer_id, store_id, first_name, last_name, email, address_id, active, create_date, last_update);

没有任何数据

同样我们重新创建一张表,插入数据,不做truncate是可以抽取出数据的。

mysql> use liuyang
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> insert into  customer (store_id,first_name,last_name,email,address_id,create_date) values (1,'liu','yang','www.yihaodian.com','141',sysdate());
Query OK, 1 row affected (0.00 sec)

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

。。。。。。


mysql> insert into  customer (store_id,first_name,last_name,email,address_id,create_date) values (1,'liu','yang','www.yihaodian.com','141',sysdate());
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./page_parser -5 -f /var/lib/mysql/liuyang/customer.ibd 
Opening file: /var/lib/mysql/liuyang/customer.ibd:
2054		ID of device containing file
1867914		inode number
33200		protection
1		number of hard links
203		user ID of owner
156		group ID of owner
0		device ID (if special file)
425984		total size, in bytes
4096		blocksize for filesystem I/O
840		number of blocks allocated
1373361248	time of last access
1373362171	time of last modification
1373362171	time of last status change
425984	Size to process in bytes
104857600	Disk cache size in bytes

[root@db-42 0-21]# ls -lrt
total 192
-rw-r--r-- 1 root root 16384 Jul  9 17:30 9-00000012.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 8-00000011.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 7-00000010.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 6-00000009.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 5-00000008.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 4-00000007.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 21-00000024.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 20-00000023.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 16-00000019.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 13-00000016.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 10-00000013.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 0-00000003.page
[root@db-42 0-21]# cat *.page  > /tmp/temp.page
[root@db-42 0-21]# cd ..
[root@db-42 FIL_PAGE_INDEX]# cd ..
[root@db-42 pages-1373362201]# cd ..
[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser  -5 -f /tmp/temp.page 
customer	1299	1	"liu"	"yang"	"www.yihaodian.com"	141	1	"2013-07-09 17:29:19"	"2013-07-09 09:29:19"
customer	1300	1	"liu"	"yang"	"www.yihaodian.com"	141	1	"2013-07-09 17:29:19"	"2013-07-09 09:29:19"
customer	1301	1	"liu"	"yang"	"www.yihaodian.com"	141	1	"2013-07-09 17:29:19"	"2013-07-09 09:29:19"
customer	1302	1	"liu"	"yang"	"www.yihaodian.com"	141	1	"2013-07-09 17:29:19"	"2013-07-09 09:29:19"
customer	1303	1	"liu"	"yang"	"www.yihaodian.com"	141	1	"2013-07-09 17:29:19"	"2013-07-09 09:29:19"
customer	1304	1	"liu"	"yang"	"www.yihaodian.com"	141	1	"2013-07-09 17:29:19"	"2013-07-09 09:29:19"
customer	1305	1	"liu"	"yang"	"www.yihaodian.com"	141	1	"2013-07-09 17:29:19"	"2013-07-09 09:29:19"
customer	1306	1	"liu"	"yang"	"www.yihaodian.com"	141	1	"2013-07-09 17:29:19"	"2013-07-09 09:29:19"

.............

通过LOAD语句直接将文件导入到MySQL

mysql> show tables;
+-------------------+
| Tables_in_liuyang |
+-------------------+
| customer          |
+-------------------+
1 row in set (0.00 sec)

mysql> truncate table  customer;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE '/tmp/customer.csv' REPLACE INTO TABLE `customer` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'customer\t' (customer_id, store_id, first_nam 
e, last_name, email, address_id, active, create_date, last_update);
Query OK, 2306 rows affected (0.07 sec)
Records: 2306  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
|     2306 |
+----------+
1 row in set (0.01 sec)

mysql> select * from customer limit 1;
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
| customer_id | store_id | first_name | last_name | email             | address_id | active | create_date         | last_update         |
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
|           1 |        1 | liu        | yang      | www.yihaodian.com |        141 |      1 | 2013-07-09 17:28:00 | 2013-07-09 09:28:00 |
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

可以看到在正常情况下可以顺利的unload出数据,这为一些非极端的环境下的恢复提供了很多的帮助。

Ref:如何从MySQL/InnoDB数据文件中的恢复数据
Ref:MySQL数据库InnoDB数据恢复工具使用总结