Master MySQL: From Database Basics to Full Installation Guide

1. The Emergence of Databases

A database is simply a place to store data; for user authentication, the system must compare entered credentials with stored ones, which originally used plain text files that became inefficient as data grew.

Major drawbacks of text‑based databases include:

Data redundancy and inconsistency

Difficult data access

Data isolation after file splitting

Integrity issues

Atomicity problems

Concurrent access challenges

Security limitations

2. Database Management Systems

To manage data more precisely, a middle layer called a Database Management System (DBMS) was created, providing APIs, command‑line interfaces, permission control, and efficient data handling.

3. Database Models

Common models include hierarchical, network, relational (proposed by E.F. Codd in 1975 and implemented by Oracle), and NoSQL. NoSQL systems often trade off CAP properties, satisfying only two of Consistency, Availability, and Partition tolerance.

4. Relational DBMS Architecture

Key components:

Storage files: data files, index files, transaction logs.

Disk space manager: manages disk storage and indexing.

Buffer manager: caches frequently accessed data in memory.

Access method interface: handles SQL statement execution.

Transaction manager: ensures ACID properties (Atomicity, Consistency, Isolation, Durability).

Lock manager: prevents concurrent conflicts.

Recovery manager: restores data from transaction logs after crashes.

SQL query engine: consists of parser, optimizer, planner, executor, thread pool, client API, ODBC, and follows ANSI SQL standards.

5. MySQL Server Installation and Basic Usage

Installation sources:

System‑provided RPM

Official MySQL RPM (not recommended)

Generic binary package (recommended)

Source compilation (recommended)

Typical binary installation steps:

# wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.44-linux2.6-x86_64.tar.gz

# tar -xf mysql-5.5.44-linux2.6-x86_64.tar.gz

# cp -r mysql-5.5.44-linux2.6-x86_64 /usr/local/

# ln -sv mysql-5.5.44-linux2.6-x86_64 /usr/local/mysql

Create MySQL system user and group:

# groupadd -r mysql

# useradd -g mysql -r -s /sbin/nologin mysql

Set ownership, initialize the database, create LVM for data storage, configure /etc/fstab, and start the service:

# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mydata --user=mysql

# cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld

# chkconfig --add mysqld

# service mysqld start

Update PATH and verify installation with mysql -u root -p.

6. MySQL Client and Command Basics

Configuration file /etc/my.cnf contains three sections: [mysql] – client‑specific settings [mysqld] – server settings [client] – common client options

Common client commands (no terminator needed): help, status, quit. Use

to send a command,

for vertical output.

Server‑side commands include SELECT, SHOW DATABASES, USE, SHOW TABLES, CREATE DATABASE, DROP DATABASE, etc.

Execution modes: interactive shell, batch mode ( mysql < script.sql), and non‑login execution ( mysql -e "SQL").

7. Important MySQL Concepts

Constraints define permissible values for columns, such as primary key, foreign key, unique key, check, and NOT NULL.

Keys:

Primary key – uniquely identifies each row.

Candidate key – any column(s) that could serve as a primary key.

Unique key – enforces uniqueness but may allow NULL.

Foreign key – references a primary key in another table.

Table creation requires specifying column names, data types, and constraints. Common data types include:

Character: CHAR, VARCHAR, BINARY, VARBINARY, TEXT, BLOB Numeric: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE Date/Time: DATE, TIME, DATETIME, TIMESTAMP Enumerated: ENUM,

SET