spatie / Db Dumper
Projects that are alternatives of or similar to Db Dumper
Dump the contents of a database
This repo contains an easy to use class to dump a database using PHP. Currently MySQL, PostgreSQL, SQLite and MongoDB are supported. Behind
the scenes mysqldump
, pg_dump
, sqlite3
and mongodump
are used.
Here's are simple examples of how to create a database dump with different drivers:
MySQL
Spatie\DbDumper\Databases\MySql::create()
->setDbName($databaseName)
->setUserName($userName)
->setPassword($password)
->dumpToFile('dump.sql');
PostgreSQL
Spatie\DbDumper\Databases\PostgreSql::create()
->setDbName($databaseName)
->setUserName($userName)
->setPassword($password)
->dumpToFile('dump.sql');
SQLite
Spatie\DbDumper\Databases\Sqlite::create()
->setDbName($pathToDatabaseFile)
->dumpToFile('dump.sql');
MongoDB
Spatie\DbDumper\Databases\MongoDb::create()
->setDbName($databaseName)
->setUserName($userName)
->setPassword($password)
->dumpToFile('dump.gz');
Support us
We invest a lot of resources into creating best in class open source packages. You can support us by buying one of our paid products.
We highly appreciate you sending us a postcard from your hometown, mentioning which of our package(s) you are using. You'll find our address on our contact page. We publish all received postcards on our virtual postcard wall.
Requirements
For dumping MySQL-db's mysqldump
should be installed.
For dumping PostgreSQL-db's pg_dump
should be installed.
For dumping SQLite-db's sqlite3
should be installed.
For dumping MongoDB-db's mongodump
should be installed.
Installation
You can install the package via composer:
composer require spatie/db-dumper
Usage
This is the simplest way to create a dump of a MySql db:
Spatie\DbDumper\Databases\MySql::create()
->setDbName($databaseName)
->setUserName($userName)
->setPassword($password)
->dumpToFile('dump.sql');
If you're working with PostgreSQL just use that dumper, most methods are available on both the MySql. and PostgreSql-dumper.
Spatie\DbDumper\Databases\PostgreSql::create()
->setDbName($databaseName)
->setUserName($userName)
->setPassword($password)
->dumpToFile('dump.sql');
If the mysqldump
(or pg_dump
) binary is installed in a non default location you can let the package know by using thesetDumpBinaryPath()
-function:
Spatie\DbDumper\Databases\MySql::create()
->setDumpBinaryPath('/custom/location')
->setDbName($databaseName)
->setUserName($userName)
->setPassword($password)
->dumpToFile('dump.sql');
If your application is deployed and you need to change the host (default is 127.0.0.1), you can add the setHost()
-function:
Spatie\DbDumper\Databases\MySql::create()
->setDbName($databaseName)
->setUserName($userName)
->setPassword($password)
->setHost($host)
->dumpToFile('dump.sql');
Dump specific tables
Using an array:
Spatie\DbDumper\Databases\MySql::create()
->setDbName($databaseName)
->setUserName($userName)
->setPassword($password)
->includeTables(['table1', 'table2', 'table3'])
->dumpToFile('dump.sql');
Using a string:
Spatie\DbDumper\Databases\MySql::create()
->setDbName($databaseName)
->setUserName($userName)
->setPassword($password)
->includeTables('table1, table2, table3')
->dumpToFile('dump.sql');
Don't use column_statics table with some old version of MySql service.
In order to use "--column-statistics=0" as option in mysqldump command you can use doNotUseColumnStatistics() method.
If you have installed mysqldump 8, it queries by default column_statics table in information_schema database. In some old version of MySql (service) like 5.7, this table it not exists. So you could have an exception during the execution of mysqldump. To avoid this, you could use doNotUseColumnStatistics() method.
Spatie\DbDumper\Databases\MySql::create()
->setDbName($databaseName)
->setUserName($userName)
->setPassword($password)
->doNotUseColumnStatistics()
->dumpToFile('dump.sql');
Excluding tables from the dump
Using an array:
Spatie\DbDumper\Databases\MySql::create()
->setDbName($databaseName)
->setUserName($userName)
->setPassword($password)
->excludeTables(['table1', 'table2', 'table3'])
->dumpToFile('dump.sql');
Using a string:
Spatie\DbDumper\Databases\MySql::create()
->setDbName($databaseName)
->setUserName($userName)
->setPassword($password)
->excludeTables('table1, table2, table3')
->dumpToFile('dump.sql');
Do not write CREATE TABLE statements that create each dumped table.
$dumpCommand = MySql::create()
->setDbName('dbname')
->setUserName('username')
->setPassword('password')
->doNotCreateTables()
->getDumpCommand('dump.sql', 'credentials.txt');
Adding extra options
If you want to add an arbitrary option to the dump command you can use addExtraOption
$dumpCommand = MySql::create()
->setDbName('dbname')
->setUserName('username')
->setPassword('password')
->addExtraOption('--xml')
->getDumpCommand('dump.sql', 'credentials.txt');
If you're working with MySql you can set the database name using --databases
as an extra option. This is particularly useful when used in conjunction with the --add-drop-database
mysqldump
option (see the mysqldump docs).
$dumpCommand = MySql::create()
->setUserName('username')
->setPassword('password')
->addExtraOption('--databases dbname')
->addExtraOption('--add-drop-database')
->getDumpCommand('dump.sql', 'credentials.txt');
With MySql, you also have the option to use the --all-databases
extra option. This is useful when you want to run a full backup of all the databases in the specified MySQL connection.
$dumpCommand = MySql::create()
->setUserName('username')
->setPassword('password')
->addExtraOption('--all-databases')
->getDumpCommand('dump.sql', 'credentials.txt');
Please note that using the ->addExtraOption('--databases dbname')
or ->addExtraOption('--all-databases')
will override the database name set on a previous ->setDbName()
call.
Using compression
If you want to compress the outputted file, you can use one of the compressors and the resulted dump file will be compressed.
There is one compressor that comes out of the box: GzipCompressor
. It will compress your db dump with gzip
. Make sure gzip
is installed on your system before using this.
$dumpCommand = MySql::create()
->setDbName('dbname')
->setUserName('username')
->setPassword('password')
->useCompressor(new GzipCompressor())
->dumpToFile('dump.sql.gz');
Creating your own compressor
You can create you own compressor implementing the Compressor
interface. Here's how that interface looks like:
namespace Spatie\DbDumper\Compressors;
interface Compressor
{
public function useCommand(): string;
public function useExtension(): string;
}
The useCommand
should simply return the compression command the db dump will get pumped to. Here's the implementation of GzipCompression
.
namespace Spatie\DbDumper\Compressors;
class GzipCompressor implements Compressor
{
public function useCommand(): string
{
return 'gzip';
}
public function useExtension(): string
{
return 'gz';
}
}
Changelog
Please see CHANGELOG for more information what has changed recently.
Testing
composer test
Contributing
Please see CONTRIBUTING for details.
Security
If you discover any security related issues, please email [email protected] instead of using the issue tracker.
Credits
Initial PostgreSQL support was contributed by Adriano Machado. SQlite support was contributed by Peter Matseykanets.
License
The MIT License (MIT). Please see License File for more information.