Mysql – linux thesql case insensitive lower_case_table_names=1 make server cannot be started

The simplest option is to just back up everything in the filesystem and carry it over.

To find out where your data is, look at the datadir variable with in mysql

test> show variables like 'datadir';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /var/lib/mysql/   |
+---------------+-------------------+
1 row in set (0.00 sec)

Note Your actual value may differ.

Then from your shell shut down mysql, back it up and copy it over. Something like:

> sudo service mysql shutdown
> cd /var/lib/mysql
> sudo tar -czf /path/with/enough/space/db.tgz *

Copy your db.tgz over to your new server and uncompress it on your new servers datadir while it is shutdown

I was able to solve this problem by doing the following :

1. Move all the contents of my data directory (in my case /var/lib/mysql/ ) to a safe place.
2. Execute the command service mysqld start, this will:
   • recreate two DBs:
     • mysql
     • test
   • recreate ibdata1, ib_logfile0, and ib_logfile0
3. Copy the contents that you backed up (in step 1) from the safe place to the data directory again, of course, except both mysql and test directories.
4. Using the following command mysqladmin -u root -p'' password yournewpass reset the password of your root user.

An Advice:

Always make sure you make a backup of your databases structure and data to avoid looping in such loops, just delete every thing, re-install your engine, and restore your data :)