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怎么卸载干净
扫一扫
957
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
