Abhishek Gupta

Posted on Dec 9, 2025 • Edited on Dec 21, 2025

Frequently Asked Questions for Exam Preparation (RDBMS USING MySQL)

#mysql #sql #database #beginners

Q1: What is MySQL

MySQL is an open-source Relational Database Management System (RDBMS) that is used to store, manage, and organize data in the form of tables (rows and columns).

It uses SQL (Structured Query Language) to perform operations such as inserting, updating, deleting, and retrieving data.

⭐ Key Features of MySQL

Open-source & free to use
Fast and reliable
Stores data in structured tables
Supports large databases
Highly secure
Works on all major operating systems
Used in web applications like Facebook, YouTube, WordPress

⭐ Why MySQL is Popular?

Easy to learn and use
Cross-platform (Windows, Linux, macOS)
Supports multi-user environments
Works well with programming languages (PHP, Java, Python)
High performance for large data

Q2: Describe five DML commands with an example.

DML commands are used to insert, update, delete, and retrieve data stored in a database table.
These commands affect the data inside tables, not the table structure.

Below are the five most important DML commands with clear explanations and examples.

🟦 1. INSERT

Used to add new records (rows) into a table.

✔ Example:

INSERT INTO students (id, name, age)
VALUES (1, 'Abhishek', 20);

🟦 2. SELECT

Used to retrieve (fetch) data from a table.

✔ Example:

SELECT name, age FROM students;

🟦 3. UPDATE

Used to modify existing data in a table.

✔ Example:

UPDATE students
SET age = 21
WHERE id = 1;

🟦 4. DELETE

Used to remove rows from a table.

✔ Example:

DELETE FROM students
WHERE id = 1;

🟦 5. MERGE / REPLACE (MySQL alternative)

Used to insert a row if it does not exist
OR
update the row if it already exists (similar to UPSERT).

MySQL commonly uses REPLACE for this.

✔ Example:

REPLACE INTO students (id, name, age)
VALUES (1, 'Abhishek', 22);

⭐ Q3:- Explain MySQL Client–Server Architecture

MySQL works on a Client–Server Architecture, which means the database system is divided into two separate parts:
the Client, which sends requests, and the Server, which processes those requests and manages the data.

This architecture allows multiple users and applications to access the same database efficiently and securely.

🟦 1. MySQL Client

The MySQL Client is any software or application that sends SQL commands to the MySQL server.

Examples of MySQL Clients

MySQL Command-Line Client
MySQL Workbench
Web applications using PHP, Java, Python, Node.js, etc.
Desktop or mobile applications connected to MySQL

Role of MySQL Client

Sends SQL queries such as SELECT, INSERT, UPDATE, DELETE
Receives query results from the server
Displays data to the user

📌 Important Point (for exam):
Clients do not store data.
They only request data and receive results from the server.

🟦 2. MySQL Server

The MySQL Server is the core component of the architecture.
It runs as a background process called mysqld.

🔄 Working of MySQL Client–Server Architecture

Client sends an SQL query to the MySQL server
Server authenticates the user
Query parser checks the syntax
Query optimizer selects the best execution plan
Execution engine executes the query
Storage engine accesses data from disk
Result is sent back to the client

✅ Advantages of Client–Server Architecture

Supports multiple users simultaneously
Provides better security through centralized control
Improves performance with query optimization
Enables scalability and distributed access
Ensures data integrity and consistency

🟦 3. How the Architecture Works

(Explain in 4 simple steps)

✔ Step 1 — Client sends a SQL query

Example:

SELECT * FROM students;

✔ Step 2 — Server checks login & permissions

✔ Step 3 — Server processes the query

Parser checks SQL
Optimizer finds best execution plan
Executor runs the query

✔ Step 4 — Server sends result back to the client

🟦 4. Simple Diagram (Perfect for Notes)

Client (Sends SQL)
       |
       v
MySQL Server 
(Parser → Optimizer → Executor)
       |
       v
Storage Engine (InnoDB)
       |
       v
Client (Receives Result)

🟦 5. Advantages of Client–Server Architecture

Many clients can connect at the same time
Centralized database management
High security & performance
Works over network/internet
Easy to maintain and scale

⭐Q4 Explain Locking in MySQL. Discuss the Types of Locking.

Locking in MySQL is a mechanism used to control access to data when multiple users try to read or modify the same data at the same time.

When a lock is applied, it prevents other users from making changes until the first user finishes the operation.
This ensures data safety, accuracy, and consistency.

✔ Why Locking is Important?

Prevents two users from modifying the same row together
Avoids data corruption
Ensures correct results in transactions
Maintains database consistency in multi-user environments

🟦 Types of Locking in MySQL

MySQL provides five major types of locking, mainly depending on storage engines like InnoDB and MyISAM.

⭐ 1. Table-Level Locking

