How to Deploy Multiple MySQL Instances on a Single Server: Step‑by‑Step Guide

Basic Concepts

MySQL multi‑instance means running several MySQL server processes on different ports on the same machine, sharing the same installation but using separate configuration files, data directories, and resources.

Use Cases

Adopt a pseudo‑distributed architecture during early project stages.

Avoid MySQL’s limitation on SMP architectures by binding instances to specific CPUs.

Improve recovery speed of replica slaves by deploying multiple instances on one server.

Provide hot standby for critical data in a separate data center when multi‑master replication is unavailable.

Separate databases per game server (MMO/MMORPG, Web Game) to reduce maintenance errors.

Benefits and Issues

Better utilization of server resources when capacity is idle.

Potential resource contention (memory, CPU, NUMA, disk I/O, network interrupts) requiring careful binding and configuration.

Installation Steps

Synchronize time and install required packages (epel, ntp, autoconf, automake, zlib, libxml, ncurses‑devel, libgcrypt, libtool, openssl, cmake).

Create mysql group and user, prepare data directories (e.g., /data/{3306,3307,3308}).

Compile MySQL from source with appropriate CMake options.

Initialize each instance with scripts/mysql_install_db specifying different datadirs.

Generate a multi‑instance configuration file using mysqld_multi --example > /data/multi.cnf and edit it to define [mysqld1], [mysqld2], [mysqld3] sections (ports 3306‑3308, sockets, pid files, datadirs).

Start instances with mysqld_multi --defaults-file=/data/multi.cnf start 1,2,3 and verify listening ports.

Log in via socket, perform basic security tasks (drop test database, clean mysql.user entries, set root password).

Stop instances using mysqld_multi --defaults-file=/data/multi.cnf stop 1,2,3 or mysqladmin.

Optional: use the provided Bash management script to start, stop, report, or restart instances.

For an additional instance on port 3309, create its data directory, initialize it, and add a [mysqld] section to a separate configuration file.

Conclusion

The multi‑instance setup is straightforward and does not require advanced tricks; it offers flexibility to choose between a single configuration file or multiple files, and can be refined further as needed.