Backup & Restore Magento2 Database using N98-magerun2

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.