MySQL/MariaDB Multiple Instances on One Host¶

Useful for a local dev/staging instance alongside production, or for a same-host replica used purely to run backups without locking the primary instance (see Backup and Restore). Each instance needs its own data directory, socket, and port.

Create the Data Structure for the Second Instance¶

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

Create the Configuration File¶

cp /etc/my.cnf /etc/my2.cnf

# /etc/my2.cnf
[mysqld]
datadir         = /var/lib/mysql2
socket          = /var/lib/mysql2/mysql2.sock
port            = 3307
user            = mysql
symbolic-links  = 0

[mysqld_safe]
log-error       = /var/log/mysqld2.log
pid-file        = /var/run/mysqld/mysqld2.pid

Initialize the New Instance's Data Directory¶

mysql_install_db --user=mysql --datadir=/var/lib/mysql2/

Start It¶

mysqld_safe --defaults-file=/etc/my2.cnf &

or, running the daemon directly rather than through mysqld_safe:

/usr/libexec/mysqld --defaults-extra-file=/etc/my2.cnf --datadir=/var/lib/mysql2 --user=mysql \
  --socket=/var/lib/mysql2/mysql2.sock --port=3307 &

Secure and Connect to It¶

mysql -u root -p -S /var/lib/mysql2/mysql2.sock
# or
mysql -u root -p -h 127.0.0.1 -P 3307

Run mysql_secure_installation (or the manual steps in Administration: Security Hardening) against the new instance just as you would the primary — it starts with its own blank/default account set.

Small Wrapper Scripts¶

Convenient for repeatedly starting/connecting to each instance without retyping flags:

# mysql1-connect.sh
#!/bin/bash
mysql -h 127.0.0.1 -P 3306 "$@"

# mysql2-connect.sh
#!/bin/bash
mysql -h 127.0.0.1 -P 3307 "$@"

Init Script for Automatic Startup¶

To have the second instance survive a reboot on an init.d-based system:

cp /etc/init.d/mysqld /etc/init.d/mysqld2

Edit the copy to reference /etc/my2.cnf, the alternate PID file, and the alternate data directory rather than the defaults — the exact edits depend on your init script's structure, but the values needed are the same ones set in /etc/my2.cnf above. On a systemd-managed host, prefer a templated or duplicated systemd unit instead of an init.d script.

Combining Multiple Instances in One my.cnf¶

Rather than separate config files, some setups define both instances ([mysqld] / [mysqld2]-style sections, adjusted per your version's supported syntax) in a single file. This is workable but makes it easy to accidentally restart both instances together when you only meant to touch one — separate config files per instance, as shown above, are the safer default for anything beyond a quick local test.

Backup and Restore: Physical Backups — an LVM-snapshot alternative to running a whole second instance just for backup purposes
Replication — pointing a same-host second instance at the primary as a local replica, rather than running it standalone