TLDR;

  • Export Database: drush sql:dump --result-file="$(date +'%Y%m%dT%H%M%S') [Database] $(drush status --field=db-name).sql"
  • Export Filebase: zip -r -0 "$(date +'%Y%m%dT%H%M%S') [Filebase] path$(drush status --field=root|sed 's%/%-%g').zip" .
  • Import Database: drush sql:drop -y && drush sql:cli < example.sql

Drush Dump Database (sql:dump)

Export Drupal’s (default) database as SQL fiel using mysql dump or equivalanet command: (*here drush status --field=db-name is used to get the default database’s name

1

drush sql:dump --result-file="$(date +'%Y%m%dT%H%M%S') [Database] $(drush status --field=db-name).sql"

Similarly I use the following command to zip all files in public_html folder (as filebase backup): (*here drush status --field=db-name is used to get the root folder’s path, and -0 is used to let zip command run without compression (fastest)

1

zip -r -0 "$(date +'%Y%m%dT%H%M%S') [Filebase] path$(drush status --field=root|sed 's%/%-%g').zip" .

Drush Import Database (sql:drop, sql:cli / sql:connect / sql:query)

Before importing from file to drupal’s database, drop all the existing tables:

1

drush sql:drop

Then import from sql file via one of the following command:

1
2
3

drush sql:cli < example.sql
(OR) $(drush sql:connect) < example.sql
(OR) drush sql:query --file=example.sql

Drush Connect Database / Run Query (sql:cli, sql:connect, sql:query)

You can use drush sql:cli to open a SQL command-line interface using Drupal’s credentials (*you also can use the drush sql:connect command to get a string to do the same thing)

1
2
3
4
5
6
7

> drush sql:cli
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 448433
    Server version: 10.6.24-MariaDB MariaDB Server
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    MariaDB [iro_d10]>

OR

1
2
3
4
5
6
7
8
9

> drush sql:connect
    mysql --user=example_admin --password='example_password' --database=example_d10 --host=localhost --port=3306 -A
> mysql --user=example_admin --password='example_password' --database=example_d10 --host=localhost --port=3306 -A
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 448433
    Server version: 10.6.24-MariaDB MariaDB Server
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    MariaDB [iro_d10]>

Similarly you can also use drush sql:query to execute a query against the database without going into the interactive session.

For instance, if you want to: delete all columns from the key_value_expire table for rows, where the collection value starts with "theme:", ends with ":http", and has any characters in between, you can do that via the following command:

1

drush sql-query "DELETE FROM key_value_expire WHERE collection LIKE 'theme:%:http';"

Reference