> For the complete documentation index, see [llms.txt](https://www.anandisheladiya.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.anandisheladiya.com/skills-and-experience/database/mysql.md).

# MySQL

### **Introduction**

MySQL is an **open-source relational database management system (RDBMS)** that uses **Structured Query Language (SQL)** for managing and manipulating data. It is widely used for web applications, enterprise applications, and data-driven services.

### **Key Features of MySQL**

1. **Relational Database** – Stores data in tables with structured relationships.
2. **ACID Compliance** – Ensures reliability through transactions.
3. **High Performance** – Supports indexing, caching, and optimized queries.
4. **Scalability** – Works well with small and large-scale applications.
5. **Security** – Provides **user authentication, role-based access control (RBAC), and encryption**.
6. **Replication & High Availability** – Master-slave and master-master replication for failover support.
7. **Stored Procedures & Triggers** – Supports procedural programming within the database.

### **MySQL Architecture**

The MySQL architecture consists of multiple components:

1. **Client Layer** – Applications communicate using MySQL drivers.
2. **SQL Parser & Optimizer** – Parses and optimizes queries for efficiency.
3. **Storage Engine Layer** – Manages how data is stored and retrieved.
   * **InnoDB** (default) – ACID-compliant, supports transactions.
   * **MyISAM** – Fast for read-heavy operations but lacks transactions.
4. **Buffer Pool & Query Cache** – Speeds up query execution.
5. **Replication & Clustering** – Provides **failover and load balancing**.

<figure><img src="/files/aBXFZSXa71kfq3Vf3keZ" alt=""><figcaption></figcaption></figure>

### **Basic MySQL Operations**

#### **1. Creating a Database**

```sql
CREATE DATABASE my_database;
USE my_database;

```

2. Creating a Table

```sql
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

```

3. Inserting Data

```sql
INSERT INTO users (username, email, password)
VALUES ('john_doe', 'john@example.com', 'hashed_password');

```

4. Retrieving Data

```sql
SELECT * FROM users WHERE email = 'john@example.com';

```

5. Updating Data

```sql
UPDATE users SET password = 'new_hashed_password' WHERE username = 'john_doe';

```

6. Deleting Data

```sql
DELETE FROM users WHERE username = 'john_doe';

```

### **MySQL Use Cases**

* **Web Applications** – Used by Facebook, Twitter, and WordPress.
* **E-Commerce Platforms** – Manages transactions, orders, and users.
* **Enterprise Applications** – Suitable for CRM, ERP, and HR systems.
* **Data Warehousing** – Stores structured data for reporting and analytics.