介绍如何将官网下载的 MySQL zip 包配置成系统服务,以及 MySQL 的基本配置和初始化。

免安装版下载

点击进入 MySQL 官网下载页面,选:Windows (x86, 64-bit), ZIP Archive


配置

解压至:C:\dev\mysql-8.0.11-winx64,并在此目录添加一个空目录:data

配置文件

在解压目录添加 my.ini,内容如下

1
2
3
4
5
6
7
8
9
10
11
12
13
[mysqld]
basedir = C:\dev\mysql-8.0.11-winx64
datadir = C:\dev\mysql-8.0.11-winx64\data
port = 3306
lower_case_table_names = 2
default_authentication_plugin=mysql_native_password
character-set-server = utf8mb4

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

my.ini 文件,网上文章也有说可以不用添加的,暂时未测试。

lower_case_table_names 参数说明

官方文档:Identifier Case Sensitivity

  1. lower_case_table_names是mysql一个大小写敏感设置的属性,此参数不可以动态修改,必须重启数据库。
  2. unix,linux下lower_case_table_names默认值为 0 .Windows下默认值是 1 .Mac OS X下默认值是 2。

参数说明

  • lower_case_table_names=0 表名存储为给定的大小写。比较时:区分大小写。大小写敏感(Unix,Linux默认)。

     创建的库表将原样保存在磁盘上。如create database TeSt;将会创建一个TeSt的目录,create table AbCCC …将会原样生成AbCCC.frm。SQL语句也会原样解析。

  • lower_case_table_names=1 表名存储为小写。比较时:不区分大小写。大小写不敏感(Windows默认)。

    创建的库表时,MySQL将所有的库表名转换成小写存储在磁盘上。SQL语句同样会将库表名转换成小写。如需要查询以前创建的Test_table(生成Test_table.frm文件),即便执行select from Test_table,也会被转换成select from test_table,致使报错表不存在。

  • lower_case_table_names=2 表名存储为给定的大小写。比较时:小写。

    创建的库表将原样保存在磁盘上。但SQL语句将库表名转换成小写。

修改lower_case_table_names导致的常见不良隐患:如果在lower_case_table_names=0时,创建了含有大写字母的库表,改为lower_case_table_names=1后,则会无法被查到。

将默认的lower_case_tables_name为0设置成1

需先将已经存在的库名和表名都转换为小写。

  • 针对仅表名存在大写字母的情况:
    • lower_case_tables_name=0时,执行rename table成小写。
    • 设置lower_case_tables_name=1,重启生效。
  • 针对库名存在大写字母的情况:
    • lower_case_tables_name=0时,使用mysqldump导出,并删除老的数据库。
    • 设置lower_case_tables_name=1,重启生效。
    • 导入数据至实例,此时包含大写字母的库名已转换为小写。

安装成系统服务

以管理员身份运行 cmd,进入:C:\dev\mysql-8.0.11-winx64\bin 运行:mysqld –install MySQL

1
2
C:\dev\mysql-8.0.11-winx64\bin>mysqld --install MySQL
Service successfully installed.

初始化数据库

在MySQL安装目录的 bin 目录下执行初始化命令:mysqld –initialize –console

1
2
3
4
C:\dev\mysql-8.0.11-winx64\bin>mysqld --initialize --console
2018-05-10T09:06:15.354597Z 0 [System] [MY-013169] [Server] C:\dev\mysql-8.0.11-winx64\bin\mysqld.exe (mysqld 8.0.11) initializing of server in progress as process 11688
2018-05-10T09:06:19.145900Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: k,h>nFBok8Hw
2018-05-10T09:06:20.432456Z 0 [System] [MY-013170] [Server] C:\dev\mysql-8.0.11-winx64\bin\mysqld.exe (mysqld 8.0.11) initializing of server has completed

  1. 注意看提示:A temporary password is generated for root@localhost: k,h>nFBok8Hw
  2. 我们先记住初始密码,一会儿会用上(每次初始化的密码都不一样,你要看自己的提示):k,h>nFBok8Hw
  3. 删掉 data 目录下的所有目录和文件,再执行一遍初始化命令,又会重新生成,相当于重新初始化。

在 win10 上安装 5.7.23 报错

my.ini的参数lower_case_tables_name=0的情况下

1
2
3
4
C:\dev\mysql-5.7.23-winx64\mysql-5.7.23-winx64\bin>mysqld --initialize --console
2018-12-09T13:04:02.545614Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-12-09T13:04:03.309670Z 0 [ERROR] The server option 'lower_case_table_names' is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination. Please consider either using a case sensitive file system for your data directory or switching to a case-insensitive table name mode.
2018-12-09T13:04:03.314831Z 0 [ERROR] Aborting

大小写敏感要操作系统支持,Windows本身大小写不敏感,因此不能设置成大小写敏感。

该问题,可以参考官方文档:Identifier Case Sensitivity

官方文档如下:

  • Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.
  • Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.

my.ini的参数lower_case_tables_name改为 2,则正常

