Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to run multiple MySQL in Windows". The content in the article is simple and clear, easy to learn and understand. Please follow the editor's train of thought to study and learn "how to run multiple MySQL in Windows".

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

two。 Set up a profile for each new instance to specify relevant options

The my3307.cnf file is as follows:

[mysql] # set mysql client default character set default-character-set=utf8mb4 [mysqld] # set port 3307 port = 330 setting mysql installation directory basedir=D:\ mysql-5.7.25-win32# set mysql database data storage directory, MySQL 8 + does not need the following configuration, the system can generate its own Otherwise, there may be an error datadir=D:\ mysql-5.7.25-win32\ data3307# allows maximum number of connections the character set used by the max_connections=200# server defaults to the 8-bit encoded latin1 character set character-set-server=utf8mb4# the default storage engine default-storage-engine=INNODBexplicit_defaults_for_timestamp=truelog-error=D:\ mysql-5.7.25-win32\ mysql3307.errpid-file=D:\ mysql-5.7.25-win32 that will be used when creating a new table \ mysqld3307.pidsocket = D:\ mysql-5.7.25-win32\ mysql3307.sock

The my3308.cnf file is as follows:

[mysql] # set mysql client default character set default-character-set=utf8mb4 [mysqld] # set port 3308 port = 330 setting mysql installation directory basedir=D:\ mysql-5.7.25-win32# set mysql database data storage directory, MySQL 8 + does not need the following configuration, the system can generate its own Otherwise, there may be an error datadir=D:\ mysql-5.7.25-win32\ data3308# allows maximum number of connections the character set used by the max_connections=200# server defaults to the 8-bit encoded latin1 character set character-set-server=utf8mb4# the default storage engine default-storage-engine=INNODBexplicit_defaults_for_timestamp=truelog-error=D:\ mysql-5.7.25-win32\ mysql3308.errpid-file=D:\ mysql-5.7.25-win32 that will be used when creating a new table \ mysqld3308.pidsocket = 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\ data3307D:\ 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.cnfD:\ 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 to change the password

