Solved

Create a new instance of MySQL

Posted on 2013-06-27
9
496 Views
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?
0
Comment
Question by:Nashua58
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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?
0
 

Author Comment

by:Nashua58
Comment Utility
Hi,

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.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
0
 

Author Comment

by:Nashua58
Comment Utility
Thanks I will look at these later when I get back to my office

Art
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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.
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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.
0
 

Author Comment

by:Nashua58
Comment Utility
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.

Art
wb.png
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
Comment Utility
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
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…
This video discusses moving either the default database or any database to a new volume.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now