How to run multiple MySQL instances in Windows

2021-12-12 06:09:41
OfStack

Preface

In Windows, you can either manually start multiple MySQL instances with appropriate operational parameters per instance from a command behavior or run by installing multiple servers as Windows services.

1. Create data directories data3307, data3308 for two new instances in the base directory of MySQL in windows

2. Set up a profile for each new instance to specify related options

The my3307.cnf file reads as follows:


[mysql]
#  Settings mysql Client default character set 
default-character-set=utf8mb4

[mysqld]
#  Settings 3307 Port 
port = 3307
#  Settings mysql Installation directory of 
basedir=D:\mysql-5.7.25-win32
#  Settings  mysql The storage directory of the data in the database, MySQL 8+  The following configuration is not required, and can be generated by the system itself, otherwise an error may be reported 
datadir=D:\mysql-5.7.25-win32\data3307
#  Maximum number of connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8mb4
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3307.err
pid-file=D:\mysql-5.7.25-win32\mysqld3307.pid
socket =D:\mysql-5.7.25-win32\mysql3307.sock

The my3308.cnf file reads as follows:


[mysql]
#  Settings mysql Client default character set 
default-character-set=utf8mb4

[mysqld]
#  Settings 3308 Port 
port = 3308
#  Settings mysql Installation directory of 
basedir=D:\mysql-5.7.25-win32
#  Settings  mysql The storage directory of the data in the database, MySQL 8+  The following configuration is not required, and can be generated by the system itself, otherwise an error may be reported 
datadir=D:\mysql-5.7.25-win32\data3308
#  Maximum number of connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8mb4
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock

3. Initialize the new database


D:\mysql-5.7.25-win32\bin>mysqld --defaults-file=D:\mysql-5.7.25-win32\my3307.cnf --initialize --basedir=D:\mysql-5.7.25-win32 --datadir=D:\mysql-5.7.25-win32\data3307

D:\mysql-5.7.25-win32\bin>mysqld --defaults-file=D:\mysql-5.7.25-win32\my3308.cnf --initialize --basedir=D:\mysql-5.7.25-win32 --datadir=D:\mysql-5.7.25-win32\data3308

Step 4 Start the database


D:\mysql-5.7.25-win32\bin>mysqld --defaults-file=D:\mysql-5.7.25-win32\my3307.cnf

D:\mysql-5.7.25-win32\bin>mysqld --defaults-file=D:\mysql-5.7.25-win32\my3308.cnf

Each server starts in the foreground (no new prompts appear until the server exits later), so you need to issue these two commands in a separate console window.

5. Log in to the database and change the password


C:\Users\Administrator>mysql --port=3307 --host=127.0.0.1 --user=root --password=U0U?KinrdWHb
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.25

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\Users\Administrator>mysql --port=3307 --host=127.0.0.1 --user=root --password=123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.25 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit


C:\Users\Administrator>mysql --port=3308 --host=127.0.0.1 --user=root
Enter password: ************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.25

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye


C:\Users\Administrator>mysql --port=3308 --host=127.0.0.1 --user=root --password=123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.25 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

6. To shut down the database server, connect to each instance using the appropriate port number and execute the following command:


C:\Users\Administrator>mysqladmin --port=3307 --host=127.0.0.1 --user=root --password=123456 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

C:\Users\Administrator>mysqladmin --port=3308 --host=127.0.0.1 --user=root --password=123456 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

The above configuration allows clients to connect via TCP/IP. If your version of Windows supports named pipes and you also want to connect using named pipes, specify the Enable named pipe option and specify its name. Named pipe connections supported by each instance must use 1 pipe name with only 1. For example:

The my3307. cnf file reads as follows:


[mysql]
#  Settings mysql Client default character set 
default-character-set=utf8mb4

[mysqld]
#  Settings 3307 Port 
port = 3307
#  Settings mysql Installation directory of 
basedir=D:\mysql-5.7.25-win32
#  Settings  mysql The storage directory of the data in the database, MySQL 8+  The following configuration is not required, and can be generated by the system itself, otherwise an error may be reported 
datadir=D:\mysql-5.7.25-win32\data3307
#  Maximum number of connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8mb4
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3307.err
pid-file=D:\mysql-5.7.25-win32\mysqld3307.pid
enable-named-pipe
socket =D:\mysql-5.7.25-win32\mysql3307.sock

