Drush: how to Import and Export mySql database

MySQL Database Import and Export via Drush

Databases have the urge to grow, in some cases they are so big to manipulate, which means that import and export is impossible with the current tools like cPanel .

The Drush team created a tool which will help us to manipulate the database, which was defined in the settings configuration, regardless its size.

pay attention, most chance that import database will be available to files which ware created by export activity.

How to Import and Export Drupal MySQL Database using Drush ?

Database and Site information

drush status

make sure you are working with desire site, database and versions of site and drush.

If you are working with new database/site, don't forget to create the database and its connection information in the settings configuration.

Export Drupal 8 MySQL Database

drush cr
drush sql-dump > /pathto/mysql-dump-drupal8.sql

Import Drupal 8 and MySQL Database

drush sql-drop 
drush sql-cli 
mysql> source /pathto/mysql-dump-drupal8.sql

Export Drupal 7 MySQL Database

drush cc all
drush sql-dump > /pathto/mysql-dump-drupal7.sql

Import Drupal 7 MySQL Database

//Method 1
drush sql-drop
drush sql-cli < /pathto/mysql-dump-drupal.sql

//Method 2
drush sql-drop
drush sql-cli
mysql> source /pathto/mysql-dump-drupal.sql

Export to a gz compressed file

drush sql-dump | gzip > mysql-dump-drupal.sql.gz
drush sql-dump --gzip > mysql-dump-drupal.sql.gz

Import a GZ compressed file

gunzip < mysql-dump-drupal.sql.gz | drush sqlc

Dump specific tables

drush sql-dump --data-only --gzip --tables-list="node,users"

Drush shortcuts

drush sql-cli = drush sqlc
drush sql-query = drush sqlq

Drush sql commandes

drush sql-cli       //Open a SQL command-line interface using Drupal's User and Password
drush sql-connect
drush sql-create   //Create a database
drush sql-drop        // Delete All tables of the current database
drush sql-dump        // Export current drupal Database to a file (like mysqldump)
drush sql-query    //Execute a query
drush sql-sanitize   //Run sanitization operations on the current database
drush sql-sync       //Copies the database contents from a source site to a target site. Transfers the database dump via rsync