
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
Local development environment
When you have to set up a development environment quickly on a Windows...
-
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...
-
‘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...
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.
Edit Olah, Web developer
