drush examples for dumping a database

Submitted by gareth on Thu, 29/06/2023 - 16:20

In a second part to the drush examples post last month, I thought I should also share the DB commands I have been using to ensure they don't get lost either.

This time I am using drush manual regarding the the drush configuration options (https://www.drush.org/12.x/using-drush-configuration/#non-options):

  structure-tables:
    common:
      - cache
      - 'cache_*'
      - history
      - 'search_*'
      - 'sessions'
      - 'watchdog'

Again it looks similar to the variables I used to pass around when doing a mysqldump.

The old way of creating a cache less dump would have been to create a structure only dump and then doing a second dump of the data which did not include the data in the cache tables, like this:

TABLES=$(mysql --skip-column-names -e 'show tables' -u ${USER} --password="${PASS}" -h ${HOST} ${DB})
mysqldump --complete-insert --disable-keys --single-transaction --no-data -u ${USER} --password="${PASS}" -h ${HOST} ${DB} ${TABLES} > "${DB}.${DATE}".sql
TABLES2=$(echo "$TABLES" | grep -Ev "^(accesslog|cache.*|flood|search_.*|semaphore|sessions|feeds_log|watchdog)$")
mysqldump --complete-insert --disable-keys --single-transaction --no-create-info -u ${USER} --password="${PASS}" -h ${HOST} ${DB} ${TABLES2} >> "${DB}.${DATE}".sql
<code>
rsync -rltDPh --exclude '*.tar.gz' --exclude 'files/js/js_*' --exclude 'files/css/css_*' --exclude 'files/imagecache/*' --exclude 'files/styles/*' --exclude 'files/php/*' --delete -e ssh ${SSH_URL}:${FILES_LOCATION} ${SYNC_FILES_LOCATION}

Once again it looks simpler if I just start using a drush command that looks like this:

drush -y sql:dump --gzip --structure-tables-key=common --result-file=../$BACKUP_LOCATION/${DB}.${DATE}".sql

Add new comment