Technical Recipes

How to set up separate MySQL instance on Windows

It is possible to use a different MySQL server binary per instance, or use the same binary for multiple instances, or any combination of the two approaches.
Whether or not you use distinct server binaries, each instance that you run must be configured with unique values for several operating parameters. This eliminates the potential for conflict between instances. Parameters can be set on the command line, in option files, or by setting environment variables. To see the values used by a given instance, connect to it and execute a SHOW VARIABLES statement.
    > show variables;
1. Create option file for each instance
     To start multiple servers manually, create an option file for each server where each file contains the parameters of the server by copying the original option file to a new option file. Usually the original option file is sitting under the binary distribution root, e.g., C:\Program Files\MySQL\MySQL Server 5.5
    > copy C:\Program Files\MySQL\MySQL Server 5.5\my.ini C:\Program Files\MySQL\MySQL Server 5.5\my-example1.ini

2. Setting Up Multiple Data Directories
    The primary resource managed by a MySQL instance is the data directory. Each instance should use a different data directory, which is configured in the optional file.
    On Windows, the data directory is included in MySQL distributions. Copy the data directory from the installation location to where you are setting up the new instance. Usually the data files are sitting under C:\ProgramData\MySQL on Windows.
    > copy C:\ProgramData\MySQL\MySQL Server 5.5\data C:\ProgramData\MySQL\MySQL Server 5.5\data-example1
    Create the /log, /tmp and /bdb subf-folders under the new data directory, which are configured in the option file.
3. Modify the new option files
    Within these two new option files, modify the parameters within the [mysqld] options group accordingly.
    basedir="c:/Program Files/MySQL/MySQL Server 5.5/"
    datadir="C:/ProgramData/MySQL/MySQL Server x.y/data-example1/"
    You may need to change the error log or other parameters to point the new data directory.
4. Start the new instance in command line
    To start the servers from the command line, include the defaults-file option.
    > mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server 5.5\my-example1.ini"
    This command will hold the DOS prompt as a server daemon. Use a separate window as client to connect to the particular server instance with the appropriate parameters to verify whether the new instance is up and running.
    > mysql -u root -p --port 3308
5. Shutdown the new instance in command line
    In the client window or other separate window, run the following command to shutdown the new instance, which will release the first DOS prompt which starts the new instance.
    > mysqladmin -u root -p --port 3308 shutdown
6. Install the new instance as Windows Service
    Start the DOS prompt as Administrator by right clicking the DOS prompt icon and choose "Run as Administrator", then enter the following command in order to run the new instance in "manual" mode, make sure the new Windows service name is associated with the new instance name and put before the --defaults-file parameter.
    > mysqld --install-manual mysql-example1--defaults-file="C:\Program Files\MySQL\MySQL Server 5.5\my-example1.ini"
    Optionally, the service can be started under "automatic" mode by running:
    > mysqld --install mysql-example1--defaults-file="C:\Program Files\MySQL\MySQL Server 5.5\my-example1.ini"
7. Remove the new Windows Service
    If you need to remove the Windows Service, run  DOS prompt as Administrator and enter the following command after stop and disable the new service being created:
    > mysqld --remove mysql-example1
    If the service is failed to be removed, run the following command and make sure the Windows "Service" panel is closed:
    > sc delete mysql-example1
8. Start and stop the Windows Services from command line or script
    > NET START mysql-lac
    > NET STOP mysql-lac