️⃣ UNIT – I : Introduction, Installation, SQL Commands, Logs
⭐ 1. Introduction to MySQL
- MySQL is an open-source relational database (RDBMS).
- Stores data in tables (rows & columns).
- Uses SQL language to manage data.
- Fast, secure, free, and widely used.
⭐ 2. Installing MySQL
A. Windows Installation
- Download MySQL Installer (MSI).
- Run Setup → Developer Default.
- Set root password.
- Install MySQL Server + Workbench.
- Open Workbench to connect.
B. Linux Installation Methods
✔ RPM Installation
sudo rpm -ivh mysql.rpm
sudo systemctl start mysqld
✔ Binary Installation
- Download
.tar.gz - Extract files
- Set permissions
- Start server manually with
mysqld &
✔ Source Installation
cmake .
make
sudo make install
⭐ 3. Starting & Stopping MySQL
Start MySQL (Linux - 4 Ways)
sudo systemctl start mysqld
sudo service mysql start
/etc/init.d/mysql start
mysqld &
Stop MySQL
sudo systemctl stop mysqld
mysqladmin -u root -p shutdown
⭐ 4. Basic SQL Queries
DML
INSERT, UPDATE, DELETE, SELECT
DDL
CREATE, ALTER, DROP, TRUNCATE
TCL
COMMIT, ROLLBACK, SAVEPOINT
⭐ 5. Types of Joins
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
- SELF JOIN
⭐ 6. UNION
Combines result of 2 SELECT queries.
⭐ 7. MySQL Logs & Uses
| Log Type | Description |
|---|---|
| Error Log | Server startup/shutdown errors |
| General Query Log | All SQL queries |
| Slow Query Log | Queries taking long time |
| Binary Log (binlog) | All changes (used for replication & backup) |
| Relay Log | Copy of binlog on slave |
Binlog Formats:
✔ Statement
✔ Row
✔ Mixed
📘 UNIT – II : Client Programs, Workbench, Upgrading, Locking
⭐ 1. MySQL Server/Client Architecture
- Client sends SQL commands
- Server executes queries and returns results
⭐ 2. MySQL Client Programs
mysqlmysqladminmysqldumpmysqlshowmysqlimport
⭐ 3. Non-Client Programs
-
mysqld→ MySQL server mysqld_safemysql_install_db
⭐ 4. Upgrading MySQL
Why Upgrade?
- Bug fixes
- Security
- New features
- Better speed
Upgrade Checklist
- Take full backup
- Stop MySQL server
- Install new version
- Verify data compatibility
⭐ 5. mysqladmin Commands
| Command | Use |
|---|---|
mysqladmin shutdown |
Stop server |
mysqladmin ping |
Check server status |
mysqladmin flush-logs |
Rotate logs |
⭐ 6. MySQL Workbench
- GUI tool for running queries
- Database design (ER diagrams)
- User management
- Backup/restore
⭐ 7. MySQL Locking
| Type | Description |
|---|---|
| Internal Locking | Handled by MySQL automatically |
| Table-Level Locking | Locks whole table |
| Row-Level Locking | Locks specific row |
| External Locking | Done by OS |
| Deadlock | Two transactions wait for each other |
📘 UNIT – III : Table Maintenance, Schemas, Backup, Replication
⭐ 1. Table Maintenance Commands
| Command | Purpose |
|---|---|
ANALYZE TABLE |
Update statistics |
CHECK TABLE |
Detect errors |
REPAIR TABLE |
Fix corrupted table |
OPTIMIZE TABLE |
Improve performance |
CHECKSUM TABLE |
Generate checksum |
BACKUP TABLE |
Backup table data |
RESTORE TABLE |
Restore table |
⭐ 2. Information Schema
- Stores metadata
-
Important tables:
- TABLES
- COLUMNS
- STATISTICS
- SCHEMATA
⭐ 3. Performance Schema
Used for:
- Query performance
- I/O monitoring
- Memory usage
⭐ 4. MySQL Backup & Recovery
Backup Types
- Logical (mysqldump)
- Physical (copying files)
- Hot Backup
- Cold Backup
Commands
mysqldump -u root -p db > backup.sql
Load Data
LOAD DATA INFILE 'file.csv' INTO TABLE table;
Export
SELECT * INTO OUTFILE 'data.csv' FROM students;
⭐ 5. MySQL Replication
What is Replication?
Copying data from one server (Master) to another (Slave).
Types
- Master-Slave
- Master-Master
- Circular replication
Architecture Components
- Binary Log
- Relay Log
- I/O Thread
- SQL Thread
⭐ 6. MySQL User Management
CREATE USER 'u'@'localhost' IDENTIFIED BY 'p';
DROP USER 'u';
RENAME USER 'old' TO 'new';
GRANT SELECT ON db.* TO 'u';
REVOKE ALL PRIVILEGES ON db.* FROM 'u';
⭐ 7. Routines & Triggers
Stored Procedure
Reusable SQL code.
Function
Returns a value.
Trigger
Runs automatically on:
- INSERT
- UPDATE
- DELETE
📘 UNIT – V : Memory, Security, Optimization
⭐ 1. Resource Allocation
MySQL uses memory for:
- Query cache
- Sorting
- Joins
- Buffers
- Connections
Monitor using:
SHOW STATUS;
⭐ 2. Securing MySQL
Operating System Security
- Use strong OS passwords
- Disable unnecessary accounts
File System Security
- Protect
/var/lib/mysql/ - Correct file permissions
Network Security
- Disable remote root login
- Use firewall
- Allow only trusted IPs
Log Security
- Protect binlogs and slow logs
⭐ 3. Query Optimization
Steps:
- Identify slow queries
- Use EXPLAIN
- Add indexes
- Avoid SELECT *
- Normalize tables
- Use LIMIT
EXPLAIN
EXPLAIN SELECT * FROM students WHERE age > 20;
SHOW WARNINGS
Shows errors and warnings.
📝 IMPORTANT EXAM QUESTIONS (Most Repeated)
UNIT – I
- Explain different MySQL installation methods.
- What are DDL, DML, TCL commands?
- Explain MySQL logs and their uses.
- What are joins? Explain types of joins.
UNIT – II
- Explain MySQL Server/Client Architecture.
- What are mysqladmin commands?
- What is locking? Explain table-level and row-level locking.
- What is MySQL Workbench?
UNIT – III
- Explain table maintenance commands.
- Information schema vs performance schema.
- Explain MySQL backup and recovery.
- Explain replication architecture.
- What are routines and triggers?
UNIT – V
- Explain MySQL resource allocation.
- What are security measures in MySQL?
- What is query optimization?
- Explain EXPLAIN and SHOW WARNINGS.
Top comments (0)