Linux 安装 MySQL8的坎坷经历

最新推荐文章于 2024-09-02 00:27:15 发布

置顶 Canney Chen

最新推荐文章于 2024-09-02 00:27:15 发布

阅读量734

点赞数

分类专栏：服务端文章标签： MySQL8 lower_case mysqld.cnf linux安装mysql mysql8修改密码

本文链接：https://blog.csdn.net/canney_chen/article/details/114287462

版权

服务端专栏收录该内容

4 篇文章 0 订阅

订阅专栏

Linux 安装 MySQL8的坎坷

文章目录

Linux 安装 MySQL8的坎坷

一、前奏

第一次使用 Linux 指令的方式安装软件，把 Linux 相关指令粗略看了一遍。
开始安装 MySQL8。
个人有些嗜好，所有软件使用最新版本。
操作环境：

Ubuntu: 20.04 LTS (GNU/Linux 5.4.0-42-generic x86_64)
MySQ: 8.0.23

转载请注明 Canney原创https://blog.csdn.net/canney_chen/article/details/114287462

二、演奏

2.1 Install MySQL:

sudo apt-get update    
sudo apt-get install mysql-server -y

installed directory view:https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/option-files.html Table 4.2

2.2 Stop the MySQL service:

sudo service mysql stop

2.3 Delete the MySQL data directory:

sudo rm -rf /var/lib/mysql

2.4 Recreate the MySQL data directory (yes, it is not sufficient to just delete its content):

sudo mkdir /var/lib/mysql    
sudo chown mysql:mysql /var/lib/mysql
sudo chmod 700 /var/lib/mysql

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

2.6 Re-initialize MySQL with --lower_case_table_names=1:

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

2.7 Start the MySQL service:

sudo service mysql start

2.8 Retrieve the new generated password for MySQL user root:

查找root默认密码

sudo grep 'temporary password' /var/log/mysql/error.log

Expected output:

2021-03-02T03:54:25.674059Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: d!KJqVwjh5oa
2021-03-02T07:10:43.812630Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Vu;uDvByo2g:

2.9 Change the password of MySQL user root either by:

sudo mysql -u root -p

and executing:

use mysql;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newPassword';

afterwards, OR by calling the “hardening” script anyway:

sudo mysql_secure_installation

2.10 You can verify the lower_case_table_names setting by entering the MySQL shell:

sudo mysql -u root -p

and executing:

SHOW VARIABLES LIKE 'lower_case_%';

Expected output:

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+

三、坑坑

3.1 MySQL8 修改用户密码

MySQL8 修改密码方式变更，参考精华步骤 [2.9] 部分；

3.2 为什么修改`lower_case_table_names`参数

错误描述

在Linux上安装完成后lower_case_table_names=0, 此时表名区分大小写，而大家默认写SQL语句或 Spring生成的SQL中的表名与数据库中的表名不匹配。

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_database    |
+-----------------------+
| TABLE_ONE             |
| TABLE_TWO             |
| TABLE_THREE           |
+-----------------------+
mysql> SELECT * FROM table_ONE;
ERROR 1146 (42S02): Table 'database.table_ONE' doesn't exist

上面错误表名table_ONE在数据库存储为TABLE_ONE;

错误处理

apt 安装MySQL 后会立即启动 mysql服务，会在启动服务之前完成初始化。而修改lower_case_table_names需要在初始化时以文件或参数方式输入查看演奏步骤 [2.5-6] 内容将lower_case_table_names=1。
官方原话(点击查看):
>lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited.

3.3 2003 - Can’t connect to MySQL server on ‘xxx’(10038)

出现此错误可能存在3种原因：

3.3.1 防火墙未开启

a. 云服务器，联系管理员配置策略，端口(3306),IP放行。
b. 常规电脑，自行谷歌，进行配置。

3.3.2 bind-address=127.0.0.1

修改MySQL启动配置文件/etc/mysql/mysql.conf.d/mysqld.cnf

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

修改bind-address=127.0.0.1 (只可本机访问）为以下参数(所有设备可访问)

bind-address            = 0.0.0.0

同时可以在上述配置文件修改启动端口等其它参数；

3.3.3 root host=‘localhost’

UPDATE mysql.user SET host='%' where user='root';

确认结果

select host,user from mysql.user;

Expected output:

+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+

四、伴奏

4.1 mysql服务管理

sudo service mysql start
sudo service mysql restart
sudo service mysql stop

五、总结

安装完成MySQL8后，第一步先修改 lower_case_table_names参数, 以免重新演奏。

引用

MySQL8 修改密码查看 https://www.cnblogs.com/chloneda/p/12449819.html；
修改lower_case_table_names 参考[https://stackoverflow.com/questions/51803216/lower-case-table-names-settings-in-mysql-8-0-12/63069763#63069763] Thomas答案；

mysqld 配置文件