MySQL

See SQL Queries for query examples

---

Access the command line

enter without user specified

mysql

enter for specific user and prompt for password

mysql -u _USERNAME_ -p

---

List Commands

List databases

show databases;

Connect to database

use _DATABASE_NAME_;

Database in use

SELECT DATABASE() FROM DUAL;

OR

status;

List of users

SELECT User FROM mysql.user;

List tables

show tables from _DATABASE_NAME_;

OR

use _DATABASE_NAME_;
show tables;

List tables with size in MB

select TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` from information_schema.TABLES where TABLE_SCHEMA = "_DATABASE_NAME_" order by (DATA_LENGTH + INDEX_LENGTH) desc;

List tables with row counts

select table_name, table_rows from information_schema.tables where table_schema='_DATABASE_NAME_';

List tables with specified column name

select distinct table_name from information_schema.columns where column_name in ('_COLUMN_NAME_') and table_schema='_DATABASE_NAME_';

List of tables with foreign key IDs -- LIKE ('%_id')

select distinct table_name from information_schema.columns where column_name LIKE ('%_id') and table_schema='exhibits_staging';

List tables and show last updated date

select max(updated_at) from _TABLE_NAME_;
select id, updated_at from _TABLE_NAME_ where updated_at between '2021-12-16 00:00:00' and '2021-12-16 23:59:59'; # substituting the date returned by the first query for 2021-12-16

List tables with column encodings and collations

for all tables

SELECT table_name, column_name, character_set_name, collation_name, column_type FROM information_schema.`COLUMNS` WHERE table_schema = '_DATABASE_NAME_';

for specific table

SELECT table_name, column_name, character_set_name, collation_name, column_type FROM information_schema.`COLUMNS` WHERE table_schema = '_DATABASE_NAME_' and table_name='_TABLE_NAME_';

List columns in a table

show columns from _TABLE_NAME_ in _DATABASE_NAME_;

OR

use _DATABASE_NAME_;
show columns from _TABLE_NAME_;

---

Create & Delete commands

Create database

CREATE DATABASE _DATABASE_NAME_ DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Delete database

DROP DATABASE _DATABASE_NAME_;

Add user

CREATE USER '_USERNAME_'@'localhost' IDENTIFIED BY '_USERPASSWORD_';

---

Privileges & Access

Give user permissions for a specific database

use _DATABASE_NAME_;
GRANT ALL ON _DATABASE_NAME_.* TO _USERNAME_@localhost IDENTIFIED BY '_USERPASSWORD_';

Queries for table values

List values for a specific column

select _COLUMN_NAME_ from _DATABASE_NAME_._TABLE_NAME_;

OR

use _DATABASE_NAME_;
select _COLUMN_NAME_ from _TABLE_NAME_;

---

Working with threads and connection pools

How many connections are in use?

show status where variable_name = 'threads_connected';

What is running in the connections?

show processlist;

Get kill command to kill long running processes

select group_concat(concat('KILL ', id, ';') separator ' ') from information_schema.processlist where user = '_USER_' and Time > '300';

NOTE: Replace _USER_ with a user that has privileges to execute the query.

Submit the results of the select query to kill the long running processes.