mysql - Ubuntu 18.04 上的 lower_case_table_names=1 不允许 mysql 启动

Question

我在 ubuntu 18.04 上安装了 mysql 社区服务器 8.013，但遇到以下问题。我想设置 lower_case_table_names=1 以便在我的数据库中使用不区分大小写的表名称。我编辑了/etc/mysql/mysql.conf.d/mysqld.cnf 并在 [mysqld] 下添加了以下行

lower_case_table_names=1

mysqld.cnf现在如下

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

我使用停止了 mysql 服务器

sudo service mysql stop

然后我尝试使用再次启动它

sudo service mysql start

但我收到以下错误

Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

我试过了

systemctl status mysql.service

我得到了以下信息

christoph@christoph-Latitude-E6530:/etc/init.d$ systemctl status mysql.service
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Thu 2018-11-01 16:38:14 EET; 24s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 6681 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
  Process: 6642 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 6681 (code=exited, status=1/FAILURE)
   Status: "SERVER_BOOTING"
    Error: 2 (No such file or directory)

Νοε 01 16:38:12 christoph-Latitude-E6530 systemd[1]: Starting MySQL Community Server...
Νοε 01 16:38:14 christoph-Latitude-E6530 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Νοε 01 16:38:14 christoph-Latitude-E6530 systemd[1]: mysql.service: Failed with result 'exit-code'.
Νοε 01 16:38:14 christoph-Latitude-E6530 systemd[1]: Failed to start MySQL Community Server.

使用journalctl -xe我得到了以下信息

-- The result is RESULT.
Νοε 01 17:32:00 christoph-Latitude-E6530 sudo[2526]: pam_unix(sudo:session): session closed for user root
Νοε 01 17:32:04 christoph-Latitude-E6530 wpa_supplicant[743]: wlp3s0: WPA: Group rekeying completed with 4c:5e:0c:7a:95:cf [GTK=CCMP]
Νοε 01 17:32:08 christoph-Latitude-E6530 gnome-shell[1565]: Some code accessed the property 'discreteGpuAvailable' on the module 'appDisplay'. That property w
Νοε 01 17:32:08 christoph-Latitude-E6530 gnome-shell[1565]: Some code accessed the property 'WindowPreviewMenuItem' on the module 'windowPreview'. That proper
lines 1349-1371/1371 (END)
Νοε 01 17:31:58 christoph-Latitude-E6530 systemd[1]: Starting MySQL Community Server...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- Unit mysql.service has begun starting up.
Νοε 01 17:31:58 christoph-Latitude-E6530 audit[2593]: AVC apparmor="STATUS" operation="profile_replace" info="same as current profile, skipping" profile="unconfined" name="/usr/sbin/mysqld" pid=2593 comm=
Νοε 01 17:31:58 christoph-Latitude-E6530 kernel: kauditd_printk_skb: 28 callbacks suppressed
Νοε 01 17:31:58 christoph-Latitude-E6530 kernel: audit: type=1400 audit(1541086318.959:39): apparmor="STATUS" operation="profile_replace" info="same as current profile, skipping" profile="unconfined" name
Νοε 01 17:32:00 christoph-Latitude-E6530 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Νοε 01 17:32:00 christoph-Latitude-E6530 systemd[1]: mysql.service: Failed with result 'exit-code'.
Νοε 01 17:32:00 christoph-Latitude-E6530 systemd[1]: Failed to start MySQL Community Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- Unit mysql.service has failed.

我还尝试使用

启动服务器

sudo service mysql start --initialize lower_case_table_names=1

如此处所述 lower_case_table_names Settings in MySQL 8.0.12但我仍然遇到同样的错误并且 mysql 根本没有启动

如果我运行以下命令

sudo vi /var/log/mysql/error.log

我得到以下错误原因

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

有什么想法为什么会发生这种情况以及如何解决它吗？

Answer 1

为了使其在 MySQL 8.0 和 Linux 中工作，请按照以下步骤操作

在执行以下步骤之前备份 mysql 架构

mysqldump -h localhost -u root -p mysql >/home/用户名/dumps/mysqldump.sql

然后使用停止mysql

sudo service mysql stop

1. 移动或删除/var/lib/mysql 目录。这将删除所有数据库!!!

   mv /var/lib/mysql /tmp/mysql
   

2. 创建一个新的/var/lib/mysql 目录并将 mysql 用户作为所有者

   mkdir /var/lib/mysql
   chown -R mysql:mysql /var/lib/mysql
   chmod 750 /var/lib/mysql
   

3. 编辑/etc/mysql/mysql.conf.d/mysqld.cnf并在后面添加以下行

   [mysqld]
   
   lower_case_table_names=1
   

4. 使用以下命令初始化 mysql

   mysqld --defaults-file=/etc/mysql/my.cnf --initialize lower_case_table_names=1 --user=mysql --console
   

将 defaults-file 更改为默认文件的实际位置。有关 mysql 初始化的更多信息:https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization-mysqld.html

(可选)重复

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

如果/var/lib/mysql 中文件的所有者不是 mysql

使用启动mysql

sudo service mysql start

如果一切正常，请使用打开 mysql

mysql -u root -p

并通过运行此查询

SHOW VARIABLES where Variable_name like 'lower%';

你会得到

'lower_case_table_names', '1'

使用步骤 0 中创建的转储恢复 mysql 架构。

执行 mysql_upgrade 创建 sys 架构