怎样从frm，ibd恢复MYSQL

怎样从frm，ibd恢复MYSQL

同学你好，很高兴为您解答！

    有两种方法，一种方法使用mysql的check table和repair table 的sql语句，另一种方法是使用MySQL提供的多个myisamchk, isamchk数据检测恢复工具。前者使用起来比较简便。推荐使用。
1. check table 和 repair table
登陆mysql 终端：
mysql -uxxxxx -p dbname
check table tabTest;
如果出现的结果说Status是OK，则不用修复，如果有Error，可以用：
repair table tabTest;
进行修复，修复之后可以在用check table命令来进行检查。在新版本的phpMyAdmin里面也可以使用check/repair的功能。
2. myisamchk, isamchk
其中myisamchk适用于MYISAM类型的数据表，而isamchk适用于ISAM类型的数据表。这两条命令的主要参数相同，一般新的系统都使用MYISAM作为缺省的数据表类型，这里以myisamchk为例子进行说明。当发现某个数据表出现问题时可以使用：
myisamchk tablename.MYI
进行检测，如果需要修复的话，可以使用：
myisamchk -of tablename.MYI
关于myisamchk的详细参数说明，可以参见它的使用帮助。需要注意的时在进行修改时必须确保MySQL服务器没有访问这个数据表，保险的情况下是最好在进行检测时把MySQL服务器Shutdown掉。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
另外可以把下面的命令放在你的rc.local里面启动MySQL服务器前：
[ -x /tmp/mysql.sock ] && /pathtochk/myisamchk -of /DATA_DIR/*/*.MYI
其中的/tmp/mysql.sock是MySQL监听的Sock文件位置，对于使用RPM安装的用户应该是/var/lib/mysql/mysql.sock，对于使用源码安装则是/tmp/mysql.sock可以根据自己的实际情况进行变更，而pathtochk则是myisamchk所在的位置，DATA_DIR是你的MySQL数据库存放的位置。
需要注意的时，如果你打算把这条命令放在你的rc.local里面，必须确认在执行这条指令时MySQL服务器必须没有启动！检测修复所有数据库(表)


　　希望我的回答能帮助您解决问题，如您满意，请采纳为最佳答案哟。


　　再次感谢您的提问，更多财会问题欢迎提交给高顿企业知道。


高顿祝您生活愉快！

method #1 – create work tables 1. start up clean/fresh instance of mysql with innodb_file_per_table enabled. 2. now, we need to find the table id that mysql is currently set at, as well as the table id for the table we need to recover. note: step 2 (2a – 2f) is simply to find the table id that is stored inside of the .ibd file. i’ve written a php script to determine this, so using the script can save a bunch of time. see the bottom of this page (under “associated files”) for the exact script. 2a. create a test database: mysql> create database test1; mysql> use test1; 2b. issue the create table command for the table: mysql> create table `product` ( `product_id` bigint(20) unsigned not null auto_increment, `brand_id` int(10) unsigned default null, `product_type_id` int(10) unsigned default null, `group_id` int(10) unsigned default null, `product_name` varchar(500) not null, `default_email_id` varchar(48) default null, `product_status` tinyint(1) not null, `client_id` bigint(20) unsigned default null, `last_modified_by` varchar(45) not null, `last_modified_date` datetime not null, primary key (`product_id`) ) engine=innodb; 2c. discard the tablespace, which will delete the newly created .ibd file: mysql> alter table product discard tablespace; 2d. copy the pre-existing .ibd file to the datadir/test1 folder 2e. import this tablespace: mysql> alter table product import tablespace; this should produce the following error (at least this is most likely). the only way it would not is if mysql’s current table id matched that of the preexisting ibd table id. in which case, you can now dump your table. error 1030 (hy000): got error -1 from storage engine 2f. so, now to check the error log (manually). look for the following entry: 081010 11:47:40 innodb: error: tablespace id in file '.test1product.ibd' is 1193, but in the innodb innodb: data dictionary it is 1. so, now we know the internal table id is at 1, and that of the ibd table is 1193. 3. clean up working database: 3a. manually move the ibd file from the $datadir to a safe location (as you will need this file again). 3b. drop this table. mysql> drop table product; note this does not re-set the internal table counter. 4. you’ll need to create the number of tables you need to increase the internal table id value. in this case, you’d create 1191 test innodb tables (already at 1, and need to leave 1 for the actual table, so 1193-2=1191). run below in a loop. for ($1=1; $i<=1191; $1++) { create table t# (id int) engine=innodb; } i accomplished this via a simple php script. see the bottom of this page (under "associated files") for the exact script. 5. after these are created, go ahead and drop this database and all tables (as they are not needed). drop db test1; 6. now, re-perform steps 2a through 2e. mysql> create database test1; mysql> use test1; mysql> create table `product` ( ... ) engine=innodb; mysql> alter table product discard tablespace; <-- here is where you copy back the original ibd file to /$datadir/test1/ --> mysql> alter table product import tablespace; success!

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息