Today, I wanted to show you how to set lower case tables names for MySQL on Ubuntu. I recently had this issue as some software we used required this to be set.
I’ll explain why this was a pain in the introduction. For now, just know that you can read this on Medium if you prefer!
How To Screenshot On All 3 Desktop OS’s
Knowing how to screenshot is one of the corner stones to receiving or providing technical…
Scripting Secrets with PowerShell and OneTimeSecret.com
In my day job, I often have to send out links to customers. These links…
How to Add Telephone Links to Your Website
So you’ve gone through all the trouble of creating a stunning website for your business,…
Who Owns Microsoft?
Microsoft is one of the largest and most recognisable companies on the planet, operating in…
7 New Website Inspiration Tools
Over the last couple of months, I’ve found and kept 7 new website inspiration tools…
Microsoft Clarity – The Definitive Guide
I know I’m late to discovering this tool, but for anyone else out that that…
Introduction
I mentioned above that this was a painful process. It’s painful because MySQL ships with this NOT set by default. And once MySQL is initialised, you can’t just flick a switch. You instead need to rebuild MySQL and start again!
So I wanted to take some time today and show you that process.
This took me about 3 hours to figure out and put together, so I hope someone appreciates it…
Set Lower Case Tables Names for MySQL on Ubuntu
First, we need to stop MySQL:
sudo service mysql stop
Then we need to remove the MySQL directory inside of lib and recreate it with the correct permissions and owner:
sudo rm -rf /var/lib/mysql
sudo mkdir /var/lib/mysql
sudo chown mysql:mysql /var/lib/mysql
sudo chmod 700 /var/lib/mysql
If you’re interested in what the above commands achieve, let me explain. First, delete the directory and all contents recursively. Then create a new directory, make the mysql user and mysql group the owner and set the permissions so that only the mysql user has full control.
Next, we need to edit the mysqld.cnf file:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Inside of here, add: lower_case_table_names=1
Now that we have the new directories and the updated config file, we can initialise MySQL with the correct settings:
sudo mysqld --defaults-file=/etc/mysql/my.cnf --initialize --lower_case_table_names=1 --user=mysql --console
We should now have MySQL ready but not running. At this point, we won’t know the randomly generated root password so we will need to grab that too:
sudo service mysql start
sudo grep 'temporary password' /var/log/mysql/error.log
The second command in the section above should output the temporary root password for MySQL. We can now enter the database and update this using:
sudo mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'New Password Here';
exit
You’re now all set! And to confirm everything is happy and running with lower case table names, you can use this command:
SHOW VARIABLES LIKE 'lower_case_%';
Enjoy! 🎉
