Home

Awesome

mysqldump-php

Run tests Total Downloads Monthly Downloads Daily Downloads Latest Stable Version

This is a PHP version of mysqldump cli that comes with MySQL. It can be used for interacting with the data before creating the database dump. E.g. it can modify the contents of tables and is thus good for anonymize data.

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

mysqldump-php supports:

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

You can also specify the limits as an array where the first value is the number of rows and the second is the offset

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

$dumper->setTableLimits([
    'users' => [20, 10], //MySql query equivalent "... LIMIT 20 OFFSET 10"
]);

Dump Settings

Dump settings can be changed from default values with 4th argument for Mysqldump constructor:

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

All options:

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

Privileges

To dump a database, you need the following privileges:

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 --wait --build
docker compose exec php81 /app/tests/scripts/create_users.sh
docker compose exec php81 /app/tests/scripts/create_users.sh db2
docker compose exec php81 /app/tests/scripts/create_users.sh db3
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
docker compose exec -w /app/tests/scripts php82 ./test.sh
docker compose exec -w /app/tests/scripts php83 ./test.sh
docker compose exec -w /app/tests/scripts php74 ./test.sh db2
docker compose exec -w /app/tests/scripts php80 ./test.sh db2
docker compose exec -w /app/tests/scripts php81 ./test.sh db2
docker compose exec -w /app/tests/scripts php82 ./test.sh db2
docker compose exec -w /app/tests/scripts php83 ./test.sh db2
docker compose exec -w /app/tests/scripts php74 ./test.sh db3
docker compose exec -w /app/tests/scripts php80 ./test.sh db3
docker compose exec -w /app/tests/scripts php81 ./test.sh db3
docker compose exec -w /app/tests/scripts php82 ./test.sh db3
docker compose exec -w /app/tests/scripts php83 ./test.sh db3

Credits

Forked from Diego Torres's version which have latest updates from 2020. Use it for PHP 7.3 and older. 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