MySQL Command Line – Part 1

This is a simple reminder of the mysql command line tool.

In windows, open a command line, navigate to mysql binary folder (if you have it on your PATH, you can skip the navigation part), and use:

To login:

mysql -u #username# -p#password#

(if you dont add the #password#.. it will ask you for one. Also, note that the password doesnt have a space between the switch)

To show all the databases:

mysql> show databases;

To switch a database:

mysql> use dbname;

To list the tables in the selected db:

mysql> show tables;

To see database’s field formats:

mysql> describe table_name;

To show columns and column information of table_name:

mysql> show columns from table_name;

All mysql commands apply:

mysql> select * from table_name where field_name="blahblah";

NOTE – Always end the command with ;

MYSQL Dump/Restore

On the binary folder, theres another command – mysqldump – that makes, well, dumps.

To make a dump of all the databases on the server:

mysqldump -u #username# -p#password# -A > dumpfile.sql

Dump only certain databases:

mysqldump -u #username# -p#password# db_1 db_2 db_3 > dumpfile.sql

Restore a database dump:

mysql --verbose -user=#username# --password=#password# db_1 < dumpfile.sql

NOTE – note the difference in the mysqdump and mysql when adding the switch for username and password, you can use either “-u #username#” or “–username=#username#”.

Command line help:

General help:

mysql> help

See the help contents:

mysql> help contents

To see help on a specific command/category type:

mysql> help _command_or_category_

Some examples:

mysql> help select
mysql> help administration
mysql> help me because i dont know anything about mysql and want to learn everything in 3 days (ok, doesnt work, but you can still try it for the error message)

Links to resources:

MYSQL commands

mysqldump