create (in batch) a single variable to set data path of mysql

Question

I have created a batch variable to the data path of mysql (wamp):

set MYSQLDATAPATH="%HOMEDRIVE%\wamp\bin\mysql\mysql5.7.21\data"
or
set MYSQLDATAPATH="%HOMEDRIVE%\wamp64\bin\mysql\mysql5.7.21\data"

And it changes according to the version (mysql-wamp and x86/x64):

set MYSQLDATAPATH="%HOMEDRIVE%\wamp\bin\mysql\mysql5.7.19\data"
or
set MYSQLDATAPATH="%HOMEDRIVE%\wamp64\bin\mysql\mysql5.7.19\data"

etc...

How can I create (in batch) a single variable that set the data path of mysql wamp (MYSQLDATAPATH), regardless of the version (wampXX and mysqlXX)

Thanks!

Is it ORIGEN or ORIGIN?

Adam
– Adam

2018-04-09 20:17:11 +00:00
Commented Apr 9, 2018 at 20:17 — Adam
– Adam, Commented Apr 9, 2018 at 20:17
fixed up. change to MYSQLDATAPATH

acgbox
– acgbox

2018-04-09 22:14:57 +00:00
Commented Apr 9, 2018 at 22:14 — acgbox
– acgbox, Commented Apr 9, 2018 at 22:14

Magoo · Accepted Answer · 2018-04-10 04:45:16Z

1

@ECHO OFF
SETLOCAL
SET "sourcedir=U:\sourcedir"
SET "mysqldatapath="
FOR %%a IN (wamp wamp64) DO (
 FOR /f %%d IN ('dir /ad /b "%sourcedir%\%%a\bin\mysql\mysql*" 2^>nul') DO (
  IF exist "%sourcedir%\%%a\bin\mysql\%%d\data" (
   SET "candidate=%sourcedir%\%%a\bin\mysql\%%d"
   CALL :latest %%d
  )
 )
)

ECHO mysqldatapath=%mysqldatapath%

GOTO :EOF

:: latest version required? Format %1 may be mysqlA.C.E or mysqlAB.CD.EF
:latest
IF NOT defined mysqldatapath GOTO newver
:: is newly-detected version in %1 preferred to %sql_ver%?
SET "new_ver=%1"
:: remove "mysql"
SET "new_ver=%new_ver:~5%"
:: replace '.' with ' '
SET "new_ver=%new_ver:.= %"
CALL :vcompare %sql_ver% %new_ver%
:: :vcompare will clear 'new_ver' if new version is preferred
IF NOT DEFINED new_ver GOTO :eof
:newver
SET "mysqldatapath=%candidate%"
SET "sql_ver=%1"
:: remove "mysql"
SET "sql_ver=%sql_ver:~5%"
:: replace '.' with ' '
SET "sql_ver=%sql_ver:.= %"
GOTO :eof

:: version comparison. old old old new new new  as 1 or 2-digit

:vcompare
IF "%4"=="" GOTO :EOF
SET "v_1=0000%1"
SET "v_4=0000%4"
SET /a v_1=1%v_1:~-3%
SET /a v_4=1%v_4:~-3%
IF %v_1%==%v_4% shift&GOTO vcompare
IF %v_1% gtr %v_4% SET "new_ver="
GOTO :eof

You would need to change the setting of sourcedir to suit your circumstances.

Not as easy as one may at first expect. The tricky part is in selecting the required version from the candidates.

The first block searches wamp and then wamp64 for a directory named mysql* and then nominates a candidate if the mysql* directory contains data. The :latest routine then checks the version numbers and selects the latest to be assigned to mysqldatapath.

:latest sets new_ver to the version number found, eg. 5.7.19 and converts this to 5 7 19. The :vcompare routine then does the mechanics of comparing the three levels, taking care of the potential leading-zero on each level. Note that attempting to compare using literals here will fail, as 5.10.01 would be less than 5.9.99 if compared as a literal, but is actually a later (and one would expect, greater) version.

answered Apr 10, 2018 at 4:45

Magoo

80.8k8 gold badges68 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

acgbox Over a year ago

Thanks for your script. But I do not understand what you mean by "change the setting of sourcedir to suit your circumstances" (What circumstances?... In my question I clarify that the path is %HOMEDRIVE%). Could you give a real example of SET "sourcedir=U:\sourcedir"? Thanks

Magoo Over a year ago

u:\sourcedir is a real example. It's the directory that I use for testing of solutions I post on SO. If you set sourcedir to %HOMEDRIVE% which suits your setup then all should work smoothly. Please note however how I use quotes. This is the accepted way on SO to overcome problems associated with trailing spaces on lines and avoids the gymnastics required combining variables which have quoted values

Magoo Over a year ago

Save the entire script in a file say setmysqlpath.bat but remove the setlocal command. Then in your calling script, simply use the command call setmysqlpath - where setmysqlpath.bat resides in any directory in your path, which you can display by simply typing path at the prompt (this is a semicolon-separated list if directories that windows examines in order to find an executable if the executable does not reside in the current directory.)

Magoo Over a year ago

To include it within a script, simply copy the entire batch into your script, preferably at the end. Replace the first 2 lines with a label, say :getmysqdir` and ensure that the line before :getmysqldir is a GOTO or an exit. Then within your script body, use call :getmysqldir . Observe the colons - they are important. call :getmysqldir looks for a routine in this batch called :getmysqldir whereas call getmysqldir (without the colon) searches for an external executable called getmysqldir (ie. a program file residing in a directory on the path)

acgbox Over a year ago

add: I have tested your script for mariadb and it also works. Great job. Thank you

Collectives™ on Stack Overflow

create (in batch) a single variable to set data path of mysql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related