Author: Edit Olah

My often used MySQL commands

Basic MySQL commands

Basic commands for managing databases in MySQL on the command-line in local development environments and hosting servers.

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;

- ! TIP ! -

Autofill a command in the CLI that you have already used

Use

ctrl + r

then start typing, when term is offered, hit enter.

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/

Related blog posts

  • Setting up multiple projects in WAMP 3 with aliases

    Setting up multiple projects in WAMP 3 with aliases

    Local development environment

    When you have to set up a development environment quickly on a Windows...

    More details >>

  • How to export MySQL database into a ‘.sql’ file on WAMP

    How to export MySQL database into a ‘.sql’ file on WAMP

    MySQL dump in Windows command prompt

    If you are used to Linux/Unix based environments using the command line...

    More details >>

  • ‘mysqldump Version Mismatch’ warning from MySQL Workbench

    ‘mysqldump Version Mismatch’ warning from MySQL Workbench

    Changing the path to mysqldump program file

    How to make sure that the mysqldump.exe version of MySQL Workbench is equal...

    More details >>

Contact Edit

Get in touch

If you are interested in hiring me, please drop me an email. I would be happy to send you my up-to-date resume.

Drupal Association member
Image of Edit