n98-magerun is a handy CLI tool to work on Magento from command line.
Backup/Dump Database
Backup Database in a plain SQL File
n98-magerun2.phar db:dump var/projectName_date.sql
Print only the MySQL command. Do not execute it. This can be done by using the --only-command option.
n98-magerun2.phar db:dump --only-command var/projectName_date.sql
You may also use the time command which will also show the time taken to execute the command:
time n98-magerun2.phar db:dump var/projectName_date.sql
Backup Database in a Gzip Compressed File
You can also backup the database into a gzip compressed file:
n98-magerun2.phar db:dump --compression="gzip" var/projectName_date.sql.gz
Exclude certain tables in the database backup
You can exclude some tables in the database dump using the --strip option.
https://github.com/netz98/n98-magerun/wiki/Stripped-Database-Dumps
This is particularly useful for development purposes where you might need to dump the database by excluding tables of sales order, customer, logs, etc.
- You can separate each table to strip by a space.
- You can use wildcards like
*and?in the table names to strip multiple tables. - You can also specify pre-defined table groups that start with an
@.
Example:
n98-magerun2.phar db:dump --strip="dataflow_batch_export unimportant_module_* @log @stripped"
Database dump/backup with Gzip compression
– excluding third-party modules’ tables
– excluding Magento tables like core_config_data, cron_schedule, etc.
n98-magerun2.phar db:dump --compression="gzip" --exclude="@development @stripped @idx @ee_changelog *_state api* adobe_* aw_* braintree_* fastly_* dms_* klevu_* magento_giftcard* magento_gift* mageplaza_* mageworx_* mailchimp_* nosto_* oauth_* queue* review* report* rw_* sd_* shipperhq_* signifyd_* urapidflow_* xtento_* yotpo_* gift_message cron_schedule core_config_data setup_module session" var/backups/projectName-2022-10-25.sql.gz
Import/Restore Database Backup
To import a database backup file, n98-magerun2 uses the database configuration present in the app/etc/env.php.
Import a plain SQL File
n98-magerun2.phar db:import var/projectName-2022-10-25.sql
Print only the MySQL command. Do not execute it. This can be done by using the --only-command option.
n98-magerun2.phar db:import --only-command var/projectName-2022-10-25.sql
Import a Gzip Compressed File
n98-magerun2.phar db:import --compression="gzip" var/projectName-2022-10-25.sql.gz
Database Dump Manual
n98-magerrun2.phar db:dump --help
Description:
Dumps database with mysqldump cli client
Usage:
db:dump [options] [--] [<filename>]
Arguments:
filename Dump filename
Options:
--connection=CONNECTION Select DB connection type for Magento configurations with several databases [default: "default"]
-t, --add-time[=ADD-TIME] Append or prepend a timestamp to filename if a filename is provided. Possible values are "suffix", "prefix" or "no". [default: "no"]
-c, --compression=COMPRESSION Compress the dump file using one of the supported algorithms
--only-command Print only mysqldump command. Do not execute
--print-only-filename Execute and prints no output except the dump filename
--dry-run Do everything but the actual dump
--set-gtid-purged-off add --set-gtid-purged=OFF
--no-single-transaction Do not use single-transaction (not recommended, this is blocking)
--human-readable Use a single insert with column names per row. Useful to track database differences. Use db:import --optimize for speeding up the import.
--git-friendly Use one insert statement, but with line breaks instead of separate insert statements. Similar to --human-readable, but you wont need to use --optimize to speed up the import.
--add-routines Include stored routines in dump (procedures & functions)
--no-tablespaces Use this option if you want to create a dump without having the PROCESS privilege
--stdout Dump to stdout
-s, --strip[=STRIP] Tables to strip (dump only structure of those tables)
-e, --exclude[=EXCLUDE] Tables to exclude entirely from the dump (including structure)
-i, --include[=INCLUDE] Tables to include entirely in the dump (including structure)
-f, --force Do not prompt if all options are defined
--keep-column-statistics Keeps the Column Statistics table in SQL dump
-h, --help Display this help message
-q, --quiet Do not output any message
-V, --version Display this application version
--ansi Force ANSI output
--no-ansi Disable ANSI output
-n, --no-interaction Do not ask any interactive question
--root-dir[=ROOT-DIR] Force magento root dir. No auto detection
--skip-config Do not load any custom config.
--skip-root-check Do not check if n98-magerun runs as root
--skip-core-commands Do not include Magento 2 core commands
--skip-magento-compatibility-check Do not check for Magento version compatibility
-v|vv|vvv, --verbose Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
Help:
Dumps configured magento database with `mysqldump`. You must have installed
the MySQL client tools.
On debian systems run `apt-get install mysql-client` to do that.
The command reads app/etc/env.php to find the correct settings.
See it in action: http://youtu.be/ttjZHY6vThs
- If you like to prepend a timestamp to the dump name the --add-time option
can be used.
- The command comes with a compression function. Add i.e. `--compression=gz`
to dump directly in gzip compressed file.
Compression option
Supported compression: gzip
The gzip cli tool has to be installed.
Additionally, for data-to-csv option tar cli tool has to be installed too.
Strip option
If you like to skip data of some tables you can use the --strip option.
The strip option creates only the structure of the defined tables and
forces `mysqldump` to skip the data.
Separate each table to strip by a space.
You can use wildcards like * and ? in the table names to strip multiple
tables. In addition you can specify pre-defined table groups, that start
with an @ symbol.
Example: "dataflow_batch_export unimportant_module_* @log"
$ n98-magerun2.phar db:dump --strip="@stripped"
Available Table Groups
@admin Admin tables.
@oauth OAuth tables.
@log Log tables.
@sessions Database session tables.
@stripped Standard definition for a stripped dump (logs, sessions,
dotmailer).
@sales Sales data (orders, invoices, creditmemos etc).
@quotes Cart (quote) data.
@customers Customer data - Should not be used without @sales.
@trade Current trade data (customers and orders). You usally do
not want those in developer systems..
@development Removes logs and trade data so developers do not have to
work with real customer data.
@ee_changelog Changelog tables of new indexer since EE 1.13.
@search Search related tables.
@idx Tables with _idx suffix.
@dotmailer Dotmailer tables.
@2fa Two Factor Auth tables.
@newrelic_reporting New Relic reporting tables.
@aggregated Aggregated tables.
@replica Replica tables.
@klarna Klarna tables.
@temp Temporary indexer tables.
@mailchimp Mailchimp tables.
Database Import Manual
n98-magerrun2.phar db:import --help
Description:
Imports database with mysql cli client according to database defined in env.php
Usage:
db:import [options] [--] [<filename>]
Arguments:
filename Dump filename
Options:
--connection=CONNECTION Select DB connection type for Magento configurations with several databases [default: "default"]
-c, --compression=COMPRESSION The compression of the specified file
--only-command Print only mysql command. Do not execute
--only-if-empty Imports only if database is empty
--optimize Convert verbose INSERTs to short ones before import (not working with compression)
--drop Drop and recreate database before import
--drop-tables Drop tables before import
--force Continue even if an SQL error occurs
--skip-authorization-entry-creation Do not create authorization rule/role entries if they are missing
-h, --help Display this help message
-q, --quiet Do not output any message
-V, --version Display this application version
--ansi Force ANSI output
--no-ansi Disable ANSI output
-n, --no-interaction Do not ask any interactive question
--root-dir[=ROOT-DIR] Force magento root dir. No auto detection
--skip-config Do not load any custom config.
--skip-root-check Do not check if n98-magerun runs as root
--skip-core-commands Do not include Magento 2 core commands
--skip-magento-compatibility-check Do not check for Magento version compatibility
-v|vv|vvv, --verbose Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
Help:
Imports an SQL file with mysql cli client into current configured database.
You need to have MySQL client tools installed on your system.
Compression option
Supported compression: gzip
The gzip cli tool has to be installed.
Additionally, for data-to-csv option tar cli tool has to be installed too.
Hope this helps. Thanks.