MySQL: Dump a database from a SQL query

Answer 1

If you don't want to use command line tools, in other words you want to do it completely within say php or whatever language you are using then why don't you iterate over the tables using SQL itself. for example to check the table structure one simple technique would be to capture a snapsot of the table structure with SHOW CREATE TABLE table_name, store the result and then later make the call again and compare the results.

Have you looked at the source code for mysqldump? I am sure most of what you want would be contained within that.

DC

Answer 2

Unless you build the export yourself, I don't think there is a simple solution to export and verify the data. If you do it table per table, LOAD DATA INFILE and SELECT ... INTO OUTFILE may be helpful.

I find it easier to rebuild the database for every test. At least, I can know the exact state of the data. Of course, it takes more time to run those tests, but it's a good incentive to abstract away the operations and write less tests that depend on the database.

An other alternative I use on some projects where the design does not allow such a good division, using InnoDB or some other transactional database engine works well. As long as you keep track of your transactions, or disable them during the test, you can simply start a transaction in setUp() and rollback in tearDown().

Answer 3

Given you're requirements, I think you are left with (pseudo-code + SQL)

tables = mysql_fetch "SHOW TABLES"
foreach table in tables
    create = mysql_fetch "SHOW CREATE TABLE table"
    print create
    rows = mysql_fetch "SELECT * FROM table"
    foreach row in rows
        // or could use VALUES (v1, v2, ...), (v1, v2, ...), .... syntax (maybe preferable for smaller tables)
        insert = "INSERT (fiedl1, field2, field2, etc) VALUES (value1, value2, value3, etc)"
        print insert

Basically, fetch the list of all tables, then walk each table and generate INSERT statements for each row by hand (most apis have a simple way to fetch the list of column names, otherwise you can fall back to calling DESC TABLE).

SHOW CREATE TABLE is done for you, but I'm fairly certain there's nothing analogous to do SHOW INSERT ROWS.

And of course, instead of printing the dump you could do whatever with it.