Is there a query to export whole DB?

[strucnjak79]

I have a mysql database with 28 tables, and I need to make a backup of that DB from my app. Is there any sql statement for getting whole DB in form of SQL query, so I can simple run that query again and get my DB back?

PLZ help it's urgent!

 

[OsakaWebbie]

If you can log into your account (command line shell), the most direct way is the command "mysqldump". The way I manage my databases allows another method also, that doesn't require logging in: I always install phpMyAdmin, which has an Export feature that will give you what you want (even with some options that might be handy). If you have a similar tool to phpMyAdmin for managing your database structure, it may also have such a tool. If there is a simple SELECT statement that spews everything, I am not aware of it. But I recommend phpMyAdmin for many reasons besides this.

Hope this helps.

 

[spookie]

Here is a nice article about how take take DB dump using 'MySQLDump' utility.

http://www.devarticles.com/c/a/MySQL/Backing-Up-Your-MySQL-Databases-With-MySQLDump/

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[strucnjak79]

Well I know all about MySQLDump and I use it often... But not now! I'm developing a client application running on windows which connects to remote MySQL server running on linux... I was thinking to develop automatic backup directly from my application but I think that's stupid cause it will be more then one client connecting to DB and no need for users to create backups... MySQLDump and crone should be enough

But I'm steal interested in this, is there some sql query to do that? How phpmyadmin do that (guess it returns all tables names, and then 'select * from' one by one getting structure of tables before)? I was thinking maby there is some elegant way implemented in MySQL which I didn't know, but it seems there is no something like that(but maby it will be a good idea for guy's developing mysql)!

 

[OsakaWebbie]

I hope this isn't too big a chunk of text to put in a post, but here is what appears to be the relevent code snippet from a file called export.php in phpMyAdmin (version 2.6.2 - kinda old, but it has the relevent stuff). Export.php is the "action" of the form on db_details_export.php, which has many options, so it may be hard to decipher. If you need more context, you can of course install phpMyAdmin yourself and look at as many files as you want. I'm sorry that I don't have the time to sift through this myself and give you a nice neat set of SQL statements. Hope this helps. (I chose to include the programmer's "fake loop" so that if you encountered a break in the code and wondered where execution would go, you would know.)

// Fake loop just to allow skip of remain of this code by break, I'd really
// need exceptions here :-)
do {

// Add possibly some comments to export
if (!PMA_exportHeader()) break;

// Will we need relation & co. setup?
$do_relation = isset($GLOBALS[$what . '_relation']);
$do_comments = isset($GLOBALS[$what . '_comments']);
$do_mime     = isset($GLOBALS[$what . '_mime']);
if ($do_relation || $do_comments || $do_mime) {
    require_once('./libraries/relation.lib.php');
    $cfgRelation = PMA_getRelationsParam();
}
if ($do_mime) {
    require_once('./libraries/transformations.lib.php');
}

// Include dates in export?
$do_dates   = isset($GLOBALS[$what . '_dates']);

/**
 * Builds the dump
 */
// Gets the number of tables if a dump of a database has been required
if ($export_type == 'server') {
    /**
     * Gets the databases list - if it has not been built yet
     */
    if ($server > 0 && empty($dblist)) {
        PMA_availableDatabases();
    }

    if (isset($db_select)) {
        $tmp_select = implode($db_select, '|');
        $tmp_select = '|' . $tmp_select . '|';
    }
    // Walk over databases
    foreach ($dblist AS $current_db) {
        if ((isset($tmp_select) && strpos(' ' . $tmp_select, '|' . $current_db . '|'))
            || !isset($tmp_select)) {
            if (!PMA_exportDBHeader($current_db))
                break 2;
            if (!PMA_exportDBCreate($current_db))
                break 2;
            $tables = PMA_DBI_get_tables($current_db);
            foreach ($tables as $table) {
                $local_query  = 'SELECT * FROM ' . PMA_backquote($current_db) . '.' . PMA_backquote($table);
                if (isset($GLOBALS[$what . '_structure'])) {
                    if (!PMA_exportStructure($current_db, $table, $crlf, $err_url, $do_relation, $do_comments, $do_mime, $do_dates))
                        break 3;
                }
                if (isset($GLOBALS[$what . '_data'])) {
                    if (!PMA_exportData($current_db, $table, $crlf, $err_url, $local_query))
                        break 3;
                }
            }
            if (!PMA_exportDBFooter($current_db))
                break 2;
        }
    }
} elseif ($export_type == 'database') {
    if (!PMA_exportDBHeader($db))
        break;

    if (isset($table_select)) {
        $tmp_select = implode($table_select, '|');
        $tmp_select = '|' . $tmp_select . '|';
    }
    $i = 0;
    foreach ($tables as $table) {
        $local_query  = 'SELECT * FROM ' . PMA_backquote($db) . '.' . PMA_backquote($table);
        if ((isset($tmp_select) && strpos(' ' . $tmp_select, '|' . $table . '|'))
            || !isset($tmp_select)) {

            if (isset($GLOBALS[$what . '_structure'])) {
                if (!PMA_exportStructure($db, $table, $crlf, $err_url, $do_relation, $do_comments, $do_mime, $do_dates))
                    break 2;
            }
            if (isset($GLOBALS[$what . '_data'])) {
                if (!PMA_exportData($db, $table, $crlf, $err_url, $local_query))
                    break 2;
            }
        }
    }
    if (!PMA_exportDBFooter($db))
        break;
} else {
    if (!PMA_exportDBHeader($db))
        break;
    // We export just one table

    if ($limit_to > 0 && $limit_from >= 0) {
        $add_query  = ' LIMIT '
                    . (($limit_from > 0) ? $limit_from . ', ' : '')
                    . $limit_to;
    } else {
        $add_query  = '';
    }

    if (!empty($sql_query)) {
        $local_query = $sql_query . $add_query;
        PMA_DBI_select_db($db);
    } else {
        $local_query  = 'SELECT * FROM ' . PMA_backquote($db) . '.' . PMA_backquote($table) . $add_query;
    }

    if (isset($GLOBALS[$what . '_structure'])) {
        if (!PMA_exportStructure($db, $table, $crlf, $err_url, $do_relation, $do_comments, $do_mime, $do_dates))
            break;
    }
    if (isset($GLOBALS[$what . '_data'])) {
        if (!PMA_exportData($db, $table, $crlf, $err_url, $local_query))
            break;
    }
    if (!PMA_exportDBFooter($db))
        break;
}
if (!PMA_exportFooter()) break;

} while (FALSE);
// End of fake loop

 

[strucnjak79]

TNX, that's what I was thinking: too complicated to make this if not nececcery! I'll lost at least one day to implement this in my app.

I'll just let admin's to use phpmyadmin or mysqldump or whatewer they want to make a export/backup!