MySQL Database Access from Command Line in Linux

In this article, there are descriptions on how to connect to MySQL Database Server access. As we can see, the connection process to MySQL Database Server is actually can be done in an operating system based on GUI (Graphical User Interface) easily by using several MySQL Database Editor like MySQL Workbench, MySQL Enterprise Manager, SQLYog, Navicat or any other types of similar software, applications.

But when it comes to an operating system based on CLI (Command Line Interface), it is necessary to know several steps which is involved to be included so that the connection process to MySQL Database Server is can be achieved. Below are those process :

1. Make sure the MySQL Database Server’s service is alive. It can be done by checking the service whether it exists or not. Below is the command to achieve it :

cat /etc/service | grep 3306

Description : 
cat : It is a command which is execute to print the content of a file in text form and print it on the standard output such as monitor screen. 
/etc/service : It is a file named service which is located in /etc which is used as a configuration file for mapping service name to its corresponding port number
| : It is a sign which is a pipe sign used to redirect the output which is generated from the command before the '|' sign
3306 : It is the default number for MySQL Database Server port.

By executing it in the bash prompt of a terminal, we can check whether MySQL Database Server’s service exist or not. Below is the execution of the above command in a bash prompt :

user@hostname:~$ cat /etc/services | grep 3306
mysql           3306/tcp
mysql           3306/udp
user@hostname:~$

2.The above output describes that MySQL Database Server’s port number listens or operates in port 3306. So, based on the information given which is used to check whether it is active or not, just try to execute the following command :

netstat -tulpn | grep 3306

user@hostname:~$ netstat -tulpn | grep 3306
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      -
user@hostname:~$

Description : 
netstat : It is a command used to print network connections, routing tables, interface statistics and in this context is to check the active's port number which handle a certain service in this context is MySQL Database Server
-t  : It is a parameter which is used to filter the output limited to an active service or port which is represented by TCP packet. 
-u : It is a parameter which is used to filter the output limited to an active service or port which is represented by UDP packet. 
-l : It is a parameter which is used to filter the output limited to an active service or port which is in listening state
-p : It is a parameter which is used to display information of PID (Process ID) and also the name of the program
-n : It is a parameter which is used to display information of host, port in a numerical addresses.

To check whether the service of MySQL Database Server is listening to handle request or not, there is another way to check it  which is  by executing the following command :

service mysql status

Below is the output of the executed command :

user@hostname:~$ service mysql status  
● mysql.service - MySQL Community Server 
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) 
   Active: active (running) since Jum 2016-09-23 10:24:48 WIB; 2 days ago 
  Process: 1419 ExecStartPost=/usr/share/mysql/mysql-systemd-start post (code=exited, status=0/SUCCESS) 
  Process: 1331 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS) 
 Main PID: 1416 (mysqld) 
    Tasks: 28 
   Memory: 1.0M 
      CPU: 1min 49.588s 
   CGroup: /system.slice/mysql.service 
           └─1416 /usr/sbin/mysqld 
 
Warning: Journal has been rotated since unit was started. Log output is incomplete or unavailable. 
user@hostname:~$ 

The above output stated that MySQL server is active and it has already started.

3. After finding the MySQL Database Server’s service is active, we can directly access MySQL command console by executing the following command in the bash prompt :

mysql -uusername -p

Below is the output of the command executed :

user@hostname:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Assumed that MySQL server has already installed and the correct information passed as login information is correct including the username and its corresponding password, the connection to MySQL Database Server to the MySQL command console as shown above is a success.

5 thoughts on “MySQL Database Access from Command Line in Linux”

Leave a Reply