I am writing a PHP application which needs to create new databases as data is added. I want a nice and simple way of specifying how the created databases should look like (i.e. what tables they contain, what indices these tables should contain, ...) and this is currently an EER Diagram in MySql Workbench which I then forward engineer onto the server. I do not consider lots of CREATE-statements in PHP code "nice and simple". Triggered by some event in the PHP application I need to do something which creates a new database with a specified name which is a copy of the template database.
This is sorta a duplicate of this question:
MySQL copy/duplicate database
But the answers to that question are not suitable for me.
I do not want to use mysqldump because I cannot take the security risk of executing shell code on the server, which may be vulnerable to code injection as the name of the created database has to be inserted. Also this.
I also do not want to break the database apart with SHOW-statements and manually execute CREATE-statements to stitch together a new database because that becomes cumbersome fast and it may not capture all the aspects of the template database and therefore introduce hard to find bugs. I am also not sure how well this method handles foreign keys when a referencing table is created before the referenced one.
Ideally I'd like to use a simple SQL statement like CREATE DATABASE ? LIKE TemplateDatabase (where ? represents the database name in some form of prepared statement). Unfortunately though it does not seem like that functionality exists in MySQL.
Apparently PostgreSQL can do something like that just fine but I don't want to migrate just because of a single feature.
How can this be accomplished while fulfilling the aforementioned constraints?
