Many developers mistakenly believe that simply copying the MySQL installation folder is sufficient to create a new instance. While this approach might seem logical, MySQL on Windows requires specific configuration for multiple instances to work properly.

You've correctly identified some key configuration parameters that need to differ between instances:

# Server 1
server-id=1
port=3306
datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data
socket=MySQL1

# Server 2
server-id=2
port=3307
datadir=C:/ProgramData/MySQL/MySQL Server 5.7-2/Data
socket=MySQL2

Instead of running mysqld directly, you should install each instance as a Windows service:

# For first instance (using default settings)
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld" --install MySQL57

# For second instance
"C:\Program Files\MySQL\MySQL Server 5.7-2\bin\mysqld" --install MySQL57_2 --defaults-file="C:\Program Files\MySQL\MySQL Server 5.7-2\my.ini"

Each instance needs its own configuration file with unique settings:

[mysqld]
# Instance 1
port=3306
server-id=1
datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data
socket=MySQL1

# Instance 2
port=3307
server-id=2
datadir=C:/ProgramData/MySQL/MySQL Server 5.7-2/Data
socket=MySQL2

After starting the services, verify both instances are running:

# Check services
sc query MySQL57
sc query MySQL57_2

# Connect to each instance
mysql -u root -P 3306 -p
mysql -u root -P 3307 -p

If instances fail to start, check these logs:

# Default log locations
C:\ProgramData\MySQL\MySQL Server 5.7\Data\hostname.err
C:\ProgramData\MySQL\MySQL Server 5.7-2\Data\hostname.err

When running multiple instances, configure resource limits:

# Memory allocation example
innodb_buffer_pool_size=256M  # For dev environment
max_connections=100

While copying the MySQL installation folder might seem like a quick solution, this method has several critical flaws:

• Service registration conflicts in Windows
• Port number collisions (default 3306)
• Shared program data directory issues
• Missing proper instance initialization

Here's the correct way to configure multiple MySQL instances on Windows:

# Initialize data directories (run as admin)
mysqld --initialize-insecure --basedir="C:\\Program Files\\MySQL\\MySQL Server 5.7" --datadir="C:\\ProgramData\\MySQL\\Instance1\\Data"
mysqld --initialize-insecure --basedir="C:\\Program Files\\MySQL\\MySQL Server 5.7" --datadir="C:\\ProgramData\\MySQL\\Instance2\\Data"

Each instance needs its own my.ini with unique settings:

# Instance1 my.ini
[mysqld]
port=3307
server-id=1
datadir=C:/ProgramData/MySQL/Instance1/Data
socket=MySQL1
pid-file=MySQL1.pid

# Instance2 my.ini
[mysqld]
port=3308
server-id=2
datadir=C:/ProgramData/MySQL/Instance2/Data
socket=MySQL2
pid-file=MySQL2.pid

To properly run them as services:

# Install first instance
mysqld --install MySQL1 --defaults-file="C:\\ProgramData\\MySQL\\Instance1\\my.ini"

# Install second instance
mysqld --install MySQL2 --defaults-file="C:\\ProgramData\\MySQL\\Instance2\\my.ini"

# Start services
net start MySQL1
net start MySQL2

Check with these commands:

# List MySQL services
sc query | find "MySQL"

# Connect to instances
mysql -u root -P 3307 -h 127.0.0.1
mysql -u root -P 3308 -h 127.0.0.1

If services fail to start:

1. Check error logs in datadir
2. Verify port availability (netstat -ano)
3. Ensure proper permissions on datadirs
4. Confirm no leftover processes (taskkill /F /IM mysqld.exe)