16.4 Using Multiple Servers
It's common to run a single MySQL server on a host, but it's possible to run multiple servers. Managing multiple servers is of course a more complex undertaking than running a single server because you must make sure that the servers don't interfere with each other. None of the servers can share resources that must be used exclusively by a single server. These resources include the following:
Each server must have its own network interfaces, including the TCP/IP port, the named pipe (for Windows), and the Unix socket file (for Unix). One server cannot use network interfaces that are used by another server; it will not even start up properly if it discovers that its network interfaces are already in use. Note that it isn't necessary to set up multiple hostnames for the server host. All the MySQL servers running on a given host can share the same hostname. They can also share the same IP address as long as they listen on different TCP/IP port numbers. Under Windows NT, servers that are run as services must each use a unique service name. Each server must have its own log files. Multiple servers writing to the same log files results in unusable logs. This is also true for status files such as the PID file in which a server records its process ID. InnoDB tablespace files cannot be shared by multiple servers. Each server that uses InnoDB must have its own tablespace. The same is true of the InnoDB log files. Each server normally manages its own data directory, although it's possible for servers to share a data directory under certain circumstances: If the data directory is located on read-only media, there won't be a problem of multiple servers attempting updates of the same data simultaneously. (This precludes use of InnoDB tables because InnoDB currently cannot be used on read-only media.) On read-write media, external locking must be enabled so that servers can cooperate for access to database files. However, external locking does not work on all systems, is disabled by default as of MySQL 4, and does not apply to the InnoDB storage engine anyway.
You can ensure that each server uses its own network interfaces by starting each with a unique value for the --port and --socket options. Similarly, to make sure that each server manages a different data directory, start each one with a unique value for the --datadir option. Normally, having distinct data directories is sufficient to ensure distinct sets of log files as well because logs are created by default in the data directory if you specify their names using relative pathnames.
To set up Windows servers with distinct service names, follow the --install option of the service installation command with a service name. For example:
shell> mysqld --install mysql1
shell> mysqld --install mysql2
Installed that way, these servers will read options from the [mysql1] and [mysql2] groups, respectively, in the standard option files.
Another way to install MySQL as a service is to follow the service name with an option naming the file from which the server should read options when it starts:
shell> mysqld --install mysql1 --defaults-file=C:\mysq1.cnf
shell> mysqld --install mysql2 --defaults-file=C:\mysq2.cnf
In this case, each server will ignore the standard option files and will instead read options only from the [mysqld] group of the option file named by the --defaults-file option.
On Unix, some administrative assistance is available for controlling multiple servers. mysqld_multi is a Perl script intended to make it easier to manage multiple servers on a single host. It can start or stop servers, or report on whether servers are running. mysqld_multi can either start servers directly, or indirectly by invoking mysqld_safe. (An advantage of using mysqld_safe is that it sets up the error log and monitors the server.) mysqld_multi requires installation of the Perl DBI module.
|
