Download the PHP package druidfi/mysqldump-php without Composer

mysqldump-php

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:

• 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 cloud storage (Google Cloud Storage, Amazon S3, ...) over PHP stream wrappers

Requirements

• PHP 8.4 or newer with PDO - see supported versions
• MySQL 8.0 or newer (and compatible MariaDB)

Versions

Version	Branch	PHP	Status	Tests
3.x	main	8.4+	In development
2.x	2.x	8.1+	Maintenance
1.x	1.x	7.4 / 8.0	Legacy

Upgrading from 2.x to 3.x

The dump output and the day-to-day API — constructor, start(), the transform/info hooks, setTableWheres()/setTableLimits() and the dump settings with their defaults — are unchanged from 2.x. The following changes may require action:

• PHP 8.4 or newer is required (2.x supports PHP 8.1+).
• Connections are no longer persistent by default. 2.x always set PDO::ATTR_PERSISTENT => true. If you relied on persistent connections, pass PDO::ATTR_PERSISTENT => true in the $pdoOptions constructor argument.
• Mysqldump::addTypeAdapter() no longer affects other instances. In 2.x the adapter class was stored statically and leaked into every Mysqldump instance in the same process. Call addTypeAdapter() on each instance that needs a custom adapter.
• CompressManagerFactory::$methods was removed. Use the CompressMethod enum, or the unchanged class constants such as CompressManagerFactory::GZIP.
• The Druidfi\Mysqldump\Attribute\Deprecated attribute class was removed. Deprecations now use the native PHP 8.4 #[\Deprecated] attribute.
• ConfigValidator::checkDeprecated() return value changed. The reason and alternative keys were replaced by a single message key (deprecated and since are unchanged).
• DumpSettings::get() returns the setting's actual type. In 2.x every value was cast to string; now arrays, booleans and integers come back unchanged, and unknown options return null instead of the string "". Prefer the typed getters (getWhere(), getNetBufferLength(), ...) where one exists.
• TypeAdapterInterface gained quoteIdentifier(). Identifiers (table, view, column, ... names) are now escaped wherever they are interpolated into SQL, so names containing backticks dump correctly. Custom type adapters must implement the new method.
• compress-level is validated per method. Levels up to the method maximum are accepted (Gzip 1-9, Lz4 1-12, Zstd 1-22) where 2.x rejected everything above 9, and a level above the method maximum now throws with a method-specific message.
• The protected Mysqldump::tableColumnTypes() accessor was removed. Column type maps are now freed as soon as each table or view has been dumped, so memory no longer grows with the number of tables; the accessor had nothing meaningful to return anymore.
• Exceptions are now typed. The library throws subclasses of Druidfi\Mysqldump\Exception\MysqldumpException instead of bare Exception. No action is needed — the base class extends Exception, so existing catch (\Exception $e) blocks keep working — but you can now catch more specific types, see Error handling.

Also fixed in 3.x with no action needed: 2.x settings validation rejected the Zstd, Lz4 and Gzipstream compression methods due to a mismatch between the allowed values and the factory; they now validate correctly.

Installing

Install using Composer:

Getting started

The sections below cover the most common use cases. All configuration options are listed under Tests section describes how the output is compared against native mysqldump.

Error handling

All exceptions thrown by the library extend Druidfi\Mysqldump\Exception\MysqldumpException, which itself extends the native Exception. Catch the base class to handle any library error, or a subclass to react to a specific failure:

• ConnectionException — the database connection could not be established: malformed DSN string or a PDO connection failure. Thrown from the constructor (DSN parsing) and from start() (connecting).
• ConfigurationException — invalid dump settings: unknown options, values failing validation, conflicting options (e.g. replace + insert-ignore), include-tables that do not exist in the database, or a compression method whose PHP extension is not installed.
• DumpException — the dump itself failed: output file not writable, a write error (e.g. disk full), or an unexpected result from the server while reading object structures.

Providing your own database connection

By default the connection is built from the DSN, username and password given to the constructor. If you need to reuse an existing PDO instance or apply a custom connection strategy, implement Druidfi\Mysqldump\ConnectionInterface and inject it before starting the dump:

getHost() and getDbName() are used in the dump file header comments and in the SHOW/CREATE DATABASE statements, so return the values of the database being dumped.

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:

Anonymization recipes

For the common GDPR-sanitization cases the Anonymizer class provides ready-made, dependency-free helpers. Anonymizer::columnMap() builds a row-transform hook from a table => column => transformer map; tables and columns not in the map pass through untouched:

Notes on the helpers:

• All of them keep NULL values as NULL, so nullability semantics survive anonymization.
• hash() and email() are deterministic — the same input always produces the same output — so unique indexes and joins on the anonymized values keep working across tables and dumps.
• Deterministic output of guessable data (names, phone numbers, SSNs) can be re-identified by hashing candidate values, so pass a secret salt for those columns.
• mask() preserves the string length, keeping the dump's data shape realistic.

Anonymization here means the dump never contains the original values; it is not a substitute for access control on the source database itself.

For realistic-looking fake data (names, addresses, phone numbers) the hooks combine well with FakerPHP — see docs/faker.md for recipes, including how to keep Faker output deterministic so joins and unique indexes survive.

