In most of the cases, you want to run or launch multiple servers of MySQL on the same host or machine. It is useful for many administrative purposes such as testing a new release of MySQL or partitioning client groups into different servers. One Server Instance must not share a file system or network resources with other server instances. Resources like Data and Log Files, Socket file, named pipe or shared memory base name, PID file, Network Socket (combination of IP address and TCP port) must not be shared. Create a new configuration file – mysqld_multi, that will help manage multiple similar servers with different settings.
To achieve multiple servers of MySQL on the same machine, the settings given below must be unique.
Data Directory: Start each server with a unique value for the –datadir option.
Connection Layer: Specify unique connection parameters by starting each server with a unique value for the –port (or –bind-address), –socket, and –shared-memory-basename options.
Log and PID Files: By default, these are present in the data directory and must be unique for each server. If you use non-default locations, specify unique values for –log-error, other log file options and the –pid-file option.
InnoDB Tablespaces and Log Files: By default, these are present in the data directory. If you use non-default locations, specify unique values for tablespaces.
Now let’s talk about mysqld_multi (the configuration file for multiple instances)
The new configuration file is designed to manage several mysqld processes on the same host. Each mysqld listens for connections on a unique UNIX socket file, TCP/IP socket, named pipe or shared memory base name. It applies options to each server N from groups named [mysqldN] in configuration files. Each group contains options that apply to a single numbered host. For example, [mysqld1], [mysqld3] and so on. Specify configuration files in the usual way like standard configuration files including /etc/my.cnf and files that you specify with –defaults-file or –defaults-extra-file.
mysqld_multi: Example Configuration File
…………..File Content Start…………file name would be multi.cnf ……
[mysqld1] ## The [mysqld1] option group contains the settings applied by the first of the servers managed by mysqld_multi.
Datadir=/mysql/data2 ## Each server instance has a unique data directory, port and socket.
User=mysql ## The user settings has a non-default value but it does not need to be unique.
###### Any option that you do not specify in the configuration file assume its default values. In some cases, the default values are file paths relative to the data directory and are effectively unique. For example, the default location of the PID file is in the data directory of the MySQL server, so all three servers in the configuration file have a unique PID file, even though it is not explicitly configured. If you are a beginner, you can get DBA Training to enhance skills for the same. ######
…………………… End Of File……………………..
mysqld_multi – Example:
If I want to start multiple instances using the same command, apply settings from only multi.cnf file. It will read the sections [mysqld1] and [mysqld3], respectively. The command we need to fire is:
mysqldmulti – -default-file=multi.cnf start 1,3
We can use mysqld_multi – – log=filename option to write MySQL log information for all instances to the single file. Please check the command:
Mysqld_multi – -default-file=multi.cnf – -log=multi.log start 1, 3
Alternatively, use per-instance – -log-error=filename in the mysqld_multi configuration file.
Subject Matter Expert
(Oracle Database, Developer, Middleware, MySql, SalesForce)