The entire table is locked during a read or write operation.
Used by: MyISAM storage engine
Simple but slower when many users try to access the table.

✔ Example:

If one user updates a table → others must wait.

✔ Pros:

Easy to implement
Good for read-heavy operations

✔ Cons:

Low concurrency (many users cannot update together)

⭐ 2. Row-Level Locking

Only the specific rows being read or updated are locked.
Used by: InnoDB (default engine)

✔ Why it’s better?

Multiple users can work on different rows at the same time.

✔ Pros:

High concurrency
Better performance

✔ Cons:

Slightly more complex for the server to manage

⭐ 3. Page-Level Locking

(A combination of table-level and row-level)

Locks a group of rows (called a page) at once.
Used by older storage engines (not common now)

✔ Advantage:

Faster than row-level in some cases

✔ Disadvantage:

More locking conflict than row-level

⭐ 4. Internal Locking

These locks are handled automatically inside MySQL.
Protects internal server data structures like:
- Cache
- Indexes
- Buffers

Users do not control internal locking; MySQL manages it itself.

⭐ 5. External Locking

These locks use the operating system’s file-level locks.
Mainly used to ensure MySQL and other programs do not access the same file simultaneously.

This is used less in modern MySQL installations.

⭐ Deadlock (Important Part of Locking)

A deadlock happens when:

Transaction A waits for B
Transaction B waits for A

→ Both are stuck forever.

✔ InnoDB automatically:

Detects deadlocks
Cancels one transaction
Releases locks

⭐ Q5: What are the various methods for making backup in MySQL? What is BACKUP TABLE? Is it useful?

📘 Backup in MySQL (Easy Explanation)

A backup in MySQL means creating a safe copy of your database so that you can recover it in case of data loss, server crash, accidental delete, or corruption.

MySQL provides many ways to take backups, depending on the requirement.

🟦 Various Methods for Making Backup in MySQL

⭐ 1. Logical Backup (mysqldump)

This is the most common backup method.

Creates a .sql file containing SQL commands (CREATE TABLE, INSERT).
Easy to read, edit, move, and restore.

Example:

mysqldump -u root -p school > school_backup.sql

⭐ 2. Physical Backup

This backup copies actual database files from disk.

Copies .ibd, .frm, .myd, .myi, and log files.
Useful for large databases because it is faster.

Types:

Cold backup → take backup when MySQL server is stopped
Hot backup → take backup while MySQL is running (needs extra tools)

⭐ 3. Binary Log Backup

MySQL stores all changes (INSERT, UPDATE, DELETE) in the binary log.

Binary logs are used for:

Point-in-time recovery
Replication

Useful for restoring data up to the last executed command.

⭐ 4. Hot Backups Using Tools

These allow backup without stopping the server.

Tools:

Percona XtraBackup
MySQL Enterprise Backup

Best option for live production databases.

⭐ 5. Export Data Using SELECT INTO OUTFILE

Exports table data to a text or CSV file.

SELECT * FROM students
INTO OUTFILE 'students.csv';

Used for quick export of large tables.

⭐ 6. Import Data Using LOAD DATA INFILE

Fastest method to import huge files:

LOAD DATA INFILE 'students.csv'
INTO TABLE students;

⭐ 7. Backup Using GUI Tools

phpMyAdmin
MySQL Workbench
DBeaver

Easy interface → ideal for beginners.

⭐ 3. What is Recovery?

Recovery is the process of restoring the database using backup files after a failure.

✔ Why Recovery is needed?

Server crash
Table corruption
Data deletion
Hardware failure

🟦 Q6 What is BACKUP TABLE?

BACKUP TABLE is an old MySQL command used to back up only MyISAM tables.

✔ Syntax:

BACKUP TABLE table_name TO '/backup/';

✔ How it works:

Copies the table files (.frm, .MYD, .MYI) to a backup location.

🟦 Is BACKUP TABLE useful today?

❌ Not very useful now.

Reasons:

Works only on MyISAM (old engine).
Modern MySQL uses InnoDB, which does NOT support this command.
Tools like mysqldump, XtraBackup, and binary logs are much better.

✔ Useful only for:

Very old MySQL versions
Small MyISAM tables

What are the Various Logs Used in MySQL

MySQL maintains several types of logs, each having a specific role:

Log Type	Purpose
Error Log	Records server errors, warnings, and startup/shutdown details
General Query Log	Records all SQL queries executed by the server
Binary Log (Binlog)	Records all changes made to the database
Slow Query Log	Records slow-running queries
Relay Log	Stores replicated data from master to slave
Redo Log	Ensures data durability and crash recovery
Undo Log	Helps rollback transactions
DDL Log	Records metadata changes (table structure)

🟦 1. Error Log

What is Error Log?

The Error Log records:

Errors
Warnings
Informational messages related to MySQL server operation

It is the most important log file in MySQL.