Filtering rows when exporting

The same hook can drop rows entirely: return null instead of the row and it is left out of the dump. Unlike the where/setTableWheres() settings this filters in PHP, so it works for conditions that are hard to express in SQL or that depend on data outside the database:

Skipped rows are not counted in the dump's row-count comments or in the info hook's rowCount. Prefer where/setTableWheres() when the condition is expressible in SQL — filtering in the database avoids transferring the skipped rows at all.

Getting information about the dump

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

For tables the $info array contains name, rowCount and completed (set to true once the table has been fully dumped).

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:

[!WARNING] The where dump setting, setTableWheres() and setTableLimits() values are inserted into the dump's SELECT statements as raw SQL by design — that is what makes arbitrary conditions possible. They are not escaped or validated, so never build them from untrusted input (request parameters, user-supplied data, etc.).

Table specific export limits

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

You can also specify the limit as a two-value array, which maps to MySQL's LIMIT offset, row_count syntax: the first value is the offset and the second is the number of rows

Dumping to cloud storage and other streams

The filename given to start() is opened with PHP's fopen(), so any registered stream wrapper works as a dump target: built-in ones like php://stdout or ftp://, and cloud storage wrappers registered by the respective SDKs — gs:// by google/cloud-storage, s3:// by aws/aws-sdk-php, or any league/flysystem adapter exposed as a wrapper. No cloud SDK dependencies are needed in this library:

[!NOTE] The Gzip and Bzip2 compression methods use gzopen()/bzopen(), which only work on local files. When dumping to a stream wrapper, use Gzipstream (identical gzip output written through fopen()), Zstd, Lz4 or None.

Dump Settings

Dump settings can be changed from default values with the 4th argument of the Mysqldump constructor. PDO options can be passed as the 5th argument:

All options:

• 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
  • Possible values: Bzip2|Gzip|Gzipstream|Zstd|Lz4|None, default is None
  • Could be specified using the CompressMethod enum values (e.g. CompressMethod::Gzip->value) or the consts: CompressManagerFactory::GZIP, CompressManagerFactory::BZIP2, CompressManagerFactory::GZIPSTREAM, CompressManagerFactory::ZSTD, CompressManagerFactory::LZ4 or CompressManagerFactory::NONE
  • Zstd requires ext-zstd and Lz4 requires ext-lz4
• compress-level
  • Compression level to use (integer), default is 0 (use the method's own default level)
  • For Gzip: 1-9
  • For Zstd: 1-22 (default: 3)
  • For Lz4: 1-12 (default: 1)
• 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
  • MySQL docs 8.0
• add-drop-table
  • MySQL docs 8.0
• add-drop-triggers
  • MySQL docs 8.0
• add-locks
  • MySQL docs 8.0
• complete-insert
  • MySQL docs 8.0
• databases
  • MySQL docs 8.0
• default-character-set
  • Possible values: utf8|utf8mb4, default is utf8
  • utf8 is compatible option and utf8mb4 is for full utf8 compliance
  • Could be specified using the consts: DumpSettings::UTF8 or DumpSettings::UTF8MB4
  • MySQL docs 8.0
• disable-keys
  • MySQL docs 8.0
• events
  • MySQL docs 8.0
• extended-insert
  • MySQL docs 8.0
• hex-blob
  • MySQL docs 8.0
• insert-ignore
  • MySQL docs 8.0
• replace
  • Use REPLACE INTO instead of INSERT INTO statements. Cannot be used together with insert-ignore.
• lock-tables
  • MySQL docs 8.0
• net_buffer_length
  • MySQL docs 8.0
• no-autocommit
  • Option to disable autocommit (faster inserts, no problems with index keys)
  • MySQL docs 8.0
• no-create-info
  • MySQL docs 8.0
• no-data
  • Do not dump data for these tables (array of table names), support regexps, true to ignore all tables
  • MySQL docs 8.0
• routines
  • MySQL docs 8.0
• single-transaction
  • MySQL docs 8.0
• skip-comments
  • MySQL docs 8.0
• skip-dump-date
  • MySQL docs 8.0
• skip-triggers
  • MySQL docs 8.0
• skip-tz-utc
  • MySQL docs 8.0
• skip-definer
  • MySQL docs 8.0
• where
  • MySQL docs 8.0
  • Raw SQL by design — see the warning under Table specific export conditions

The following option is deprecated. Passing it triggers a deprecation notice, and it will be removed in a future version; use init_commands to control FOREIGN_KEY_CHECKS manually if needed.

• disable-foreign-keys-check
  • MySQL docs 8.0

Privileges

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:

• Which are the minimum privileges required to get a backup of a MySQL database schema?
• PROCESS privilege from MySQL 5.7.31 and MySQL 8.0.21 in July 2020

Security considerations

The library never stores credentials — they are passed to the constructor and used only to open the PDO connection. Keeping them safe is the calling application's responsibility. Do not hardcode credentials in code or commit them to version control; read them from environment variables or a secret manager instead:

Remember that the where, setTableWheres() and setTableLimits() values are raw SQL and must not be built from untrusted input — see the warning under Table specific export conditions.

To report a vulnerability, see SECURITY.md.

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:

Credits

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