Create a new instance of MySQL

I have been given the job to convert a VB 2010 application from Microsoft SQL Server to MySQL. For the most part I have had very good success. I’m using MySql 5.6. I have the code to determine MySQL is installed and running locally. How would I create a new instance without using the workbench application, is there a command line I can run?
Who is Participating?
Steve BinkConnect With a Mentor Commented:
MSSQL's concept of server instances does not apply in the world of MySQL.  In MSSQL, each instance is its own separate "installation", so to speak, of the full server.  Instances are configured by default with dynamic ports, so special connection methods (handled by MSSQL communication libraries) are needed to be sure to talk to the correct one.  Connections are facilitated by the SQL Server Browser, or the Listening Service in older versions.

In MySQL, an "instance" is a full service, as well, but there is no overarching management of these installations.  That means there will be no internal optimization of their resource usage, and manual configuration is necessary for each one.  You are limited to a single service listening on a given port, which necessarily means other-than-standard configurations for additional "instances".

Because of this difference, migrating your code to use MySQL will actually simplify it a fair bit.  You no longer need to detect "instances", but rather only if the service is running.  By default, your app can check the standard localhost:port.  If it is detected, prompt the user for connection credentials.  If it is not detected, prompt the user to either supply connection parameters and credentials, or choose to install a new MySQL installation.
Dave BaldwinFixer of ProblemsCommented:
MySQL doesn't have 'instances'.  What are you trying to create?  MySQL does have a command line program you can use for many things which is 'mysql'.  And why do you want to avoid using MySQL WorkBench?
Nashua58Author Commented:

The program has to be able to with code create it's own tables. It also has to determine if a an instance of MySQL is running . If not then I will have to install MySQL, if it is running  I would use that instance but if password's are set then I can't access the server. The next step would be to create an new instance. It is not that I don't want to use workbench but the users will have no idea wht to do.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Nashua58Author Commented:
Thanks I will look at these later when I get back to my office

Dave BaldwinFixer of ProblemsCommented:
Creating a second running 'instance' of MySQL on a machine can be a real problem.  How will it know how to distinguish which instance all the programs belong to?  Especially the 'mysqld' server daemon.  How will it know not to kill the existing instance which people are already using?  You have a number of problems to solve still.
Steve BinkCommented:
I agree with DaveBaldwin.  The MySQL service is intended to be a single running service, not a collection of services.  As I see it, the solution to your question comes in two parts, depending on if MySQL is running or not:

1) If MySQL is not running, install the service as normal.  Be sure to provide all the appropriate credentials for the installation and secure it properly.  Be equally sure that these credentials can be retrieved by the local administrator.

2) If MySQL is running, prompt the user to provide sufficient credentials to allow for connection to the server and whatever operations your application needs to complete.  This may mean altering your application to allow for the user creating not only a set of credentials for your app to use, but perhaps the database as well.

One special note, because one of your responses implied an item of concern: be *sure* to secure the database and server if your application needs to create it.  The service should be installed and run by an appropriate user (ideally, a user created specifically for that role), all root/admin accounts should receive strong passwords, and network accessibility should be limited to only what is critically necessary.  Any users created on the server should have access limited to only what they need.
Nashua58Author Commented:
Thanks for the input everyone, I think there is some confusion as to what I'm asking here. I am not looking to run two separate installations of MySQL. I completely understand why that won't work.

As I understand MySQL and this is based on my experience with SQL Server I am looking to run a second named server or what I call an instance. I am calling it an instance just like I would in SQL Server because in MySQL Workbench it uses the "instance" terminology. Please see the attached picture.

I think it would be best if I outline my goals and issues that I'm having with MySQL and I will admit that it may be a case the I may not be seeing the trees because of the forest. Please keep in mind that everything I am trying to do has been accomplished with SQL Server. The goal of this project is to provide the user with a program that will be as turnkey as possible and there is no need for the user to enter any credentials.

The first time the user runs the program the program will check to see if MySql is running on the users computer. The version that we have now uses the Microsoft SQL Client to enumerate all the SQL Servers running on that computer. I have had to resort to enumerating all the running services one by one to find out if MySQL is running. I personally think this is a poor way to do it but it works to a degree.

The current program would then attempt to create the database with the server that it found. If it cannot for any reason it will run SQL Server Express to create an instance that my program can access. Keep in mind that I am not installing a second program just creating a new server with it's own name and properties.

It is my understanding that I have to run mysqlinstanceconfig.exe to create a new instance. I have no problems with having to run another program from inside my program except. I have not been able to get the path of mysqld.exe from polling the running windows service.

If MySQL is not found on the system I would just install MySQL with the parameters that I  specify and pass them along to the user for future reference.

The next step is to create the database file and the needed tables. I don't anticipate this being a problem at this time.

So I guess I need to ask is any of this even possible with MySQL and if so how would any of you approach this. I have to tell you that I have been told that all of this has to happen automatically and the user is not to be asked for any information.

In the database world this may not be the most preferred way to do something but for the users that this product is aimed at it is the best way. The users are people that may not have any idea how or what a computer needs to do to run. It needs to be a simple as just opening Microsoft Word and just typing a letter. Simple Simple.

I do appreciate all of the feedback that everyone has provided and I hope my more detailed scenario makes it clearer as to what I need to do.

Dave BaldwinFixer of ProblemsCommented:
According to , you run MySQL Server Instance Config Wizard to configure the current installation.  It is not to create a new install or a new 'named instance' like SQL Server.  And MySQLInstanceConfig.exe is not a command line program but a GUI program that asks questions about how you want to configure your server.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.