MySQL: How to avoid case sensitivity in SQL queries when migrate from Windows to Linux

Just altering the lower_case_table_names setting isn't enough. It needs to be done before you import your database(s).

The MySQL 5.1 documentation lists a procedure for moving between Windows and Linux/UNIX. This will ensure that your desired rules for enforcing case sensitivity are followed. Take a look and verify that you did these steps in the correct order:

To convert one or more entire databases, dump them before setting lower_case_table_names, then drop the databases, and reload them after setting lower_case_table_names:

1 - Use mysqldump to dump each database:

mysqldump --databases db1 > db1.sql

mysqldump --databases db2 > db2.sql

... Do this for each database that must be recreated.

2 - Use DROP DATABASE to drop each database.

3 - Stop the server, set lower_case_table_names in the [mysqld] section of your \etc\mysql\my.cnf file, and restart the server.

4 - Reload the dump file for each database. Because lower_case_table_names is set, each database and table name will be converted to lowercase as it is recreated:

mysql < db1.sql

mysql < db2.sql

Linux -> Windows you have two obvious options.

1. Setup a SSHD on your Windows machine and use the above command (mysql binary would need to be in your search path under Windows).

2. Configure your root (or similar privileged account) to access your Windows MySQL host over network then do:

mysqldump -u username -p 'password' db-name | mysql -h windowsip -u username -p 'password' db-name

Unfortunately with #2, if you have complex indicies (or huge tables) you'll have issues with net_read_timeout on your Linux host. For most situations I would expect this to just work, though.