moving binary database folder is causing issues with innodb tables

Question

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!)

Answer 1

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

• 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 !!!

• nodejsj: I copied the ibdata1 as well ... pretty much moved the mysql folder to a different server.Some databases are showing others arent
• RolandoMySQLDBA: You cannot move ibdata1 from on server to another because the data dictionary is shared by all subfolders in the old server. Any references to databases in the target server are now inaccessible. You will have to reattach tables in the target server. LESSON: If you move ibdata1, everything should comes with it. In the future (MySQL 5.7), InnoDB will no longer need a data dictionary.