Dump the contents of a database

This repo contains an easy to use class to dump a database using PHP. Currently MySQL, MariaDB, PostgreSQL, SQLite and MongoDB are supported. Behind the scenes mysqldump, mariadb-dump, pg_dump, sqlite3 and mongodump are used.

Here 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');

MariaDB

Spatie\DbDumper\Databases\MariaDb::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 MariaDB-db's mariadb-dump 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.

For compressing dump files, gzip and/or bzip2 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');

Handling AUTO_INCREMENT Values in Dumps

When creating a database dump, you might need to control the inclusion of AUTO_INCREMENT values. This can be crucial for avoiding primary key conflicts or for maintaining ID consistency when transferring data across environments.

Skipping AUTO_INCREMENT Values

To omit the AUTO_INCREMENT values from the tables in your dump, use the skipAutoIncrement method. This is particularly useful to prevent conflicts when importing the dump into another database where those specific AUTO_INCREMENT values might already exist, or when the exact values are not relevant.

Spatie\DbDumper\Databases\MySql::create()
    ->setDbName('dbname')
    ->setUserName('username')
    ->setPassword('password')
    ->skipAutoIncrement()
    ->dumpToFile('dump.sql');

Including AUTO_INCREMENT values in the dump

By default, the AUTO_INCREMENT values are included in the dump. However, if you previously used the skipAutoIncrement method and wish to ensure that the AUTO_INCREMENT values are included in a subsequent dump, use the doNotSkipAutoIncrement method to explicitly include them.

Spatie\DbDumper\Databases\MySql::create()
    ->setDbName('dbname')
    ->setUserName('username')
    ->setPassword('password')
    ->doNotSkipAutoIncrement()
    ->dumpToFile('dump.sql');

Use a Database URL

In some applications or environments, database credentials are provided as URLs instead of individual components. In this case, you can use the setDatabaseUrl method instead of the individual methods.

Spatie\DbDumper\Databases\MySql::create()
    ->setDatabaseUrl($databaseUrl)
    ->dumpToFile('dump.sql');

When providing a URL, the package will automatically parse it and provide the individual components to the applicable dumper.

For example, if you provide the URL mysql://username:password@hostname:3306/dbname, the dumper will use the hostname host, running on port 3306, and will connect to dbname with username and password.

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 _columnstatics table in _informationschema database. In some old version of MySql (service) like 5.7, this table doesn't exist. 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

You can exclude tables from the dump by using an array:

Spatie\DbDumper\Databases\MySql::create()
    ->setDbName($databaseName)
    ->setUserName($userName)
    ->setPassword($password)
    ->excludeTables(['table1', 'table2', 'table3'])
    ->dumpToFile('dump.sql');

Or by using a string:

Spatie\DbDumper\Databases\MySql::create()
    ->setDbName($databaseName)
    ->setUserName($userName)
    ->setPassword($password)
    ->excludeTables('table1, table2, table3')
    ->dumpToFile('dump.sql');

Excluding table data from the dump

If you want to dump the structure of certain tables but not their data (e.g. log tables), you can use excludeTablesData. This works with both MySQL and MariaDB.

Using an array:

Spatie\DbDumper\Databases\MySql::create()
    ->setDbName($databaseName)
    ->setUserName($userName)
    ->setPassword($password)
    ->excludeTablesData(['logs', 'sessions'])
    ->dumpToFile('dump.sql');

You can combine this with excludeTables to fully exclude some tables and only skip data for others:

Spatie\DbDumper\Databases\MySql::create()
    ->setDbName($databaseName)
    ->setUserName($userName)
    ->setPassword($password)
    ->excludeTables(['cache'])
    ->excludeTablesData(['logs', 'sessions'])
    ->dumpToFile('dump.sql');

Including routines in the dump

To include stored procedures and functions in your MySQL or MariaDB dump, use the includeRoutines method:

Spatie\DbDumper\Databases\MySql::create()
    ->setDbName($databaseName)
    ->setUserName($userName)
    ->setPassword($password)
    ->includeRoutines()
    ->dumpToFile('dump.sql');

Do not write CREATE TABLE statements that create each dumped table

You can use doNotCreateTables to prevent writing create statements.

$dumpCommand = MySql::create()
    ->setDbName('dbname')
    ->setUserName('username')
    ->setPassword('password')
    ->doNotCreateTables()
    ->getDumpCommand('dump.sql', 'credentials.txt');

Do not write row data

You can use doNotDumpData to prevent writing row data.

$dumpCommand = MySql::create()
    ->setDbName('dbname')
    ->setUserName('username')
    ->setPassword('password')
    ->doNotDumpData()
    ->getDumpCommand('dump.sql', 'credentials.txt');

Append instead of overwriting a dump file

You can use useAppendMode with MySQL to append to the file instead of overwriting it. This is useful for two-step dumps when you want to dump the whole schema but only some of the data (for example: only migrations, or only product but not customer data).

$dumpCommand = MySql::create()
    ->setDbName('dbname')
    ->setUserName('username')
    ->setPassword('password')
    ->useAppendMode()
    ->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 the output file to be compressed, you can use a compressor class.

There are two compressors that come out of the box:

• GzipCompressor - This will compress your db dump with gzip. Make sure gzip is installed on your system before using this.
• Bzip2Compressor - This will compress your db dump with bzip2. Make sure bzip2 is installed on your system before using this.

$dumpCommand = MySql::create()
    ->setDbName('dbname')
    ->setUserName('username')
    ->setPassword('password')
    ->useCompressor(new GzipCompressor()) // or `new Bzip2Compressor()`
    ->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';
    }
}

Testing

$ composer test

Changelog

Please see CHANGELOG for more information on what has changed recently.

Contributing

Please see CONTRIBUTING for details.

Security Vulnerabilities

Please review our security policy on how to report security vulnerabilities.

Credits

• Freek Van der Herten
• All Contributors

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.