The my3308.cnf file reads as follows:


[mysql]
#  Settings mysql Client default character set 
default-character-set=utf8mb4

[mysqld]
#  Settings 3308 Port 
port = 3308
#  Settings mysql Installation directory of 
basedir=D:\mysql-5.7.25-win32
#  Settings  mysql The storage directory of the data in the database, MySQL 8+  The following configuration is not required, and can be generated by the system itself, otherwise an error may be reported 
datadir=D:\mysql-5.7.25-win32\data3308
#  Maximum number of connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8mb4
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock

Then start the MySQL instance. The process of wanting clients to connect through shared memory is similar to the above process. Use the-shared-memory option for each connection and the-shared-memory-base-name option to specify a unique shared memory name for each instance.

Start multiple MySQL instances as Windows services in Windows

In Windows, one MySQL server can run as an Windows service. In order to set up multiple MySQL services, you must ensure that each instance uses a different service name and other parameters must have only 1 value for each instance. To register an MySQL server as an Windows service, use the mysqld-install or mysqld-install-manual options.

Based on the above information, there are several ways to set up multiple Windows services. You need to shut down and delete any existing Windows services before registering the Windows service.

Method 1

Specify all service options in a standard options file. This requires specifying a different service name for each MySQL service. Assume that the service name of the MySQL instance at port 3307 is mysqld1, and the service name of the MySQL instance at port 3308 is mysqld2. Then D:\ mysql-5. 7.25-win32\ my.ini is set as follows:


[mysqld1]
#  Settings 3307 Port 
port = 3307
#  Settings mysql Installation directory of 
basedir=D:\mysql-5.7.25-win32
#  Settings  mysql The storage directory of the data in the database, MySQL 8+  The following configuration is not required, and can be generated by the system itself, otherwise an error may be reported 
datadir=D:\mysql-5.7.25-win32\data3307
#  Maximum number of connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8mb4
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3307.err
pid-file=D:\mysql-5.7.25-win32\mysqld3307.pid
socket =D:\mysql-5.7.25-win32\mysql3307.sock


[mysqld2]
#  Settings 3308 Port 
port = 3308
#  Settings mysql Installation directory of 
basedir=D:\mysql-5.7.25-win32
#  Settings  mysql The storage directory of the data in the database, MySQL 8+  The following configuration is not required, and can be generated by the system itself, otherwise an error may be reported 
datadir=D:\mysql-5.7.25-win32\data3308
#  Maximum number of connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8mb4
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock

Register services, using the full service pathname to ensure that Windows registers the correct executable for each service:


D:\mysql-5.7.25-win32\bin>mysqld --install mysqld1
Service successfully installed.

D:\mysql-5.7.25-win32\bin>mysqld --install mysqld2
Service successfully installed.

To start the MySQL service, use the service manager or use net start servicename:


[mysql]
#  Settings mysql Client default character set 
default-character-set=utf8mb4

[mysqld]
#  Settings 3308 Port 
port = 3308
#  Settings mysql Installation directory of 
basedir=D:\mysql-5.7.25-win32
#  Settings  mysql The storage directory of the data in the database, MySQL 8+  The following configuration is not required, and can be generated by the system itself, otherwise an error may be reported 
datadir=D:\mysql-5.7.25-win32\data3308
#  Maximum number of connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8mb4
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock

The mysqld1 service is starting.

The mysqld1 service has started successfully.


[mysql]
#  Settings mysql Client default character set 
default-character-set=utf8mb4

[mysqld]
#  Settings 3308 Port 
port = 3308
#  Settings mysql Installation directory of 
basedir=D:\mysql-5.7.25-win32
#  Settings  mysql The storage directory of the data in the database, MySQL 8+  The following configuration is not required, and can be generated by the system itself, otherwise an error may be reported 
datadir=D:\mysql-5.7.25-win32\data3308
#  Maximum number of connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8mb4
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock

