One of the nice features of MySQL as a database software is its noinstall option in which you download the files themselves and perform a manual installation that takes just under a gigabyte of space on your drive.  If you’re a developer or a student, this means you can quickly create a new self-contained instance wherever you need it and then blow it away if you need to without cluttering up your registry.

This does mean working on the command line so you’ll want to have a certain comfort level with that.  You will also need to be working with Administrative privileges.

1.)  Download the files

From https://dev.mysql.com/downloads/mysql/ , you’ll need the Windows (x86, 64-bit), ZIP Archive download, assuming your machine is 64-bit. As of this writing, the current version is 8.0.14.  Just download this to a convenient location on your computer.

2.)  Extract the files

It’s best to choose a simple location for the MySQL files; C:\MySQL is common.  Once you’ve extracted the ZIP archive and placed them, your working folder should look like this:

3.) Create an option file

MySQL needs a my.ini settings file to tell it about key settings and directory.  This text file can contain a lot of settings but to start out, you just need to tell the initialization program where the files are located.  MySQL will look for this file in different places but to keep it simple, just create it at C:\MySQL\my.ini, paste the following text into it using your favorite text editor and save it.  Be sure to change the directories shown here to match your actual installation.

[mysqld] 
# basedir = the installation path 
basedir=C:/mysql 
# datadir = the location of the data directory 
datadir=C:/mysql/data

The second directory shown is the directory in which MySQL will store your database files. It does not need to be in the same directory as the rest of the files but it’s good to keep things contained.

If you’re running more than one instance of MySQL or another database server, you might need to change the port that MySQL communicates on.  By default, it’s 3306 but you can change it to any other port that’s not currently being used such as 3360.  To do this, add a single line to your option file at the end of the [mysqld] section shown above.

port=3360

This line will actually need to be put in twice so that you can login via the command line. A separate section, [client], specifies settings for the command line client program.

[client]
port=3360

4.)  Initialize the data directory

MySQL needs to create a data directory and create all the necessary files to maintain your databases there so the next step is to run the initialization program that will carry this out.  From the Windows command line, run either of the following commands:

mysqld --initialize --console
mysqld --initialize-insecure --console

The –initialize option on its own will create the data directory and assign a random password for the root user on this instance of MySQL. The console output during the initialization will include the password so you’ll need to be paying attention during the process.  If you would prefer to create your own password later (as I usually do), just use the –initialize-insecure option.

The output should look something like this.

5.) Add the MySQL base and data directories to your PATH statement.

If you’re going to be working from the command line, it’s easier if you don’t have to specify the installation directory every time you want to work with MySQL.  To be safe, add the MySQL and MySQL\bin directories to your system path.

6.)  Start the server for the first time

With a minimal installation that’s run from the command line, you will need to start the server each time you want to use it.  You can do this with the mysqld program in the bin directory.

mysqld --console

This command will start the MySQL server but the window will not return you to the command prompt until you shut down the server. To login to the server and work with databases, you will need to minimize the command window you used to start the server and open a new one.

When you want to shut down the server, you can either use the CTRL-C shortcut in the window that’s running the server or open another command window and enter the following command.

mysqladmin -u root shutdown

When you run this command, the original window will display the shutdown routine.

You can also install MySQL as a service that will start when Windows starts up by using the same command with a different switch.

mysqld --install

The –remove switch will remove the service from Windows.

7.)  Secure your MySQL instance

As mentioned earlier, you can choose to leave the root password blank during installation and set it later.  Your root user, which has full rights to everything, should definitely have a strong password even on a local installation.  There are also a few other steps that you can take such as removing the test database that’s open to everyone.

Once you’ve started your server, you can run though the list of steps for securing your installation with one command

mysql_secure_installation

This program automates the process of securing the instance as shown below.

8.)  Login to your server

Once everything is setup, you just need to login using the regular mysql program command.

mysql -u root -p

The -u switch specifies the user and the -p is used on its own so that MySQL will prompt you for the password and hide the characters while you type.

Don’t forget, as I did in the above screenshot, that once you’re working in MySQL, most commands are terminated with a semi-colon.

To log out, simply type exit at the mysql> prompt.

For more information …

This has been a basic installation on Windows.  If you’re working with Linux or Mac or need more information on any of the steps, please see Chapter 2 of the official MySQL documentation.