mysql5.7无法安装、服务无法启动、与xampp同时使用冲突、找不到my.ini配置文件以及报sql_mode=only_full_group_by解决方案

admin管理员组

文章数量:1598902

mysql5.7无法安装、服务无法启动、与xampp同时使用冲突、找不到my.ini配置文件以及报sql_mode=only_full_group_by解决方案

一.问题总结

原先服务器使用的xampp，最近由于php披露了漏洞，服务器一直被攻击，所以打算停用xampp改为单独使用mysql，更新为mysql5.7版本，但是不管是windows server还是我自己的windows主机，mysql安装均失败，无法启动服务。包括为了安全起见，打算同时保留xampp，此文记录具体安装步骤。

二.MySQL安装准备工作

1.官网下载MySQL

Mysql官网下载地址： MySQL :: Download MySQL Installer (Archived Versions)
使用的5.7.44版本

2.在安装前，电脑系统先安装Mircosoft Visual C++运行库 2010-2022，Microsoft Visual C++ Redistributable下载

3.检查服务列表，是否已有mysql服务，xampp默认启动mysql服务，防止新安装服务冲突

4.如果目前正在使用xampp，提前将xampp mysql端口修改，如3308；修改后，mysql可正常使用新端口进行连接，服务、项目可使用新端口配置正常启动

5.如果前期已经尝试安装mysql，需要将对应的服务、已失败mysql卸载清除

需要注意的是，检查下mysql服务指定的地址，看下是否是xampp mysql服务，防止误删

卸载服务

sc delete mysql #卸载安装失败的服务

进入程序列表，卸载失败的mysql，查看mysql安装目录，彻底删除

有的人还推荐将注册表删除，我这边怕影响到原mysql没有删除

在进行新的MySQL安装前，保证windows环境已更新，mysql清理干净，原xampp能正常使用

三.MySQL安装

1.运行mysql安装包

运行mysql-installer-community-5.7.44.0.msi

选择安装类型为自定义安装

前面的步骤图我使用了其他参考的文档，安装步骤基本不变

2.选择安装的产品和功能

依次点开MySQL Servers-MySQL Servers-MySQL Servers 5.7-MySQL Servers 5.7.44-X64，

然后点击绿色箭头将选中的版本移到右侧框中；

3.安装版本设置

点击右侧的“MySQL Servers 5.7.44-X64”，可以自定义安装路径和数据存放路径

默认在C盘， 修改完成之后点击OK

4.配置mysql安装路径

5.一路Next，直到端口配置页面

Config Type可以根据需要选择开发还是服务器类型

由于xampp原端口为3306，后面两个端口要进行区分

新安装的端口保留3306，前面已把xampp端口修改为3308

6.下一步，密码设置

7.下一步，注册windows服务

建议检查下原mysql服务，xampp默认使用mysql服务，如果这里设置的名称也为mysql，再次使用xampp时原mysql会失效，会启动新绑定的服务，建议服务名称进行区分，设置为mysql57；

根据需要勾选是否开机自启

8.然后继续next

9.继续Excute

到达这一步，我安装时的问题出现

在进行配置项应用时，提示Initializing database失败，

再次点击excute，服务可正常注册，但是无法启动，

到服务列表手动启动服务也失败，

其实，进行到现在mysql已经安装完成，只是配置无法生效，服务无法启动。

直接点击Cancel关闭安装应用

10.删除已失败服务

打开服务列表，检查是否已安装mysql57，

如果已安装，用管理员权限运行cmd，输入“sc delete mysql57”，将服务卸载掉

sc delete mysql #卸载安装失败的服务

11.移除配置文件

然后打开mysql安装目录，在安装目录下找到my.ini配置文件，直接移动到bin文件夹里

12.删除原数据库数据
将mysql安装目录下data文件夹下所有文件删除，保留data文件夹

13.初始化mysql数据

然后用管理员权限运行命令行工具，进入到bin文件夹里，

运行mysql进行初始化数据，成功后会有信息提示

（注：root@localhost:后面的是你的登录密码，记得保存）

mysqld --initialize --user=mysql --console

14.服务注册

然后进行mysql服务注册，注册名称为mysql57，与已有服务区分

mysqld --install mysql57

到这一步，服务列表刷新后应该能正常启动新安装的mysql服务了

同时，可以使用默认生成的密码进行mysql登陆和连接了

登陆mysql

mysql -uroot -p

15.密码修改

接下来，继续使用命令行把初始密码修改掉并设置成永久有效

