Services Spotlight:

MySQL Server Administration Cheat Sheet

Written By Corlew Solutions
Updated July 26, 2016
Published January 18, 2014
Why Corlew Solutions?

We write great software and provide amazing technical support. Contact us today if you need help with a software project:

  • Website: Send an inquiry
  • Email:
  • Phone: (703) 688-3058

Article Technology Info

This article discusses the following technologies:

Login to MySQL

1
mysql -u [username] -p[password]   # Do not put a space after -p

Navigating

List MySQL Databases
1
show databases;
List MySQL Tables In A Database
1
2
use [dbname];
show tables;

Exporting

The following commands are executed in the terminal using the mysqldump command.

Note: If your password contains the $ character you will probably have to wrap your password in single quotes when using the -p option. The dollar sign has a special meaning on the bash shell.

Export All MySQL Databases
1
mysqldump -u [username] -p[password] --all-databases > [filename.sql]

Note: Each database will have a CREATE DATABASE … statement before it.

Export All MySQL Databases With Compression
1
mysqldump -u [username] -p[password] --all-databases | gzip -9 > [filename.sql.gz]

Note: Each database will have a CREATE DATABASE … statement before it.

Export Database Structure Without Data (-d option)
1
mysqldump -d -u [username] -p[password] --all-databases > [filename.sql]
Export Specific Database (Without CREATE DATABASE….IF NOT EXIST or DROP DATABASE … )
1
mysqldump -u [username] -p[password] [dbname] > [filename.sql]
Export A Specific Database with CREATE … IF NOT EXIST
1
mysqldump -u [username] -p[password] --databases [dbname] > [filename.sql]
Export A Specific Database with CREATE … IF NOT EXIST and DROP DATABASE
1
mysqldump -u [username] -p[password] --databases --add-drop-database [dbname] > [filename.sql]
Export Specific Database & Gzip
1
mysqldump -u [username] -p[password] [database] | gzip -9 > [filename.sql.gz]

Importing

Extract Gzipped MySQL Export File
1
gunzip [filename]
Import Multiple MySQL Databases from SQL File
1
mysql -u [username] -p[password] < [filename.sql]
Import Single MySQL Databases from SQL File
1
2
# Create the database in the database first, then execute:
mysql -u [username] -p -D [db_name_here] -o < [filename.sql]

Management

Delete MySQL Database
1
2
#Login to mysql first
drop database [dbname]
Create MySQL Database
1
2
#Login to mysql first
create database [dbname]

We hope you found this article useful. If you see any mistakes, missing features or ways to improve it, please let us know in the comments below so we can update its contents. If you're willing to link to us, we would sincerely appreciate it!

Corlew Solutions is a Web Design and Web Application Development company based in Fairfax Virginia, minutes away Washington D.C. If you're looking for great web design from the Northern Virginia area or web design from the Washington D.C. area we can help. Contact Us today!

comments powered by Disqus