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 Upgrade MySQL Community to MySQL Enterprise on Red Hat 9
Been a while since I made a post, so let’s jump straight back in with…
How to Setup WordPress Backups (FREE!)
Personally, I like my website. A lot. And I wouldn’t want one mistake to make…
Install NGINX on an Offline RHEL System – 2 Systems Required!
Been a while since my previous post, but let’s hope the magic hasn’t rubbed off…
MySQL 8.4 Replication with SSL on Ubuntu 22.02
So I recently wrote a post and made a YouTube video on setting up MySQL…
Gutenberg Just Broke my Website
More of a story and vent than an informative article. So to save you reading…
How to Design a Website in 5 Steps
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! 🎉