SET PASSWORD = PASSWORD('新密码');   #设置新密码

ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER; #设置密码永不过期，以绝后患

flush privileges; #更新生效

16.指定配置文件

现在虽然可以正常运行了，现在前面我们把mysql安装目录下的my.ini移除了，

进入mysql安装路径中手动创建一个文件，命名为：my.ini

需要注意的是，basedir、datadir为mysql安装目录以及data目录，

以及sql_mode，需要把“only_full_group_by”去掉，

创建好文件后运行命令行绑定配置文件

mysqld  --defaults-file="D:\MySQL\MySQL Server 5.7\my.ini"

my.ini内容如下：

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录(你自己的目录)
basedir="D:/MySQL/MySQL Server 5.7/"
# 设置mysql数据库的数据的存放目录
datadir="D:/MySQL/MySQL Server 5.7/Data"
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
#sql_mode配置，关闭ONLY_FULL_GROUP_BY
sql_mode= 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
aximum number of threads permitted inside of InnoDB. A value 
# of 0 (the default) is interpreted as infinite concurrency (no limit). This 
# variable is intended for performance tuning on high concurrency systems.
# InnoDB tries to keep the number of threads inside InnoDB less than or equal to 
# the innodb_thread_concurrency limit. Once the limit is reached, additional threads 
# are placed into a "First In, First Out" (FIFO) queue for waiting threads. Threads 
# waiting for locks are not counted in the number of concurrently executing threads.
innodb_thread_concurrency=9

# The increment size (in MB) for extending the size of an auto-extend InnoDB system tablespace file when it becomes full.
innodb_autoextend_increment=64

# The number of regions that the InnoDB buffer pool is divided into.
# For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,
# by reducing contention as different threads read and write to cached pages.
innodb_buffer_pool_instances=8

# Determines the number of threads that can enter InnoDB concurrently.
innodb_concurrency_tickets=5000

# Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before
# it can be moved to the new sublist.
innodb_old_blocks_time=1000

# When this variable is enabled, InnoDB updates statistics during metadata statements.
innodb_stats_on_metadata=0

# When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table
# in a separate .ibd file, rather than in the system tablespace.
innodb_file_per_table=1

# Use the following list of values: 0 for crc32, 1 for strict_crc32, 2 for innodb, 3 for strict_innodb, 4 for none, 5 for strict_none.
innodb_checksum_algorithm=0

# If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and
# synchronize unflushed data to disk.
# This option is best used only on systems with minimal resources.
flush_time=0

# The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use
# indexes and thus perform full table scans.
join_buffer_size=256K

# The maximum size of one packet or any generated or intermediate string, or any parameter sent by the
# mysql_stmt_send_long_data() C API function.
max_allowed_packet=4M

# If more than this many successive connection requests from a host are interrupted without a successful connection,
# the server blocks that host from performing further connections.
max_connect_errors=100

# Changes the number of file descriptors available to mysqld.
# You should try increasing the value of this option if mysqld gives you the error "Too many open files".
open_files_limit=4161

# If you see many sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the
# sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization
# or improved indexing.
sort_buffer_size=256K

# Specify the maximum size of a row-based binary log event, in bytes.
# Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256.
binlog_row_event_max_size=8K

# If the value of this variable is greater than 0, a replica synchronizes its master.info file to disk.
# (using fdatasync()) after every sync_master_info events.
sync_master_info=10000

# If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk.
# (using fdatasync()) after every sync_relay_log writes to the relay log.
sync_relay_log=10000

# If the value of this variable is greater than 0, a replica synchronizes its relay-log.info file to disk.
# (using fdatasync()) after every sync_relay_log_info transactions.
sync_relay_log_info=10000

# Load mysql plugins at start."plugin_x ; plugin_y".
# plugin_load

# The TCP/IP Port the MySQL Server X Protocol will listen on.
mysqlx_port=0.0

绑定成功后，重启mysql57服务，使用navicat root账户正常连接数据库

同时，选择mysql表，新建查询，查看sql_mode修改是否生效

SELECT @@Global.sql_mode

17.至此MySQL5.7安装完成！

新的mysql和xampp可以同时运行！

后续：
项目在进行mysql连接配置时，需要添加useSSL=false

参考文档：
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘localhost’ (10061)
问题：Xampp中mysql与MySQL冲突
mysql 8.0 找不到my.ini配置文件以及报sql_mode=only_full_group_by解决方案
mysql怎么卸载干净

本文标签： 找不到配置文件无法启动冲突解决方案