Search code examples
mysqlinnodbcentos7

Set up mysql 8 with lower_case_table_names on CentOS 7

I'm trying to set up a mysql 8 intsall on centos 7 and am having a hell of a time. We have some legacy software that was connecting to old mysql dbs via lowercase names and I want to carry that over to this new setup.

I'm having issues with setting this config properly, I don't know what file to set or when to set it or even what user/permission level to set it under.

When I set it to 1 in /etc/my.cnf then try to start up mysqld I get:

[ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('0') and data dictionary ('1').

I've reinstalled mysql thinking a fresh start would help with my /etc/my.cnf with the following settings:

lower_case_table_names=1
default_authentication_plugin=mysql_native_password
bind-address=REDACTED
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

After installing mysql-server and copying the .cnf file to it's spot I run:

mysqld --defaults-file=/etc/my.cnf

and am greeted with:

2018-10-22T21:30:27.826322Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2018-10-22T21:30:27.826414Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2018-10-22T21:30:28.057606Z 0 [Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/be-db-10.lower-test
2018-10-22T21:30:28.057668Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 27077
2018-10-22T21:30:28.060609Z 0 [Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/be-db-10.lower-test
2018-10-22T21:30:28.060628Z 0 [Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/be-db-10.lower-test
2018-10-22T21:30:28.061062Z 0 [ERROR] [MY-010187] [Server] Could not open file '/var/log/mysqld.log' for error logging: Permission denied
2018-10-22T21:30:28.061098Z 0 [ERROR] [MY-010119] [Server] Aborting

I assume at some point I royally screwed up some permissions or mysql isn't being fully removed?

Solution

  • I'm pretty sure lower_case_table_names must be set before installing. The new "Data Dictionary" needs to be constructed once with the setting frozen.

    So, yes, fully remove, and completely start over (after changing my.cnf).