mysql 设置表名大小写忽略

最新推荐文章于 2026-05-01 05:28:14 发布

原创最新推荐文章于 2026-05-01 05:28:14 发布 · 373 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #数据库

数据库专栏收录该内容

1 篇文章

订阅专栏

这篇博客详细记录了如何配置MySQL服务器，包括设置lower_case_table_names参数，清空datadir目录，重新初始化数据库，创建tmp目录，启动mysqld服务，并修改初始密码及创建远程登录用户的过程。

1. 改变配置文件

主要设置lower_case_table_names=1

$ cat my.cnf
[client]
port = 3307
socket=/home/mysql/data/mysql.sock

[mysqld]
port = 3307


socket = /home/mysql/data/mysql.sock
pid-file = /home/mysql/data/mysql.pid
basedir = /home/mysql/mysql8
datadir = /home/mysql/data/
tmpdir = /home/mysql/data/tmp
user = mysql
secure_file_priv=''
#max_allowed_packet=1KB
lower_case_table_names=1

2. 按照上述配置文件中datadir设置, 清空 data 目录

rm -rf data/*

3. 重新初始化

$ mysqld --initialize --basedir=/home/mysql/mysql8 --datadir=/home/mysql/data --lower_case_table_names=1
2021-08-26T22:00:43.747984Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2021-08-26T22:00:43.748038Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2021-08-26T22:00:43.749513Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2021-08-26T22:00:43.749959Z 0 [System] [MY-013169] [Server] /home/mysql/mysql8/bin/mysqld (mysqld 8.0.18) initializing of server in progress as process 80433
2021-08-26T22:00:45.964542Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: lNJduj5RNW?G

4. 创建tmp目录

$ mkdir data/tmp

5. 启动mysqld

$ mysqld  --defaults-file=/home/mysql/my.cnf &
[1] 80508
$ 2021-08-26T22:01:01.169764Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2021-08-26T22:01:01.169780Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2021-08-26T22:01:01.415013Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2021-08-26T22:01:01.415100Z 0 [System] [MY-010116] [Server] /home/mysql/mysql8/bin/mysqld (mysqld 8.0.18) starting as process 80508
2021-08-26T22:01:02.373004Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-08-26T22:01:02.392736Z 0 [System] [MY-010931] [Server] /home/mysql/mysql8/bin/mysqld: ready for connections. Version: '8.0.18'  socket: '/home/mysql/data/mysql.sock'  port: 3307  MySQL Community Server - GPL.
2021-08-26T22:01:02.633268Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060

6. 修改初始密码，创建远程登录用户

$ mysql -uroot -S /home/mysql/data/mysql.sock  -P3307 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 8.0.18

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user root identified by '123456';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user user() identified by '123456';
Query OK, 0 rows affected (0.02 sec)

mysql> create user mysql@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to mysql@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>