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


🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 10 comments so far... add one


CategoryList of Unix and Linux commands
Disk space analyzersdf duf ncdu pydf
File Managementcat cp mkdir tree
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Modern utilitiesbat exa
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg glances gtop jobs killall kill pidof pstree pwdx time vtop
Searchingag grep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
10 comments… add one
  • jitendra Dec 30, 2008 @ 7:22

    Thanks it was really useful command

  • leordoff Oct 30, 2009 @ 7:40

    Simple Yet Informative

  • benny Gero Mar 22, 2011 @ 15:49

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

    Thanks have a nice days.

  • mediafirehunt Jun 12, 2011 @ 2:52

    nice tutorial….

  • anon Jan 29, 2012 @ 6:53

    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 Jun 7, 2012 @ 2:28

    Thank you very much, it is very useful.

  • Bharath Gandhi Oct 11, 2012 @ 16:43

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

  • Khankhungkhernitz Feb 18, 2015 @ 1:48

    Thanks a lot buddy.. :D

  • student Jul 6, 2015 @ 0:58

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

    • Modou Kebbeh May 27, 2016 @ 20:49

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

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum