Re-initialize MySQL

 

前言

MySQL8.0 新增 data dictionary的概念, 初始化時默認"lower-case-table-names=0"

如果要更改, 必須 Re-initialize MySQL

如果直接更改它會令 Service start 失敗

log

... Different lower_case_table_names settings for server ('1') and data dictionary ('0').
... Data Dictionary initialization failed.
... Aborting

 


Re-initialize

 

Backup

service mysql stop

mv /var/lib/mysql /var/lib/mysql.orig

mkdir /var/lib/mysql

chown mysql: /var/lib/mysql

chmod 770 /var/lib/mysql

Re-initialize

 

Add "lower_case_table_names = 1" to the [mysqld] section in /etc/mysql/mysql.conf.d/mysqld.cnf

mysqld --initialize-insecure --datadir=/var/lib/mysql --user=mysql --lower_case_table_names=1 --console

P.S. 一舨 initialize

cd /var/lib/mysql

# secure by default (generation of a random initial root password, password is marked as expired)

mysqld --initialize --user=mysql

# no root password is generated

mysqld --initialize-insecure --user=mysql

ls -l /var/lib/mysql

service mysql start

設定 MySQL 的 root A/C

# 設定 pw

ALTER USER 'root'@'localhost' IDENTIFIED BY 'some_pass';

# 建立 'root'@'%'

CREATE USER 'root'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
GRANT GRANT OPTION ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

Checking

mysql -p

SHOW VARIABLES LIKE 'lower_case_%';

 

Creative Commons license icon Creative Commons license icon