MySQL | Connecting to a Database from the Console
This article explains how to connect to MySQL from a console or telnet environment.
Basic Connection Method
$ mysql -u {username} -p{password} {database_name}
Note: There is no space in -p{password}. If the password is 1234, enter -p1234.
Other Connection Methods
mysql -u {username} -p {database_name}
mysql -u root (when no account or password has been created)
mysql -u root -p (when the root password exists)
Run a Query Saved in an External File
Use the following command to run a query saved in an external file.
$ mysql -u {UserName} -p{Password} {Database} < {external_file_name}
This method is mainly used to import a file exported as SQL statements.
Example
First, create an external file at /Users/devkuma/test.sql.
SELECT * FROM store_information
Move to the directory where the mysql command is stored.
$ cd /usr/local/mysql/bin/
Run the external SQL file with the command.
$ ./mysql -u root -p1234 devkuma < /Users/devkuma/test.sql
Warning: Using a password on the command line interface can be insecure.
region_name store_name sales txn_date
West Los Angeles 1500 1999-01-05
West San Diego 250 1999-01-07
West Los Angeles 300 1999-01-08
East Boston 700 1999-01-08
View the Current Status
mysql> status
Example
mysql> status
--------------
./mysql Ver 14.14 Distrib 5.6.17, for osx10.7 (x86_64) using EditLine wrapper
Connection id: 1068
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.17 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 1 hour 9 min 39 sec
Threads: 5 Questions: 4186 Slow queries: 0 Opens: 128 Flush tables: 1 Open tables: 107 Queries per second avg: 1.001
--------------
List Databases
mysql> show databases;
Example
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| devkuma |
| mysampledata |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
Select the Database to Use
mysql> use {database_name}
Example
mysql> use devkuma
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
List Tables
mysql> show tables;
Example
mysql> show tables;
+----------------------------+
| Tables_in_devkuma |
+----------------------------+
| geography |
| store_information |
+----------------------------+
46 rows in set (0.00 sec)
Exit the Connection
mysql> exit
Bye
Or press Ctrl+C.