当前位置：首页 > article >正文

windows10使用bat脚本安装前后端环境之msyql5.7安装配置并重置用户密码

article 2024/9/30 21:08:37

首先需要搞清楚msyql在本地是怎么安装配置、然后在根据如下步骤编写bat脚本：

思路

1.下载mysql5.7 zip格式安装包
2.新增data文件夹与my.ini配置文件
3.初始化数据库
4.安装mysql windows服务
5.启动并修改root密码（新增用户初始化授予权限）
6.新增数据库

在这里插入图片描述
my.ini 文件：

[client]
port=13306
[mysql]
default-character-set=utf8
[mysqld]
basedir=C:\other\mysql-5.7.42
datadir=C:\mysql-5.7.42\data
port=13306
max_connections=200
character-set-server=utf8
default-storage-engine=INNODB

bat脚本

@echo off
echo windows10 x64 server Mysql init
REM 请求管理员权限并隐藏窗口
%1 mshta vbscript:CreateObject("Shell.Application").ShellExecute("cmd.exe","/c %~s0 :: & echo 执行中，请稍候... & timeout 2 > nul","","runas",0)(window.close)&&exit
setlocal enabledelayedexpansion

rem 设置 Mysql 目录变量，方便后续引用和修改
set ServiceName=MySQL57_LINSN

%~d0
cd %~dp0
set CURRENT_DIR=%~dp0
set CURRENT_DIR=%CURRENT_DIR:~0,-1%

set NEW_BASEDIR=%CURRENT_DIR%\mysql-5.7.42
set NEW_DATADIR=%CURRENT_DIR%\mysql-5.7.42\data
set MY_INI_PATH=%CURRENT_DIR%\mysql-5.7.42\my.ini

rem 创建一个临时文件用于存储更新后的配置
set TMP_FILE=%MY_INI_PATH%.tmp

rem 删除临时文件，如果它已经存在
if exist "%TMP_FILE%" del "%TMP_FILE%"

echo query %ServiceName% state info ...
sc query "%ServiceName%" >nul 2>&1
if %errorLevel% equ 0 (
    rem 服务存在，检查服务状态
    for /f "tokens=2 delims=: " %%a in ('sc query "%ServiceName%" ^| find "STATE"') do (
        if /i "%%a" equ "RUNNING" (
            echo Mysql server state is RUNNING...
        ) else if /i "%%a" equ "STOPPED" (
            echo Mysql server state is STOPPED...
            net start %ServiceName% 
        ) else (
			echo Mysql server state is %%a...
        )
    )
) else (
	rem 读取原始的my.ini并更新basedir和datadir。这里的循环就是更新了，更新的逻辑是如果看到配置文件中的一行以 basedir= 开头，我们就更新
	echo mysql-server update my.ini begin...
	for /f "tokens=*" %%A in ('type "%MY_INI_PATH%"') do (
		set "LINE=%%A"
		if "!LINE:~0,8!"=="basedir=" set "LINE=basedir=%NEW_BASEDIR%"
		if "!LINE:~0,8!"=="datadir=" set "LINE=datadir=%NEW_DATADIR%"
		echo !LINE!>>"%TMP_FILE%"
	)

	rem 用更新后的配置替换原始的my.ini文件
	move /Y "%TMP_FILE%" "%MY_INI_PATH%"

	echo my.ini is update...
	
	REM 初始化 MySQL
	echo 初始化 MySQL...
	"%NEW_BASEDIR%\bin\mysqld.exe" --initialize-insecure --user=mysql

	rem 服务不存在，初始化服务
	echo mysql-server is not exist, init server %ServiceName%
	"%NEW_BASEDIR%\bin\mysqld.exe" --install %ServiceName%

	echo mysql-server start server %ServiceName%
	net start %ServiceName%	
	
	REM 假设 SQL 脚本名为 adserver-simple，位于当前目录下
	echo adserver-simple.sql ...
	"%NEW_BASEDIR%\bin\mysql.exe" -u root < adserver-simple.sql

	REM 重置 root 用户密码
	echo reset root psd...
	"%NEW_BASEDIR%\bin\mysql.exe" -u root < mysql-user-init.sql
)

echo windows-server-mysql success
endlocal

REM 执行完成后自动关闭
timeout /t 2 >nul
exit

mysql-user-init.sql：重置root密码（并新增zhangsan用户授予权限）

use mysql;
alter user 'root'@'localhost' IDENTIFIED BY 'Yess5678!';
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'Abc123!@';
GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'localhost';
FLUSH PRIVILEGES;