Moving/Backing-Up Innodb tables from Ubuntu to MacOSX or Windows *argh*
I just had the most frustrating and long battle with moving a MySQL database (both MyISAM and InnoDB tables) from my Ubuntu Dapper Drake 6.06 LTS. Currently, I have a MySQL slave server set up to accept a trickle down of data from the master of a VPN line to the office. This allows me to shutdown the slave without interrupting the production environment and take snapshots of the database. Well, I was doing my due diligence and wanted to check my restore process and write a shell script or little C program to handle some automation. Come to find out, when I restored my copy of the database on my Mac OSX (10.4.10) I would get an error saying that my Innodb tables didn’t exist (“ERROR 1146 (42S02): Table ‘db.table’ doesn’t exist”) but I was very careful when creating a binary backup of the files. I checked and double checked the config files and my Innodb set-up. Everything seemed in order.
After searching for days ( here and there, when I had the time to get caught in a rabbit chase ), I finally found a flag that I thought might show some effect. Indeed it did.
[mysqld] lower_case_table_names=1
See: http://dev.mysql.com/doc/refman/5.0/en/moving.html
This however encourages this flag to be set before any tables have been created. Too late! So, I switched the flag to 0 and it fixed all my problems on both Mac OSX and Windows XP. Apparently, by default, this flag is set to 1 now. Hope this helps someone that is perplexed by this problem.
[mysqld] lower_case_table_names=0