windows NT mysql5.1升级到5.7.18

mysql5.7.18升级步骤说明：

操作人员账号具有管理员相关权限

1. 安装vcredist2013_x86.exe (5.7.18 mysqld等程序需要用到msvcr120.dll msvcp120.dll等dll)
2. 退出testhost\mysql目录及mysql相关操作，否则会导致操作拒绝访问
3. 直接在当前目录执行mysqlup.cmd，按照提示输入mysql的root用户密码，按任意键开始，过程需要若干分钟
4. 程序执行过程中会自动生成导出数据库数据alldata.sql、日志文件update.log
5. 等待控制台界面出现 “请按任意键继续” 才算结束 ，出现mysql_upgrade 1728错误 不影响

 

PS:如果出现升级失败的情况，查看update.log查看具体日志和update.bat脚本 判断出错的步骤，根据不同情况恢复原来的数据库或者继续执行

恢复原来数据库：
升级执行的时候，会备份原来的数据库目录为mysql5.1_backup
将新的mysql目录删除或备份，mysql5.1_backup => mysql
CMD进入mysql\bin目录，执行 

mysqld -install
net start mysql

使用原有账户test_user登陆mysql，列举表看看是否正常
如果test_user无法登陆，则用root登陆

mysql -uroot -p

然后设置test_user权限

grant select,insert,update,delete on test.* to 'test_user'@'localhost';
grant super on *.* to 'test_user'@'localhost';
grant lock tables on test.* to 'test_user'@'localhost';

test_user登陆 查询正常 即可
 

确认安装成功，系统运行正常，本升级目录及文件删除

my.ini放到mysql目录下，内容：

[mysqld]
port = 8306
character-set-server=utf8
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_connections=300
log-bin=mysql-bin
binlog_format=mixed
expire_logs_days=99
server-id = 1

[client]
port=8306

[mysql]
default-character-set=utf8

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

 

mysqlup.cmd 脚本如下：

 

@echo off

echo update mysql to version:5.7.18，this process will spend a few minutes...
echo input mysql root pwd:
set /p rootpwd=
echo rootpwd:%rootpwd%
if "%rootpwd%"=="" (
   echo rootpwd is null
   exit;
)

echo 确认密码无误后，按任意键开始，密码错误请关闭本窗口，重新执行...

call "%cd%\update.bat" > update.log

pause

update.bat脚本如下：

@echo off

echo update mysql5.1.48 to mysql5.7.18...
echo root:%rootpwd%

pause
::获取安装目录
echo get testhost intallpath from reg...
if /i "%PROCESSOR_ARCHITECTURE%"=="AMD64" (
	echo 64-bit system
	for /f "tokens=3 delims= " %%i in ('Reg Query "HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\testhost" /v "Install" ') do set installpath=%%i
) else (
	echo 32-bit system
	for /f "tokens=3 delims= " %%i in ('Reg Query "HKEY_LOCAL_MACHINE\SOFTWARE\testhost" /v "Install" ') do set installpath=%%i
)
echo intallpath:%installpath%


echo export mysql data...
echo "%installpath%\mysql\bin\mysqldump.exe -uroot -p%rootpwd% --all-databases > alldata.sql"
%installpath%\mysql\bin\mysqldump.exe -uroot -p"%rootpwd%" --all-databases > alldata.sql
if errorlevel 1 (
  echo mysqldump export mysql fail...
  pause
  exit
) else (
  echo mysqldump export mysql success...
)

if not exist alldata.sql (
  echo alldata.sql not exist...
  pause
  exit
)

echo stop apache...
httpd -k stop

echo stop mysql...
net stop mysql

echo delete mysql service...
sc delete mysql
if errorlevel 1 (
  echo sc delete mysqlfail
  exit
) else (
  echo sc delete mysqlsuccess
)

echo rename mysql dir...
ren "%installpath%\mysql" "mysql5.1_backup"
if errorlevel 1 (
  echo rename fail
  exit
) else (
  echo rename success
)
echo xcopy %cd%\mysql to %installpath%\mysql...
xcopy "%cd%\mysql" "%installpath%\mysql" /s /e /k /q /i /y
if errorlevel 1 (
  echo xcopy fail...
  exit
) else (
  echo xcopy success...
)

::初始化
echo "%installpath%\mysql\bin\mysqld.exe --initialize"
%installpath%\mysql\bin\mysqld.exe --initialize

::安装
echo %installpath%\mysql\bin\mysqld.exe --install mysql --defaults-file="%installpath%\mysql\my.ini" ...
%installpath%\mysql\bin\mysqld.exe --install mysql --defaults-file="%installpath%\mysql\my.ini"

::获取mysql临时密码 5.7版本安装后会生成root的临时密码
echo get mysql temporary password from %computername%.err
for /f "tokens=3,5,6,11 delims= " %%i in ('type %installpath%\mysql\data\%computername%.err') do ( 
 if "%%i"=="[Note]" (
    if "%%j"=="temporary" (
	if "%%k"=="password" (
   	   set temppwd=%%l
	   goto :NEXTB
	)
     )
  )
)
:NEXTB
echo temppwd:"%temppwd%"

if "%temppwd%"=="" (
  echo mysql's temppwd is null...
  exit
)
echo net start mysql...
net start mysql
if errorlevel 1 (
  echo net start mysql fail...
  exit
) else (
  echo net start mysql success...
)

echo %installpath%\mysql\bin\mysql.exe -h localhost -uroot -p"%temppwd%" --connect-expired-password
%installpath%\mysql\bin\mysql.exe -h localhost -uroot -p"%temppwd%" --connect-expired-password -e"alter user 'root'@'localhost' identified by '%rootpwd%';source alldata.sql;"
if errorlevel 1 (
  echo import sql fail...
  exit
) else (
  echo import sql success...
)

::升级数据库后需要使用mysql_upgrade升级数据结构
echo %installpath%\mysql\bin\mysql_upgrade.exe --force -uroot -p"%rootpwd%"
%installpath%\mysql\bin\mysql_upgrade.exe --force -uroot -p"%rootpwd%"

echo start apache...
httpd -k start

echo update finish...