PDO MySQL backups function

Question

This function here http://davidwalsh.name/backup-mysql-database-php

Has been floating around the internets for a while and is pretty famous, but it's using old MySQL API. Does anyone have the same but in PDO? If not does anyone want to make one?

Is it even possible, I read somewhere that PDO doesn't do SHOW CREATE TABLE - is that right?

Answer 1

That backup script is ridiculous and no one should make another version of it. I've seen that script before, as well as similar attempts, and they have a lot of problems:

• Doesn't delimit table names in back-ticks
• Doesn't handle NULLs
• Doesn't handle character sets
• Doesn't handle binary data
• Doesn't back up VIEWs
• Doesn't back up TRIGGERs or STORED PROCEDUREs or STORED FUNCTIONs or EVENTs
• Uses obsolete mysql extension (but this is why you want a PDO version, isn't it?)
• Uses addslashes() instead of a proper MySQL escaping function.
• Appends all data for all tables into one really long string, before outputting the whole content. This means you have to be able to store your entire database in one string, which will almost certainly blow out your PHP max memory limit.

See also my past answer about the unfortunate David Walsh backup script:

• Why is my database backup script not working in php?

---

Re your comment:

Read the comments on the page you linked to. A lot of folks have identified problems, and some have fixes or at least suggestions.

The fact that this script appends everything into one string is a deal-breaker, I think, but it shouldn't be difficult to change the script to open the output file first, then output each row's data during the loop, then close the file after the loop. That's kind of a no-brainer, I'm not sure why the script doesn't do that. But it's pretty clear that the script was not tested very well.

But anyway, I would not try to reinvent this wheel. Mysqldump or mydumper do this job fine. FWIW, you don't have to run mysqldump on the same server where the database resides. Mysqldump supports an option for --host so you can run mysqldump anywhere to back up a remote database, as long as firewalls don't block your client from connecting. Basically, if you can connect a PHP app to the database from some client host, you can connect mysqldump.

If that's really not an option, then I would use the database dump feature of phpmyadmin. These are mature and well-tested and they dump everything correctly. Here's an article that describes how to use the dump feature:

http://www.techrepublic.com/blog/smb-technologist/import-and-export-databases-using-phpmyadmin/

---

[Copying my comments from your answer:]

This is getting into code review, which is not the purpose of StackOverflow. But briefly:

• no proper support for NULL (you convert them to '');
• not consistently delimiting table names;
• using non-ANSI double-quotes as string delimiters;
• using buffered queries on huge tables will break PHP max memory limit;
• appending all rows for a huge table will break PHP max memory limit;
• using addslashes() instead of PDO::quote();
• checking for query errors only at the end of the function;
• not checking for failed file creation;
• gzip extension may not be loaded
• Also, probably still doesn't support UTF8 data.

but it is getting there, no?

Yes, this is better than the original David Walsh script. :-)

whats wrong with NULLs to ''?

NULL is not the same as '' in SQL (except in Oracle, but they are not complying with the SQL standard in this case). See MySQL, better to insert NULL or empty string?

table structure has to be very very big to max memory. each insert row is written to file individually so again, row has to be very very big to max memory.

I misread the code on the memory limit issue. You are writing output for each row, so that's okay (unless the row contains a 1GB blob or something).

But you shouldn't just output a single INSERT statement with a comma-separated set of rows. Even mysqldump --extended-insert outputs a finite length of data, then starts a new INSERT statement. The criteria is whether the length of the INSERT statement fits within the option argument for --net-buffer-length.

whats with wrong with "" string delimiters? how do i get the ANSI one?

In ANSI SQL, single-quotes '' are used to delimit string literals or date literals. Double-quotes "" are used to delimit identifiers like table name or column names. By default, MySQL treats them the same, but this is non-standard. See Do different databases use different name quote?. If you try to import your backup data on a MySQL server where you have SET SQL_MODE=ANSI_QUOTES, the import will fail.

and what tables aren't delimited?

Example: query('SELECT * FROM '.$table); and in fact each of the other cases where you use $table in a query. You only delimited the table once, in the INSERT statement your script outputs.

all $tables aren't delimited, do they all need to be with " "?

