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

Featured Articles:

• 20 Linux System Monitoring Tools Every SysAdmin Should Know
• 20 Linux Server Hardening Security Tips
• My 10 UNIX Command Line Mistakes
• Linux: 20 Iptables Examples For New SysAdmins
• 25 PHP Security Best Practices For Sys Admins
• The Novice Guide To Buying A Linux Laptop
• 10 Greatest Open Source Software Of 2009
• Top 5 Email Client For Linux, Mac OS X, and Windows Users
• Top 20 OpenSSH Server Best Security Practices
• Top 10 Open Source Web-Based Project Management Software
• Top 5 Linux Video Editor Software