Saved searches
Use saved searches to filter your results more quickly
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.
PHP version of mysqldump cli that comes with MySQL
License
ifsnop/mysqldump-php
This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.
Name already in use
A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?
Sign In Required
Please sign in to use Codespaces.
Launching GitHub Desktop
If nothing happens, download GitHub Desktop and try again.
Launching GitHub Desktop
If nothing happens, download GitHub Desktop and try again.
Launching Xcode
If nothing happens, download Xcode and try again.
Launching Visual Studio Code
Your codespace will open once ready.
There was a problem preparing your codespace, please try again.
Latest commit
Git stats
Files
Failed to load latest commit information.
README.md
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.
From version 2.0, connections to database are made using the standard DSN, documented in PDO connection string.
$ 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 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(); > 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);
- include-tables
- Only include these tables (array of table names), include all if empty.
- Exclude these tables (array of table names), include all if empty, supports regexps.
- Only include these views (array of view names), include all if empty. By default, all views named as the include-tables array are included.
- 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.
- Gzip, Bzip2, None.
- Could be specified using the declared consts: IMysqldump\Mysqldump::GZIP, IMysqldump\Mysqldump::BZIP2 or IMysqldump\Mysqldump::NONE
- 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
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_add-drop-database
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_add-drop-table
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_add-drop-trigger
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_add-locks
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_complete-insert
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_databases
- utf8 (default, compatible option), utf8mb4 (for full utf8 compliance)
- Could be specified using the declared consts: IMysqldump\Mysqldump::UTF8 or IMysqldump\Mysqldump::UTF8MB4BZIP2
- https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html
- https://mathiasbynens.be/notes/mysql-utf8mb4
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_disable-keys
- https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_events
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_extended-insert
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_hex-blob
- https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_insert-ignore
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_lock-tables
- https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_net_buffer_length
- Option to disable autocommit (faster inserts, no problems with index keys)
- https://dev.mysql.com/doc/refman/4.1/en/commit.html
- Option to disable the dump of create database statements.
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_no-create-db
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_no-create-info
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_no-data
- Do not dump data for these tables (array of table names), support regexps, true to ignore all tables
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_routines
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_comments
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_dump-date
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_triggers
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_tz-utc
- https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer
- https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_where
The following options are now enabled by default, and there is no way to disable them since they should always be used.
- PDO::ATTR_PERSISTENT
- PDO::ATTR_ERRMODE
- PDO::MYSQL_ATTR_INIT_COMMAND
- PDO::MYSQL_ATTR_USE_BUFFERED_QUERY
- https://secure.php.net/manual/en/ref.pdo-mysql.php
- https://stackoverflow.com/questions/13728106/unexpectedly-hitting-php-memory-limit-with-a-single-pdo-query/13729745#13729745
- https://secure.php.net/manual/en/mysqlinfo.concepts.buffering.php
To dump a database, you need the following privileges :
- SELECT
- In order to dump table structures and data.
- If any databases has views, else you will get an error.
- If any table has one or more triggers.
- If «lock tables» option was enabled.
Use SHOW GRANTS FOR user@host; to know what privileges user has. See the following link for more information:
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.
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.
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
About
PHP version of mysqldump cli that comes with MySQL