MySQL always recognizes back-ticks as identifier delimiters, and single-quotes for strings/dates. But double-quotes change meaning depending on the SQL_MODE I mentioned. You can't assume which SQL_MODE is in effect on the MySQL instance you restore on, so it's best if you use the back-ticks for identifiers, and single-quotes for strings. The reason you'd delimit them as you query your table is that you might have table names that are SQL reserved words, or which contain special characters, etc.

can you insert floats without delimiters into mysql, or do the need the ''? thanks

You can insert all numeric types without delimiters. Only strings and dates need delimiters. See dev.mysql.com/doc/refman/5.6/en/literals.html

Answer 2

To anyone looking for the function which acts like the mysqldump, here is the latest draft, with the imperfections discussed in the comments above/below ironed out.

require 'login.php';
$DBH = new PDO("mysql:host=$db_hostname;dbname=$db_database; charset=utf8", $db_username, $db_password);

//put table names you want backed up in this array.
//leave empty to do all
$tables = array();

backup_tables($DBH, $tables);

function backup_tables($DBH, $tables) {
    $DBH->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL);

    //Script Variables
    $compression = false;
    $BACKUP_PATH = "";
    $nowtimename = time();

    //create/open files
    if ($compression) {
        $zp = gzopen($BACKUP_PATH . $nowtimename . '.sql.gz', "a9");
    } else {
        $handle = fopen($BACKUP_PATH . $nowtimename . '.sql', 'a+');
    }

    //array of all database field types which just take numbers
    $numtypes = array('tinyint', 'smallint', 'mediumint', 'int', 'bigint', 'float', 'double', 'decimal', 'real');

    //get all of the tables
    if (empty($tables)) {
        $pstm1 = $DBH->query('SHOW TABLES');
        while ($row = $pstm1->fetch(PDO::FETCH_NUM)) {
            $tables[] = $row[0];
        }
    } else {
        $tables = is_array($tables) ? $tables : explode(',', $tables);
    }

    //cycle through the table(s)

    foreach ($tables as $table) {
        $result = $DBH->query("SELECT * FROM $table");
        $num_fields = $result->columnCount();
        $num_rows = $result->rowCount();

        $return = "";
        //uncomment below if you want 'DROP TABLE IF EXISTS' displayed
        //$return.= 'DROP TABLE IF EXISTS `'.$table.'`;';

        //table structure
        $pstm2 = $DBH->query("SHOW CREATE TABLE $table");
        $row2 = $pstm2->fetch(PDO::FETCH_NUM);
        $ifnotexists = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $row2[1]);
        $return .= "\n\n" . $ifnotexists . ";\n\n";

        if ($compression) {
            gzwrite($zp, $return);
        } else {
            fwrite($handle, $return);
        }
        $return = "";

        //insert values
        if ($num_rows) {
            $return = 'INSERT INTO `' . $table . '` (';
            $pstm3 = $DBH->query("SHOW COLUMNS FROM $table");
            $count = 0;
            $type = array();

            while ($rows = $pstm3->fetch(PDO::FETCH_NUM)) {
                if (stripos($rows[1], '(')) {
                    $type[$table][] = stristr($rows[1], '(', true);
                } else {
                    $type[$table][] = $rows[1];
                }

                $return .= "`" . $rows[0] . "`";
                $count++;
                if ($count < ($pstm3->rowCount())) {
                    $return .= ", ";
                }
            }

            $return .= ")" . ' VALUES';

            if ($compression) {
                gzwrite($zp, $return);
            } else {
                fwrite($handle, $return);
            }
            $return = "";
        }
        $count = 0;
        while ($row = $result->fetch(PDO::FETCH_NUM)) {
            $return = "\n\t(";

            for ($j = 0; $j < $num_fields; $j++) {

                //$row[$j] = preg_replace("\n","\\n",$row[$j]);

                if (isset($row[$j])) {

                    //if number, take away "". else leave as string
                    if ((in_array($type[$table][$j], $numtypes)) && (!empty($row[$j]))) {
                        $return .= $row[$j];
                    } else {
                        $return .= $DBH->quote($row[$j]);
                    }
                } else {
                    $return .= 'NULL';
                }
                if ($j < ($num_fields - 1)) {
                    $return .= ',';
                }
            }
            $count++;
            if ($count < ($result->rowCount())) {
                $return .= "),";
            } else {
                $return .= ");";
            }
            if ($compression) {
                gzwrite($zp, $return);
            } else {
                fwrite($handle, $return);
            }
            $return = "";
        }
        $return = "\n\n-- ------------------------------------------------ \n\n";
        if ($compression) {
            gzwrite($zp, $return);
        } else {
            fwrite($handle, $return);
        }
        $return = "";
    }

    $error1 = $pstm2->errorInfo();
    $error2 = $pstm3->errorInfo();
    $error3 = $result->errorInfo();
    echo $error1[2];
    echo $error2[2];
    echo $error3[2];

    if ($compression) {
        gzclose($zp);
    } else {
        fclose($handle);
    }
}

Answer 3

As recommended by https://stackoverflow.com/a/18281687/2259391 use mysqldump with exec. It boils down to this:

<?php

function importDatabase($host, $user, $password, $database, $backupFilePath)
{
    //returns true iff successfull
    return exec('mysqlimport --host '. $host .' --user '. $user .' --password '. $password .' '. $database .' '.targetFilePath) === 0;
}

function exportDatabase($host, $user, $password, $database, $targetFilePath)
{
    //returns true iff successfull
    return exec('mysqldump --host '. $host .' --user '. $user .' --password '. $password .' '. $database .' --result-file='.targetFilePath) === 0;
}

Answer 4

All PDO and ext/mysql do are wrap commands to the underlying database (MySQL in this case). That is to say that there is nothing stopping PDO from running SHOW CREATE TABLE or the other commands.

For all intents and purposes you can pretty much just replace:

- $link = mysql_connect($host,$user,$pass);
- mysql_select_db($name,$link);
+ $link = new PDO("mysql:host=$host;dbname=$name", $user, $pass);

And instead of

$result = mysql_query($query);
mysql_fetch_assoc($result);

Use

$result = $link->query($query);
$result->fetch();

Answer 5

i've just finished making the PDO version of david walsh's original backup function.
i've also improved it to address the issues mentioned in Bill Karwin's answer; handles NULL, writes indvidual lines so no memory issues, with backticks etc.
Gives out pretty much exactly what mysqldump does.
Could do with a little tidying but here it is, please advise on any improvements

require 'login.php';
$DBH = new PDO("mysql:host=$db_hostname;dbname=$db_database; charset=utf8", $db_username, $db_password);




//put table names you want backed up in this array.
//leave empty to do all
$tables = array();

backup_tables($DBH, $tables);



function backup_tables($DBH, $tables) {

$DBH->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_TO_STRING );

//Script Variables
$compression = false;
$BACKUP_PATH = "";
$nowtimename = time();


//create/open files
if ($compression) {
$zp = gzopen($BACKUP_PATH.$nowtimename.'.sql.gz', "w9");
} else {
$handle = fopen($BACKUP_PATH.$nowtimename.'.sql','a+');
}


//array of all database field types which just take numbers 
$numtypes=array('tinyint','smallint','mediumint','int','bigint','float','double','decimal','real');

//get all of the tables
if(empty($tables)) {
$pstm1 = $DBH->query('SHOW TABLES');
while ($row = $pstm1->fetch(PDO::FETCH_NUM)) {
$tables[] = $row[0];
}
} else {
$tables = is_array($tables) ? $tables : explode(',',$tables);
}

//cycle through the table(s)

foreach($tables as $table) {
$result = $DBH->query('SELECT * FROM '.$table);
$num_fields = $result->columnCount();
$num_rows = $result->rowCount();

$return="";
//uncomment below if you want 'DROP TABLE IF EXISTS' displayed
//$return.= 'DROP TABLE IF EXISTS `'.$table.'`;'; 


//table structure
$pstm2 = $DBH->query('SHOW CREATE TABLE '.$table);
$row2 = $pstm2->fetch(PDO::FETCH_NUM);
$ifnotexists = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $row2[1]);
$return.= "\n\n".$ifnotexists.";\n\n";


if ($compression) {
gzwrite($zp, $return);
} else {
fwrite($handle,$return);
}
$return = "";

//insert values
if ($num_rows){
$return= 'INSERT INTO `'.$table."` (";
$pstm3 = $DBH->query('SHOW COLUMNS FROM '.$table);
$count = 0;
$type = array();

while ($rows = $pstm3->fetch(PDO::FETCH_NUM)) {

if (stripos($rows[1], '(')) {$type[$table][] = stristr($rows[1], '(', true);
} else $type[$table][] = $rows[1];

$return.= $rows[0];
$count++;
if ($count < ($pstm3->rowCount())) {
$return.= ", ";
}
}

$return.= ")".' VALUES';

if ($compression) {
gzwrite($zp, $return);
} else {
fwrite($handle,$return);
}
$return = "";
}

while($row = $result->fetch(PDO::FETCH_NUM)) {
$return= "\n\t(";
for($j=0; $j<$num_fields; $j++) {
$row[$j] = addslashes($row[$j]);
//$row[$j] = preg_replace("\n","\\n",$row[$j]);


if (isset($row[$j])) {
//if number, take away "". else leave as string
if (in_array($type[$table][$j], $numtypes)) $return.= $row[$j] ; else $return.= '"'.$row[$j].'"' ;
} else {
$return.= '""';
}
if ($j<($num_fields-1)) {
$return.= ',';
}
}
$count++;
if ($count < ($result->rowCount())) {
$return.= "),";
} else {
$return.= ");";

}
if ($compression) {
gzwrite($zp, $return);
} else {
fwrite($handle,$return);
}
$return = "";
}
$return="\n\n-- ------------------------------------------------ \n\n";
if ($compression) {
gzwrite($zp, $return);
} else {
fwrite($handle,$return);
}
$return = "";
}



$error1= $pstm2->errorInfo();
$error2= $pstm3->errorInfo();
$error3= $result->errorInfo();
echo $error1[2];
echo $error2[2];
echo $error3[2];

if ($compression) {
gzclose($zp);
} else {
fclose($handle);
}
}

Answer 6

function backupDB()
{
    $db_config = getDbConfigFromWordPress();
    if ($db_config === false) {
        unset($db_config);
        logMessage('Unable to get database configuration from WordPress', true, 'red');
        return false;
    }

    $new_backup_file = __DIR__ . DIRECTORY_SEPARATOR . 'newbackup_xxx_date.sql';
    if (is_file($new_backup_file) && is_writable($new_backup_file)) {
        @unlink($new_backup_file);
    } elseif (is_file($new_backup_file) && !is_writable($new_backup_file)) {
        logMessage('Unable to remove new backup SQL file. This is necessary to create backup SQL file.', true, 'red');
        return false;
    }
    unset($new_backup_file);

    $dbh = new \PDO('mysql:dbname=' . $db_config['dbname'] . ';host=' . $db_config['dbhost'] . ';charset=' . $db_config['dbcharset'], $db_config['dbuser'], $db_config['dbpassword']);
    $dbh->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_OBJ);
    $dbh->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);

    $sth = $dbh->prepare('SHOW TABLES');
    $sth->execute();
    $result = $sth->fetchAll(\PDO::FETCH_COLUMN);
    $tables = [];
    if (is_array($result) && !empty($result)) {
        foreach ($result as $row) {
            if (is_string($row) && stristr($row, $db_config['tableprefix']) !== false) {
                $tables[] = $row;
            } elseif (is_array($row) && array_key_exists(0, $row) && stristr($row[0], $db_config['tableprefix']) !== false) {
                $tables[] = $row[0];
            }
        }// endforeach;
        natcasesort($tables);
    }
    $sth->closeCursor();
    unset($result, $row, $sth);

    // begins export string header.
    $export_sql = '-- Manual backup SQL Dump'."\n\n";
    $export_sql .= 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";'."\n\n\n";
    $export_sql .= '--'."\n";
    $export_sql .= '-- Database: `' . $db_config['dbname'] . '`'."\n";
    $export_sql .= '--'."\n\n";
    unset($db_config);
    writeDownBackupDB($export_sql);
    unset($export_sql);

    // starting to loop thru tables.
    if (isset($tables) && is_array($tables)) {
        foreach ($tables as $table) {
            $export_sql = '-- --------------------------------------------------------'."\n\n";
            $export_sql .= '--'."\n";
            $export_sql .= '-- Table structure for table `' . $table . '`'."\n";
            $export_sql .= '--'."\n\n";
            $export_sql .= 'DROP TABLE IF EXISTS `' . $table . '`;'."\n";
            $sth = $dbh->prepare('SHOW CREATE TABLE `' . $table . '`');
            $sth->execute();
            $row = $sth->fetch(\PDO::FETCH_NUM);
            if (isset($row[1])) {
                $create_sql_string = $row[1];
                $create_sql_string = str_replace(['CREATE TABLE `'], ['CREATE TABLE IF NOT EXISTS `'], $create_sql_string);
                if (substr($create_sql_string, -1) != ';') {
                    $create_sql_string .= ' ;';
                }
            } else {
                $create_sql_string = '';
            }
            unset($row);
            $export_sql .= $create_sql_string."\n\n";
            $sth->closeCursor();
            unset($sth);
            writeDownBackupDB($export_sql);
            unset($export_sql);

            $export_sql = '--'."\n";
            $export_sql .= '-- Dumping data for table `' . $table . '`'."\n";
            $export_sql .= '--'."\n\n";
            writeDownBackupDB($export_sql);
            unset($export_sql);

            // get fields
            $sth = $dbh->prepare('SELECT * FROM `' . $table . '` LIMIT 1');
            $sth->execute();
            $result = $sth->fetch(\PDO::FETCH_ASSOC);
            if (is_array($result)) {
                $fields = array_keys($result);
            } else {
                $fields = [];
            }
            $sth->closeCursor();
            unset($result, $sth);

            // get fields type
            $sth = $dbh->prepare('DESCRIBE `' . $table . '`');
            $sth->execute();
            $table_columns = $sth->fetchAll();
            $columns = [];
            if (is_array($table_columns)) {
                foreach ($table_columns as $column) {
                    $columns[$column->Field] = [
                        'field' => $column->Field,
                        'type' => $column->Type,
                        'null' => $column->Null,
                        'default' => $column->Default,
                    ];
                }// endforeach;
                unset($column);
            }
            $sth->closeCursor();
            unset($sth, $table_columns);

            if (isset($fields) && is_array($fields) && !empty($fields)) {
                $select_string = 'SELECT ';
                $i_count_field = 1;
                foreach ($fields as $field) {
                    $select_string .= 'IF (`' . $field . '` IS NULL, \'FIELD_VALUE_NULL\', `' . $field . '`) AS `' . $field . '`';
                    if ($i_count_field < count($fields)) {
                        $select_string .= ', ';
                    }
                    $i_count_field++;
                }// endforeach;
                unset($i_count_field, $field);
                $select_string .= ' FROM `' . $table . '`';
                $sth = $dbh->prepare($select_string);
                unset($select_string);
                $sth->execute();
                $result = $sth->fetchAll();
                $export_sql = '';
                if (is_array($result) && !empty($result)) {
                    // generate INSERT INTO `table_name` string.
                    $export_sql .= 'INSERT INTO `' . $table . '` (';
                    $i_count = 1;
                    foreach ($fields as $field) {
                        $export_sql .= '`' . $field . '`';
                        if ($i_count < count($fields)) {
                            $export_sql .= ', ';
                        }
                        $i_count++;
                    }// endforeach;
                    unset($field, $i_count);
                    $export_sql .= ') VALUES'."\n";
                    writeDownBackupDB($export_sql);
                    unset($export_sql);

                    // generate VALUES of INSERT INTO.
                    if (is_array($result)) {
                        $i_count = 1;
                        $i_count_break = 1;
                        foreach ($result as $row) {
                            $export_sql = '(';
                            $i_count_fields = 1;
                            foreach ($fields as $field) {
                                $field_value = $row->{$field};
                                // escape slash
                                $field_value = str_replace('\\', '\\\\', $field_value);
                                // sanitize new line
                                $field_value = str_replace(["\r\n", "\r", "\n"], ['\r\n', '\r', '\n'], $field_value);
                                // escape single quote
                                $field_value = str_replace('\'', '\'\'', $field_value);
                                // change value to NULL if it is NULL.
                                if ($field_value === 'FIELD_VALUE_NULL') {
                                    $field_value = 'NULL';
                                }

                                // detect field value type and cloak with single quote.
                                if (isset($columns[$field]['type']) && 
                                    (
                                        stristr($columns[$field]['type'], 'tinyint(') !== false ||
                                        stristr($columns[$field]['type'], 'smallint(') !== false ||
                                        stristr($columns[$field]['type'], 'mediumint(') !== false ||
                                        stristr($columns[$field]['type'], 'int(') !== false ||
                                        stristr($columns[$field]['type'], 'bigint(') !== false
                                    )
                                ) {
                                    // this field column type is int
                                    if (!is_numeric($field_value) && $field_value !== 'NULL') {
                                        $field_value = '\'' . $field_value . '\'';
                                    }
                                } else {
                                    if ($field_value !== 'NULL') {
                                        $field_value = '\'' . $field_value . '\'';
                                    }
                                }

                                $export_sql .= $field_value;
                                unset($field_value);

                                if ($i_count_fields < count($fields)) {
                                    $export_sql .= ', ';
                                }
                                $i_count_fields++;
                            }// endforeach;
                            unset($field, $i_count_fields);
                            $export_sql .= ')';

                            if ($i_count < count($result)) {
                                if ($i_count_break >= 30) {
                                    $export_sql .= ';'."\n";
                                    writeDownBackupDB($export_sql);
                                    unset($export_sql);
                                    $i_count_break = 0;

                                    $export_sql = 'INSERT INTO `' . $table . '` (';
                                    $i_count_fields = 1;
                                    foreach ($fields as $field) {
                                        $export_sql .= '`' . $field . '`';
                                        if ($i_count_fields < count($fields)) {
                                            $export_sql .= ', ';
                                        }
                                        $i_count_fields++;
                                    }// endforeach;
                                    unset($field, $i_count_fields);
                                    $export_sql .= ') VALUES'."\n";
                                    writeDownBackupDB($export_sql);
                                    unset($export_sql);
                                    $export_sql = '';
                                } else {
                                    $export_sql .= ','."\n";
                                }
                            } else {
                                $export_sql .= ';'."\n\n";
                            }
                            $i_count++;
                            $i_count_break++;
                            writeDownBackupDB($export_sql);
                            unset($export_sql);
                        }// endforeach;
                        unset($i_count, $i_count_break, $result, $row);
                    }
                } else {
                    $export_sql .= "\n";
                    writeDownBackupDB($export_sql);
                    unset($export_sql);
                }
                unset($fields);
                $sth->closeCursor();
                unset($result, $sth);
            } else {
                $export_sql = "\n";
                writeDownBackupDB($export_sql);
                unset($export_sql);
            }
            unset($export_sql);
        }// endforeach;
        unset($table);
    }
    unset($tables);

    unset($dbh);
    logMessage('Backup DB completed. Max memory usage is ' . formatBytes(memory_get_peak_usage(true)) . '.', true, 'green');
    return true;
}// backupDB


/**
 * Write content to backup SQL file by append.
 * 
 * @param string $content
 */
function writeDownBackupDB($content)
{
    $new_backup_file = __DIR__ . DIRECTORY_SEPARATOR . 'newbackup_xxx_date.sql';
    $handle = fopen($new_backup_file, 'a+');
    fwrite($handle, $content);
    fclose($handle);
    unset($handle, $new_backup_file);
}// writeDownBackupDB


logMessage('Beginning backup DB.', true, 'light_gray');
backupDB();

Please note that...

1. Some functions are missing such as logMessage(), getDbConfigFromWordPress(). Please remove from before you use it.
2. Something like $db_config['tableprefix'] or $db_config[...] must be changed.
3. There are many things that cannot handle as @Bill Karwin said.
4. I'm not sure is it support UTF-8 data but as I see it is support many languages and also support emoji ().
5. Export using mysql command is always better idea.

Answer 7

I implemented Lan's latest version with a few modifications (see code below):

• Tables are saved in a folder whose name is today's date; previous versions of the same day are overwritten
• It supports several formats, including comma-separated CSV
• It displays table sizes in bytes and numbers of rows
• Using my (admittedly old-fashioned) tabulations

A reason for adding the csv option is that I found it impossible to import from sql files data in TEXT (UTF8) format when it is multibyte (Asian scripts). It works indeed with BLOB format, but then we can't index it as FULLTEXT. I probably miss a point in formatting the tables...

Here is the code anyway:

function backup_tables($DBH,$tables,$compression,$format) {
$DBH->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL );
//Script Variables
$BACKUP_PATH = DUMP;
$date = date("Y-m-d");
$olddir =  getcwd();
chdir($BACKUP_PATH);
if(!file_exists($date)) {
    echo "<font color=red>Created ‘".$date."’ folder</font><br />";
    $cmd = "mkdir ".$date;
    exec($cmd);
    }
chdir($date);

//array of all database field types which just take numbers 
$numtypes = array('tinyint', 'smallint', 'mediumint', 'int', 'bigint', 'float', 'double', 'decimal', 'real');

//get all of the tables
if(empty($tables)) {
    $pstm1 = $DBH->query('SHOW TABLES');
    while($row = $pstm1->fetch(PDO::FETCH_NUM)) {
        $tables[] = $row[0];
        }
    }
else {
    $tables = is_array($tables) ? $tables : explode(',',$tables);
    }

//cycle through the table(s)
echo "<font color=blue>Dumping tables to DB_DUMP:</font>";
echo "<ul>";
foreach($tables as $table) {
    //create/open files
    if($format == "csv") {
        $filename = $table.".csv";
        $handle = fopen($filename,"w");
        }
    else {
        if($compression) {
            $filename = $table.".sql.gz";
            $zp = gzopen($filename,"wb9");
            }
        else {
            $filename = $table.".sql";
            $handle = fopen($filename,"w");
            }
        }
    echo "<li><small><font color=blue>".$filename."</font>";
    $result = $DBH->query("SELECT * FROM $table");
    $num_fields = $result->columnCount();
    $num_rows = $result->rowCount();
    $return = "";
    $return .= 'DROP TABLE IF EXISTS `'.$table.'`;'; 

    //table structure
    $pstm2 = $DBH->query("SHOW CREATE TABLE $table");
    $row2 = $pstm2->fetch(PDO::FETCH_NUM);
    $ifnotexists = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $row2[1]);
    $return .= "\n\n".$ifnotexists.";\n\n";

    if($format <> "csv") {
        if($compression) gzwrite($zp, $return);
        else fwrite($handle,$return);
        }
    $return = "";

    //insert values
    if($num_rows) {
        $return = 'INSERT INTO `'."$table"."` (";
        $pstm3 = $DBH->query("SHOW COLUMNS FROM $table");
        $count = 0;
        $type = array();

        while($rows = $pstm3->fetch(PDO::FETCH_NUM)) {
            if(stripos($rows[1], '(')) {
                $type[$table][] = stristr($rows[1], '(', true);
                }
            else $type[$table][] = $rows[1];

            $return .= "`".$rows[0]."`";
            $count++;
            if($count < ($pstm3->rowCount())) {
                $return .= ", ";
                }
            }
        $return .= ")".' VALUES';
        if($format <> "csv") {
            if($compression) gzwrite($zp, $return);
            else fwrite($handle,$return);
            }
        $return = "";
        }
    $count = 0;
    while($row = $result->fetch(PDO::FETCH_NUM)) {
        if($format <> "csv") $return = "\n\t(";
        for($j=0; $j < $num_fields; $j++) {
            //$row[$j] = preg_replace("\n","\\n",$row[$j]);
            if(isset($row[$j])) {
                if($format == "csv") $return .= '"'.$row[$j].'"';
                else {
                    //if number, take away "". else leave as string
                    if((in_array($type[$table][$j],$numtypes)) && (!empty($row[$j])))
                        $return .= $row[$j];
                    else
                        $return .= $DBH->quote($row[$j]);
                    }
                }
            else {
                if($format == "csv") $return .= '';
                else $return .= 'NULL';
                }
            if($j < ($num_fields-1)) $return .= ',';
            }
        $count++;
        if($format == "csv") $return .= "\n";
        else {
            if($count < ($result->rowCount()))
                $return .= "),";
            else $return .= ");";
            }
        if($format == "csv") fwrite($handle,$return);
        else {
            if($compression) gzwrite($zp, $return);
            else fwrite($handle,$return);
            }
        $return = "";
        }
    $return = "\n\n-- ------------------------------------------------ \n\n";
    echo " (".$count." records)";

    if($format <> "csv") {
        if($compression) gzwrite($zp, $return);
        else fwrite($handle,$return);
        }
    $return = "";

    $error1 = $pstm2->errorInfo();
    $error2 = $pstm3->errorInfo();
    $error3 = $result->errorInfo();
    echo $error1[2];
    echo $error2[2];
    echo $error3[2];

    if($format == "csv") fclose($handle);
    else {
        if($compression) gzclose($zp);
        else fclose($handle);
        }
    $filesize = filesize($filename);
    echo " - ".$filesize." bytes</small></li>";
    }
echo "</ul>";
chdir($olddir);
return;
}