MySql DataDir change at runtime

I am using Delphi XE8 and FireDac.

How can I create a Database at run time to one or more different data folders as the default folder spesified in the my.ini
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
MySQL doesn't work that way.  It is designed to use only one directory for it's data and log files.
gerhard9121Author Commented:
I have been using Pervasive for 30 years and now want to convert to MySql. In the mentioned document they say that you can use different instances with different datadir options. How can I do this at run time.

5.3.1 Setting Up Multiple Data Directories
Each MySQL Instance on a machine should have its own data directory. The location is specified using the --datadir=dir_name option.
Bruno BuesserCommented:
If you are the only MySQL user you could programmatically stop MySQL Server, change entry in my.ini and restart the Server before creating your database.
But that's really only feasible if only your application is accessing MySQL otherwise other applications would lose connection and not find their databases.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

gerhard9121Author Commented:
What affordable SQL server will give me the option to define different data folders. My current system developed over the last 30 years is structured to create temporary files for users under different folders on the server. Changing this will basically force me to do a total rewrite
gerhard9121Author Commented:
After consideration it looks like I will have to be content with MySql.

How can I create a Database at runtime
Dave BaldwinFixer of ProblemsCommented:
That is nothing more than the appropriate SQL statements.  You do have to have very high privileges to do that.
Bruno BuesserCommented:
I guess you would like to create the database with Delphi 8.Use the FireDac library of Delphi. Perhaps you will have to buy a Client/Server license from Embarcadero.
You find a full example here
Use the appropriate MySql syntax
> "What affordable SQL server will give me the option to define different data folders."
While MySQL be a good option, consider looking at different RDBMs such as Firebird, which is totally affordable (free), and gives the flexibility of assigning DBs from different folders (think of it as connection string).

With Firebird you can create your DB wherever you like and connect to it using whatever architecture you setup (Server/Client or locally as Embedded).

PS: Shall you adopt using Firebird, there are differences you need to be aware of first from other RDBMs.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.