Mysql 8 的大小写问题

1 删除数据库

1.1 删除error.log

为了方便查看错误日志，可以先将/var/log/mysql/error.log删除。

sudo service mysql stop;
sudo rm /var/log/mysql/error.log;

1.2 删除系统数据库与用户数据库

sudo rm -rf /var/lib/mysql;

1.3 创建数据库目录

sudo mkdir /var/lib/mysql;

2 赋权

sudo chown -R mysql:mysql /var/lib/mysql;
sudo chmod 750 /var/lib/mysql;

3 初始化mysql

sudo mysqld --defaults-file=/etc/mysql/my.cnf --initialize --lower_case_table_names=1 --user=mysql --basedir=/var/lib/mysql --datadir=/var/lib/mysql --console;

注意这里：

1. –initialize
2. –lower_case_table_names=1
3. –user=mysql
4. –basedir=/var/lib/mysql
5. –datadir=/var/lib/mysql (这个目录可以与basedir不一样，但是我没有成功，原因不明)

4 添加lower_case_table_names=1

修改/etc/mysql/mysql.conf.d/mysqld.cnf

sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# 增加lower_case_table_names
lower_case_table_names=1
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
# /var/lib/mysql变更为/var/lib/mysql/data
datadir		= /var/lib/mysql/data
log-error	= /var/log/mysql/error.log

启动mysql服务

sudo service mysql start;

5 shell

#!/bin/sh
# ---------------------------------------------------------------------
# 初始化mysql
# ---------------------------------------------------------------------
echo "停止mysql服务\n"
sudo service mysql stop;

echo "删除/var/log/mysql/error.log"
sudo rm /var/log/mysql/error.log;
echo "删除/var/lib/mysql\n"
sudo rm -rf /var/lib/mysql;

echo "创建数据库基础目录/var/lib/mysql"
sudo mkdir /var/lib/mysql;
echo "为数据库基础目录增加mysql用户"
sudo chown -R mysql:mysql /var/lib/mysql;
echo "为数据库基础目录赋权/var/lib/mysql\n"
sudo chmod 750 /var/lib/mysql;

echo "创建数据目录/var/lib/mysql/data"
sudo mkdir -p /var/lib/mysql/data;
echo "为数据目录增加mysql用户"
sudo chown -R mysql:mysql /var/lib/mysql/data;
echo "为数据目录赋权/var/lib/mysql/data\n"
sudo chmod 750 /var/lib/mysql/data;

echo "初始化mysql\n"
sudo mysqld --defaults-file=/etc/mysql/my.cnf --initialize --lower_case_table_names=1 --user=mysql --basedir=/var/lib/mysql --datadir=/var/lib/mysql/data --console;

echo "启动mysql服务"
sudo service mysql start;

6 character_set_server=utf8

如果在nacos环境下使用mysql 8，需要在配置文件中增加character_set_server=utf8。

[mysqld]
lower_case_table_names=1
character_set_server=utf8
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql/data
log-error	= /var/log/mysql/error.log

可参考 https://www.cnblogs.com/feng-jq/p/10282134.html