Access MySQL Server From The Shell Prompt (Command Line)

by on March 28, 2006 · 7 comments· LAST UPDATED December 5, 2009

in , ,

How do I access MySQL server from the shell prompt (command line)?

MySQL software includes mysql client. It is a text-based client for mysqld, a SQL-based relational database server. It works interactive and non-interactive mode.

mysql Client Syntax

mysql -u {mysql-user} -p {mysql-password} -h {mysql-server}

Where,

  • -u {mysql-user} : Specify MySQL user name. Use root only when connecting to local system.
  • -p {mysql-password}: Specify password, Employ the specified password when connecting to the database server. If a password is not supplied, it will be requested interactively.
  • -h {mysql-server}: Connect to the specified host (remote or local)

For example remote connect to MySQL server called mysql10.nixcraft.in and user vivek:

$ mysql -u vivek -h mysql10.nixcraft.in -p

Sample outputs:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.15-Debian_1-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

You can type an sql statement at mysql> prompt. In this example, you will list tables from the demo database, run;

USE demo;
SHOW TABLES;

Sample session:

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31855130
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use nqod;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_nqod |
+----------------+
| quotes         |
| quotes_meta    |
+----------------+
2 rows in set (0.00 sec)
mysql> \q
Bye

After typing an SQL statement, end it with ";" and press [Enter] key. To exit type quit or \q:

quit

OR

q

Sample session:

 mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31853999
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> quit
Bye

Batch Mode

You can execute SQL statements in a script file (batch file) as follows:

mysql database_name < input.script.sql > output.file
mysql -u user -p'password' database_name < input.script.sql > output.file

Recommend readings:

Type the following command to mysql command man page:
man mysql

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 7 comments… read them below or add one }

1 jitendra December 30, 2008 at 7:22 am

Thanks it was really useful command

Reply

2 leordoff October 30, 2009 at 7:40 am

Simple Yet Informative

Reply

3 benny Gero March 22, 2011 at 3:49 pm

Hello thanks, Is posible execute this command with bat file? Because I need executed automatically.

Thanks have a nice days.

Reply

4 mediafirehunt June 12, 2011 at 2:52 am

nice tutorial….

Reply

5 anon January 29, 2012 at 6:53 am

This isn’t working for me :( I type in mysql etc etc… and it doesn’t do anything, it just goes to the next ~]# line. guhhhhh I really want to setup remote mysql management, it’s been something I’ve wanted to do for a long time now. ;__;

Reply

6 Firy June 7, 2012 at 2:28 am

Thank you very much, it is very useful.

Reply

7 Bharath Gandhi October 11, 2012 at 4:43 pm

short and sweet… a right pointer to the beginners….

Reply

Leave a Comment

Tagged as: , , , , , , , , , , , , , , , ,

Previous Faq:

Next Faq: