≡ Menu

Access MySQL Server From The Shell Prompt (Command Line)

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

Share this tutorial on:

Your support makes a big difference:
I have a small favor to ask. More people are reading the nixCraft. Many of you block advertising which is your right, and advertising revenues are not sufficient to cover my operating costs. So you can see why I need to ask for your help. The nixCraft, takes a lot of my time and hard work to produce. If you use nixCraft, who likes it, helps me with donations:
Become a Supporter →    Make a contribution via Paypal/Bitcoin →   

Don't Miss Any Linux and Unix Tips

Get nixCraft in your inbox. It's free:



{ 10 comments… add one }
  • jitendra December 30, 2008, 7:22 am

    Thanks it was really useful command

  • leordoff October 30, 2009, 7:40 am

    Simple Yet Informative

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

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

    Thanks have a nice days.

  • mediafirehunt June 12, 2011, 2:52 am

    nice tutorial….

  • anon January 29, 2012, 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. ;__;

  • Firy June 7, 2012, 2:28 am

    Thank you very much, it is very useful.

  • Bharath Gandhi October 11, 2012, 4:43 pm

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

  • Khankhungkhernitz February 18, 2015, 1:48 am

    Thanks a lot buddy.. :D

  • student July 6, 2015, 12:58 am

    i cant go to mysql
    why does the mysql -u root doesn’t work???

    • Modou Kebbeh May 27, 2016, 8:49 pm

      “mysql -u root -p” and hit enter, that should work

Leave a Comment

You can use these HTML tags and attributes: <strong> <em> <pre> <code> <a href="" title="">


   Tagged with: , , , , , , , , , , , , , , , ,