dbexport.php - instantly exports your WordPress database to a database.sql file;

dbexport.php

<?php
/**
 * Instantly exports your WordPress database to a database.sql file
 * @author Adrian S. (http://amsquared.co.uk/)
 * @version 1.0
 *
 * Usage instructions - supposed your wordpress site is at http://example.com/:
 * (1) Edit the line 19 below and put in the there a randomString;
 * (2) Upload the file in the root of your WordPress installation (via FTP);
 * (3) Point your browser to http://example.com/dbexport.php?secret=randomString
 *
 */


 
/**
 * Replace someRandomString below with a random one: http://tinyurl.com/RandomString7
 */
$download_key ='someRandomString';


$dbconn = false;

function connect_db_legacy($host='localhost', $username='root', $password='', $database='test', $charset='utf8', $port=false){

    global $dbconn;

    $host = $port ? ( $host . ':' . $port ) : $host; $dbconn = @mysql_connect( $host, $username, $password, false); if ( ! $dbconn ) return false;

    @mysql_set_charset($charset, $dbconn); return @mysql_select_db($database, $dbconn);
}


function autoconnect_db($mysqli=true, $host='localhost'){

    //-- defaults ---//
    $username = 'root';
    $password = '';
    $database = 'test';
    $port = false;
    //-- defaults ---//

    //--- WordPress db settings ---//
    if( defined('DB_HOST') ) $host = DB_HOST;
    if( defined('DB_USER') ) $username = DB_USER;
    if( defined('DB_PASSWORD') ) $password = DB_PASSWORD;
    if( defined('DB_NAME') ) $database = DB_NAME;
    if( defined('DB_CHARSET') ) $charset = DB_CHARSET;
    //--- WordPress db settings ---//

    return connect_db_legacy($host, $username, $password, $database, $charset, $port);
    
}

function backup_db($ignore_tables=array()){

    global $dbconn; autoconnect_db(false);

    if ( ! $dbconn )
        $dbconn = @mysql_connect( DB_HOST, DB_USER, DB_PASSWORD );

    if ( ! $dbconn ) return false;

    if ( function_exists( 'mysql_set_charset') and defined('DB_CHARSET') ) @mysql_set_charset( DB_CHARSET, $dbconn );

    // Begin new backup of MySql
    $tables = @mysql_query( 'SHOW TABLES' );

    $sql_file  = "# WordPress : " . get_bloginfo( 'url' ) . " MySQL database backup\n";
    $sql_file .= "#\n";
    $sql_file .= "# Generated: " . date( 'l j. F Y H:i T' ) . "\n";
    $sql_file .= "# Hostname: " . DB_HOST . "\n";
    $sql_file .= "# Database: " . sql_backquote( DB_NAME ) . "\n";
    $sql_file .= "# --------------------------------------------------------\n";

    save_to_database_export_file($sql_file);

    for ( $i = 0; $i < mysql_num_rows( $tables ); $i++ ) {

        $curr_table = mysql_tablename( $tables, $i );

        //---- should we ignore the table? ---//
        if ($ignore_tables and in_array($curr_table, $ignore_tables) ) continue;
        //---- should we ignore the table? ---//

        // Create the SQL statements
        $sql_file  = "# --------------------------------------------------------\n";
        $sql_file .= "# Table: " . sql_backquote( $curr_table ) . "\n";
        $sql_file .= "# --------------------------------------------------------\n";

        db_make_sql( $sql_file, $curr_table );

    }

    mysql_close($dbconn);

    return true; //finished generating sql

}

function sql_backquote( $a_name ) {

    if ( ! empty( $a_name ) && $a_name !== '*' ) {

        if ( is_array( $a_name ) ) {

            $result = array();

            reset( $a_name );

            while ( list( $key, $val ) = each( $a_name ) )
                $result[$key] = '`' . $val . '`';

            return $result;

        } else {

            return '`' . $a_name . '`';

        }

    } else {

        return $a_name;

    }

}


function sql_addslashes( $a_string = '', $is_like = false ) {

    if ( $is_like )
        $a_string = str_replace( '\\', '\\\\\\\\', $a_string );

    else
        $a_string = str_replace( '\\', '\\\\', $a_string );

    $a_string = str_replace( '\'', '\\\'', $a_string );

    return $a_string;
}


