How To Set lower_case_table_names In MySQL On Ubuntu – 2024

So I wanted to show how to set lower_case_table_names in MySQL on Ubuntu.

This is a really niche issue, I encountered it recently when trying to deploy an application that used MySQL 8.0.36 running on Ubuntu. The issue was that the application required lower case table names, something that MySQL doesn’t do by default and is a bit of a pain to rectify.

I’ll explain more in the intro!

Before we jump in, I’ve also written this post on Medium in case you prefer reading over there!

So to explain a bit more, by default MySQL doesn’t enable lower case table names. This can be a problem for an application that expects it. And is an even bigger pain to fix, essentially requirement a complete uninstall and reinstall of MySQL.

If you didn’t know MySQL is a popular and open-source database system. Efficiently storing and organising your data. It’s most widely used in web applications and uses the time proven SQL (Structured Query Language) to manage.

So a big more about my environment! I’m running MySQL 8.0.36 on Ubuntu 22.04 LTS. I’m going this simple because it offers the best stability and security at the moment.

If you’re reading this 5 months into the future, there may be better versions to run. I’d always recommend checking out the provider’s website to find out the latest, stable and supported versions of their software.

First things first, we need to stop MySQL:

sudo service mysql stop

No that MySQL is stopped, we need to move the lib directory for MySQL. Yep, seems extreme but it’s not enough to just delete the content of the directory:

sudo rm -rf /var/lib/mysql

Next, we need to recreate that folder and set the required permissions:

sudo mkdir /var/lib/mysql    
sudo chown mysql:mysql /var/lib/mysql
sudo chmod 700 /var/lib/mysql

Ace, now update your mysqld.cnf file to have lower_case_table_names set to 1:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add 'lower_case_table_names = 1'

Now we can initialize MySQL with the new settings plus a flag to set lower case table names:

sudo mysqld --defaults-file=/etc/mysql/my.cnf --initialize --lower_case_table_names=1 --user=mysql --console

We can now start the MySQL service:

sudo service mysql start

We’ll now set a new root password. To do this, we need to get the randomly generated one from the logs and update it inside MySQL:

sudo grep 'temporary password' /var/log/mysql/error.log
#Ji88H!e43
sudo mysql -u root -p
#Enter password here
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPasswordHere';
exit

All done! You can now start configuring your MySQL server. Best place to begin is with the secure installation script:

sudo mysql_secure_installation

If you simply want to check if this setting is enabled or to check if the above procedure worked, you can enter your MySQL environment and check it:

sudo mysql -u root -p
SHOW VARIABLES LIKE 'lower_case%';

It’ll likely return two items, but we are only concerned about ‘lower_case_table_names’.

Well that’s it. Nice and quick today, I’ve enjoyed writing this as it’s gone back to my roots. When I first started this website, it was focused on the issues I had faced during my technical woes.

I’m glad it’s grown into more than that, don’t get me wrong. But it’s still nice to revisit and do what I love most: writing about tech stuff no one cares about!

Hope you learnt something today.

Enjoy! 🎉