1
2
3
4
5
6
7
C:\dev\mysql-5.7.23-winx64\mysql-5.7.23-winx64\bin>mysqld --initialize --console
2018-12-09T14:25:32.965759Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-12-09T14:25:34.370212Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-12-09T14:25:34.700644Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-12-09T14:25:34.842230Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 4a949feb-fbbe-11e8-beee-002535150600.
2018-12-09T14:25:34.857245Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-12-09T14:25:34.912538Z 1 [Note] A temporary password is generated for root@localhost: kDLL5K<iGsmo

和 MySQL Workbench 连接的时候,提示:

1
2
3
4
5
6
7
8
9
10
11
12
[Window Title]
MySQL Workbench

[Main Instruction]
Server Configuration Problems

[Content]
A server configuration problem was detected.
The server is in a system that does not properly support the selected lower_case_table_names option value. Some problems may occur.
Please consult the MySQL server documentation.

[ ] Don't show this message again [OK]

可不用管。

启动

cmd 执行:net start MySQL

1
2
3
C:\dev\mysql-8.0.11-winx64\bin>net start MySQL
The MySQL service is starting.
The MySQL service was started successfully.

添加环境变量

  1. 新增mysql环境变量:MYSQL_HOME = C:\dev\mysql-8.0.11-winx64
  2. 修改Path环境变量,Path变量下,末尾新增 ;%MYSQL_HOME%\bin;

修改默认密码

初始化之后,必须修改默认密码,否则使用不了,如下,试着创建新数据库。

先用默认密码登录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
C:\Users\AndyChen>mysql -uroot -p
Enter password: ************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.11

Copyright (c) 2000, 2018, 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> create schema if not exists apg default character set utf8mb4;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>

修改默认密码

1
2
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'RoOtPaSs';
Query OK, 0 rows affected (0.02 sec)

创建数据库并导入数据

1
2
3
4
5
6
7
8
9
10
mysql> create schema if not exists apg default character set utf8mb4;
Query OK, 1 row affected (0.04 sec)

mysql> use apg;
Database changed
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> source C:\workspace\apg20180509.sql
...

检查导入的表

1
2
3
4
5
6
7
mysql> show tables;
+--------------------------+
| Tables_in_apg |
+--------------------------+
...
+--------------------------+
38 rows in set (0.01 sec)

绿色版卸载

以管理员身份执行:mysqld –remove
重启,删除 mysql 根目录下 my.ini 配置的 data 目录,删除 mysql server 相关文件。

1
2
3
4
5
6
7
8
C:\WINDOWS\system32>mysqld --remove
Failed to remove the service because the service is running
Stop the service and try again
C:\WINDOWS\system32>net stop mysql
MySQL 服务正在停止..
MySQL 服务已成功停止。
C:\WINDOWS\system32>mysqld --remove
Service successfully removed.

遇到的坑

启动时报错

1
2
3
4
C:\dev\mysql-8.0.11-winx64\bin>net start mysql
发生系统错误 193。

*** 不是有效的 Win32 应用程序。

解决办法:进入 Mysql 根目录下的 bin 目录,找到 mysqld 这个大小为 0 kb 的“二逼”文件,删除,然后就可以各种开心的玩耍了。
并非每台电脑都如此。

再次 mysqld –install 时失败

因为执行了 mysqld –remove 之后,系统服务显示“禁用“,再次 install 及 remove 均会提示失败,重启一下就可以重新 install 了。

druid 连接报错

1
2
java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
java.sql.SQLException: Unknown system variable 'query_cache_size'

原因是数据库驱动版本和数据库版本不匹配。
解决:根据自己的数据库版本去这个地址选择合适的驱动版本
比如我的数据库版本是 8.0.11,那么对应的驱动为:

1
2
3
4
5
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>

连接 MySQL 报错

出现在中文操作系统 win10 上,英文操作系统 win10 正常

1
The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

解决:数据库连接参数添加

1
&serverTimezone=Hongkong

完整连接参数如下:

1
jdbc.url=jdbc:mysql://127.0.0.1:3306/apg?useSSL=false&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false&socketTimeout=20000&serverTimezone=Hongkong

workbench

官方、免费、强大的 mySQL 客户端,推荐使用
workbench 下载

5.6.x 服务启动后,修改密码

1
2
3
4
5
set password for 'root'@'localhost'= password('@#$kd3429DAnd');
```

## 问题
MySQL 5.6.X,MySQL Workbench 连接时报错。

[Window Title]
MySQL Workbench

[Main Instruction]
Server Configuration Problems

[Content]
A server configuration problem was detected.
The server is in a system that does not properly support the selected lower_case_table_names option value. Some problems may occur.
Please consult the MySQL server documentation.

[X] Don’t show this message again [OK]

1
2

当前值为2:

lower_case_table_names = 2

1
改为1

lower_case_table_names = 1

1
2
3
重启之后,就正常了。

# 配置 mysql-8.0.19-winx64

mysqld –install MySQL

1
提示:

mysqld.exe - 系统错误
由于找不到 VCRUNTIME140_1.dll,无法继续执行代码。重新安装程序可能会解决此问题。
`

链接: https://pan.baidu.com/s/1dQF8FTFWAjL_zEGjj57kkw 提取码: j2ty
下载该文件放到 C:\Windows\System32,再次执行即可。