snippetsqlMinor
How to recover a crashed innodb table...?
Viewed 0 times
crashedinnodbrecoverhowtable
Problem
I Have a
I don't have changed anything in my.cnf.I don't know how it get crashed.
How can i recover it
...
Content of error log are as
EDIT
Someone has opened the City.frm file and modified it's content.So How will I recover it.? I am using
UPDATE: Adding my.cnf as asked by some peoples.
Innodb Specific variables in My.Cnf
```
innodb_file_per_table
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 8M
innodb_flush_log_at_tr
Innodb table i got the error asmysql> check table City;
+-------------------+-------+----------+----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------+-------+----------+----------------------------------------------------------+
| world_innodb.City | check | Error | Incorrect information in file: './world_innodb/City.frm' |
| world_innodb.City | check | error | Corrupt |
+-------------------+-------+----------+----------------------------------------------------------+
2 rows in set (0.00 sec)I don't have changed anything in my.cnf.I don't know how it get crashed.
How can i recover it
...
Content of error log are as
120207 11:50:47 InnoDB: highest supported file format is Barracuda.
120207 11:50:47 InnoDB: Waiting for the background threads to start
120207 11:50:48 InnoDB: 1.1.8 started; log sequence number 5713326
120207 11:50:48 [Note] Event Scheduler: Loaded 0 events
120207 11:50:48 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.5.20-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)
120207 11:50:57 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information in file: './world_innodb/City.frm'EDIT
Someone has opened the City.frm file and modified it's content.So How will I recover it.? I am using
innodb_file_per_table.UPDATE: Adding my.cnf as asked by some peoples.
Innodb Specific variables in My.Cnf
```
innodb_file_per_table
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 8M
innodb_flush_log_at_tr
Solution
I created a table called test2
I then inserted one record
I stopped MySQL and then added a bunch of characters to the test2.frm file with vi.
I then started MySQL and saw the following
I then created another table with the same structure
I then stopped mysql and copied test3.frm over test2.frm
I restarted MySQL and had access to test2
Here is the record that I inserted
If you know the structure of the table City , you could create an identical table and then copy the .frm file over the existing corrupted one
CREATE TABLE `test2` ( `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;I then inserted one record
insert into test2 values(1);I stopped MySQL and then added a bunch of characters to the test2.frm file with vi.
I then started MySQL and saw the following
mysql> show table status \G;
*************************** 1. row ***************************
Name: test2
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: Incorrect information in file: './test2/test2.frm'I then created another table with the same structure
CREATE TABLE `test3` ( `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;I then stopped mysql and copied test3.frm over test2.frm
cp /var/lib/mysql/testdb/test3.frm /var/lib/mysql/testdb/test2.frmI restarted MySQL and had access to test2
mysql> show table status \G;
*************************** 1. row ***************************
Name: test2
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 25675431936
Auto_increment: NULL
Create_time: 2012-02-07 16:24:20
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:Here is the record that I inserted
mysql> select * from test2;
+------+
| t |
+------+
| 1 |
+------+
1 row in set (0.00 sec)If you know the structure of the table City , you could create an identical table and then copy the .frm file over the existing corrupted one
Code Snippets
CREATE TABLE `test2` ( `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;insert into test2 values(1);mysql> show table status \G;
*************************** 1. row ***************************
Name: test2
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: Incorrect information in file: './test2/test2.frm'CREATE TABLE `test3` ( `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;cp /var/lib/mysql/testdb/test3.frm /var/lib/mysql/testdb/test2.frmContext
StackExchange Database Administrators Q#12370, answer score: 3
Revisions (0)
No revisions yet.