Stars: 1187
Forks: 317
Pull Requests: 122
Issues: 146
Watchers: 55
Last Updated: 2023-05-20 06:10:03
PHP version of mysqldump cli that comes with MySQL
License: GNU General Public License v3.0
Languages: PHP, Shell
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:
From version 2.0, connections to database are made using the standard DSN, documented in PDO connection string.
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
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.
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');
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'];
});
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'
));
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 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);
true
to ignore all tablesThe following options are now enabled by default, and there is no way to disable them since they should always be used.
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:
Which are the minimum privileges required to get a backup of a MySQL database schema?
Current code for testing is an ugly hack. Probably there are much better ways of doing them using PHPUnit, so PR's are welcomed. 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. After this commit, some test are performed using phpunit.
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.
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.
mysqldump-php is not backwards compatible with php 5.2 because we it uses namespaces. However, it could be trivially fixed if needed.
Write more tests, test with mariadb also.
Format all code to PHP-FIG standards. https://www.php-fig.org/
This project is open-sourced software licensed under the GPL license
After more than 8 years, there is barely anything left from the original source code, but:
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
Currently maintained, developed and improved by Diego Torres. https://github.com/ifsnop