How to Generate Magento2 Code and DB dumps

CODE_AND_DB_DUMP.MD

Code dump

You can use tiny script m2dump it is doing the same thing as described below.

Estimate size of directories and exclude no needed.

du -sh ./* | sort -h

tar -czf /tmp/`date +%Y-%m-%d`_$USER.tar.gz . \
  --exclude=pub/media/catalog/* \
  --exclude=pub/media/* \
  --exclude=pub/media/backup/* \
  --exclude=pub/media/import/* \
  --exclude=pub/media/tmp/* \
  --exclude=pub/static/* \
  --exclude=var/* \
  --exclude=private \
  --exclude=tests

DB dump

Declar the following variables with appropriate values, before executing the above command

cat app/etc/env.php | grep -A10 connection

export DB_NAME=$(grep [\']db[\'] -A 20 app/etc/env.php | grep dbname | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//");
export MYSQL_HOST=$(grep [\']db[\'] -A 20 app/etc/env.php | grep host | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//");
export DB_USER=$(grep [\']db[\'] -A 20 app/etc/env.php | grep username | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//");
export MYSQL_PWD=$(grep [\']db[\'] -A 20 app/etc/env.php | grep password | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//");
mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME

Get current URL:

mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SELECT core_config_data.value FROM core_config_data WHERE path = 'web/unsecure/base_url'" | grep http | head -1

Connect to db and execute the following query, it will show you all the tables with their size.

mysql -h $MYSQL_HOST -u $DB_USER -p$MYSQL_PWD $DB_NAME -e "
SELECT
  table_schema,
  table_name,
  round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB'
FROM information_schema.TABLES WHERE table_schema = '$DB_NAME'
ORDER BY (data_length + index_length) ASC"

Consider which tables can be skipped, for instance we can ignore data from temporary Magento tables like %_idx and %_tmp.

Then add those tables to the list with option --ignore-table, like in the example below

(mysqldump --no_data --routines --force --single-transaction --create-options --extended-insert --set-charset --quick --add-drop-table -h $MYSQL_HOST -u $DB_USER -p$MYSQL_PWD $DB_NAME | sed -e 's/DEFINER[ ]*=[ ]*[Backup dumps without backup.sh script^*]*\*/\*/' && \
mysqldump --force --skip-add-drop-table --no-create-info --single-transaction --extended-insert --quick \
 --ignore-table=$DB_NAME.cache_tag \
 --ignore-table=$DB_NAME.sales_bestsellers_aggregated_daily \
 --ignore-table=$DB_NAME.core_cache \
 --ignore-table=$DB_NAME.magento_logging_event \
 --ignore-table=$DB_NAME.magento_logging_event_changes \
 --ignore-table=$DB_NAME.customer_log \
 --ignore-table=$DB_NAME.report_event \
 --ignore-table=$DB_NAME.report_viewed_product_index \
 --ignore-table=$DB_NAME.search_query \
 --ignore-table=$DB_NAME.catalog_product_index_price_final_idx \
 --ignore-table=$DB_NAME.catalog_product_index_price_bundle_opt_idx \
 --ignore-table=$DB_NAME.catalog_product_index_price_bundle_idx \
 --ignore-table=$DB_NAME.catalog_product_index_price_downlod_idx \
 --ignore-table=$DB_NAME.catalog_product_index_price_cfg_opt_idx \
 --ignore-table=$DB_NAME.catalog_product_index_price_opt_idx \
 --ignore-table=$DB_NAME.catalog_product_index_price_cfg_opt_agr_idx \
 --ignore-table=$DB_NAME.catalog_product_index_price_opt_agr_idx \
 --ignore-table=$DB_NAME.catalog_product_index_price_bundle_sel_idx \
 --ignore-table=$DB_NAME.catalog_product_index_eav_decimal_idx \
 --ignore-table=$DB_NAME.cataloginventory_stock_status_idx \
 --ignore-table=$DB_NAME.catalog_product_index_eav_idx \
 --ignore-table=$DB_NAME.catalog_product_index_price_idx \
 --ignore-table=$DB_NAME.catalog_product_index_price_downlod_tmp \
 --ignore-table=$DB_NAME.catalog_product_index_price_cfg_opt_tmp \
 --ignore-table=$DB_NAME.catalog_product_index_eav_tmp \
 --ignore-table=$DB_NAME.catalog_product_index_price_tmp \
 --ignore-table=$DB_NAME.catalog_product_index_price_opt_tmp \
 --ignore-table=$DB_NAME.catalog_product_index_price_cfg_opt_agr_tmp \
 --ignore-table=$DB_NAME.catalog_product_index_eav_decimal_tmp \
 --ignore-table=$DB_NAME.catalog_product_index_price_opt_agr_tmp \
 --ignore-table=$DB_NAME.catalog_product_index_price_bundle_tmp \
 --ignore-table=$DB_NAME.catalog_product_index_price_bundle_sel_tmp \
 --ignore-table=$DB_NAME.cataloginventory_stock_status_tmp \
 --ignore-table=$DB_NAME.catalog_product_index_price_final_tmp \
 --ignore-table=$DB_NAME.catalog_product_index_price_bundle_opt_tmp \
 --ignore-table=$DB_NAME.magento_catalogpermissions_index_tmp \
 --ignore-table=$DB_NAME.magento_catalogpermissions_index_product_tmp \
 --ignore-table=$DB_NAME.catalog_category_product_index_tmp \
 --ignore-table=$DB_NAME.catalog_category_product_index_replica \
 --ignore-table=$DB_NAME.catalog_product_index_price_replica \
 -h $MYSQL_HOST -u $DB_USER -p$MYSQL_PWD $DB_NAME &) | gzip > /tmp/`date +%Y-%m-%d`_$USER.sql.gz

After execution you will be able to find dump in the directory /tmp

ls -laht /tmp/*.gz

Your dump will be shown at the top of the listed dumps.

Encrypt Dumps

openssl enc -aes-256-cbc -in /tmp/`date +%Y-%m-%d`_$USER.sql.gz -out pub/media/`date +%Y-%m-%d`_$USER.sql.gz.enc
openssl enc -aes-256-cbc -in /tmp/`date +%Y-%m-%d`_$USER.tar.gz -out pub/media/`date +%Y-%m-%d`_$USER.tar.gz.enc
rm /tmp/`date +%Y-%m-%d`_$USER.tar.gz /tmp/`date +%Y-%m-%d`_$USER.sql.gz

Decrypt Dumps

openssl enc -d -aes-256-cbc -in encrypted-file.sql.gz -out db.sql.gz
openssl enc -d -aes-256-cbc -in encrypted-code.tar.gz -out code.tar.gz