MySQL Backup Script

In 2011 the standard trick for backing up MySQL from PHP was a homegrown mysql_dump() function that walked mysql_list_tables() and DESCRIBE output and assembled CREATE TABLE and INSERT statements by hand. It worked, barely — and every part of it is gone today. Here is why, and what to use instead.

Why the original approach is dead

• The mysql_* extension was removed in PHP 7.0 (2015). mysql_list_tables() had been deprecated since PHP 4.3 — php.net keeps the page only as a tombstone. The modern equivalents are MySQLi or PDO.
• It reinvented mysqldump, badly. addslashes() is not SQL escaping (multi-byte character sets break it), NULL values were dumped as empty strings, and DEFAULT '' was emitted for every column whose DESCRIBE default is NULL — which is most of them. Unique and foreign keys were lost entirely; only the primary key survived.
• It was never consistent. Tables were read one at a time with no transaction, so rows written while the script ran produced a dump that never existed at any single point in time.
• Bonus trivia: each(), used in the inner loop, was deprecated in PHP 7.2 and removed in PHP 8.0.

The modern one-liner

Everything that function tried to do, mysqldump does correctly:

mysqldump --single-transaction --routines --triggers --events \
  --user=backup_user --password mydb | gzip > mydb-$(date +%F).sql.gz

• --single-transaction dumps InnoDB tables from one consistent snapshot without blocking writers. It only helps with InnoDB — MyISAM tables still require table locks.
• Triggers are included by default; stored routines and events need --routines and --events.
• Run it as a dedicated backup account with the minimum privileges listed in the mysqldump manual — not as root.

Restoring

gunzip < mydb-2026-09-11.sql.gz | mysql --user=backup_user --password mydb

Porting the 2011 function

If you are dragging an old codebase forward: SHOW TABLES (or information_schema.TABLES) replaces mysql_list_tables(), PDO with prepared statements replaces the string-concatenated queries, and — honestly — the right port is no PHP at all: shell out to mysqldump. For anything bigger than a few gigabytes, physical backups (MySQL’s Clone plugin, or your host’s volume snapshots) beat logical dumps on both speed and restore time. The backup and recovery chapter of the MySQL manual is the best starting point.

Original post (2011)

The following script returns a SQL query with all your database data structure and data.

if (!function_exists('mysql_dump')) {

   function mysql_dump($database) {

      $query = "";

      $tables = @mysql_list_tables($database);
      while ($row = @mysql_fetch_row($tables)) { $table_list[] = $row[0]; }

      for ($i = 0; $i < @count($table_list); $i++) {

         $results = mysql_query('DESCRIBE ' . $database . '.' . $table_list[$i]);

         $query .= 'DROP TABLE IF EXISTS `' . $database . '.' . $table_list[$i] . '`;' . lnbr;
         $query .= lnbr . 'CREATE TABLE `' . $database . '.' . $table_list[$i] . '` (' . lnbr;

         $tmp = '';

         while ($row = @mysql_fetch_assoc($results)) {

            $query .= '`' . $row['Field'] . '` ' . $row['Type'];

            if ($row['Null'] != 'YES') { $query .= ' NOT NULL'; }
            if ($row['Default'] != '') { $query .= ' DEFAULT \''. $row['Default'] .'\''; }
            if ($row['Extra']) { $query .= ' '. strtoupper($row['Extra']); }
            if ($row['Key'] == 'PRI') { $tmp = 'primary key('. $row['Field'] .')'; }

            $query .= ','. lnbr;

         }

         $query .= $tmp . lnbr . ');' . str_repeat(lnbr, 2);

         $results = mysql_query('SELECT * FROM ' . $database . '.' . $table_list[$i]);

         while ($row = @mysql_fetch_assoc($results)) {

            $query .= 'INSERT INTO `'. $database .'.'. $table_list[$i] .'` (';

            $data = Array();

            while (list($key, $value) = @each($row)) { $data['keys'][] = $key; $data['values'][] = addslashes($value); }

            $query .= join($data['keys'], ', ') .')'. lnbr .'VALUES (\''. join($data['values'], '\', \') .'\');'. lnbr;

         }

         $query .= str_repeat(lnbr, 2);

      }

      return $query;

   }

}