moving binary database folder is causing issues with innodb tables so I have a mix of myisam and innodb tables on a database. (its a pretty big db 30gb) I copied the folder of the database from one server to the next /var/lib/mysql/databasename well the myisam tables are opening The innodb tables are listed but when I click on any of them on the left tab (phpmyadmin) I get this error: #1146 - Table 'databasename.users' doesn't exist this would be for the users table. Other innodb tables are having the same issue. I would like to know how to remedy this issue. I logged in to mysql directly through ssh as root, when I do : SHOW TABLES; the table users and the other innodb tables are all listed. When I do SHOW COLUMNS FROM users; I get :

mysql&gt; show columns from article;
ERROR 1146 (42S02): Table 'sitecontent.article' doesn't exist


any ideas please ? (issue resides in innodb tables only!)
You cannot copy a database folder that has InnoDB tables because InnoDB storage engine maintains a symbiotic relationship between the InnoDB tables' physical files (frm and .ibd) and the data dictionary.



The data dictionary is inside the system tablespace file known as ibdata1.

You cannot copy a data folder and expect it to be independent of ibdata1. 

SHOW TABLES; works because it only scans for .frm files and does not access metadata.

You could do one of two things


mysqldump the database and load the mysqldump into the target server
Run FLUSH TABLES;, disconnect the .ibd files from ibdata1 before copying to the other server. Then, reattach the .ibd files on the target server


Recover MySQL database from data folder without ibdata1 from ibd files
Other Posts



GIVE IT A TRY !!!