MySQLDump - PHP

Requirements | Installing | Getting started | API | Settings | PDO Settings | TODO | License | Credits

This is a php version of mysqldump cli that comes with MySQL, without dependencies, output compression and sane defaults.

Out of the box, MySQLDump-PHP supports backing up table structures, the data itself, views, triggers and events.

MySQLDump-PHP is the only library that supports:

• output binary blobs as hex.
• resolves view dependencies (using Stand-In tables).
• output compared against original mysqldump. Linked to travis-ci testing system (testing from php 5.3 to 7.3 & hhvm)
• dumps stored routines (functions and procedures).
• dumps events.
• does extended-insert and/or complete-insert.
• supports virtual columns from MySQL 5.7.
• does insert-ignore, like a REPLACE but ignoring errors if a duplicate key exists.
• modifying data from database on-the-fly when dumping, using hooks.
• can save directly to google cloud storage over a compressed stream wrapper (GZIPSTREAM).
• can restore a dump from a file, when no mysql executable is available.

Important

From version 2.0, connections to database are made using the standard DSN, documented in PDO connection string.

Requirements

• PHP 5.3.0 or newer
• MySQL 4.1.0 or newer
• PDO

Installing

Using Composer:

$ composer require ifsnop/mysqldump-php

Using Curl to always download and decompress the latest release:

$ curl --silent --location https://api.github.com/repos/ifsnop/mysqldump-php/releases | grep -i tarball_url | head -n 1 | cut -d '"' -f 4 | xargs curl --location --silent | tar xvz

Getting started

With Autoloader/Composer:

<?php

use Ifsnop\Mysqldump as IMysqldump;

try {
    $dump = new IMysqldump\Mysqldump('mysql:host=localhost;dbname=testdb', 'username', 'password');
    $dump->start('storage/work/dump.sql');
} catch (\Exception $e) {
    echo 'mysqldump-php error: ' . $e->getMessage();
}

Plain old PHP:

<?php

    include_once(dirname(__FILE__) . '/mysqldump-php-2.0.0/src/Ifsnop/Mysqldump/Mysqldump.php');
    $dump = new Ifsnop\Mysqldump\Mysqldump('mysql:host=localhost;dbname=testdb', 'username', 'password');
    $dump->start('storage/work/dump.sql');

Refer to the wiki for some examples and a comparision between mysqldump and mysqldump-php dumps.

Changing values when exporting

You can register a callable that will be used to transform values during the export. An example use-case for this is removing sensitive data from database dumps:

$dumper = new IMysqldump\Mysqldump('mysql:host=localhost;dbname=testdb', 'username', 'password');

$dumper->setTransformTableRowHook(function ($tableName, array $row) {
    if ($tableName === 'customers') {
        $row['social_security_number'] = (string) rand(1000000, 9999999);
    }

    return $row;
});

$dumper->start('storage/work/dump.sql');

Getting information about the dump

You can register a callable that will be used to report on the progress of the dump

$dumper->setInfoHook(function($object, $info) {
    if ($object === 'table') {
        echo $info['name'], $info['rowCount'];
    });

Table specific export conditions

You can register table specific 'where' clauses to limit data on a per table basis. These override the default where dump setting:

$dumper = new IMysqldump\Mysqldump('mysql:host=localhost;dbname=testdb', 'username', 'password');

$dumper->setTableWheres(array(
    'users' => 'date_registered > NOW() - INTERVAL 3 MONTH AND deleted=0',
    'logs' => 'date_logged > NOW() - INTERVAL 1 DAY',
    'posts' => 'isLive=1'
));

Table specific export limits

You can register table specific 'limits' to limit the returned rows on a per table basis:

$dumper = new IMysqldump\Mysqldump('mysql:host=localhost;dbname=testdb', 'username', 'password');

$dumper->setTableLimits(array(
    'users' => 300,
    'logs' => 50,
    'posts' => 10
));

Constructor and default parameters

/**
 * Constructor of Mysqldump. Note that in the case of an SQLite database
 * connection, the filename must be in the $db parameter.
 *
 * @param string $dsn        PDO DSN connection string
 * @param string $user       SQL account username
 * @param string $pass       SQL account password
 * @param array  $dumpSettings SQL database settings
 * @param array  $pdoSettings  PDO configured attributes
 */
public function __construct(
    $dsn = '',
    $user = '',
    $pass = '',
    $dumpSettings = array(),
    $pdoSettings = array()
)

$dumpSettingsDefault = array(
    'include-tables' => array(),
    'exclude-tables' => array(),
    'compress' => Mysqldump::NONE,
    'init_commands' => array(),
    'no-data' => array(),
    'if-not-exists' => false,
    'reset-auto-increment' => false,
    'add-drop-database' => false,
    'add-drop-table' => false,
    'add-drop-trigger' => true,
    'add-locks' => true,
    'complete-insert' => false,
    'databases' => false,
    'default-character-set' => Mysqldump::UTF8,
    'disable-keys' => true,
    'extended-insert' => true,
    'events' => false,
    'hex-blob' => true, /* faster than escaped content */
    'insert-ignore' => false,
    'net_buffer_length' => self::MAXLINESIZE,
    'no-autocommit' => true,
    'no-create-db' => false,
    'no-create-info' => false,
    'lock-tables' => true,
    'routines' => false,
    'single-transaction' => true,
    'skip-triggers' => false,
    'skip-tz-utc' => false,
    'skip-comments' => false,
    'skip-dump-date' => false,
    'skip-definer' => false,
    'where' => '',
    /* deprecated */
    'disable-foreign-keys-check' => true
);

$pdoSettingsDefaults = array(
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false
);

// missing settings in constructor will be replaced by default options
$this->_pdoSettings = self::array_replace_recursive($pdoSettingsDefault, $pdoSettings);
$this->_dumpSettings = self::array_replace_recursive($dumpSettingsDefault, $dumpSettings);

Dump Settings

• include-tables
  • Only include these tables (array of table names), include all if empty.
• exclude-tables
  • Exclude these tables (array of table names), include all if empty, supports regexps.
• include-views
  • Only include these views (array of view names), include all if empty. By default, all views named as the include-tables array are included.
• if-not-exists
  • Only create a new table when a table of the same name does not already exist. No error message is thrown if the table already exists.
• compress
  • Gzip, Bzip2, None.
  • Could be specified using the declared consts: IMysqldump\Mysqldump::GZIP, IMysqldump\Mysqldump::BZIP2 or IMysqldump\Mysqldump::NONE
• reset-auto-increment
  • Removes the AUTO_INCREMENT option from the database definition
  • Useful when used with no-data, so when db is recreated, it will start from 1 instead of using an old value
• add-drop-database
  • https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_add-drop-database
• add-drop-table
  • https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_add-drop-table
• add-drop-triggers
  • https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_add-drop-trigger
• add-locks
  • https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_add-locks
• complete-insert
  • https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_complete-insert

The following options are now enabled by default, and there is no way to disable them since they should alwa