Importance of Error Log

Identifies startup and shutdown issues
Detects configuration mistakes
Records critical system failures
Helps in crash recovery
Essential for server troubleshooting

Specific Uses of Error Log

Finding why MySQL server failed to start
Detecting corrupted tables
Monitoring storage engine problems
Tracking authentication and permission errors
Diagnosing memory, disk, or file access issues

Example Error Log Entries

[ERROR] Can't open the mysql.plugin table.
[Warning] Aborted connection 12 to db: 'test'
[Note] Server shutdown complete

🟦 2. General Query Log

What is General Query Log?

The General Query Log records every SQL statement sent to the MySQL server, including:

SELECT
INSERT
UPDATE
DELETE
Connection and disconnection events

Importance of General Query Log

Helps in query debugging
Monitors user activity
Useful for security auditing
Assists in application behavior analysis

⚠️ Usually disabled in production because it affects performance.

Specific Uses of General Query Log

Tracking executed SQL statements
Identifying unauthorized queries
Debugging application-level SQL issues
Testing and development analysis

🟦 3. Binary Log (Binlog)

What is Binary Log?

The Binary Log records all changes made to the database, such as:

INSERT
UPDATE
DELETE
CREATE / DROP operations

❌ It does not record SELECT queries.

Importance and Use

Used for replication
Enables point-in-time recovery
Helps in data recovery after crash
Used for backup synchronization

🟦 4. Slow Query Log

What is Slow Query Log?

The Slow Query Log records SQL queries that:

Take longer than a defined execution time
Do not use indexes efficiently

Importance and Use

Identifies performance bottlenecks
Helps optimize slow queries
Improves overall database performance

🟦 5. Relay Log

What is Relay Log?

The Relay Log is used in replication.
It stores data changes received from the master server before applying them on the slave server.

Importance and Use

Ensures accurate replication
Acts as a temporary storage in replication
Helps recover replication failures

🟦 6. Redo Log

What is Redo Log?

The Redo Log records data modifications before they are written to disk.

Importance and Use

Ensures durability (ACID property)
Helps recover committed transactions after a crash
Used by InnoDB storage engine

🟦 7. Undo Log

What is Undo Log?

The Undo Log stores previous versions of data.

Importance and Use

Used to rollback transactions
Supports MVCC (Multi-Version Concurrency Control)
Maintains data consistency

🟦 8. DDL Log

What is DDL Log?

The DDL Log records metadata operations like:

CREATE TABLE
ALTER TABLE
DROP TABLE

Importance and Use

Ensures consistency during structural changes
Helps recover interrupted DDL operations

⭐ Q9:- Explain Information Scheme in MySQL

Information Schema is a system database in MySQL that stores metadata, i.e., information about the structure of databases.
It provides details about databases, tables, columns, indexes, and privileges present in the MySQL server.

It is read-only and does not store actual user data.
The data in Information Schema is generated automatically by MySQL.

Uses of Information Schema

To view database structure
To get table and column details
To check indexes and keys
To help database administration

Below is your answer written strictly in Question–Answer format, exam-ready, complete, and easy to memorize.
You can write it as it is in the exam.

Q10. What is Replication?

Replication in MySQL is the process of automatically copying data from one MySQL server (Master) to one or more other servers (Slaves).

Replication is mainly used to:

Improve data availability
Increase read performance
Provide backup and recovery
Reduce load on the main server

Q11. What is Replication Architecture in MySQL?

Replication in MySQL follows a Master–Slave architecture.

In this architecture:

Master Server performs all write operations and records changes.
Slave Server copies data from the master and applies those changes.

Components of Replication Architecture

Master Server

Executes INSERT, UPDATE, DELETE operations
Writes all changes to the Binary Log (Binlog)

Slave Server

Reads changes from the master
Stores them in Relay Log
Applies changes to its own database

Replication Threads

I/O Thread → Reads binary log from master
SQL Thread → Executes relay log on slave

Q12. What are the Types of Replication in MySQL?

MySQL supports the following types of replication:

1. Asynchronous Replication

Master does not wait for slave
Fastest and default replication type
Slave may lag behind

2. Semi-Synchronous Replication

Master waits until at least one slave confirms receiving data
More reliable than asynchronous

3. Synchronous Replication

Master waits until all slaves apply changes
Highest data consistency
Slowest type

4. Master–Slave Replication

One master, one or more slaves
Master handles writes, slaves handle reads

5. Master–Master Replication

Both servers act as master and slave
Both can perform write operations
High availability but risk of conflicts

Q13. What is the Replication Process in MySQL?

The replication process works in the following steps:

Client performs a write operation on Master
Master records the change in Binary Log
Slave’s I/O Thread reads binary log from master
Slave stores events in Relay Log
Slave’s SQL Thread executes relay log events
Slave database becomes an exact copy of master

