How to run multiple MySQL instances in Windows

How to run multiple MySQL instances in Windows

Preface

In Windows, you can start multiple MySQL instances manually from the command line using the appropriate parameters for each instance, or by installing multiple servers as Windows services.

1. Create two new instance data directories data3307 and data3308 in the base directory of MySQL in Windows.

2. Set up a configuration file for each new instance to specify the relevant options

The contents of the my3307.cnf file are as follows:

[mysql]
# Set the default character set of the mysql client to default-character-set=utf8mb4

[mysqld]
# Set port 3307 port = 3307
# Set the installation directory of mysql to basedir=D:\mysql-5.7.25-win32
# Set the storage directory for the MySQL database data. MySQL 8+ does not require the following configuration, the system can generate it by itself, otherwise an error may be reported datadir=D:\mysql-5.7.25-win32\data3307
# Maximum number of connections allowed max_connections=200
# The default character set used by the server is the 8-bit latin1 character set character-set-server=utf8mb4
# The default storage engine that will be used when creating a new table 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 contents of the my3308.cnf file are as follows:

[mysql]
# Set the default character set of the mysql client to default-character-set=utf8mb4

[mysqld]
# Set port 3308 port = 3308
# Set the installation directory of mysql to basedir=D:\mysql-5.7.25-win32
# Set the storage directory for the MySQL database data. MySQL 8+ does not require the following configuration, the system can generate it by itself, otherwise an error may be reported datadir=D:\mysql-5.7.25-win32\data3308
# Maximum number of connections allowed max_connections=200
# The default character set used by the server is the 8-bit latin1 character set character-set-server=utf8mb4
# The default storage engine that will be used when creating a new table 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 a 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

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 is started in the foreground (no new prompt appears until the server exits later), so you need to issue these two commands in separate console windows.

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 want to connect using named pipes, specify the Enable named pipes option and specify its name. Each instance supporting named pipe connections must use a unique pipe name. For example:

The contents of the my3307.cnf file are as follows:

[mysql]
# Set the default character set of the mysql client to default-character-set=utf8mb4

[mysqld]
# Set port 3307 port = 3307
# Set the installation directory of mysql to basedir=D:\mysql-5.7.25-win32
# Set the storage directory for the MySQL database data. MySQL 8+ does not require the following configuration, the system can generate it by itself, otherwise an error may be reported datadir=D:\mysql-5.7.25-win32\data3307
# Maximum number of connections allowed max_connections=200
# The default character set used by the server is the 8-bit latin1 character set character-set-server=utf8mb4
# The default storage engine that will be used when creating a new table 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 contents of the my3308.cnf file are as follows:

[mysql]
# Set the default character set of the mysql client to default-character-set=utf8mb4

[mysqld]
# Set port 3308 port = 3308
# Set the installation directory of mysql to basedir=D:\mysql-5.7.25-win32
# Set the storage directory for the MySQL database data. MySQL 8+ does not require the following configuration, the system can generate it by itself, otherwise an error may be reported datadir=D:\mysql-5.7.25-win32\data3308
# Maximum number of connections allowed max_connections=200
# The default character set used by the server is the 8-bit latin1 character set character-set-server=utf8mb4
# The default storage engine that will be used when creating a new table 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 for connecting clients via 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.

Starting multiple MySQL instances as Windows services in Windows

On Windows, a MySQL server can be run as a Windows service. To set up multiple MySQL servers, you must ensure that each instance uses a different service name and that all other parameters have unique values ​​for each instance. To register the MySQL server as a Windows service, use the mysqld --install or mysqld --install-manual option.

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

Method 1

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

[mysqld1]
# Set port 3307 port = 3307
# Set the installation directory of mysql to basedir=D:\mysql-5.7.25-win32
# Set the storage directory for the MySQL database data. MySQL 8+ does not require the following configuration, the system can generate it by itself, otherwise an error may be reported datadir=D:\mysql-5.7.25-win32\data3307
# Maximum number of connections allowed max_connections=200
# The default character set used by the server is the 8-bit latin1 character set character-set-server=utf8mb4
# The default storage engine that will be used when creating a new table 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]
# Set port 3308 port = 3308
# Set the installation directory of mysql to basedir=D:\mysql-5.7.25-win32
# Set the storage directory for the MySQL database data. MySQL 8+ does not require the following configuration, the system can generate it by itself, otherwise an error may be reported datadir=D:\mysql-5.7.25-win32\data3308
# Maximum number of connections allowed max_connections=200
# The default character set used by the server is the 8-bit latin1 character set character-set-server=utf8mb4
# The default storage engine that will be used when creating a new table 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 the service, using the full service pathname to ensure 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:

C:\Users\Administrator>net start mysqld1

The mysqld1 service is starting.

The mysqld1 service has been started successfully.

C:\Users\Administrator>net start mysqld2

The mysqld2 service is starting.

The mysqld2 service has been started successfully.

