How to install MySQL 8.0 with lower_case_table_names=1 on Ubuntu Server 20.04 LTS successfully?
Solution 1
So far, I can get it to work with a workaround: by re-initializing MySQL with the new value for lower_case_table_names
after its installation. These are the requiered steps:
- Install MySQL:
sudo apt-get update sudo apt-get install mysql-server -y
- Stop the MySQL service:
sudo service mysql stop
- Delete the MySQL data directory:
sudo rm -rf /var/lib/mysql
- Recreate the MySQL data directory (yes, it is not sufficient to just delete its content):
sudo mkdir /var/lib/mysql sudo chown mysql:mysql /var/lib/mysql sudo chmod 700 /var/lib/mysql
- Add
lower_case_table_names = 1
to the[mysqld]
section in/etc/mysql/mysql.conf.d/mysqld.cnf
. - Re-initialize MySQL with
--lower_case_table_names=1
:sudo mysqld --defaults-file=/etc/mysql/my.cnf --initialize --lower_case_table_names=1 --user=mysql --console
- Start the MySQL service:
sudo service mysql start
- Retrieve the new generated password for MySQL user
root
:sudo grep 'temporary password' /var/log/mysql/error.log
- Change the password of MySQL user
root
either by:
and executing:sudo mysql -u root -p
afterwards, OR by calling the "hardening" script anyway:ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPa$$w0rd';
sudo mysql_secure_installation
After that, you can verify the lower_case_table_names
setting by entering the MySQL shell:
sudo mysql -u root -p
and executing:
SHOW VARIABLES LIKE 'lower_case_%';
Expected output:
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 1 |
+------------------------+-------+
Solution 2
It is also possible to create the directory /etc/mysql/mysql.conf.d/
and the corresponding mysqld.cnf
in there before installing mysql-server.
If you configure lower_case_table_names = 1
in mysqld.cnf
and then install the server, it will initialize with lower case.
stackprotector
Updated on September 18, 2022Comments
-
stackprotector over 1 year
It seems to be a lot of trouble to get MySQL running with
lower_case_table_names=1
as can be seen in:- lower_case_table_names=1 on Ubuntu 18.04 doesn't let mysql to start
- lower_case_table_names Settings in MySQL 8.0.12
- Set up mysql 8 with lower_case_table_names on CentOS 7
- Mysql : can't set lower_case_table_names variable
- lower_case_table_names option not working
I'm trying to install MySQL 8.0.19 on Ubuntu Server 20.04 LTS.
lower_case_table_names=1
cannot be set after mysql has already been initialized, which happens automatically when installing from the package repositories.According to the documentation Server System Variables | sysvar_lower_case_table_names:
For APT installations on Debian and Ubuntu, however, the server is initialized for you, and there is no opportunity to configure the setting in an option file beforehand. You must therefore use the
debconf-set-selection
utility prior to installing MySQL using APT to enablelower_case_table_names
. To do so, run this command before installing MySQL using APT:sudo debconf-set-selections <<< "mysql-server mysql-server/lowercase-table-names select Enabled"
I did that on a freshly installed Ubuntu Server 20.04 and ran
sudo apt-get install mysql-server
afterwards, but it did not changelower_case_table_names
to 1.If at all, what am I doing wrong? How can I successfully install MySQL 8.0 with
lower_case_table_names
set to 1?-
user2149308 almost 4 yearsPlease check if these instructions work for you: stackoverflow.com/questions/53103588/…
-
stackprotector almost 4 years@user2149308 They finally helped me to find a workaround at least. You have to respect the typo from the comments and add additional steps at the end to gain control over root again. I summarizd all necessary steps in an answer below.
-
stackprotector about 3 yearsSounds like a good shortcut! Is it enough to insert this one line into
mysqld.cnf
and will other defaults be added by the installation or should you have a completemysqld.cnf
ready before installing? -
Panki about 3 yearsTo be honest I am not sure, which is why I went with the complete config.