Sign into mysql

mysql -u mysqlusername -p
Enter password: 

or

mysql -u mysqlusername -p password

or when you use Drush with Drupal:

drush sqlc

List databases

SHOW DATABASES;

The output should look something like this:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_new_database    |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

Select database you would like to affect

USE name_of_database

(USE is like QUIT, doesn’t need a semicolon.)

Source: http://dev.mysql.com/doc/refman/5.7/en/database-use.html

Look inside the database

SHOW TABLES;

More info: http://dev.mysql.com/doc/refman/5.7/en/show-tables.html

Create a database

CREATE DATABASE `name_of_database` DEFAULT COLLATE utf8_general_ci DEFAULT CHARACTER SET utf8;

or

CREATE DATABASE name_of_database;

Delete database

DROP DATABASE name_of_database;

Assign privileges

Assign all privileges on global level to the given mysql user and create your project db:

For this you don't have to be logged into MySQL.

mysql -e "GRANT ALL ON *.* TO 'mysqlusername'@'hostname' IDENTIFIED BY 'password'"

-e: will make it run the command and exit, instead of leaving you at the mysql> prompt.

(You might need ‘sudo’ at the beginning of the command.)

e.g. - with wildcard as hostname:

sudo mysql -e "GRANT ALL ON *.* TO 'drupal'@'%' IDENTIFIED BY 'mypassword123'"

More info: http://dev.mysql.com/doc/refman/5.7/en/grant.html

To clear the server’s memory of cached information and reload the privileges from the grant tables in the mysql database:

sudo mysql -e "FLUSH PRIVILEGES"

More info: http://dev.mysql.com/doc/refman/5.7/en/flush.html

Log out of MySQL

exit

Dump MySQL database into a ‘.sql’ file

To do this, you can use the 'mysqldump' client. The dump will contain SQL statements to create and populate the table.

When you use this, you are not logged in to the MySQL console.

mysqldump -u mysqlusername -p name_of_database > /path/to/put/dumpfiles/dbdump_date.sql

More info: http://linuxcommand.org/man_pages/mysqldump1.html

When you use Drupal and Drush the command is:

drush sql-dump --result-file= /path/to/put/dumpfiles/dbdump_date.sql

Import data from .sql file into a database

If it is an already existing database you would like to import into, log into MySQL on the command-line, drop the database, and create it again then exit MySQL.

Outside of the MySQL console:

mysql -u mysqlusername -p name_of_database < databasedumpfilename.sql

e.g.:

mysql -u root -p name_of_database < path/to/dbdumpfiles/dbdump_date.sql

If you need to move one directory up and into another the path could be something like this:

mysql -u root -p name_of_database < ../dbdumpfiles/dbdump_date.sql

Search for tables

Lists all tables with the search phrase in the name in database.

Select the database:

USE name_of_database

Use ‘show tables’ command with a filter:

SHOW TABLES LIKE '%search_phrase%';

More info: http://dev.mysql.com/doc/refman/5.7/en/show-tables.html

Get information about a table

To get information about a table’s structure or query execution plans:

DESCRIBE table_name;

For example:

DESCRIBE filter_format;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| format | varchar(255)        | NO   | PRI | NULL    |       |
| name   | varchar(255)        | NO   | UNI |         |       |
| cache  | tinyint(4)          | NO   |     | 0       |       |
| status | tinyint(3) unsigned | NO   | MUL | 1       |       |
| weight | int(11)             | NO   |     | 0       |       |
+--------+---------------------+------+-----+---------+-------+
5 rows in set (0.08 sec)

In 'Field' column you will see the columns of the ‘filter_format’ table listed.

To select columns from a table

SELECT columnName1, columnName2 FROM table_name;

e.g.:

SELECT format, status FROM filter_format;

So, the steps for interrogating a table would be:

USE name_of_database
SHOW TABLES;
DESCRIBE table_name;
//In 'Field' column you will see the columns of the table listed.
SELECT everything FROM table_name WHERE condition;

e.g.:

USE mydatabase
SHOW TABLES;
DESCRIBE variable;
SELECT * FROM variable WHERE name = 'theme_settings';

In MySQL Workbench the same would be:

SELECT * FROM mydatabase.variable WHERE name = 'theme_settings';

Resources:

Creating and Using a Database:
http://dev.mysql.com/doc/refman/5.7/en/database-use.html

mysqldump:
http://linuxcommand.org/man_pages/mysqldump1.html

A Basic MySQL Tutorial:
https://www.digitalocean.com/community/tutorials/a-basic-mysql-tutorial

Getting Started with MySQL:
http://dev.mysql.com/doc/mysql-getting-started/en/