Populating a MySQL database with values

Question

I have a locally installed MySQL server on my laptop, and I want to use the information in it for a unit-test, so I want to create a script to generate all the data automatically. I'm using MySQL Workbench which already generates the tables (from the model). Is it possible to use it, or another tool, to create an automatic script to populate it with data?

EDIT: I see now that I wasn't clear. I do have meaningful data for the unit test. When I said "generate all the data automatically", I meant the tool should take the meaningful data I have in my local DB today and create a script to generate the same data in other developers' DBs.

Answer 1

The most useful unit tests are those that reflect data you expect or have seen in practice. Pumping your schema full of random bits is not a substitute for carefully crafted test data. As @McWafflestix suggested mysqldump is a useful tool, but if you want something simplier, consider using LOAD DATA with INFILE, which populates a table from a CSV.

Some other things to think about:

1. Test with a database in a known state. Wrap all your database interaction unit tests in transactions that always roll back.
2. Use dbunit to achieve the same end.

Update

If you're in a Java environment, dbUnit is a good solution:

1. You can import and export data in an XML format through its APIs, which would solve the issue of going from your computer to other members on your team.
2. It's designed to restore database state. So it snapshots the database before tests are executed and then restores at then end. So tests are side effect free (i.e. they don't permanently change data).