This guide walks through two common operational tasks: bootstrapping several isolated MySQL instances on the same host and regaining access when the root credentials are lost.

1. Anatomy of a MySQL Option File

# /etc/my.cnf or any *.cnf under /etc/my.cnf.d/
[mysqld]               # server-only settings
user=mysql
basedir=/usr/local/mysql
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysql.sock
port=3306
server_id=10           # must be unique across replicas

[client]               # applies to every client binary
socket=/var/run/mysqld/mysql.sock
port=3306

2. Regaining Root Access

When the root password is unknown, you can bypass privilege checking and reset it. Choose any of the approaches below; they are ordered from quicekst to most formal.

2.1 Launch the server with privilege checking disabled

# Stop the running service
sudo systemctl stop mysqld

# Start in background skipping grants
mysqld --skip-grant-tables --skip-networking &

# Connect locally (no password)
mysql -uroot

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Sup3rS3cret!';
mysql> FLUSH PRIVILEGES;
mysql> SHUTDOWN;

# Return to normal operation
sudo systemctl start mysqld

2.2 Temporary directive in my.cnf

sudo systemctl stop mysqld
echo -e "[mysqld]\nskip-grant-tables" | sudo tee -a /etc/my.cnf
sudo systemctl start mysqld

# ... connect and reset password as above ...

sudo sed -i '/skip-grant-tables/d' /etc/my.cnf
sudo systemctl restart mysqld

2.3 One-liner via mysqladmin

mysqladmin -uroot password 'Sup3rS3cret!'

2.4 SQL-level commands

-- Works only if you can already log in
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('Sup3rS3cret!');
-- or
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Sup3rS3cret!';
FLUSH PRIVILEGES;

2.5 Direct UPDATE on the grant tables

-- For 5.6 and earlier
UPDATE mysql.user SET password=PASSWORD('Sup3rS3cret!')
WHERE user='root' AND host='localhost';

-- For 5.7+
UPDATE mysql.user SET authentication_string=PASSWORD('Sup3rS3cret!')
WHERE user='root' AND host='localhost';

FLUSH PRIVILEGES;

3. Running Three Independent Instances

Below we create three side-by-side instances listening on ports 3307, 3308 and 3309. Each has its own data directory, socket, error log and systemd unit.

3.1 Directory layout

3.2 Prepare the filesystem

sudo mkdir -p /data/{3307,3308,3309}/data
sudo chown -R mysql:mysql /data/33*

3.3 Initialize each data directory

mysqld --initialize-insecure \
       --user=mysql \
       --basedir=/usr/local/mysql \
       --datadir=/data/3307/data

mysqld --initialize-insecure \
       --user=mysql \
       --basedir=/usr/local/mysql \
       --datadir=/data/3308/data

mysqld --initialize-insecure \
       --user=mysql \
       --basedir=/usr/local/mysql \
       --datadir=/data/3309/data

3.4 Minimal configuration files

# /data/3307/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3307/data
port=3307
socket=/data/3307/mysql.sock
server_id=7
log_error=/data/3307/mysql.log

# /data/3308/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
server_id=8
log_error=/data/3308/mysql.log

# /data/3309/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3309/data
port=3309
socket=/data/3309/mysql.sock
server_id=9
log_error=/data/3309/mysql.log

3.5 systemd service units

sudo tee /etc/systemd/system/mysql3307.service >/dev/null <<'EOF'
[Unit]
Description=MySQL Server instance on 3307
After=network.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
Restart=on-failure
LimitNOFILE=65535
[Install]
WantedBy=multi-user.target
EOF

sudo tee /etc/systemd/system/mysql3308.service >/dev/null <<'EOF'
[Unit]
Description=MySQL Server instance on 3308
After=network.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
Restart=on-failure
LimitNOFILE=65535
[Install]
WantedBy=multi-user.target
EOF

sudo tee /etc/systemd/system/mysql3309.service >/dev/null <<'EOF'
[Unit]
Description=MySQL Server instance on 3309
After=network.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
Restart=on-failure
LimitNOFILE=65535
[Install]
WantedBy=multi-user.target
EOF

3.6 Enable and start

sudo systemctl daemon-reload
sudo systemctl enable --now mysql3307 mysql3308 mysql3309

3.7 Sanity checks

# Processes
ps -eo pid,args | awk '/mysqld.*330[7-9]/ {print}'

# Listening ports
ss -lntp | awk '/mysqld/ {print $1,$4,$5}'

# Confirm server_id
for p in 3307 3308 3309; do
  mysql -uroot -S /data/$p/mysql.sock -e "SELECT @@server_id AS instance_$p;"
done