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