Sync database between local and remote Drupal environments

This allows users to sync the database between their local setup and the remote environment. This is achieved using the Drush command: drush sql:sync @source @target.

What is the sql:sync command?

The Drush sql:sync command is used to synchronize databases between two Drupal sites (typically local and remote environments). It copies the database from the source site to the target site, making it an essential tool for developers working on staging, development, and production environments.

In other words, the sql:sync command is meant to very handily merge a local Drupal site database with a remote site's database without having to take all the individual steps of creating a remote database dump file (using drush sql-dump), transferring the dump file to the local server (using drush rsync), and then importing it into the local database (drush sqlc < remote_db.sql).

Command: drush sql:sync @source @target [options]

Definitions:
"@source" => The Drush alias for the site where the database will be copied from.
"@target" => The Drush alias for the site where the database will be copied to.
"[options]" => Optional flags to customize the behavior of the command.

Benefits

1. It fetches the latest current database from servers.
2. The import process is fast.
3. No need to access servers to pull databases and run commands there.

Pre-requisite

1. The SSH key pair which will be used has server access enabled.

Configurations

1. Ensure that the site is up [composer install is done and settings.php is configured]
2. Install Drush, if not already: composer require drush/drush
3. The purpose for this is to make our Drush up and running, meaning it is connected to the database [Note: To validate above step, RUN vendor/bin/drush cr]
4. Create "drush" directory in the Drupal project root and then "sites" sub-directory inside of it.
5. Inside the "sites" directory, create a *.site.yml file. [e.g.: self.site.yml]
   

   --|drupal
   --|--|web
   --|--|composer.lock
   --|--|composer.json
   --|--|drush
   --|--|--|sites
   --|--|--|--|self.site.yml

6. Let's say we want the master server database to be synced with our local database.

   Sample self.site.yml:

   local:
     paths:
       files: /var/www/html/drupal/web/sites/default/files
     root: /var/www/html/drupal/web
     uri: 'http://example.drupal.com'
   
   master:
     host: <server-hostname>
     paths:
       files: <project-server-file-path>
     root: <project-server-web-path>
     uri: '<site-url>'
     user: <server-username>
     ssh:
       options: '-o PasswordAuthentication=no -i ~/.ssh/id_rsa'

   NOTE: Change the local files and root directories, according to your setup.

7. Check site aliases: vendor/bin/drush sa [sa: site:alias]
8. RUN vendor/bin/drush sql-sync @master @self [sql-sync or sql:sync]
9. It'll ask for ssh key passphrase if used and "yes/no" prompts to proceed with operation or cancel it. Read the prompts carefully and if the @source and @target are correct, proceed with yes.
10. If the SSH keys are present with proper permissions and authorizations, the import will be completed successfully.
    

    Currently there is no success message displayed after the successful import; the command just exits after completion.