slider

Data record repeat

0
1

1. create test table

[root@killdb innodb_recovery]# mysql -uroot -p

mysql> set global innodb_file_per_table=on;
mysql> show global variables like '%file_per%';

+-----------------------+-------+
|         Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table |    ON |
+-----------------------+-------+

1 row in set (0.00 sec)

mysql> use recover;
mysql> create table test_0731(a varchar(30));
mysql> insert into test_0731 values('www.killdb.com’); +++++only one record

[root@killdb recover]# ls -ltr /var/lib/mysql/recover/test_0731*
-rw-rw----. 1 mysql mysql 8554 Jun 2 21:46 /var/lib/mysql/recover/test_0731.frm
-rw-rw----. 1 mysql mysql 98304 Jun 2 21:46 /var/lib/mysql/recover/test_0731.ibd
[root@killdb recover]#

2. backup table structure

[root@killdb recover]# mysqldump --opt -d -uroot -proger recover test_0731 > /tmp/innodb_recovery/recover/test_0731.sql
[root@killdb recover]#

3. drop table

mysql> drop table test_0731;

4. stop mysql server

[root@killdb innodb_recovery]# service mysql stop
Shutting down MySQL.....                                   [  OK  ]
[root@killdb innodb_recovery]#  

5. scan lv

[root@killdb innodb_recovery]# ./stream_parser -f /var/lib/mysql/ibdata1
Opening file: /var/lib/mysql/ibdata1
File information:
ID of device containing file:        64768
inode number:                       924765
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                      496
group ID of owner:                     491
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          69632
time of last access:            1496411588 Fri Jun  2 21:53:08 2017
time of last modification:      1496411588 Fri Jun  2 21:53:08 2017
time of last status change:     1496411588 Fri Jun  2 21:53:08 2017
total size, in bytes:             35651584 (34.000 MiB)
Size to process:                  35651584 (34.000 MiB)

[root@killdb innodb_recovery]# ./stream_parser -f /dev/mapper/vg_oel6-lv_root -t 36000000k
Opening file: /dev/mapper/vg_oel6-lv_root
File information:
ID of device containing file:            5
inode number:                         6307
protection:                          60660 (block device)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       6
device ID (if special file):         64768
blocksize for filesystem I/O:         4096
number of blocks allocated:              0
time of last access:            1496212077 Wed May 31 14:27:57 2017
time of last modification:      1496113795 Tue May 30 11:09:55 2017
time of last status change:     1496113795 Tue May 30 11:09:55 2017
total size, in bytes:                    0 (0.000 exp(+0))
Size to process:               36864000000 (34.332 GiB)

Worker(0): 1.04% done. 2017-06-02 21:59:07 ETA(in 00:06:27). Processing speed: 89.844 MiB/sec
......
Worker(0): 99.16% done. 2017-06-02 21:57:28 ETA(in 00:00:02). Processing speed: 119.784 MiB/sec

All workers finished in 293 sec

6. recover table

[root@killdb innodb_recovery]# ./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t ./dictionary/SYS_TABLES.sql | grep recover/test

00000000114A    71000002210182  SYS_TABLES      "recover/test\_0731"    177     1       1       0       0       ""      2
00000000114A    71000002210182  SYS_TABLES      "recover/test\_0731"    177     1       1       0       0       ""      2
00000000114A    71000002210182  SYS_TABLES      "recover/test\_0731"    177     1       1       0       0       ""      2

SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/tmp/innodb_recovery/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);

[root@killdb innodb_recovery]# ./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t ./dictionary/SYS_INDEXES.sql | grep 177

00000000114A    71000002210110  SYS_INDEXES     177     175     "GEN\_CLUST\_INDEX"     0       1       2       4294967295
00000000114A    71000002210110  SYS_INDEXES     177     175     "GEN\_CLUST\_INDEX"     0       1       2       4294967295
00000000114A    71000002210110  SYS_INDEXES     177     175     "GEN\_CLUST\_INDEX"     0       1       2       4294967295

SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/tmp/innodb_recovery/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`);

[root@killdb innodb_recovery]# ./c_parser -6f pages-vg_oel6-lv_root/FIL_PAGE_INDEX/0000000000000175.page -t recover/test_0731.sql |  head -5

-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (1 1)
000000023E73    00000000113F    EA0000022C0110  test_0731       "www.killdb.com"
-- Page id: 3, Found records: 1, Lost records: NO, Leaf page: YES
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (1 1)
000000023E73    00000000113F    EA0000022C0110  test_0731       "www.killdb.com"

SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/tmp/innodb_recovery/dumps/default/test_0731' REPLACE INTO TABLE `test_0731` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'test_0731\t' (`a`);

root@killdb innodb_recovery]# ./c_parser -6f pages-vg_oel6-lv_root/FIL_PAGE_INDEX/0000000000000175.page -t recover/test_0731.sql > dumps/default/test_0731 2> dumps/default/test_0731_load.sql

[root@killdb innodb_recovery]# ls -ltr dumps/default/test_0731*
-rw-r--r--. 1 root root 219 Jun  2 22:11 dumps/default/test_0731_load.sql
-rw-r--r--. 1 root root 422 Jun  2 22:11 dumps/default/test_0731 

7. load table data

mysql> use recover;
mysql> source recover/test_0731.sql
mysql> source dumps/default/test_0731_load.sql

mysql> select * from test_0731;
+----------------+
| a              |
+----------------+
| www.killdb.com |
| www.killdb.com |
+————————————————+                  ++++ two record ?  why ?

Look forward to your reply! thanks a lot!

  • You must to post comments
0
0

It looks to me like stream_parser found two copies of same page #3. It might happen because one copy is saved in ib_logfile*. The ib_logfile0 and ib_logfile1 files is a REDO log, InnoDB saves there recent changes to pages.

To prevent this side effect in future you should always define a primary key in a table. There are many other reasons to do so, it’s just best practice.

If you stuck with existing table structure and get unwanted duplicates I’d recommend to define a unique index on that field (or fields) and load the dumps, the duplicates will be ignored.

  • You must to post comments
0
0

Thank you for your reply. I’ll test it again.

  • You must to post comments
Showing 2 results
Your Answer

Please first to submit.