When working with any application that utilizes a database, there are times when you will need to export a database and import it elsewhere. Most often, you would do this with a production site to work on it locally. This way, you can create a new configuration that can be exported and pushed to production, as discussed in Chapter 9, Configuration Management – Deploying in Drupal 8.
In this recipe, we will export a database dump from a production site in order to set up the local development. The database dump will be imported over the command line and sanitized. We will then execute an SQL query through Drush to verify sanitization.
Drush has the ability to use site aliases. Site aliases are configuration items that allow you to interact with a remote Drupal site. In this recipe, we will use the following alias to interact with a fictional remote site to show how a typical workflow will go to fetch a remote database.
Note that you do not need to use a Drush alias to download the database dump created in the recipe; you can use any method you are familiar with (manually, from the command line, use mysqldump or phpMyAdmin):
$aliases['drupal.production] = array( 'uri' => 'example.com', 'remote-host' => 'example.com', 'remote-user' => 'someuser', 'ssh-options' => '-p 2222', );
Note
Read the Drush documentation for more information on site aliases at http://docs.drush.org/en/master/usage/#site-aliases. Site aliases allow you to interact with remote Drupal installations.
We will also assume that the local development site has not yet been configured to connect to the database.
- We will use the
sql-dumpcommand to export the database into a file. The command returns the output that needs to be redirected to a file:$ drush @drupal.production sql-dump > ~/prod-dump.sqlThis will take the data from
sql-dumpand save it inprod-dump.sqlin yourhomedirectory. - Navigate to your local Drupal site's directory and copy
sites/default/default.settings.php to sites/default/settings.php. - Edit the new
settings.phpfile and add a database configuration array at the end of the file:// Database configuration. $databases['default']['default'] = array( 'driver' => 'mysql', 'host' => 'localhost', 'username' => 'mysql', 'password' => 'mysql', 'database' => 'data', 'prefix' => '', 'port' => 3306, 'namespace' => 'Drupal\Core\Database\Driver\mysql', );
- This will add our database connection information as the
defaultdatabase in the global$databasesvariable. - Using the
sql-clicommand, we can import the database dump that we created:$ drush sql-cli < ~/prod-dump.sqlThis will then run the SQL dump as a set of commands on the database, importing your data.
- The
sql-sanitizecommand allows you to obfuscate user e-mails and passwords in the database:$ drush sql-sanitizeThis will update all of the users in the user table by changing their usernames and passwords.
- To verify that our information has been sanitized, we will use the
sql-querycommand to run a query against the database:$ drush sql-query "SELECT uid, name, mail FROM users_field_data;"The command will return a list of the results.
When working with Drush, we have the ability to use Drush aliases. A Drush alias contains a configuration that allows the tool to connect to a remote server and interact with that server's installation of Drush.
The sql-dump command executes the proper dump command for the database driver, which is typically MySQL and the mysqldump command. It streams to the terminal and must be piped to a destination. When piped to a local SQL file, we can import it and execute the create commands to import our database schema and data.
With the sql-cli command, we are able to execute SQL commands to the database through Drush. This allows us to redirect the file contents to the sql-cli command and run the set of SQL commands. With the data imported, the sql-sanitize command replaces usernames and passwords.
Finally, the sql-query command allows us to pass an SQL command directly to the database and return its results. In our recipe, we query the users_field_data to verify that e-mails have been sanitized.
Working with Drupal over the command line simplifies working with the database. We will explore this in more detail in the following sections.
Sometimes databases can be quite large. The sql-dump command has a gzip option that will output the SQL dump using the gzip command. In order to run the command, you would simply do:
$ drush sql-dump –-gzip dump.sql.gz
The end result provides a reduction in the dump file:
-rw-r--r-- 1 user group 3058522 Jan 14 16:10 dump.sql -rw-r--r-- 1 user group 285880 Jan 14 16:10 dump.sql.gz
At the time of writing this module, Console does not provide a command for sanitizing the database. There are the database:connect and database:client commands, which will launch a database client. This allows you to be logged into the database's command-line interface:
$ drupal database:client $ drupal database:connect
These commands are similar to the sql-cli and sql-connect commands from Drush. The client command will bring you to the database's command-line tool, where connect shows the connection string.
Console also provides the database:dump command. Unlike Drush, this will write the database dump for you in the Drupal directory:
$ drupal database:dump [OK] Database exported to: /path/to/drupal/www/data.sql
- Chapter 9, Configuration Management – Deploying in Drupal 8
- Refer to Dumping Data in SQL Format with
mysqldumpat http://dev.mysql.com/doc/refman/5.7/en/mysqldump-sql-format.html