Almost one-liner dump and import of Magento database trough SSH (with ignore tables and triggers definer adjustment)

magento-dump-import-ssh.sh

#!/bin/bash
# Marko Martinović

# Prints command traces
set -x

# Tables to ignore
IGNORE_TABLES=(dataflow_batch_export dataflow_batch_import log_customer log_quote log_summary log_summary_type log_url log_url_info log_visitor log_visitor_info log_visitor_online report_event index_event enterprise_logging_event_changes core_cache core_cache_tag core_session core_cache_tag)

# Source SSH
SRC_SSH_USER=""
SRC_SSH_HOST=""
SRC_SSH_PORT="22"

# Source MySQL
SRC_MYSQLDUMP_USER=""
SRC_MYSQLDUMP_PASS=""
SRC_MYSQLDUMP_DBNAME=""
SRC_MYSQLDUMP_HOST="localhost"
SRC_MYSQLDUMP_PORT="3306"

# Destination MySQL
DST_MYSQL_USER=""
DST_MYSQL_PASS=""
DST_MYSQL_DBNAME=""
DST_MYSQL_HOST="localhost"
DST_MYSQL_PORT="3306"

set -   # stop trace
IGNORE_STRING=""
for TABLE in "${IGNORE_TABLES[@]}"; do
  IGNORE_STRING="$IGNORE_STRING --ignore-table=$SRC_MYSQLDUMP_DBNAME.$TABLE"
done
set -x # continue trace

ssh -C $SRC_SSH_USER@$SRC_SSH_HOST -p $SRC_SSH_PORT "(mysqldump -h\"$SRC_MYSQLDUMP_HOST\" -P\"$SRC_MYSQLDUMP_PORT\" -u\"$SRC_MYSQLDUMP_USER\" -p\"$SRC_MYSQLDUMP_PASS\" --no-data --compress $SRC_MYSQLDUMP_DBNAME && mysqldump $IGNORE_STRING -h\"$SRC_MYSQLDUMP_HOST\" -P\"$SRC_MYSQLDUMP_PORT\" -u\"$SRC_MYSQLDUMP_USER\" -p\"$SRC_MYSQLDUMP_PASS\" --no-create-db --no-create-info --skip-triggers --single-transaction --quick --compress $SRC_MYSQLDUMP_DBNAME) | gzip -9 -c" | gunzip | sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | mysql -P"$DST_MYSQL_PORT" -h"$DST_MYSQL_HOST" -u"$DST_MYSQL_USER" -p"$DST_MYSQL_PASS" $DST_MYSQL_DBNAME

Hi @Marko-M,

I'm a little new to mysql and have a quick question about this according magento.
After a lot of research a I see the options like --triggers and --routines.
But I don't understand the purpose of this yet.
For now i do have the config file like underneath.

Can you please explain wat is best to do for dev and production.
Many thanx in advance.

Martijn

[mysqldump]
single-transaction
routines
triggers
ignore-table=magentoshop_dev.dataflow_batch_export
ignore-table=magentoshop_dev.dataflow_batch_import
ignore-table=magentoshop_dev.log_customer
ignore-table=magentoshop_dev.log_quote
ignore-table=magentoshop_dev.log_summary
ignore-table=magentoshop_dev.log_summary_type
ignore-table=magentoshop_dev.log_url
ignore-table=magentoshop_dev.log_url_info
ignore-table=magentoshop_dev.log_visitor
ignore-table=magentoshop_dev.log_visitor_info
ignore-table=magentoshop_dev.log_visitor_online
ignore-table=magentoshop_dev.report_event
ignore-table=magentoshop_dev.index_event
ignore-table=magentoshop_dev.enterprise_logging_event_changes
ignore-table=magentoshop_dev.core_cache
ignore-table=magentoshop_dev.core_cache_tag
ignore-table=magentoshop_dev.core_session
ignore-table=magentoshop_dev.core_cache_tag)