To stop the MySQL service, use the Service Manager or use net stop servicename:

C:\Users\Administrator>net stop mysqld1

The mysqld1 service is stopping.

The mysqld1 service has been stopped successfully.

C:\Users\Administrator>net stop mysqld2

The mysqld2 service is stopping.

The mysqld2 service has been stopped successfully.

Deleting a service

D:\mysql-5.7.25-win32\bin>mysqld --remove mysqld1
Service successfully removed.

D:\mysql-5.7.25-win32\bin>mysqld --remove mysqld2
Service successfully removed.

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 option file to use. In this case, each file will use the [mysqld] option group.

The contents of the my3307.cnf file are as follows:

[mysql]
# Set the default character set of the mysql client to default-character-set=utf8mb4

[mysqld]
# Set port 3307 port = 3307
# Set the installation directory of mysql to basedir=D:\mysql-5.7.25-win32
# Set the storage directory for the MySQL database data. MySQL 8+ does not require the following configuration, the system can generate it by itself, otherwise an error may be reported datadir=D:\mysql-5.7.25-win32\data3307
# Maximum number of connections allowed max_connections=200
# The default character set used by the server is the 8-bit latin1 character set character-set-server=utf8mb4
# The default storage engine that will be used when creating a new table 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 contents of the my3308.cnf file are as follows:

[mysql]
# Set the default character set of the mysql client to default-character-set=utf8mb4

[mysqld]
# Set port 3308 port = 3308
# Set the installation directory of mysql to basedir=D:\mysql-5.7.25-win32
# Set the storage directory for the MySQL database data. MySQL 8+ does not require the following configuration, the system can generate it by itself, otherwise an error may be reported datadir=D:\mysql-5.7.25-win32\data3308
# Maximum number of connections allowed max_connections=200
# The default character set used by the server is the 8-bit latin1 character set character-set-server=utf8mb4
# The default storage engine that will be used when creating a new table 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 each MySQL instance as a Windows service

D:\mysql-5.7.25-win32\bin>mysqld --install mysqld1 --defaults-file=D:\mysql-5.7.25-win32\my3307.cnf
Service successfully installed.


D:\mysql-5.7.25-win32\bin>mysqld --install mysqld2 --defaults-file=D:\mysql-5.7.25-win32\my3308.cnf
Service successfully installed.

Start the service

C:\Users\Administrator>net start mysqld1

The mysqld1 service is starting.

The mysqld1 service has been started successfully.

C:\Users\Administrator>net start mysqld2

The mysqld2 service is starting.

The mysqld2 service has been started successfully.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:
  • Deploy MySQL 5.7.17 binary installation and multi-instance configuration on CentOS 6.5
  • MySQL multi-instance configuration solution
  • Installing MySQL multiple instances under Linux as a data backup server to achieve multi-master to one-slave multi-instance backup
  • Quickly implement MySQL deployment and one-machine multi-instance deployment
  • In-depth analysis based on MySQL multi-instance installation
  • MySQL tutorial on how to deploy multiple instances on a single machine using mysqld_multi
  • The shell uses mysqld_multi to automatically create multiple instance slave library scripts

<<:  Detailed graphic explanation of hadoop installation and configuration based on Linux7

>>:  How to use axios to make network requests in React Native

Recommend

VMware virtual machine installation Linux system graphic tutorial

This article shares the specific steps of VMware ...

Web realizes the code of popping up the window of uploading files by clicking the picture

Copy code The code is as follows: <style> ....

Graphical tutorial on Maven installation and configuration under Windows (including localized warehouse configuration)

1. Download Maven Maven official website: http://...

MySQL max_allowed_packet setting

max_allowed_packet is a parameter in MySQL that i...

Implementation steps for docker-compose to deploy etcd cluster

Table of contents Write docker-compose.yml Run do...

What are the image file formats and how to choose

1. Which three formats? They are: gif, jpg, and pn...

MySQL 5.7.27 installation and configuration method graphic tutorial

MySQL 5.7.27 detailed download, installation and ...

Detailed explanation of ES6 Promise usage

Table of contents What is a Promise? Usage of rej...

MySQL 8.0 installation tutorial under Linux

This article introduces how to install MySQL 8.0 ...

The simplest MySQL data backup and restore tutorial in history (Part 2) (Part 37)

Data backup and restore part 3, details are as fo...

Analysis of implicit bug in concurrent replication of MySQL 5.7

Preface Most of our MySQL online environments use...

CSS style control to achieve IE submission form record history click return information is still there

It is mainly a CSS style control and a META tag; C...

Briefly describe the use and description of MySQL primary key and foreign key

Table of contents 1. Foreign key constraints What...

Running PostgreSQL in Docker and recommending several connection tools

1 Introduction PostgreSQL is a free software obje...

MySQL 5.6.37 (zip) download installation configuration graphic tutorial

This article shares the download, installation an...