Q14. Explain Master–Slave Replication.

Master–Slave Replication is a replication setup where:

The Master server handles all write operations
One or more Slave servers copy data from the master

Advantages

Load balancing (reads on slaves)
High availability
Backup without affecting master
Better performance

Limitations

Slave lag may occur
Writes allowed only on master
Data inconsistency possible during delay

Q15. Explain Replication Filters in Master–Slave Replication.

What are Replication Filters?

Replication filters are used to control which databases or tables are replicated from master to slave.

They allow partial replication instead of replicating all data.

Types of Replication Filters

1. Database-Level Filters

replicate-do-db

Replicates only specified databases.

replicate-do-db = school

replicate-ignore-db

Ignores specified databases.

replicate-ignore-db = test

2. Table-Level Filters

replicate-do-table

Replicates only specified tables.

replicate-do-table = school.students

replicate-ignore-table

Ignores specific tables.

replicate-ignore-table = school.logs

3. Wildcard-Based Filters

replicate-wild-do-table

Replicates tables using wildcard.

replicate-wild-do-table = school.%

replicate-wild-ignore-table

Ignores tables using wildcard.

replicate-wild-ignore-table = %.temp%

Why Replication Filters are Used?

Reduce unnecessary data replication
Save storage space
Improve replication performance
Replicate only required databases or tables

⭐ Q16:- Explain the Different Ways to Maintain Security in MySQL

Security in MySQL means protecting the database from unauthorized users, data theft, and misuse.
MySQL provides many methods to keep data safe and secure.

1. User Authentication

MySQL allows only authorized users to access the database.

Each user must have a username and password
Without login, access is not allowed

This ensures that only valid users can use the database.

2. User Privileges (Access Control)

MySQL controls what a user can do in the database.

Some users can only read data
Some users can insert, update, or delete data

This prevents users from performing unauthorized actions.

3. Role-Based Security

Roles are groups of permissions.

A role can be given to many users
Makes permission management easy

This helps manage security in large databases.

4. Strong Password Policy

MySQL enforces strong passwords.

Passwords must be long
Use numbers and special characters

Strong passwords help prevent hacking.

5. Secure Connection (SSL/TLS)

MySQL supports encrypted connections.

Data sent between client and server is encrypted
Prevents data stealing during transfer

6. Limiting Network Access

MySQL can be configured to allow access only from certain computers or IP addresses.

Prevents remote attackers
Improves security

7. Using Views

Views allow users to see only selected data.

Sensitive data is hidden
Users cannot access full tables

8. Preventing SQL Injection

Using prepared statements and stored procedures:

Stops SQL injection attacks
Makes queries safer

9. Data Encryption

MySQL supports data encryption.

Encrypts stored data
Encrypts backup files

This keeps data safe even if files are stolen.

10. Auditing and Logging

MySQL keeps logs of activities.

Records login attempts
Records queries

Logs help detect suspicious activity.

11. Regular Backups

Backup keeps a copy of data
Helps recover data if lost or deleted

Backups ensure data safety.

12. Updating MySQL Regularly

New updates fix security issues
Protect against new threats

⭐ Q16:- How Can We Optimize Queries in MySQL? (Very Easy)

What is Query Optimization?

Query optimization in MySQL means making SQL queries run faster and use less memory and CPU.
The goal is to get results quickly without overloading the database.

Why Do We Need Query Optimization?

To make applications faster
To reduce database load
To handle more users
To avoid slow performance

Easy Ways to Optimize Queries in MySQL

1. Use Indexes

Indexes help MySQL find data quickly.

Create indexes on columns used in WHERE, JOIN, ORDER BY

Example:

CREATE INDEX idx_id ON students(id);

*2. Do Not Use SELECT **

Avoid selecting all columns.

❌ Bad:

SELECT * FROM students;

✔ Good:

SELECT name, age FROM students;

3. Use WHERE Clause

Always filter data using WHERE.

SELECT * FROM students WHERE age > 18;

4. Use EXPLAIN

EXPLAIN shows how MySQL runs a query.

EXPLAIN SELECT * FROM students WHERE id = 1;

It helps find slow queries.

5. Avoid Functions in WHERE Clause

Do not use functions on columns in WHERE.

❌ Bad:

WHERE YEAR(dob) = 2002;

✔ Good:

WHERE dob BETWEEN '2002-01-01' AND '2002-12-31';

6. Optimize JOINs

Join only required tables
Use indexes on join columns

SELECT s.name, c.course
FROM students s
JOIN courses c ON s.course_id = c.id;

7. Use LIMIT

Limit the number of rows returned.

SELECT * FROM students LIMIT 10;

8. Avoid Subqueries

Use JOIN instead of subqueries.

9. Use Proper Data Types

Use smaller data types to save memory.

10. Use Slow Query Log

Slow Query Log helps find slow queries to optimize.