I needed to have one or more MySQL-like DB instances ‘on demand‘ under Windows with minimal system modifications (like installing services, …) and baseline tasks.

A good objective might be the ability to run it without any administrative access rights requirement.

Installation

After a short research, I chose the portable version of MariaDB (one of the best MySQL clones).

Unzip and place the package where you wish.  My choice was under “C:\Program Files\MariaDB” to improve the package security.

Setup

The second step is to determine where to put each session’s data (and I choose “C:\ProgramData\MariaDB“).

In this folder, you shall:

• Copy the script below in your preferred text editor:

echo off
if %1 == "" goto end
setlocal EnableExtensions
pushd <put_your_db_binaries_folder_here>\bin
mariadb-install-db.exe --default-user --config="<put_your_db_data_folder_here>/my_config.ini" --datadir="<put_your_db_data_folder_here>/%1"
popd
:end

• • replace each <put_your_…> tag with the correct full path value without mixing ‘\‘s with ‘/‘s,
  • and save it in ‘init_db.cmd‘.

 

• Copy the script below in your preferred text editor:

@echo off
if %1 == "" goto end
setlocal EnableExtensions
pushd <put_your_db_binaries_folder_here>\bin
set MARIADB_INI=<put_your_db_data_folder_here>/%1/my.ini
start mysqld.exe --defaults-file=%MARIADB_INI% --console 
set MARIADB_INI=
popd
:end

• • replace each <put_your_…> tag with the correct full path value without mixing ‘\‘s with ‘/‘s,
  • and save it in ‘start_db.cmd‘.

 

• Copy the configuration settings template in your preferred text editor:

[client-server]
# Uncomment these if you want to use a nonstandard connection to MariaDB
socket=mysql.sock
port=3306

# This will be passed to all MariaDB clients
[client]
#password=my_password

# The MariaDB server
[mysqld]
# basedir=
# Directory where you want to put your data
datadir=
pid_file=mysql.pid

# Directory for the errmsg.sys file in the language you want to use
# language=/usr/local/share/mysql/english

# This is the prefix name to be used for all log, error and replication files
log-basename=mysqld

# Enable logging by default to help find problems
general-log
slow_query_log

• • and save it to ‘my_config.ini‘.

Initialize an instance

If you create an instance from zero, you need to initialize it by running our ‘init_db‘ script from our DB data folder:

init_db.cmd <instance_name>

This will create the proper dedicated directory in your data folder, filling it with all the needed files, including a new configuration file (my.ini).

You can also copy and paste an existing instance folder, and edit the included my.ini to update datadir entry properly.

Naturally, if you plan to run it concurrently with any of your existing instances, you shall also update the port entry in its configuration file.

Starting an instance

From our DB data folder, we shall run our ‘start_db‘ script:

start_db <instance_name>

A new server instance will be started with its dedicated console window from which we can monitor all our activities.

To close them, click on the proper console window and press ‘Ctrl+C‘ as usual.

Finally, programs like HeidiSQL are more than recommended to administer, peek, and check your DBs contents.