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.

ADVERTISEMENTS

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 SysAdmin, Linux/Unix, Open Source/DevOps topics:
CategoryList of Unix and Linux commands
File Managementcat
FirewallCentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig 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 jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNCentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
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>, <code>...</code> and <kbd>...</kbd> for code samples.