• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Configure Multiple MySQL Servers On One System Using mysqld_multi

by admin

There are just a few steps necessary to set up multiple servers on one system. First, the configuration file for MySQL will need to be adjusted. A separate group of options will need to be entered for each server. At a minimum, each server should be assigned a unique TCP/IP port and either a different socket file.

Server groups for multiple servers in the configuration file are identified by a mysqld prefix followed by a number suffix. For instance, the first server might be identified by a group heading like [mysqld1], you could start with 0 or some other number. The second server could be [mysqld2]. The numbers don’t have to be sequential, per se. Numbers may be skipped. All of the options that can be given for a [mysqld] group may be given to any additional server. This allows you to change server options to meet any special requirements that may be needed for a database. Below are the contents from a /etc/my.cnf file that is configured for three servers.

Note: It is exceptionally important to note that each instance should also have its own datadir. At a minimum, each server should be assigned a unique TCP/IP port and/or a different socket file.
# vi /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/bin/mysqld_safe

[mysqld0]
port = 3306
socket = /tmp/mysql.sock0
pid-file = /usr/local/mysql/server0/
user = mysql0
datadir = /usr/local/mysql/server0

[mysqld1]
port = 3307
socket = /tmp/mysql.sock1
pid-file = /usr/local/mysql/server1/
user = mysql1
datadir = /usr/local/mysql/server1

[mysqld2]
port = 3308
socket = /tmp/mysql.sock2
pid-file = /usr/local/mysql/server2/
user = mysql2
datadir = /usr/local/mysql/server2

First, notice that there is no group labeled [mysqld]. That would confuse things. In this configuration file, each server uses a different port. This will require adjustments to any scripts that access the server. The port will have to be given so that the default port (3306) isn’t used. The first server above uses the default port. In this example, a separate file system user is named for each server. This isn’t necessary. However, it can be useful for better security control. It will require the creation of each system user at the file system level, of course.

The last option set in each group of the configuration file above is the data directory for each server. The servers cannot share the same data directory. Therefore, this will require the creation of a separate directory on the file system. Be sure to change the ownership of the data directories to the respective system user (e.g., mysql0).

Once the configuration file has been set up properly, the system users created, and the related directories are in place, then the servers may be started. To do this easily, use the mysqld_multi script. This script will read the MySQL configuration file and look for server groups following the naming convention mentioned earlier. To start all of the servers, the following would be entered from the command-line:

$ mysqld_multi start

This line will start a separate instance of mysqld_safe for each server group listed in the configuration file. Incidentally, to use a special configuration file for mysqld_multi, just add the “–config-file = file” option before the start parameter. To stop all of the servers, replace the start parameter with stop. For server status reports, use the report parameter. To start a specific server, give the identifying number of the server after the start parameter. For instance, to stop the server labeled [mysqld2] in the configuration file shown earlier, the following would be entered:

$ mysqld_multi stop 2

The mysqld prefix is omitted. A range of servers can be named by giving the number of the first server, followed by a dash, and then the number of the last one (e.g., 2-5). Servers can be listed individually by separating their identifier by commas (e.g., 2, 4). And, a combination of both methods may be used (e.g., 2, 4-7).

For a longer set of examples of how a configuration file may be constructed, enter the following from the command-line:

$ mysqld_multi --example

Filed Under: Linux, mysql

Some more articles you might also be interested in …

  1. autorandr Command Examples in Linux
  2. How To Limit/Restrict FTP Commands On Vsftpd Services (CentOS/RHEL 6,7)
  3. Unix file basics : Inode, Soft Vs Hard link, Device files, Named pipes
  4. How to Save Command history of Selected Users in Linux
  5. How to Determine Which Process is Writing to Disk in Linux
  6. xrpd: command not found
  7. httpie Command Examples in Linux
  8. How to set custom device names using udev in CentOS/RHEL 7
  9. f5fpc Command Examples
  10. Reducing/Limiting the CPUs in CentOS/RHEL 5,6

You May Also Like

Primary Sidebar

Recent Posts

  • netselect-apt Command Examples in Linux
  • netselect-apt: command not found
  • nethogs Command Examples in Linux
  • nethogs: command not found

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright