Making Code and DB dump

CODE_AND_DB_DUMP.MD

Code dump

tar cf - ./ --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 | gzip > /tmp/`date +%s`.code.tar.gz

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

ls -laht /tmp/*.code.tar.gz

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

DB dump

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

export DB_NAME='magento2_test_db'
export DB_HOST='localhost'
export DB_USER='root'
export DB_USER_PASSWORD='root'

For obtaining the DB credentials, in the Cloud environment, you may use the following command

echo $MAGENTO_CLOUD_RELATIONSHIPS | base64 --decode | json_pp

Execute the following command, it will show you all the tables with their size.

mysql -h $DB_HOST -u $DB_USER -p$DB_USER_PASSWORD $DB_NAME -e "
SELECT
  table_schema as \`Database\`,
  table_name AS \`Table\`,
  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 %_log, %_idx and %_tmp.

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

(mysqldump --no_data --routines --force --skip-opt --create-options --disable-keys --set-charset --quick --add-drop-table --single-transaction --extended-insert -h $DB_HOST -u $DB_USER -p$DB_USER_PASSWORD $DB_NAME | sed -e 's/DEFINER[ ]*=[ ]*[Backup dumps without backup.sh script^*]*\*/\*/' && mysqldump --force --skip-opt --skip-add-drop-table --no-create-info --skip-triggers --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 \
 --single-transaction --extended-insert -h $DB_HOST -u $DB_USER -p$DB_USER_PASSWORD $DB_NAME &) | gzip > /tmp/${DB_NAME}-`date +%Y%m%d-%H%M%S-`db.dump.sql.gz

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

ls -lahtr /tmp/*db.dump.sql.gz | tail -1

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

For applying dumps use the following command

gunzip -cf "/tmp/magento2_test_db-20221005-043126.dump.sql.gz" | gunzip -cf | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | grep -v 'mysqldump: Couldn.t find table' | grep -v 'Using a password on the command line' | mysql -u $DB_USER --password=$DB_USER_PASSWORD -h $DB_HOST --force $DB_NAME

Or if you have preinstalled pv then you may use

pv "/tmp/magento2_test_db-20221005-043126.dump.sql.gz" | gunzip -cf | gunzip -cf | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | grep -v 'mysqldump: Couldn.t find table' | grep -v 'Using a password on the command line' | mysql -u $DB_USER --password=$DB_USER_PASSWORD -h $DB_HOST --force $DB_NAME

In case you are using Warden then you may apply the dump with the following command

gunzip -cf "/tmp/magento2_test_db-20221005-043126.dump.sql.gz" | gunzip -cf | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | grep -v 'mysqldump: Couldn.t find table' | grep -v 'Using a password on the command line' | warden db import

In case you have duplicated rows and noticed "Duplicate entry" ERROR, you may ignore them with the following commands

# gunzip
gunzip -cf "/tmp/magento2_test_db-20221005-043126.dump.sql.gz" | gunzip -cf | sed -e 's/INSERT INTO/INSERT IGNORE INTO/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | grep -v 'mysqldump: Couldn.t find table' | grep -v 'Using a password on the command line' | mysql -u $DB_USER --password=$DB_USER_PASSWORD -h $DB_HOST --force $DB_NAME
# pv
pv "/tmp/magento2_test_db-20221005-043126.dump.sql.gz" | gunzip -cf | gunzip -cf | sed -e 's/INSERT INTO/INSERT IGNORE INTO/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | grep -v 'mysqldump: Couldn.t find table' | grep -v 'Using a password on the command line' | mysql -u $DB_USER --password=$DB_USER_PASSWORD -h $DB_HOST --force $DB_NAME
# Warden
gunzip -cf "/tmp/magento2_test_db-20221005-043126.dump.sql.gz" | gunzip -cf | sed -e 's/INSERT INTO/INSERT IGNORE INTO/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | grep -v 'mysqldump: Couldn.t find table' | grep -v 'Using a password on the command line' | warden db import

Crate admin user

bin/magento admin:user:create --admin-user='admin' --admin-password='123123q' --admin-email='[email protected]' --admin-firstname='admin' --admin-lastname='admin'