Skip to content

WatchTowerHQ/mysqldump-php

BranchesTags

Repository files navigation

mysqldump-php

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
  • 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)

Requirements

Installing

Install using Composer:

composer require druidfi/mysqldump-php

Getting started

<?php

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

Refer to the ifsnop/mysqldump-php Wiki for some examples and a comparison 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 \Druidfi\Mysqldump\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 \Druidfi\Mysqldump\Mysqldump('mysql:host=localhost;dbname=testdb', 'username', 'password');

$dumper->setTableWheres([
    '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 \Druidfi\Mysqldump\Mysqldump('mysql:host=localhost;dbname=testdb', 'username', 'password');

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

Constructor and default parameters

/**
 * Constructor of Mysqldump.
 *
 * @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(
    string $dsn = '',
    string ?$user = '',
    string ?$pass = '',
    array $dumpSettings = [],
    array $pdoSettings = []
)

$dumpSettingsDefault = [
    'include-tables' => [],
    'exclude-tables' => [],
    'compress' => Mysqldump::NONE,
    'init_commands' => [],
    'no-data' => [],
    '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' => Mysqldump::MAXLINESIZE,
    'no-autocommit' => true,
    '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 = [
    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 = array_replace_recursive($pdoSettingsDefault, $pdoSettings);
$this->_dumpSettings = array_replace_recursive($dumpSettingsDefault, $dumpSettings);

Dump Settings

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

PDO Settings

Errors

To dump a database, you need the following privileges :

  • SELECT
    • In order to dump table structures and data.
  • SHOW VIEW
    • If any databases has views, else you will get an error.
  • TRIGGER
    • If any table has one or more triggers.
  • LOCK TABLES
    • If "lock tables" option was enabled.
  • PROCESS
    • If you don’t use the --no-tablespaces option.

Use SHOW GRANTS FOR user@host; to know what privileges user has. See the following link for more information:

Tests

The testing script creates and populates a database using all possible datatypes. Then it exports it using both mysqldump-php and mysqldump, and compares the output. Only if it is identical tests are OK.

Some tests are skipped if mysql server doesn't support them.

A couple of tests are only comparing between original sql code and mysqldump-php generated sql, because some options are not available in mysqldump.

Local setup for tests:

docker-compose up -d --build
docker-compose exec php74 /app/tests/scripts/create_users.sh
docker-compose exec php74 /app/tests/scripts/create_users.sh db2
docker-compose exec -w /app/tests/scripts php74 ./test.sh
docker-compose exec -w /app/tests/scripts php80 ./test.sh
docker-compose exec -w /app/tests/scripts php81 ./test.sh

Bugs (from mysqldump, not from mysqldump-php)

After this bug report, a new one has been introduced. _binary is appended also when hex-blob option is used, if the value is empty.

TODO

  • Handle tablespaces issues
  • Update tests (test.sh and test.php) to pass
  • Update Mysql links in this README.md

Contributing

Format all code to PHP-FIG standards. https://www.php-fig.org/

Credits

Forked from Diego Torres's version which have latest updates from 2020. https://github.com/ifsnop/mysqldump-php

Originally based on James Elliott's script from 2009. https://code.google.com/archive/p/db-mysqldump/

Adapted and extended by Michael J. Calkins. https://github.com/clouddueling

License

This project is open-sourced software licensed under the GPL license

About

PHP version of mysqldump cli that comes with MySQL

github.com/druidfi/mysqldump-php

Resources

Readme

License

GPL-3.0 license
Activity
Custom properties

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

No packages published

Languages

  • PHP 80.0%
  • Shell 19.4%
  • Dockerfile 0.6%