Most people run a single MySQL server on a designated machine, but there are situations where running multiple servers is beneficial: You can test new versions of the server with the production server running. In this case, a different server will be running.
The operating system usually imposes an overall process limit on the number of descriptors for open files. If your system has difficulty raising the limit, running multiple servers is a workaround (for example, raising the limit may require recompiling the kernel, and if the machine is outside your jurisdiction, you may not be able to do so yet). In this scenario, you might be running multiple instances of the same server.
Internet services often provide customers with their own MySQL installation, which requires a separate server. In this case, you might run multiple instances of the same server or different servers if different customers require different versions of MySQL.
Of course, running several servers simultaneously is more complicated than running just one. If you plan to install multiple versions, you cannot install them all in the same location. When a server is running, certain parameters must be unique for each server. Some of these parameters include where the server is installed: the pathname to the data directory, the TCP/IP port and UNIX domain socket pathname, and the UNIX account used to run multiple servers (if not running all under the same account server). If you decide to run multiple servers, you must keep a good description of the parameters you are using so that you do not lose track of your runs.
Configure and install multiple servers
If you plan to run different versions of the server rather than multiple instances of the same server, you should install them in different locations. If you install binary (not RPM) distribution packages, they will be installed in the directory name containing the version number. If installing from a source distribution, the easiest way to keep the different distributions separate is to use the --with-prefix option when running configure during each version of MySQL installation. This will result in an installation in a separate directory that can be linked to the distribution's version number. For example, you can configure a MySQL distribution package as follows, where version is the MySQL version number:
% ./configure ..with-prefix=/usr/local/mysql-version
The with-prefix option will also determine the server's unique data directory. You may add options to configure other server-specific values, such as TCP/IP port and socket pathname (--with-tcp-port and --with-unix-socket).
If you plan to run multiple instances of the same server, any options specific to the server will need to be specified at runtime.
Multiple server startup process
Starting multiple servers is more complicated than using a single server because both safe_mysqld and mysql.server work well on a single server. The author recommends that you study safe_mysqld carefully and use it as the basis of the startup process, unless you use a more granular copy modified to your needs.
One issue that must be dealt with is how to specify options in the options file. With multiple servers, you cannot use /etc/my.cnf for settings that change on a total server basis, only use this file for settings that are the same for all servers. If each server has a different data directory location, you can specify server-specific parameters in the my.cnf file for each server's data directory. In other words, use /etc/my.cnf for settings that are used by all servers, and DATA DIR/my.cnf for settings that are server-specific, where the DATADIR changes for each server.
Another way to specify server options is to use - - defa ul ts - file =path_name as the first option on the command line to tell the server to read options from the file specified by path_name. This way, the server options in that file can be placed uniquely to that server and then tell the server to read the file on startup. Note that if this option is specified, none of the usual option files (such as /etc/my.cnf) will be used.