Solved

Create a new instance of MySQL

Posted on 2013-06-27
9
502 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 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?
0
 

Author Comment

by:Nashua58
ID: 39284004
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 54

Expert Comment

by:Julian Hansen
ID: 39284121
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

Art
0
 
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.
0
 
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.
0
 

Author Comment

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

Art
wb.png
0
 
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
0
 
LVL 50

Accepted Solution

by:
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
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…

813 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

17 Experts available now in Live!

Get 1:1 Help Now