One of the great things about MySQL and MariaDB are their versatility of installation. They can be setup as a Windows services and trimmed out with some extra utilities or it can be installed on a flash drive as a portable server.

The easiest installation is certainly the Windows Installer with a wizard for MySQL or the MariaDB MSI that will guide you through the installation of the server and all the options you want.  Myself, I still like self-contained programs that won’t interfere with anything else on my system and can be easily removed without leaving hidden traces. I also don’t like adding a lot of stuff to my Windows startup.

If you have a basic comfort with using the Windows command line and control panel, doing a minimal installation of either MariaDB or MySQL is not difficult once you understand a few steps. The MySQL full documentation and the MariaDB Knowedgebase are available for reference on their respective sites but here are the essentials to get you started.

Download and ‘install’ the files

The “noinstall” version of MySQL (i.e. just the basic server binaries and other files) can be found in the “Other Downloads” section at https://dev.mysql.com/downloads/mysql/. As of this writing, the current version is 8.0.18 and the 64-bit version which I use on my system (not the debug version) is 272 MB in size.

For MariaDB, the Windows downloads can be found on MariaDB.org. The current stable version at this writing is 10.4.8 and the download is between 52 and 101 MB, depending on whether you select the 32- or 64-bit version and the file format.

With either database, just download the appropriate ZIP file and extract it to a directory on the drive of your choice. For simplicity, I copied the files to C:\MySQL and C:\MariaDB. For simplicity’s sake, I will be referring to the MySQL directory from this point forward. Unless otherwise noted, the instructions refer to either MySQL or MariaDB.

Add the directories to your Windows system Path statement

Since you will sometimes be working with these databases from the command line, it’s a good idea to add the directories to the Path statement so that you can run the server without having to navigate to the directory first.  This can be done under the Advanced Settings panel of the System and Security > System option in the Windows Control Panel. You can also search for “Environment Variables” on the Windows menu.

I added both C:\MySQL and C:\MySQL\bin to the path as the bin directory is where the executables are stored.  Remember to add them to the end of the Path statement so as not to interfere with any other directories that have been stored there. Windows 10 has the nifty feature where a separate dialog will let you add directories without messing around with the raw text string.

Create the options file

MySQL uses a configuration text file to store basic settings for the server, including the base and data directories. You need to create this file in the MySQL\bin directory and it can be as simple as the following:

[mysqld]
# set basedir to your installation path
basedir=C:\mysql
# set datadir to the location of your data directory
datadir=C:\mydata\data

Replace the directories in the above sample with your chosen directories and save the file as mysql.ini.

Create and initialize a data directory

As shown in the last step, MySQL uses a data subdirectory to store all of the databases and other data files, including the master database that holds user accounts and other essentials. This directory needs to be initialized by your new server and the basic files created. After creating the subdirectory and ensuring it matches the entry in the INI file you created in the last step, you need to tell MySQL to initialize it. This is where the command line fun begins.

Open an administrator command prompt in Windows. It’s important that it be an administrator prompt so that it has the necessary permissions to carry out some of the setup tasks.

For MySQL, there are two possible commands that you can use to initialize your new MySQL server.

mysqld --initialize

or:

mysqld --initialize-insecure

The first command will initialize your new server and data directory and assign a random password to the MySQL ‘root’ user.  That password will be recorded in a new ERR file in the data directory and you’ll need to use it to login the first time.

The second command will initialize the new server but leave the root password blank. If you’re just setting up a test server that you don’t plan on using to store anything sensitive, this might be fine but, in most cases, your root user should have a strong password.

You can add the –console option to either of the above commands to display the system messages on the screen while the initialize command is working.

For MariaDB, you will need to run the mysql_install_db application from the bin directory. This will do the same as the initialize option in MySQL, including creating a root user without a password. This application as a number of available options which you can learn more about in the MariaDB Knowledgebase.

Start the new server

Since you’re not running the server as a Windows service, you’ll need to start the server as a command line process whenever you want to use it.  In the command window you used to initialize the server or another administrator command window, use the following command to start the server.

mysqld --console

This will start the server and display any relevant messages to the command window.

The screenshot above shows a simple output from the server startup routine. Assuming there are no fatal errors, you will need to leave this window open (you can minimize it if you like) and open whatever other utilities you’ll be using to work with the database.

To shutdown the server, select this window and press CTRL-C twice. This will initiate a shutdown of the server.

It’s also possible to create a Windows shortcut with the above command that will start the server and run minimized.

The mysqld program has many other options that you can use to adjust the operation of your server. You can even set the server to run as a Windows service after you’ve installed it and remove the service if you choose. Refer to the official documentation listed at the beginning of this article for more information on the various options.

Login to the server

While the server is running, you will need to start another command window or a program like MySQL Workbench to login as the root user or another user you’ve created.  From the command line, enter the following command:

mysql -u root -p {enter password}

MariaDB uses the same name for many of the client programs, including mysql.exe.

If you chose the insecure initialization, you can omit the -p option and just specify the user.

From here, you can enter SQL statements or commands to work with the server. When you’re finished, type ‘exit’ to log out of the server and then ‘exit’ again to close the command window.

Installing MySQL Workbench

The command line is great for some quick operations but you’ll probably want a graphical environment to make things a bit easier and MySQL Workbench is still the main option. You can download it from the ‘Other Downloads’ section at the following address:

The MSI installer features a wizard that makes installation pretty easy. Once the program is installed, you can use it to connect to your new server.

MySQL Workbench can be used to connect to and work with multiple servers, either local or online so it’s a great tool to have on the system. Other than that, if you want to do away with the local server, it’s just a matter of deleting the directory that you created for the files and removing it from the system’s Path statement.

This article was updated in October 2019 to include information on MariaDB and latest versions.