Running two MySQL databases on Windows

This post describes the process you need to follow to set up a second MySQL database on Windows.

Preparation for configuring a secondary database

The base of this guide assumes that you have installed MySQL server for windows already and it is running as normally.

It is assumed that the location of mysqld.exe is C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe. Any places that refer to this command will need it to be replaced with the location of your mysqld.exe executable.

Setting up the database data and files

To start with you will need to pick a location to set the new database files up. For this example I have created a folder under C:\dev\mysql_database_2\ to store the configuration files and C:\dev\mysql_database_2\data\ to store the data directory.

In the main directory, you will need to create a my.ini file to start your database. This will be the full configuration file the database server will use. In my case I am copying the my.ini file used by the main MySQL server, for me this is located in C:\ProgramData\MySQL\MySQL Server 5.7\.

Once I have copied the file there are a few lines that need to be changed. I have modified the file to change these lines to the below contents.

port=3307
datadir=C:\dev\mysql_database_2\data\
secure-file-priv=C:\dev\mysql_database_2\Uploads\
loose_keyring_file_data=C:\dev\mysql_database_2\keyring\keyring

The port is the port that MySQL server will run on and must be free to use. Which means it needs to at least be changed from port 3306 which the current MySQL server runs on. I have changed the data dir parameter to the location I want to store the data in. The secure-file-priv and loose_keyring_file_data fields are internal folders used for housekeeping and we should make sure they have a valid folder.

Initializing the database data directory

Before I can start using the database it needs to be properly initialized and the data directory configured. This adds a set of default tables that MySQL will use to operate. To do this we need to run the initialization command.

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file=C:\dev\mysql_database_2\my.ini --initialize --console

Here we specify our ini file using the defaults-file command line parameter. --initialize tells MySQL to set up the new data directory and --console ensures that the temporary password is printed to the console.

Once this has been run, the final line should be something along the lines of:

2018-11-10T09:55:17.422082Z 1 [Note] A temporary password is generated for root@localhost: xxxxxxxxxx

Save this password as you will need it to log into your database and configure the root password.

Setting up a service to run the new database

Now the database has been initialized we can configure a new windows service to run the database automatically. To install a service you must run the following command in an administrator command terminal.

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --install MySQL57_2 --defaults-file=C:\dev\mysql_database_2\my.ini

The install parameter takes a single parameter, the name of the service. Here I have called the service MySQL57_2. Passing in the defaults file will create the service with the newly created my.ini

Starting the service and changing the root passwordRunning two MySQL databases on Windows

Now that the service has been created it can be started using the windows services panel. By default, the services created will be started automatically on boot.

The first time the database is logged into using the temporary root password it will request you change it. Once this is done you will be able to start using it.

Above is an image of MySQL workbench asking me to change the root password after logging in using the temporary one.

Summary

In the above steps we have created a location to store the data and created our my.ini configuration function. Once this was created we initialized the data and configured it to start on boot using a Windows service. Finally to start using MySQL workbench we changed the current temporary password to a permanent one.