mysqldump is a perfectly fine tool for many production workloads — until your database grows large enough that backups take too long and restores take even longer. Before you reach for a more exotic solution, there are several flags and session-level variables that can make a meaningful difference with zero infrastructure changes.

This article focuses on plain MySQL, but shows where to wire up these settings when you use spatie/laravel-backup and stefanzweifel/laravel-backup-restore in a Laravel project.

Where to configure this in Laravel

Both packages read dump and restore options from config/database.php, inside the dump key of your connection:

// config/database.php
'mysql' => [
    // ... standard connection settings ...
    'dump' => [
        'excludeTables' => [...],
        'useSingleTransaction' => true,
        'add_extra_option' => '...', // passed to mysqldump (backup)
        'options' => '...',          // passed to mysql client (restore)
    ],
],

add_extra_option is forwarded to mysqldump by spatie/laravel-backup.
options is forwarded to the mysql client by laravel-backup-restore.

Everything below maps directly to one of those two keys.

Dump-time optimisations

Exclude noisy, non-essential tables

Some tables accumulate millions of rows that are useful for debugging but worthless in a restore. Excluding them shrinks the dump file and cuts restore time dramatically.

'excludeTables' => [
    'telescope_entries',
    'telescope_entries_tags',
    'telescope_monitoring',
],

Laravel Telescope is the classic example: it can easily outweigh the rest of your schema combined.

Use a single transaction

--single-transaction

In spatie/laravel-backup this is:

'useSingleTransaction' => true,

This wraps the dump in a START TRANSACTION so InnoDB tables are read from a consistent snapshot without locking them. Essential for any live database.

Increase --net-buffer-length

--net-buffer-length=16777216   # 16 MB (default is 1 MB)

mysqldump groups rows into multi-row INSERT statements. The default maximum per statement is 1 MB. Raising it to 16 MB reduces the total number of statements in the file, which directly cuts parse and execution time during a restore.

Pair it with --max_allowed_packet on the server side:

--max_allowed_packet=512M

Without this, large packets are silently rejected.

Skip unnecessary LOCK/UNLOCK statements

--skip-add-locks

mysqldump normally wraps each table dump in LOCK TABLES … WRITE / UNLOCK TABLES. This is redundant when you are already using --single-transaction and when you disable table locking at restore time (see below). Removing these statements makes the dump file smaller and the restore faster.

Suppress MySQL 8 column statistics

--column-statistics=0

MySQL 8's mysqldump emits ANALYZE TABLE statements to update column statistics after each table is imported. On large tables these can block progress noticeably. If you run your own statistics collection after a restore, suppress them.

Compress the dump on the wire

--compression-algorithms=zlib

Compresses data in transit between mysqldump and the MySQL server. Useful when the client and server are on separate hosts, reducing network I/O. For socket connections the benefit is minimal, but there is no downside.

Skip binary log tracking (GTID)

--set-gtid-purged=OFF

If you use GTID-based replication, mysqldump normally writes SET @@GLOBAL.gtid_purged into the dump. During a restore this can conflict with an existing gtid_executed set. Passing OFF omits that statement and keeps restores clean on secondary or development databases.

Avoid table-level locks

--lock-tables=false

When using --single-transaction, table-level locking is already unnecessary. Explicitly disabling it avoids a redundant flush.

Use --quick for large tables

--quick

By default mysqldump buffers an entire table in memory before writing. --quick streams one row at a time, keeping memory usage flat regardless of table size. Always enable it for any production database.

Restore-time optimisations

These are passed to the mysql client via an --init-command, which MySQL executes before the dump is processed:

SET sql_log_bin=0;
SET foreign_key_checks=0;
SET unique_checks=0;
SET autocommit=0;

In config/database.php:

'options' => '--init-command="SET sql_log_bin=0; SET foreign_key_checks=0; SET unique_checks=0; SET autocommit=0;"',

SET sql_log_bin=0

Disables binary logging for the session. There is no point logging every INSERT from a dump file into the binlog — it inflates the binlog and slows the restore. Safe when you control the import and are not relying on the binlog to propagate changes to replicas. This also reduces the amount of disk space needed for the restore.

SET foreign_key_checks=0

MySQL enforces referential integrity row-by-row during inserts. In a full dump, the parent and child rows are all present — you just may not have inserted the parent yet when a child arrives. Disabling this check lets MySQL trust the dump and skip the per-row lookups, which is one of the most impactful restore optimisations available.

Re-enable it (or restart the session) after the restore to verify the restored data is consistent.

SET unique_checks=0

Similar rationale: MySQL normally verifies unique constraints on every insert. A correctly generated dump has no duplicates, so this check is redundant overhead. Disabling it reduces index update cost during the restore.

SET autocommit=0

Turns off the implicit COMMIT after every statement. Combined with the large INSERT batches produced by --net-buffer-length, this means the storage engine can batch many rows into a single transaction, dramatically reducing fsync pressure.

Putting it together

// config/database.php
'dump' => [
    'excludeTables' => [
        'telescope_entries',
        'telescope_entries_tags',
        'telescope_monitoring',
    ],
    'useSingleTransaction' => true,
    'add_extra_option' => '--set-gtid-purged=OFF --lock-tables=false --quick'
        . ' --max_allowed_packet=512M --net-buffer-length=16777216'
        . ' --compression-algorithms=zlib'
        . ' --skip-add-locks --column-statistics=0',
    'options' => '--init-command="SET sql_log_bin=0; SET foreign_key_checks=0; SET unique_checks=0; SET autocommit=0;"',
],

These settings are safe for the vast majority of production MySQL 8 setups. The restore-side flags assume you trust the dump and will verify data integrity via application-level health checks afterwards — which is standard practice regardless of how you import.

The backup side produces a smaller file, faster. The restore side skips redundant constraint checking and batches commits. Together they can reduce both phases by a significant margin without changing anything about your infrastructure or your backup tooling.

More info

• Spatie Backup
• Introducing laravel-backup-restore
• mysqldump — A Database Backup Program
• mysql — The MySQL Command-Line Client