Skip to content

Instantly share code, notes, and snippets.

Last active July 19, 2021 16:25
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
What would you like to do?
Reinitialize MySQL in Windows

Reinitialize MySQL in Windows

MySQL has an annoying habit of lowercasing table names under Windows with the default installation.

The suggested solution for this is to set lower_case_table_names=2 under the [mysqld] section in my.ini.

This works well, but with one issue. You can not change this after you install MySQL, and this is NOT the the default under Windows on a new install, and many times installs do not you allow you to edit my.ini before the server is initialized.

Here is how to change the lower_case_table_names on an existing MySQL install under Windows:

  • First, backup all databases you which to save with a standard MySQL backup.
  • Shut down MySQL Server.
  • Delete data directory (this is why you need the backups) generally in data directory of install.
  • Edit your my.ini file to include lower_case_table_names=2 under the [mysqld]
  • create a mysql-init.txt file with the following content (this may not be needed, but seems to work
    • ALTER USER 'root'@'localhost' IDENTIFIED BY 'whatEverPasswordYouWant';
  • run the following command from the install directory (note quotes and double \ are recommended in example paths):
bin\mysqld --defaults-file="c:\\wamp64\\bin\\mysql\\mysql8.0.25\\my.ini" --initialize --init-file="c:\\wamp64\\bin\\mysql\\mysql8.0.25\\mysql-init.txt" --console

This will dump the results of the command to the console (instead of the log file) so you can see the results instantly.

Now restart MySQL as a service and restore your databases. Note you may want to change table case names, as they should now be preserved correctly.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment