Some helpful database commands in MySQL / MariaDB

I personally am NOT a DBA. But as a one person company I do everything from product planning, feature prioritization, architecture, documentation, development, deployment, DBA and monitoring. Below are some helpful commands in MySQL / MariaDB that I found useful when I do DBA activities. This is more of a blog post for my own reference.

Connect to database:

mysql -u root -p

Get a list of databases:

show databases;

Use a particular database

use <DATABASE_NAME>;

Show tables in current database

show tables;

Get a list of all the stored procedures in all the databases

SHOW PROCEDURE STATUS;

or

SELECT 
    routine_schema as "Database",
    routine_name
FROM 
    information_schema.routines
WHERE 
    routine_type = 'PROCEDURE'
ORDER BY 
    routine_schema ASC, 
    routine_name ASC;

Get a list of stored procedures in all the databases that match a certain string pattern.

SHOW PROCEDURE STATUS LIKE '%pattern%';

or

SELECT 
    routine_schema as "Database",
    routine_name
FROM 
    information_schema.routines
WHERE 
    routine_type = 'PROCEDURE'
    and routine_name LIKE '%pattern%'
ORDER BY 
    routine_schema ASC, 
    routine_name ASC;

The second select statement can be modified to limit to a certain database by including filters in the where clause on “routine_schema”.

SELECT 
    routine_schema as "Database",
    routine_name
FROM 
    information_schema.routines
WHERE 
    routine_type = 'PROCEDURE'
    and routine_name LIKE '%pattern%'
    and routine_schema = 'database_name'
ORDER BY 
    routine_schema ASC, 
    routine_name ASC;

Hoping this helps someone!

Never trust the scammers who claim to have exchanged identities and who use bank accounts on other people’s name. They are either identity thieves / impersonators / anonymous hackers. Example – Erra’s, Thota’s, Bojja’s, Uttam’s, Srinivas’s etc… the R&AW hackers / spies.

–

Mr. Kanti Kalyan Arumilli

B.Tech, M.B.A

Facebook

LinkedIn

Threads

Instagram

Youtube

Founder & CEO, Lead Full-Stack .Net developer

ALight Technology And Services Limited

ALight Technologies USA Inc

Youtube

Facebook

LinkedIn

Phone / SMS / WhatsApp on the following 3 numbers:

+91-789-362-6688, +1-480-347-6849, +44-07718-273-964

+44-33-3303-1284 (Preferred number if calling from U.K, No WhatsApp)

kantikalyan@gmail.com, kantikalyan@outlook.com, admin@alightservices.com, kantikalyan.arumilli@alightservices.com, KArumilli2020@student.hult.edu, KantiKArumilli@outlook.com and 3 more rarely used email addresses – hardly once or twice a year.