C:\ Users\ Administrator > mysql-port=3307-host=127.0.0.1-user=root-password=U0U?KinrdWHbmysql: [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 6Server version: 5.7.25Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 > exitByeC:\ Users\ Administrator > mysql-- port=3307-- host=127.0.0.1-- user=root-- password=123456mysql: [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 7Server version: 5.7.25 Source distributionCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'C'to clear the current input statement.mysql > exitC:\ Users\ Administrator > mysql-- port=3308-- host=127.0.0.1-- user=rootEnter password: * Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 8Server version: 5.7.25Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 > exitByeC:\ Users\ Administrator > mysql-- port=3308-- host=127.0.0.1-- user=root-- password=123456mysql: [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 9Server version: 5.7.25 Source distributionCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 with the appropriate port number and execute the following command:

C:\ Users\ Administrator > mysqladmin-port=3307-- host=127.0.0.1-- user=root-- password=123456 shutdownmysqladmin: [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 shutdownmysqladmin: [Warning] Using a password on the command line interface can be insecure.

The above configuration allows clients to connect through TCP/IP. If your version of Windows supports named pipes and you also want to use named pipes to connect, specify the option to enable named pipes and specify its name. Each instance that supports named pipe connections must use a unique pipe name. For example:

The my3307.cnf file is as follows:

[mysql] # set mysql client default character set default-character-set=utf8mb4 [mysqld] # set port 3307 port = 330 setting mysql installation directory basedir=D:\ mysql-5.7.25-win32# set mysql database data storage directory, MySQL 8 + does not need the following configuration, the system can generate its own Otherwise, there may be an error datadir=D:\ mysql-5.7.25-win32\ data3307# allows maximum number of connections the character set used by the max_connections=200# server defaults to the 8-bit encoded latin1 character set character-set-server=utf8mb4# the default storage engine default-storage-engine=INNODBexplicit_defaults_for_timestamp=truelog-error=D:\ mysql-5.7.25-win32\ mysql3307.errpid-file=D:\ mysql-5.7.25-win32 that will be used when creating a new table \ mysqld3307.pidenable-named-pipesocket = D:\ mysql-5.7.25-win32\ mysql3307.sock

The my3308.cnf file is as follows:

[mysql] # set mysql client default character set default-character-set=utf8mb4 [mysqld] # set port 3308 port = 330 setting mysql installation directory basedir=D:\ mysql-5.7.25-win32# set mysql database data storage directory, MySQL 8 + does not need the following configuration, the system can generate its own Otherwise, there may be an error datadir=D:\ mysql-5.7.25-win32\ data3308# allows maximum number of connections the character set used by the max_connections=200# server defaults to the 8-bit encoded latin1 character set character-set-server=utf8mb4# the default storage engine default-storage-engine=INNODBexplicit_defaults_for_timestamp=truelog-error=D:\ mysql-5.7.25-win32\ mysql3308.errpid-file=D:\ mysql-5.7.25-win32 that will be used when creating a new table \ mysqld3308.pidsocket = 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 use 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, a MySQL server can run as a Windows service. To set up multiple MySQL services, you must make sure that each instance uses a different service name and that other parameters must have a unique value for each instance. To register the MySQL server as a 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 Windows services.

Method 1

Specify all service options in a standard options file. This requires that you specify a different service name for each MySQL service. Suppose 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 the D:\ mysql-5.7.25-win32\ my.ini setting is as follows:

[mysqld1] # set port 3307 port = 330 set the installation directory of mysql:\ mysql-5.7.25-win32# set the data storage directory of the mysql database. MySQL 8 + does not need the following configuration, and the system can generate it itself. Otherwise, there may be an error datadir=D:\ mysql-5.7.25-win32\ data3307# allows maximum number of connections the character set used by the max_connections=200# server defaults to the 8-bit encoded latin1 character set character-set-server=utf8mb4# the default storage engine default-storage-engine=INNODBexplicit_defaults_for_timestamp=truelog-error=D:\ mysql-5.7.25-win32\ mysql3307.errpid-file=D:\ mysql-5.7.25-win32 that will be used when creating a new table \ mysqld3307.pidsocket = D:\ mysql-5.7.25-win32\ mysql3307.sock [mysqld2] # Settings port 3308 port = 330settings installation directory of mysql:\ mysql-5.7.25-win32# sets the directory where the data of the mysql database is stored MySQL 8 + does not need the following configuration, it can be generated by the system itself. Otherwise, there may be an error datadir=D:\ mysql-5.7.25-win32\ data3308# allows maximum number of connections the character set used by the max_connections=200# server defaults to the 8-bit encoded latin1 character set character-set-server=utf8mb4# the default storage engine default-storage-engine=INNODBexplicit_defaults_for_timestamp=truelog-error=D:\ mysql-5.7.25-win32\ mysql3308.errpid-file=D:\ mysql-5.7.25-win32 that will be used when creating a new table \ mysqld3308.pidsocket = D:\ mysql-5.7.25-win32\ mysql3308.sock

Register the service, 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 mysqld1Service successfully installed.D:\ mysql-5.7.25-win32\ bin > mysqld-- install mysqld2Service successfully installed.

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

The C:\ Users\ Administrator > net start mysqld1mysqld1 service is starting. The mysqld1 service has started successfully. The C:\ Users\ Administrator > net start mysqld2mysqld2 service is starting. The mysqld2 service has started successfully. To stop the MySQL service, using the service manager or using the net stop servicename:C:\ Users\ Administrator > net stop mysqld1mysqld1 service is stopping. The mysqld1 service has been stopped successfully. The C:\ Users\ Administrator > net stop mysqld2mysqld2 service is stopping. The mysqld2 service has been stopped successfully.

Delete a service

D:\ mysql-5.7.25-win32\ bin > mysqld-- remove mysqld1Service successfully removed.D:\ mysql-5.7.25-win32\ bin > mysqld-- remove mysqld2Service 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 my3307.cnf file is as follows:

[mysql] # set mysql client default character set default-character-set=utf8mb4 [mysqld] # set port 3307 port = 330 setting mysql installation directory basedir=D:\ mysql-5.7.25-win32# set mysql database data storage directory, MySQL 8 + does not need the following configuration, the system can generate its own Otherwise, there may be an error datadir=D:\ mysql-5.7.25-win32\ data3307# allows maximum number of connections the character set used by the max_connections=200# server defaults to the 8-bit encoded latin1 character set character-set-server=utf8mb4# the default storage engine default-storage-engine=INNODBexplicit_defaults_for_timestamp=truelog-error=D:\ mysql-5.7.25-win32\ mysql3307.errpid-file=D:\ mysql-5.7.25-win32 that will be used when creating a new table \ mysqld3307.pidenable-named-pipesocket = D:\ mysql-5.7.25-win32\ mysql3307.sock

The my3308.cnf file is as follows:

[mysql] # set mysql client default character set default-character-set=utf8mb4 [mysqld] # set port 3308 port = 330 setting mysql installation directory basedir=D:\ mysql-5.7.25-win32# set mysql database data storage directory, MySQL 8 + does not need the following configuration, the system can generate its own Otherwise, there may be an error datadir=D:\ mysql-5.7.25-win32\ data3308# allows maximum number of connections the character set used by the max_connections=200# server defaults to the 8-bit encoded latin1 character set character-set-server=utf8mb4# the default storage engine default-storage-engine=INNODBexplicit_defaults_for_timestamp=truelog-error=D:\ mysql-5.7.25-win32\ mysql3308.errpid-file=D:\ mysql-5.7.25-win32 that will be used when creating a new table \ mysqld3308.pidsocket = 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.cnfService successfully installed.D:\ mysql-5.7.25-win32\ bin > mysqld-install mysqld2-defaults-file=D:\ mysql-5.7.25-win32\ my3308.cnfService successfully installed.

Start the service

The C:\ Users\ Administrator > net start mysqld1mysqld1 service is starting. The mysqld1 service has started successfully. The C:\ Users\ Administrator > net start mysqld2mysqld2 service is starting. The mysqld2 service has started successfully. Thank you for your reading, the above is the content of "how to run multiple MySQL in Windows". After the study of this article, I believe you have a deeper understanding of how to run multiple MySQL in Windows, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.