Unix Commands - Home

mysqld_multi Command in Linux

Quiz

The mysqld_multi command is a powerful utility provided by MySQL that allows you to manage multiple MySQL server instances on a single machine. This is particularly useful for testing, development, and production environments where you need to run different MySQL configurations simultaneously.

Here is a comprehensive guide to the options available with the mysqld_multi command −

Understanding mysqld_multi Command
Examples of mysqld_multi Command in Linux
Troubleshooting Tips for mysqld_multi Command

Understanding mysqld_multi Command

Before we dive into the mysqld_multi command, ensure you have the following −

A Linux system with root or sudo access.
MySQL installed on your system.
Basic knowledge of MySQL and Linux command-line operations.

Step 1: Install MySQL

If you haven't installed MySQL yet, you can do so using the following commands −

sudo apt update
sudo apt install mysql-server -y

Step 2: Configure Multiple MySQL Instances

To use mysqld_multi, you need to configure multiple MySQL instances. This involves creating separate configuration files and data directories for each instance.

Create Data Directories −

sudo mkdir -p /var/lib/mysql1
sudo mkdir -p /var/lib/mysql2
sudo chown -R mysql:mysql /var/lib/mysql1
sudo chown -R mysql:mysql /var/lib/mysql2

Create Configuration Files − Edit the MySQL configuration file (/etc/mysql/my.cnf) to include configurations for multiple instances.

mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root
password = your_root_password

[mysqld1]
port = 3307
datadir = /var/lib/mysql1
socket = /var/run/mysqld/mysqld1.sock
pid-file = /var/run/mysqld/mysqld1.pid
log-error = /var/log/mysql/mysql1.err

[mysqld2]
port = 3308
datadir = /var/lib/mysql2
socket = /var/run/mysqld/mysqld2.sock
pid-file = /var/run/mysqld/mysqld2.pid
log-error = /var/log/mysql/mysql2.err

Step 3: Initialize Data Directories

Initialize the data directories for each MySQL instance −

sudo mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql1
sudo mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql2

Step 4: Start Multiple MySQL Instances

Use the mysqld_multi command to start the MySQL instances −

sudo mysqld_multi start

You can also start specific instances by specifying their group numbers −

sudo mysqld_multi start 1
sudo mysqld_multi start 2

Step 5: Verify MySQL Instances

Verify that the MySQL instances are running −

sudo mysqld_multi report

This command provides a status report of all configured MySQL instances.

Step 6: Connect to MySQL Instances

Connect to the running MySQL instances using the mysql command-line client −

mysql --socket=/var/run/mysqld/mysqld1.sock -u root -p
mysql --socket=/var/run/mysqld/mysqld2.sock -u root -p

Examples of mysqld_multi Command in Linux

Let's explore some detailed examples and explanations of the mysqld_multi command and its usage.

Starting and Stopping Instances

To start all configured MySQL instances, use the following command −

sudo mysqld_multi start

To stop all instances, use −

sudo mysqld_multi stop

To start or stop specific instances, specify their group numbers −

sudo mysqld_multi start 1
sudo mysqld_multi stop 2

Checking the Status of Instances

To check the status of all MySQL instances, use −

sudo mysqld_multi report

This command provides detailed information about each instance, including its PID, port, and status.

Restarting Instances

To restart all MySQL instances, use −

sudo mysqld_multi restart

To restart specific instances, specify their group numbers −

sudo mysqld_multi restart 1
sudo mysqld_multi restart 2

Custom Configuration for Instances

You can customize the configuration for each MySQL instance by editing the my.cnf file. For example, to set a different buffer pool size for each instance, add the following lines under each [mysqldX] section −

[mysqld1]
innodb_buffer_pool_size = 256M

[mysqld2]
innodb_buffer_pool_size = 512M

Managing Multiple Instances with Different Users

If you need to run MySQL instances with different users, specify the user in the my.cnf file −

[mysqld1]
user = mysql1

[mysqld2]
user = mysql2

Ensure that the data directories and log files have the appropriate permissions for the specified users.

Troubleshooting Tips for mysqld_multi Command

Here are some common issues and troubleshooting tips for using mysqld_multi −

Permission Issues − Ensure that the MySQL data directories and log files have the correct permissions. Use the chown command to set the appropriate ownership −

sudo chown -R mysql:mysql /var/lib/mysql1
sudo chown -R mysql:mysql /var/lib/mysql2

Port Conflicts − Ensure that each MySQL instance is configured to use a unique port. Check the my.cnf file to verify the port settings −

[mysqld1]
port = 3307

[mysqld2]
port = 3308

Socket File Issues − Ensure that each MySQL instance is configured to use a unique socket file. Check the my.cnf file to verify the socket settings −

[mysqld1]
socket = /var/run/mysqld/mysqld1.sock

[mysqld2]
socket = /var/run/mysqld/mysqld2.sock

Log File Issues − Ensure that each MySQL instance is configured to use a unique log file. Check the my.cnf file to verify the log file settings −

[mysqld1]
log-error = /var/log/mysql/mysql1.err

[mysqld2]
log-error = /var/log/mysql/mysql2.err

Initialization Issues − If you encounter issues during the initialization of data directories, ensure that the directories are empty and have the correct permissions. Use the mysqld --initialize-insecure command to initialize the directories −

sudo mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql1
sudo mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql2

Conclusion

The mysqld_multi command is a powerful tool for managing multiple MySQL instances on a single machine. By following the steps outlined in this guide, you can configure, start, stop, and manage multiple MySQL instances with ease. This setup is particularly useful for testing, development, and production environments where different MySQL configurations are required.

With the ability to customize each instance's configuration and manage them independently, mysqld_multi provides a flexible and efficient solution for running multiple MySQL servers on a single system.

Print Page