Master MySQL/MariaDB: Core Components, Commands, and Data Types Explained

Introduction MySQL/MariaDB is an open‑source relational database management system prized for its small footprint, speed, and low total cost of ownership, making it a popular choice for small‑to‑medium web sites.

Architecture

MySQL Core Components

Connection pool: authentication, thread reuse, connection limits, memory checks, caching

SQL interface: DDL, DML, basic relational abstractions

Parser: query parsing, object permission checks

Optimizer: access paths, performance statistics

Caches and buffers: I/O performance tools for storage engines

Storage engines: MyISAM, InnoDB (XtraDB), Memory, Merge, Federated, CSV, Archive, Blackhole, Aria, SphinxSE, TokuDB

Internal Structure

Program Types and Command Options

Server programs : mysqld, mysqld_safe, mysqld_multi – start and listen on sockets.

Client programs : mysql, mysqlbinlog, mysqladmin, mysqldump – connect via the MySQL protocol.

Utility programs : myisamchk – run on the host where the server resides for maintenance.

Common Client Options

-u, --user      # specify login user

-h, --host      # specify host

-p, --password  # specify password

--protocol={tcp|socket|memory|pipe}  # choose protocol

-P, --port      # default 3306

--socket        # local socket file

--compress      # enable compression

-D, --database  # default database after connect

-H, --html      # output HTML

-X, --xml       # output XML

--safe-updates  # reject UPDATE/DELETE without WHERE

Interactive Client Commands

mysql> help          # list all commands

mysql> \?           # same as help

mysql> \c           # cancel command

mysql> \g           # send command

mysql> \G           # send command, vertical output

mysql> \q           # quit

mysql> \!           # run shell command

mysql> \s           # show server status

mysql> \u db_name   # set default database

mysqladmin Utility

mysqladmin [options] command [arg] ...

create DB_Name – create database

drop DB_Name – delete database

status – show brief status

flush-privileges – reload grant tables

flush-hosts – clear DNS cache

flush-logs – rotate logs

ping – test server connectivity

processlist – list active threads

shutdown – stop server

start-slave / stop-slave – control replication threads

Command‑Line Editing Shortcuts

Ctrl+a – move cursor to line start

Ctrl+e – move cursor to line end

Ctrl+w – delete previous word

Ctrl+u – delete from line start to cursor

Ctrl+y – paste deleted text

Prompt Symbols

mysql>   # waiting for command

->       # continuation line

'>       # incomplete single quote

">       # incomplete double quote

`>       # incomplete back‑tick

/*>       # inside comment, end with */

SQL Language Components

DDL – Data Definition Language

DCL – Data Control Language (e.g., GRANT)

DML – Data Manipulation Language

Integrity definitions – constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK

Views – virtual tables defined by SELECT statements

Transaction control

Data Dictionary and Metadata

The data dictionary (system catalog) stores metadata about databases, tables, columns, types, constraints, views, users, privileges, and statistics. Core metadata schemas include information_schema, mysql, and performance_schema.

Data Types and Modifiers

Character types : CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT; binary types BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB. Modifiers: NULL, NOT NULL, DEFAULT, CHARACTER SET, COLLATION, % and _ wildcards.

Integer types : TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Modifiers: UNSIGNED, NULL, NOT NULL, DEFAULT, AUTO_INCREMENT (requires NOT NULL and usually PRIMARY/UNIQUE).

Floating‑point types : FLOAT, DOUBLE. Modifiers: NOT NULL, NULL, DEFAULT, UNSIGNED.

Boolean : alias of TINYINT(1).

Date/Time types : DATE, TIME, DATETIME, TIMESTAMP, YEAR(2/4). Modifiers: NULL, NOT NULL, DEFAULT VALUE.

Enumerated types : ENUM('a','b'), SET('a','b','c'). Modifiers: NULL, NOT NULL, DEFAULT ''.

SQL Modes and Server Variables

SQL mode controls server behavior. Common modes: TRADITIONAL, STRICT_TRANS_TABLES, STRICT_ALL_TABLES.

Server variables are either GLOBAL (affect all sessions) or SESSION (affect only the current session). GLOBAL changes apply to new sessions; SESSION changes take effect immediately.

Variables can be modified dynamically (SET GLOBAL/SESSION) or statically (edit my.cnf and restart). Not all variables support dynamic changes.

Viewing Variables

SHOW {GLOBAL|SESSION} VARIABLES [LIKE 'pattern'];

SELECT @@{GLOBAL|SESSION}.variable_name;

Modifying Variables

SET GLOBAL variable_name='value';

SET SESSION variable_name='value';

To make a change permanent, add it to the [mysqld] section of the configuration file, e.g., sql_mode='STRICT_ALL_TABLES'.

Case Sensitivity

SQL keywords and function names are case‑insensitive.

Database, table, index, and view names depend on the underlying OS/filesystem.

Stored procedures, functions, and events are case‑insensitive; triggers are case‑sensitive.

Table aliases are case‑insensitive.

String data is case‑sensitive only for BINARY, BLOB, VARBINARY types.

DDL Operations

Database Commands

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name;

DROP {DATABASE|SCHEMA} [IF EXISTS] db_name;

ALTER {DATABASE|SCHEMA} [IF EXISTS] db_name;

Table Commands

CREATE TABLE [IF NOT EXISTS] tbl_name (col_def, ..., PRIMARY KEY(col), UNIQUE(col), INDEX(col)) [table_options];

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...;

ALTER TABLE tbl_name ADD [COLUMN] col_name col_def [FIRST|AFTER col];

ALTER TABLE tbl_name DROP [COLUMN] col_name;

ALTER TABLE tbl_name MODIFY [COLUMN] col_name col_def [FIRST|AFTER col];

ALTER TABLE tbl_name CHANGE [COLUMN] old_name new_name col_def [FIRST|AFTER col];

ALTER TABLE tbl_name RENAME TO new_tbl_name;

RENAME TABLE old_name TO new_name;

Conclusion

This overview covers the essential knowledge of MySQL/MariaDB; future articles will delve deeper into advanced topics.