MYSQL Tutorial: run multiple MySQL servers _ MySQL-mysql tutorial

MYSQL Tutorial: There are many reasons for running multiple MySQL servers to run multiple servers. for example, the above mentioned restriction can bypass the maximum file descriptor, there are also version tests and dedicated services.

Running multiple servers is much more complex than running a single server. pay attention to the following issues:

When installing programs of different versions, separate directories to store programs and data directories. If the server software of the same version is used, the program directory can be the same, but the data directory should be different. The two launch options -- basedir = dir_name and -- datadir = dir_name are available.

Specify the -- port = port_name (TCP/IP listening port), -- socket = file_name (socket file name), and -- pid-file = file_name (process ID file) for different servers from time to time) value.

If the log function is activated, you must specify different log file names for different servers.

On Windows, multiple MySQL servers installed as services must have different service names.

How to configure the option file in a multi-server environment:

Use the -- defaults-file option to specify each option file, so that each server will not read the/etc/my. cnf configuration files, but will use the configuration files you specified.

You can put some common options in the/etc/my. cnf file, and then use the -- defaults-extra-file option to indicate the specific option file of the specific server. In this way, you do not need to repeat the public options in all configuration files.

Use the mysql_multi script to start the server. it allows us to put all the options in the same option file. Each server corresponds to a quota group in the file.

The following describes how to use the mysql_multi script to start multiple servers.

Create a [mysqldxxx] quota group corresponding to the option file for each server. The options used by mysql_multi can be placed in [mysqld_multi. In this way, the/etc/my. cnf option configuration file looks like the following:

[Mysqld001]
Basedir =/usr/local/mysql/001
Datadir =/usr/local/mysql/001/data
Mysqld =/usr/local/mysql/001/bin/mysqld_safe
Socket =/usr/local/mysql/001/mysql. sock
Port = 3306
Local-infile = 1
User = mysqladm
Log = log
Log-update = update-log
Innodb_data_file_path = ibdata1: 10 M

[Mysqld002]
Basedir =/usr/local/mysql/002
Datadir =/usr/local/mysql/002/data
Mysqld =/usr/local/mysql/002/bin/mysqld_safe
Socket =/usr/local/mysql/002/mysql. sock
Port = 3307
Local-infile = 1
User = mysqladm
Log = log
Log-update = update-log
Innodb_data_file_path = ibdata1: 10 M

...
After the option file is configured, run the following command to start the server:

% Mysqld_multi -- no-log start 001,002
# Start the 001 and 002 servers, send the startup information to the console, and provide the server number in the form of a zone
Run the following command to view the Server Status:

% Mysqld_multi -- no-log -- user = root -- password = password report 001
Run the following command to stop the MySQL server:

% Mysqld_multi -- no-log -- user = root -- password = password stop 001
There are two ways to run multiple MySQL servers on windows. one is to run two instances of the same MySQL program and the other is to run multiple windows services. The following describes how to run multiple MySQL servers:

In the first case, you need to set two option files to specify different data directories, for example:

C:/mysql/my. cnf1

[Mysqld]
Basedir = c:/mysql
Datedir = c:/mysql/data1
Port = 3306

C:/mysql/my. cnf2

[Mysqld]
Basedir = c:/mysql
Datadir = c:/mysql/data2
Port = 3307
When starting the server, use the -- defaults-file option to specify the option file. For example:

C:/> mysqld -- defaults-file = c:/mysql/my. cnf1
C:/> mysqld -- defaults-file = c:/mysql/my. cnf2
In MySQL 4.0.2, you can install MySQL as a service and specify a service name, for example:

C:/> mysql-nt -- install service_name
Starting from MySQL 4.0.3, the installation service also supports the -- defaults-file = file_name option.
In this way, we can install MySQL as a series of different services. if service_name is not specified, the installed service name is MySql by default. if service_name is specified, the installed service is named service_name and corresponds to the [service_name] region Group in the option file. The server running with the default service name also supports a named pipe named MySql, the server that explicitly gives the service name will only listen for TCP/IP connections and does not support named pipelines-unless a socket file is explicitly specified using the socket option.

To remove a service, run the mysqladmin shutdown command to stop the server, and then run the following command:

C:/> mysql-nt -- remove # remove the default service
C:/> mysql-nt -- remove service_name # remove a specified service