HostOnNet Blog

MySQL access from command line on Ubuntu

First connect to your Ubuntu server using ssh and run below command

mysql -u root -p

It will give you a password prompt as follow:
Screenshot from 2014-06-10 14:58:49

Enter root password there and press enter key, then it will be connected successfully. It will show below output:
Screenshot from 2014-06-10 15:02:45

To see all databases, run

show databases

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| asterisk           |
| asteriskcdrdb      |
| db1                |
| mysql              |
| otrs               |
| performance_schema |
+--------------------+
7 rows in set (0.00 sec)

mysql>

To see all tables in a database, first you need to switch to the database using “use” command. Then “show tables

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

mysql>

To list User and Host column from user table in mysql database, run

mysql> select Host, User from user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| %         | fbuser           |
| 127.0.0.1 | root             |
| ::1       | root             |
| localhost | asterisk         |
| localhost | asteriskuser     |
| localhost | debian-sys-maint |
| localhost | fbuser           |
| localhost | otrs             |
| localhost | root             |
+-----------+------------------+
9 rows in set (0.00 sec)

mysql>

About Vineesh Mohan

A Technical Support specialist with strong customer service and good communication skills. I live in the city of Cochin, which known as the Queen of Arabian Sea.
Posted in Database, MySQL, Ubuntu