批处理 mysql 5.7 生成Mysql连接SSL证书及生成ssl帐号及测试（Windows）

 

 

生成Mysql连接SSL证书.bat：

@setlocal enabledelayedexpansion
@echo off
color f0
@REM 生成证书（前提是安装了oponssl）
title 生成Mysql连接SSL证书

rem 下面为自定义参数可修改

set basedir=%~dp0
set bin=%~dp0\bin
cd /d %basedir%

IF EXIST %basedir%\ssl (
    echo ssl 目录存在
) else (
    md ssl
)
%basedir%bin\mysql_ssl_rsa_setup --datadir=%basedir%ssl
cd /d %basedir%ssl
openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
if %errorlevel% == 0 (
    echo 测试成功
) else (
    echo 测试未成功、请检查openssl是否安装正确
    echo http://slproweb.com/products/Win32OpenSSL.html
)
:end
pause

 

[mysqld]
# 开启 MySQL 服务器 SSL 特性，注意一在[mysqld]下
ssl
# 根证书        
ssl-ca= D:\\SOFTWARE\\mysql-5.7.28-winx64\\ssl\\ca.pem
# 服务器公钥
ssl-cert= D:\\SOFTWARE\\mysql-5.7.28-winx64\\ssl\\server-cert.pem
#服务器私钥
ssl-key=D:\\SOFTWARE\\mysql-5.7.28-winx64\\ssl\\server-key.pem

 

生成ssl帐号及测试.bat

@setlocal enabledelayedexpansion
@echo off
color f0
title 强制修改root用户密码

rem 下面为自定义参数可修改
set mysqlServiceName=MySQL4506
set mysqlPort=4506
setlocal disabledelayedexpansion    ::关闭变量延迟，如果变量中有感叹号就必须关闭变量延迟
set rootPwd=test1
set sslUser=scm1
set sslPwd=scm
set sslIp=192.168.5.142

set basedir=%~dp0
set bin=%~dp0\bin
set tmpSql=!basedir!config.tmp
cd /d %~dp0

echo %tmpSql%

echo use mysql >%tmpSql% 
echo grant all privileges on *.* to %sslUser%@'%sslIp%' identified by '%sslPwd%' require ssl;>>%tmpSql%
echo flush privileges; >>%tmpSql% 
echo exit >>%tmpSql% 
 
::因为是交互式，所以从文件读取内容 
echo %bin%\mysql --host=localhost --user=root --password=%rootPwd% --port=%mysqlPort% --default-character-set=utf8 mysql<%tmpSql%
%bin%\mysql --host=localhost --user=root --password=%rootPwd% --port=%mysqlPort% --default-character-set=utf8 mysql<%tmpSql%
setlocal enabledelayedexpansion ::开启变量延迟

del %tmpSql% /F 

%bin%\mysql --host=%sslIp% --user=%sslUser% --password=%sslPwd% --ssl-cert=%basedir%ssl\client-cert.pem --ssl-key=%basedir%ssl\client-key.pem
pause 
exit

 

报错:

ERROR 1129 (HY000): Host '27.227.134.197' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
解决:
mysql> flush hosts;