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 Workstation virtual machine installation operation method

Virtual machines are very convenient testing soft...

Detailed explanation of MySQL using profile to analyze slow SQL (group left join is more efficient than subquery)

Use profile to analyze slow SQL The main purpose ...

Summary of methods for finding and deleting duplicate data in MySQL tables

Sometimes we save a lot of duplicate data in the ...

Two ways to install the Linux subsystem in Windows 10 (with pictures and text)

Windows 10 now supports Linux subsystem, saying g...

The difference between Readonly and Disabled

To summarize: Readonly is only valid for input (te...

Detailed explanation of how to install MySQL on Alibaba Cloud

As a lightweight open source database, MySQL is w...

How to make ApacheBench support multi-url

Since the standard ab only supports stress testin...

Specific use of MySQL binlog_ignore_db parameter

Preface: After studying the previous article, we ...

Detailed explanation of Vue's TodoList case

<template> <div id="root"> ...

Detailed analysis and usage of tcpdump command under Linux

Introduction To put it simply, tcpdump is a packe...

HTML table markup tutorial (2): table border attributes BORDER

By default, the border of the table is 0, and we ...

17 404 Pages You'll Want to Experience

How can we say that we should avoid 404? The reas...

Tutorial on installing mysql5.7.36 database in Linux environment

Download address: https://dev.mysql.com/downloads...

Solve the error problem caused by modifying mysql data_dir

Today, I set up a newly purchased Alibaba Cloud E...

MYSQL implements sample code to prevent duplicate addition when adding shopping cart

Preface Recently, due to work reasons, I was work...