Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Create a new instance of MySQL

Posted on 2013-06-27
Last Modified: 2013-07-07
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?
Question by:Nashua58
  • 3
  • 3
  • 2
  • +1
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39283356
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'.  http://dev.mysql.com/doc/refman/5.6/en/mysql.html  And why do you want to avoid using MySQL WorkBench?

Author Comment

ID: 39284004

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.
LVL 55

Expert Comment

by:Julian Hansen
ID: 39284121
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


Author Comment

ID: 39284193
Thanks I will look at these later when I get back to my office

LVL 83

Expert Comment

by:Dave Baldwin
ID: 39284848
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.
LVL 50

Expert Comment

by:Steve Bink
ID: 39285621
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.

Author Comment

ID: 39286220
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.

LVL 83

Expert Comment

by:Dave Baldwin
ID: 39286240
According to http://dev.mysql.com/doc/refman/5.1/en/mysql-config-wizard.html , 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.  http://dev.mysql.com/doc/refman/5.1/en/mysql-config-wizard-starting.html
LVL 50

Accepted Solution

Steve Bink earned 500 total points
ID: 39287628
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.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to access a remote mysql database with xampp 3 23
How to duplicate form in Visual Studio 2015 2 58
FInd Image Control Gridview 3 21
MySql Recovery 2 27
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question