1MySQLDump - PHP
2=========
3
4[Requirements](https://github.com/ifsnop/mysqldump-php#requirements) |
5[Installing](https://github.com/ifsnop/mysqldump-php#installing) |
6[Getting started](https://github.com/ifsnop/mysqldump-php#getting-started) |
7[API](https://github.com/ifsnop/mysqldump-php#constructor-and-default-parameters) |
8[Settings](https://github.com/ifsnop/mysqldump-php#dump-settings) |
9[PDO Settings](https://github.com/ifsnop/mysqldump-php#pdo-settings) |
10[TODO](https://github.com/ifsnop/mysqldump-php#todo) |
11[License](https://github.com/ifsnop/mysqldump-php#license) |
12[Credits](https://github.com/ifsnop/mysqldump-php#credits)
13
14[![Build Status](https://travis-ci.org/ifsnop/mysqldump-php.svg?branch=devel)](https://travis-ci.org/ifsnop/mysqldump-php)
15[![Total Downloads](https://poser.pugx.org/ifsnop/mysqldump-php/downloads)](https://packagist.org/packages/ifsnop/mysqldump-php)
16[![Monthly Downloads](https://poser.pugx.org/ifsnop/mysqldump-php/d/monthly)](https://packagist.org/packages/ifsnop/mysqldump-php)
17[![Daily Downloads](https://poser.pugx.org/ifsnop/mysqldump-php/d/daily)](https://packagist.org/packages/ifsnop/mysqldump-php)
18[![Scrutinizer Quality Score](https://scrutinizer-ci.com/g/ifsnop/mysqldump-php/badges/quality-score.png?s=d02891e196a3ca1298619032a538ce8ae8cafd2b)](https://scrutinizer-ci.com/g/ifsnop/mysqldump-php/)
19[![Latest Stable Version](https://poser.pugx.org/ifsnop/mysqldump-php/v/stable.png)](https://packagist.org/packages/ifsnop/mysqldump-php)
20
21This is a php version of mysqldump cli that comes with MySQL, without dependencies, output compression and sane defaults.
22
23Out of the box, MySQLDump-PHP supports backing up table structures, the data itself, views, triggers and events.
24
25MySQLDump-PHP is the only library that supports:
26* output binary blobs as hex.
27* resolves view dependencies (using Stand-In tables).
28* output compared against original mysqldump. Linked to travis-ci testing system (testing from php 5.3 to 7.3 & hhvm)
29* dumps stored routines (functions and procedures).
30* dumps events.
31* does extended-insert and/or complete-insert.
32* supports virtual columns from MySQL 5.7.
33* does insert-ignore, like a REPLACE but ignoring errors if a duplicate key exists.
34* modifying data from database on-the-fly when dumping, using hooks.
35* can save directly to google cloud storage over a compressed stream wrapper (GZIPSTREAM).
36
37## Important
38
39From version 2.0, connections to database are made using the standard DSN, documented in [PDO connection string](https://secure.php.net/manual/en/ref.pdo-mysql.connection.php).
40
41## Requirements
42
43- PHP 5.3.0 or newer
44- MySQL 4.1.0 or newer
45- [PDO](https://secure.php.net/pdo)
46
47## Installing
48
49Using [Composer](https://getcomposer.org/):
50
51```
52$ composer require ifsnop/mysqldump-php
53
54```
55
56Using [Curl](https://curl.haxx.se/) to always download and decompress the latest release:
57
58```
59$ 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
60```
61
62## Getting started
63
64With [Autoloader](https://www.php-fig.org/psr/psr-4/)/[Composer](https://getcomposer.org):
65
66```php
67<?php
68
69use Ifsnop\Mysqldump as IMysqldump;
70
71try {
72    $dump = new IMysqldump\Mysqldump('mysql:host=localhost;dbname=testdb', 'username', 'password');
73    $dump->start('storage/work/dump.sql');
74} catch (\Exception $e) {
75    echo 'mysqldump-php error: ' . $e->getMessage();
76}
77```
78
79Plain old PHP:
80
81```php
82<?php
83
84    include_once(dirname(__FILE__) . '/mysqldump-php-2.0.0/src/Ifsnop/Mysqldump/Mysqldump.php');
85    $dump = new Ifsnop\Mysqldump\Mysqldump('mysql:host=localhost;dbname=testdb', 'username', 'password');
86    $dump->start('storage/work/dump.sql');
87```
88
89Refer to the [wiki](https://github.com/ifsnop/mysqldump-php/wiki/Full-usage-example) for some examples and a comparision between mysqldump and mysqldump-php dumps.
90
91## Changing values when exporting
92
93You 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:
94
95```php
96$dumper = new IMysqldump\Mysqldump('mysql:host=localhost;dbname=testdb', 'username', 'password');
97
98$dumper->setTransformTableRowHook(function ($tableName, array $row) {
99    if ($tableName === 'customers') {
100        $row['social_security_number'] = (string) rand(1000000, 9999999);
101    }
102
103    return $row;
104});
105
106$dumper->start('storage/work/dump.sql');
107```
108
109## Getting information about the dump
110
111You can register a callable that will be used to report on the progress of the dump
112
113```php
114$dumper->setInfoHook(function($object, $info) {
115    if ($object === 'table') {
116        echo $info['name'], $info['rowCount'];
117    });
118```
119
120## Table specific export conditions
121
122You can register table specific 'where' clauses to limit data on a per table basis.  These override the default `where` dump setting:
123
124```php
125$dumper = new IMysqldump\Mysqldump('mysql:host=localhost;dbname=testdb', 'username', 'password');
126
127$dumper->setTableWheres(array(
128    'users' => 'date_registered > NOW() - INTERVAL 3 MONTH AND deleted=0',
129    'logs' => 'date_logged > NOW() - INTERVAL 1 DAY',
130    'posts' => 'isLive=1'
131));
132```
133
134## Table specific export limits
135
136You can register table specific 'limits' to limit the returned rows on a per table basis:
137
138```php
139$dumper = new IMysqldump\Mysqldump('mysql:host=localhost;dbname=testdb', 'username', 'password');
140
141$dumper->setTableLimits(array(
142    'users' => 300,
143    'logs' => 50,
144    'posts' => 10
145));
146```
147
148## Constructor and default parameters
149
150```php
151/**
152 * Constructor of Mysqldump. Note that in the case of an SQLite database
153 * connection, the filename must be in the $db parameter.
154 *
155 * @param string $dsn        PDO DSN connection string
156 * @param string $user       SQL account username
157 * @param string $pass       SQL account password
158 * @param array  $dumpSettings SQL database settings
159 * @param array  $pdoSettings  PDO configured attributes
160 */
161public function __construct(
162    $dsn = '',
163    $user = '',
164    $pass = '',
165    $dumpSettings = array(),
166    $pdoSettings = array()
167)
168
169$dumpSettingsDefault = array(
170    'include-tables' => array(),
171    'exclude-tables' => array(),
172    'compress' => Mysqldump::NONE,
173    'init_commands' => array(),
174    'no-data' => array(),
175    'reset-auto-increment' => false,
176    'add-drop-database' => false,
177    'add-drop-table' => false,
178    'add-drop-trigger' => true,
179    'add-locks' => true,
180    'complete-insert' => false,
181    'databases' => false,
182    'default-character-set' => Mysqldump::UTF8,
183    'disable-keys' => true,
184    'extended-insert' => true,
185    'events' => false,
186    'hex-blob' => true, /* faster than escaped content */
187    'insert-ignore' => false,
188    'net_buffer_length' => self::MAXLINESIZE,
189    'no-autocommit' => true,
190    'no-create-info' => false,
191    'lock-tables' => true,
192    'routines' => false,
193    'single-transaction' => true,
194    'skip-triggers' => false,
195    'skip-tz-utc' => false,
196    'skip-comments' => false,
197    'skip-dump-date' => false,
198    'skip-definer' => false,
199    'where' => '',
200    /* deprecated */
201    'disable-foreign-keys-check' => true
202);
203
204$pdoSettingsDefaults = array(
205    PDO::ATTR_PERSISTENT => true,
206    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
207    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false
208);
209
210// missing settings in constructor will be replaced by default options
211$this->_pdoSettings = self::array_replace_recursive($pdoSettingsDefault, $pdoSettings);
212$this->_dumpSettings = self::array_replace_recursive($dumpSettingsDefault, $dumpSettings);
213```
214
215## Dump Settings
216
217- **include-tables**
218  - Only include these tables (array of table names), include all if empty.
219- **exclude-tables**
220  - Exclude these tables (array of table names), include all if empty, supports regexps.
221- **include-views**
222  - Only include these views (array of view names), include all if empty. By default, all views named as the include-tables array are included.
223- **compress**
224  - Gzip, Bzip2, None.
225  - Could be specified using the declared consts: IMysqldump\Mysqldump::GZIP, IMysqldump\Mysqldump::BZIP2 or IMysqldump\Mysqldump::NONE
226- **reset-auto-increment**
227  - Removes the AUTO_INCREMENT option from the database definition
228  - Useful when used with no-data, so when db is recreated, it will start from 1 instead of using an old value
229- **add-drop-database**
230  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_add-drop-database
231- **add-drop-table**
232  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_add-drop-table
233- **add-drop-triggers**
234  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_add-drop-trigger
235- **add-locks**
236  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_add-locks
237- **complete-insert**
238  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_complete-insert
239- **databases**
240  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_databases
241- **default-character-set**
242  - utf8 (default, compatible option), utf8mb4 (for full utf8 compliance)
243  - Could be specified using the declared consts: IMysqldump\Mysqldump::UTF8 or IMysqldump\Mysqldump::UTF8MB4BZIP2
244  - https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html
245  - https://mathiasbynens.be/notes/mysql-utf8mb4
246- **disable-keys**
247  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_disable-keys
248- **events**
249  - https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_events
250- **extended-insert**
251  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_extended-insert
252- **hex-blob**
253  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_hex-blob
254- **insert-ignore**
255  - https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_insert-ignore
256- **lock-tables**
257  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_lock-tables
258- **net_buffer_length**
259  - https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_net_buffer_length
260- **no-autocommit**
261  - Option to disable autocommit (faster inserts, no problems with index keys)
262  - https://dev.mysql.com/doc/refman/4.1/en/commit.html
263- **no-create-info**
264  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_no-create-info
265- **no-data**
266  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_no-data
267  - Do not dump data for these tables (array of table names), support regexps, `true` to ignore all tables
268- **routines**
269  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_routines
270- **single-transaction**
271  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction
272- **skip-comments**
273  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_comments
274- **skip-dump-date**
275  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_dump-date
276- **skip-triggers**
277  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_triggers
278- **skip-tz-utc**
279  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_tz-utc
280- **skip-definer**
281  - https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer
282- **where**
283  - https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_where
284
285The following options are now enabled by default, and there is no way to disable them since
286they should always be used.
287
288- **disable-foreign-keys-check**
289  - https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html
290
291## PDO Settings
292
293- **PDO::ATTR_PERSISTENT**
294- **PDO::ATTR_ERRMODE**
295- **PDO::MYSQL_ATTR_INIT_COMMAND**
296- **PDO::MYSQL_ATTR_USE_BUFFERED_QUERY**
297  - https://secure.php.net/manual/en/ref.pdo-mysql.php
298  - https://stackoverflow.com/questions/13728106/unexpectedly-hitting-php-memory-limit-with-a-single-pdo-query/13729745#13729745
299  - https://secure.php.net/manual/en/mysqlinfo.concepts.buffering.php
300
301## Errors
302
303To dump a database, you need the following privileges :
304
305- **SELECT**
306  - In order to dump table structures and data.
307- **SHOW VIEW**
308  - If any databases has views, else you will get an error.
309- **TRIGGER**
310  - If any table has one or more triggers.
311- **LOCK TABLES**
312  - If "lock tables" option was enabled.
313
314Use **SHOW GRANTS FOR user@host;** to know what privileges user has. See the following link for more information:
315
316[Which are the minimum privileges required to get a backup of a MySQL database schema?](https://dba.stackexchange.com/questions/55546/which-are-the-minimum-privileges-required-to-get-a-backup-of-a-mysql-database-sc/55572#55572)
317
318## Tests
319
320Current code for testing is an ugly hack. Probably there are much better ways
321of doing them using PHPUnit, so PR's are welcomed. The testing script creates
322and populates a database using all possible datatypes. Then it exports it
323using both mysqldump-php and mysqldump, and compares the output. Only if
324it is identical tests are OK. After [this](https://github.com/ifsnop/mysqldump-php/commit/8496fbb1b26dde404804bc8865ec32044da5b813)
325commit, some test are performed using phpunit.
326
327Some tests are skipped if mysql server doesn't support them.
328
329A couple of tests are only comparing between original sql code and
330mysqldump-php generated sql, because some options are not available in
331mysqldump.
332
333## Bugs (from mysqldump, not from mysqldump-php)
334
335After [this](https://bugs.mysql.com/bug.php?id=80150) bug report, a new
336one has been introduced. _binary is appended also when hex-blob option is
337used, if the value is empty.
338
339## Backporting
340
341mysqldump-php is not backwards compatible with php 5.2 because we it uses
342namespaces. However, it could be trivially fixed if needed.
343
344## Todo
345
346Write more tests, test with mariadb also.
347
348## Contributing
349
350Format all code to PHP-FIG standards.
351https://www.php-fig.org/
352
353## License
354
355This project is open-sourced software licensed under the [GPL license](https://www.gnu.org/copyleft/gpl.html)
356
357## Credits
358
359After more than 8 years, there is barely anything left from the original source code, but:
360
361Originally based on James Elliott's script from 2009.
362https://code.google.com/archive/p/db-mysqldump/
363
364Adapted and extended by Michael J. Calkins.
365https://github.com/clouddueling
366
367Currently maintained, developed and improved by Diego Torres.
368https://github.com/ifsnop
369