Quick and Easy Setup of MySQL for Windows


One of the great things about MySQL is its versatility of installation. It can be setup as a Windows service 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 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 MySQL is not difficult once you understand a few steps. The full documentation is available for reference on MySQL.com 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 5.7.18 and the 64-bit version which I use on my system (not the debug version) is 316 MB in size.  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.

Screenshot showing C:\MySQL directory.

The MySQL files can installed be copying them to the directory of your choice.

Add MySQL directory to your Windows system Path statement

Since you will sometimes be working with MySQL 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.

Screenshot showing Environment Variables screen in Windows 10.

The folder in which you store the MySQL files and executables need to be added to the system Path statement.

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.

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.

Start the new server

Since you’re not running MySQL 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.

Screenshot of MySQL startup console messages.

With a minimal installation of MySQL, the server needs to be started as a command line process.

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}

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

Screenshot of MySQL login at Windows command prompt.

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:

https://dev.mysql.com/downloads/workbench/

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.

Screenshot of connection settings panel in MySQL Workbench.

Standard settings for a local MySQL connection in MySQL Workbench.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *