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);
    }
}