The mysqld2 service is starting.

The mysqld2 service has started successfully.

To stop the MySQL service, use the service manager or use net stop servicename:


[mysql]
#  Settings mysql Client default character set 
default-character-set=utf8mb4

[mysqld]
#  Settings 3308 Port 
port = 3308
#  Settings mysql Installation directory of 
basedir=D:\mysql-5.7.25-win32
#  Settings  mysql The storage directory of the data in the database, MySQL 8+  The following configuration is not required, and can be generated by the system itself, otherwise an error may be reported 
datadir=D:\mysql-5.7.25-win32\data3308
#  Maximum number of connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8mb4
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock

mysqld1 service is being stopped.

The mysqld1 service was successfully stopped.


C:\Users\Administrator>net stop mysqld2

The mysqld2 service is stopping.

The mysqld2 service stopped successfully.

Delete service


[mysql]
#  Settings mysql Client default character set 
default-character-set=utf8mb4

[mysqld]
#  Settings 3308 Port 
port = 3308
#  Settings mysql Installation directory of 
basedir=D:\mysql-5.7.25-win32
#  Settings  mysql The storage directory of the data in the database, MySQL 8+  The following configuration is not required, and can be generated by the system itself, otherwise an error may be reported 
datadir=D:\mysql-5.7.25-win32\data3308
#  Maximum number of connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8mb4
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock

Method 2

Use a separate file for each MySQL service to specify options and use the-defaults-file option when registering the service to tell each server which options file to use. In this case, each file will use the [mysqld] option group.

The my3307. cnf file reads as follows:


[mysql]
#  Settings mysql Client default character set 
default-character-set=utf8mb4

[mysqld]
#  Settings 3308 Port 
port = 3308
#  Settings mysql Installation directory of 
basedir=D:\mysql-5.7.25-win32
#  Settings  mysql The storage directory of the data in the database, MySQL 8+  The following configuration is not required, and can be generated by the system itself, otherwise an error may be reported 
datadir=D:\mysql-5.7.25-win32\data3308
#  Maximum number of connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8mb4
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock

The my3308.cnf file reads as follows:


[mysql]
#  Settings mysql Client default character set 
default-character-set=utf8mb4

[mysqld]
#  Settings 3308 Port 
port = 3308
#  Settings mysql Installation directory of 
basedir=D:\mysql-5.7.25-win32
#  Settings  mysql The storage directory of the data in the database, MySQL 8+  The following configuration is not required, and can be generated by the system itself, otherwise an error may be reported 
datadir=D:\mysql-5.7.25-win32\data3308
#  Maximum number of connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8mb4
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock


[mysql]
#  Settings mysql Client default character set 
default-character-set=utf8mb4

[mysqld]
#  Settings 3308 Port 
port = 3308
#  Settings mysql Installation directory of 
basedir=D:\mysql-5.7.25-win32
#  Settings  mysql The storage directory of the data in the database, MySQL 8+  The following configuration is not required, and can be generated by the system itself, otherwise an error may be reported 
datadir=D:\mysql-5.7.25-win32\data3308
#  Maximum number of connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8mb4
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock

Startup service


C:\Users\Administrator>net start mysqld1

The mysqld1 service is starting.

The mysqld1 service has started successfully.


[mysql]
#  Settings mysql Client default character set 
default-character-set=utf8mb4

[mysqld]
#  Settings 3308 Port 
port = 3308
#  Settings mysql Installation directory of 
basedir=D:\mysql-5.7.25-win32
#  Settings  mysql The storage directory of the data in the database, MySQL 8+  The following configuration is not required, and can be generated by the system itself, otherwise an error may be reported 
datadir=D:\mysql-5.7.25-win32\data3308
#  Maximum number of connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8mb4
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=D:\mysql-5.7.25-win32\mysql3308.err
pid-file=D:\mysql-5.7.25-win32\mysqld3308.pid
socket =D:\mysql-5.7.25-win32\mysql3308.sock

The mysqld2 service is starting.

The mysqld2 service has started successfully.

Summarize