Getting database information

Once you connect to MySQL Server and/or switch to a database, and you may use various commands to get information about database environment and your session.

Current userid and local hostname
```
%%sql
SELECT USER();
```
Connection Id of database
```
%%sql
SELECT CONNECTION_ID();
```
Database name you are connected to
```
%%sql
SELECT DATABASE();
```
MySQL Server version
```
%%sql
SELECT VERSION();
```
List of all databases in MySQL Server
```
%%sql
SHOW DATABASES;
```

MySQL Server port

%%sql
SHOW VARIABLES WHERE Variable_name = 'port';

MySQL Server hostname

%%sql
SHOW VARIABLES WHERE Variable_name = 'hostname';

All information in one go using 'status' command.

In the console, login to mysql,

mysql -h cxln2.c.thelab-240901.internal -u sqoopuser -pNHkkP876rp

and check status as follows:

use retail_db;
status;

you could see:

mysql  Ver 15.1 Distrib 5.5.47-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          25175
Current database:       retail_db
Current user:           sqoopuser@ip-172-##-##-179.ec2.internal
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MySQL
Server version:         5.6.30 MySQL Community Server (GPL)
Protocol version:       10
Connection:             ip-172-##-##-154 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 81 days 14 hours 57 min 30 sec

Threads: 25  Questions: 2214929171  Slow queries: 258  Opens: 217072  Flush tables: 1  Open tables: 431  Queries per second avg: 314.074

Previous Index

SQL with Jupyter Notebook Tutorial

Getting database information

XP

Congratulations on completing topic - SQL with Jupyter Notebook Tutorial

Please login to comment

Be the first one to comment!