CODEWITHSUNDEEP
mysqlinnodbcentos7
jawknee530
jawknee530

Reputation: 319

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?

Upvotes: 4

Views: 6010

Answers (2)

Ndrik7
Ndrik7

Reputation: 173

Before the value lower_case_table_names can be changed, unistall mysql on CentOs 7

yum remove mysql mysql-server

After uninstall process, rename or delete mysql data

mv /var/lib/mysql /var/lib/mysql_backup (backup) or rm -rf /var/lib/mysql (remove)

Install again mysql

yum install mysql mysql-server

Before run the mysql server, add lower_case_table_names=1 on /etc/my.cnf, ex: nano /etc/my.cnf Save the file and then start the mysql server with command systemctl start mysqld

checking the result can be done with query SHOW VARIABLES like 'lower%';

you should get lower_case_table_names with value 1

I hope it can help.

Upvotes: 3

Rick James
Rick James

Reputation: 142518

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).

Upvotes: 2

Related Questions