SQL: What is database connection?

 

In general, you will connect to SQL server by providing following information from SQL Client.

a.   Server name

b.   Port where SQL server is listening

c.    Username

d.   Password

e.   Database to connect

 

Once the server receives details from client, it verifies the credentials and establish a new connection with client.

 

For example, when you connect to mysql server using mysql client, you will see a mysql connection id in the response.

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.21 Homebrew

Copyright (c) 2000, 2020, 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>

As you see the output, connection id 14 is given to this new connection.

 

You can see all the connections information by executing ‘show processlist;’ command.

mysql> show processlist;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time  | State                  | Info             |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 35371 | Waiting on empty queue | NULL             |
|  8 | root            | localhost | sys  | Sleep   | 13922 |                        | NULL             |
| 14 | root            | localhost | NULL | Query   |     0 | starting               | show processlist |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
3 rows in set (0.00 sec)

How long this connection is alive?

This connection is alive until

a.   Application explicitly close/release the connection

b.   Server close the connection

When you execute the command ‘exit’ or ‘quit’ from mysql client, connection will be closed.

Previous                                                    Next                                                    Home