Now again windows stuff... I am using windows OS in office 😌... This post is about a simple tweaks about running multiple mysql instance in one windows machine. Each instance runs in a different port and behaves as a dedicated standalone server.
Why multiple instances..?
If you are a developer its for you. Sometimes you need to test your application with different type of data (like QA, Production) to replicate some error and etc., At that time no need to drop your old database and put the new one. Just use this and change the db port in your application configuration. This will be helpful for system administrators also to provide database service to his users.
Requirement:
- mysql installed windows machine
- Administrative privileges for that machine
For me mysql installed location is e:\softs\mysql\
Steps:
Create [instances\production] with in mysql installed directory (you can create anywhere). Better do everything in command prompt.Â
e:
cd \softs\mysql\
md instances\production
Copy your data and share folder to production folder
copy data instances\production\
copy share instances\production\
To create your custom ini file copy my.ini file and edit
copy my.ini instances\production.ini
Edit Production.ini
- change port as 3307 under [client] and [mysqld] section
- Change basedir and datadir in that ini file
Click Here for my production.ini file
Now add your custom ini in system starup
mysqld --install mysqldproduction --defaults-file="E:\softs\mysql\instances\production.ini"
Now goto Control Panel → Administrative tools → Services. Then search for 'mysqldproduction' and start service.
OR
net start mysqldproduction
You may think all is done. But after this only I faced problems.
Cannot find the file specified:
When I try to start that service "System error has occurred. The system cannot find the file specified."
There is registry changes needed to fix that issue
- To open registry editor Start → Run
- Type 'regedit' and enter.
- Next, browse to the registry key named:Â HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\mysqldproduction\ImagePath
- Now you can see there is no double quotes in that --defaults-file so change ImagePath to
"E:\softs\mysql\bin\mysqld" --defaults-file="E:\softs\mysql\instances\production.ini" mysqldproduction
Now start that service.
Keep watching Event Viewer. That is the best place to get debug information. Again I am getting error while starting server
Can't find messagefile:
The error is
System error 1067 has occurred.
The process terminated unexpectedly.
Can't find messagefile 'E:\softs\mysql\instances\production\share\errmsg.sys'
Fix:
Just copy errmsg.sys file from [MYSQL_BASE]shareenglishto that instancesproductionshare folder
Now everything is fine. Service will start successfully.