function db_make_sql( $sql_file, $table ) {

    global $dbconn; $chunk_mark = ''; //chunk mark when an SQL statement ends

    // Add SQL statement to drop existing table
    $sql_file .= "\n";
    $sql_file .= "\n";
    $sql_file .= "#\n";
    $sql_file .= "# Delete any existing table " . sql_backquote( $table ) . "\n";
    $sql_file .= "#\n";
    $sql_file .= "\n";
    $sql_file .= "DROP TABLE IF EXISTS " . sql_backquote( $table ) . ";\n";

    /* Table Structure */

    // Comment in SQL-file
    $sql_file .= "\n";
    $sql_file .= "\n";
    $sql_file .= "#\n";
    $sql_file .= "# Table structure of table " . sql_backquote( $table ) . "\n";
    $sql_file .= "#\n";
    $sql_file .= "\n";

    // Get table structure
    $query = 'SHOW CREATE TABLE ' . sql_backquote( $table );
    $result = mysql_query( $query, $dbconn );

    if ( $result ) {

        if ( mysql_num_rows( $result ) > 0 ) {
            $sql_create_arr = mysql_fetch_array( $result );
            $sql_file .= $sql_create_arr[1];
        }

        mysql_free_result( $result );
        $sql_file .= ' ;';

    }

    /* Table Contents */

    // Get table contents
    $query = 'SELECT * FROM ' . sql_backquote( $table );
    $result = mysql_query( $query, $dbconn );

    if ( $result ) {
        $fields_cnt = mysql_num_fields( $result );
        $rows_cnt   = mysql_num_rows( $result );
    }

    // Comment in SQL-file
    $sql_file .= "\n";
    $sql_file .= "\n";
    $sql_file .= "#\n";
    $sql_file .= "# Data contents of table " . $table . " (" . $rows_cnt . " records)\n";
    $sql_file .= "#\n";

    // Checks whether the field is an integer or not
    for ( $j = 0; $j < $fields_cnt; $j++ ) {

        $field_set[$j] = sql_backquote( mysql_field_name( $result, $j ) );
        $type = mysql_field_type( $result, $j );

        if ( $type === 'tinyint' || $type === 'smallint' || $type === 'mediumint' || $type === 'int' || $type === 'bigint'  || $type === 'timestamp')
            $field_num[$j] = true;

        else
            $field_num[$j] = false;

    }

    // Sets the scheme
    $entries = 'INSERT INTO ' . sql_backquote( $table ) . ' VALUES (';
    $search   = array( '\x00', '\x0a', '\x0d', '\x1a' );  //\x08\\x09, not required
    $replace  = array( '\0', '\n', '\r', '\Z' );
    $current_row = 0;
    $batch_write = 0;

    while ( $row = mysql_fetch_row( $result ) ) {

        $current_row++;

        // build the statement
        for ( $j = 0; $j < $fields_cnt; $j++ ) {

            if ( ! isset($row[$j] ) ) {
                $values[]     = 'NULL';

            } elseif ( $row[$j] === '0' || $row[$j] !== '' ) {

                // a number
                if ( $field_num[$j] )
                    $values[] = $row[$j];

                else
                    $values[] = "'" . str_replace( $search, $replace, sql_addslashes( $row[$j] ) ) . "'";

            } else {
                $values[] = "''";

            }

        }

        $sql_file .= " \n" . $entries . implode( ', ', $values ) . ") ;";

        //--- add the chunk mark end after the sql ---//
        if( $current_row%10 == 0 ) $sql_file.= ("\n" . $chunk_mark );
        //--- add the chunk mark end after the sql ---//

        // write the rows in batches of 100
        if ( $batch_write === 100 ) {
            $batch_write = 0;
            save_to_database_export_file( $sql_file );
            $sql_file = '';
        }

        $batch_write++;

        unset( $values );

    }

    mysql_free_result( $result );

    // Create footer/closing comment in SQL-file
    $sql_file .= "\n";
    $sql_file .= "#\n";
    $sql_file .= "# End of data contents of table " . $table . "\n";
    $sql_file .= "# --------------------------------------------------------\n";
    $sql_file .= "\n";

    save_to_database_export_file( $sql_file );

}


function save_to_database_export_file( $sql ) {
   echo $sql;  return true;
}

if( file_exists('wp-load.php') ) include_once 'wp-load.php';

if( defined('ABSPATH') ){

    if( $_GET['secret'] != $download_key ) die('...');

    header("Content-disposition: attachment; filename=database.sql");  backup_db();

}
else die("wp-load.php not found!");