How to automate WordPress database deployment?

Manual

Manual process consists of a few steps that seem to be simple but become really problematic and time consiming when the project grows.

1. Change domain from local to prod using Search&Replace script.
2. Export database using MySQL CLI or GUI.
3. Change domain from prod to local using Search&Replace script.

Automation

In this model I just ask my environment for doing this for me. The only thing that need to be done is firing ./wp.sh db:export:prod task and wait for preparing correct database file that can be uploaded to the server.

function db:export:prod() {
  wp search-replace $DOMAIN_LOCAL $DOMAIN_PROD --all-tables
  db:export
  wp search-replace $DOMAIN_PROD $DOMAIN_LOCAL --all-tables
}

function db:export:staging() {
  wp search-replace $DOMAIN_LOCAL $DOMAIN_STAGING --all-tables
  db:export
  wp search-replace $DOMAIN_STAGING $DOMAIN_LOCAL --all-tables
}

This topic is related to task automation so if you want to understand this example well, check out this or at least tools section that describes how to use those scripts.