getting error “Table doesn't exist” in magento innodb database when browsing in phpmyadmin or sqlYog

Question

I have moved magento database (copy and paste xampp/mysql directory) from another system to my laptop(windows7,xampp), and configure mysql service path(xampp control panel showing run mysql fine). But now I am not able to access magento database, only 19 tables are listed in phpmyadmin.

all tables are listed in sqlyog, but when i browse them getting error message "table not exist".

I try to recover and follow this article steps, but when I am run this command mysqld –innodb_log_file_size=5242880 –innodb_force_recovery=6 on command window, getting an error " Can't create test file \usr\local\mysql\data.lower-test". Can you please suggest me why am i getting this error and how can I resolve it?

If above error we can ignore I mean this error not responsible for get back database then please suggest me how can I get back my database?

PS: I also copied "ibdata1", "ib_logfile0" and "ib_logfile1" on mysql directory. Now I can't access database from old system.

Thanks in advance.

Answer 1

FTPing or copying and pasting innodb tables from one filesystem to another isn't going to work. Try this to restore innodb tables:

[http://egil.biz/how-to-recover-mysql-data-from-innodb/][1]

Better yet, do nightly dumps of your magento database:

mysqldump -u david -h localhost -pmypassword mymagentodatabase > mymagentodatabase.sql | gzip mymagentodatabase.sql 

To restore, open up mysql on the command line:

use mymagentodatabase
source /home/david/mymagentodatabase.sql 

Restoring from the mysql command line is way faster than using a client like navicat or phpmyadmin.

In my work environment, I also have a clone of the production code base on my development machine, such as: http://dev.magentowebsite.com. When I have to, I restore the last dump to my local mysql, then go into core_config_data and change the secure and nonsecure urls to http://dev.magentowebsite.com. Then I have the live site on my local machine and can test things without breaking the live site.

Answer 2

The same question was (kind of) answered here. I had this issue before and I wasn't able to solve it (but I've learned my lesson). I found this answer on dba.stackexchange.com. Follow the links in the answer, maybe it helps.

---

_{Maybe this question is more suited for dba.stackexchange.com since it's not a Magento question. This "issue" reproduces for any innodb database}