MySQL – Issues Moving Binary Database Folder with InnoDB Tables

innodbmyisamMySQL

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> show columns from article;
ERROR 1146 (42S02): Table 'sitecontent.article' doesn't exist

any ideas please ? (issue resides in innodb tables only!)

Best Answer

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.

InnoDB Architecture

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

GIVE IT A TRY !!!