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?

  • 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. – Alex 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.