Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Because of you referring to foreign keys, I guess you are on Innodb storage so mysqlhotcopy is out of question; but why don't you just have a small pool of empty databases ready all the time, so when a dev needs an instance, you just rename it and work the grants, and then start a new pool db generation in the background when no one is pressing you? Other alternative could be Xtrabackup from Percona. –  guido Oct 9 '13 at 12:10
    
Yes, I use InnoDb. Having a pool of pre-generated databases is a suitable solution. Thank you for this advice. I wanted to know if there is some very fast way to create or copy a database that I am not aware of. But it seems that there is no such way. –  Sanja Oct 9 '13 at 12:53
1  
well, if i remember correctly, i once was successful in restoring innodb databases by just copying .ibf files; there should be some stackoverflow question about that, which i cannot find now, however, it's a delicate process and i wouldn't rely or trust that to run automated testing. –  guido Oct 9 '13 at 13:02
    
@guido, thank you for your comments! –  Sanja Oct 9 '13 at 19:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.