I need to setup a development environment for several developers. Each of them needs to test the software with a "fresh" MySQL database. There is a SQL file with many CREATE, ALTER and INSERT queries.
Currently there is a PHP script with mysqli::multi_query that creates a new database and performs all queries from the SQL file. It is called each time when some developer needs a fresh instance of database. But it takes too much time to execute all needed queries.
I tried to change script to execute mysql < my_pre_mysqldumped_file.sql, but it is almost same slow.
Also, I tried to have an "initial" database and copy each table with CREATE TABLE ... LIKE ..., but it does not copy foreign keys.
So, the question: what is the fastest way from server performance point of view to create a new one or copy existing MySQL database?
Based on my investigation in the internet, I suppose that there is no efficient way to do it. I also have asked this question at https://dba.stackexchange.com/questions/51257/what-is-the-most-efficient-way-to-create-new-mysql-database. Guido's suggestion to have a stack of pre-generated databases seems to be the most relevant.