🧭 UNIT – I : INTRODUCTION TO MYSQL & SQL
⭐ 1. What is MySQL? (Exam Answer)
MySQL is an open-source Relational Database Management System (RDBMS) that stores data in tables and uses SQL for data operations.
Features
Stores data in rows & columns
Supports joins, triggers, procedures
Works on Windows & Linux
Fast, secure, free
Uses Client–Server architecture
⭐ 2. Installation of MySQL
Windows Installation
Download MySQL Installer (MSI).
Select "Developer Default".
Set root password.
Complete setup.
Linux Installation
a) RPM
sudo rpm -ivh mysql.rpm
sudo service mysqld start
b) Binary Installation
Extract tar
Configure folders
Start MySQL
c) Source Installation
./configure
make
make install
⭐ 3. Starting & Stopping MySQL (Linux)
Start MySQL (4 Ways)
systemctl start mysqld
service mysqld start
/etc/init.d/mysql start
mysqld &
Stop MySQL
systemctl stop mysqld
⭐ 4. SQL Commands (Short Notes)
DML
INSERT
UPDATE
DELETE
SELECT
DDL
CREATE
ALTER
DROP
TRUNCATE
TCL
COMMIT
ROLLBACK
SAVEPOINT
⭐ 5. Types of Joins
Type Use
INNER JOIN Matching rows only
LEFT JOIN All left + matching right
RIGHT JOIN All right + matching left
FULL JOIN All rows (not direct in MySQL)
CROSS JOIN All combinations
⭐ 6. UNION vs UNION ALL
UNION → removes duplicates
UNION ALL → keeps duplicates
⭐ 7. MySQL Logs
Log Type Use
Error Log Startup/shutdown errors
General Query Log All SQL queries
Slow Query Log Slow queries
Binary Log Replication, recovery
Relay Log Used by slave system
🧭 UNIT – II : CLIENT PROGRAMS, MYSQLADMIN & LOCKING
⭐ 8. MySQL Server/Client Architecture
Client sends SQL → Server processes it
SQL Engine executes commands
Storage Engines (InnoDB/MyISAM) store data
⭐ 9. MySQL Client Programs
mysql → run SQL
mysqladmin → admin tasks
mysqldump → backup
mysqlshow → show DB info
mysqld → server
⭐ 10. Why Upgrade MySQL?
Better performance
New features
Security fixes
Bug fixes
Upgrade Checklist
Take backup
Check compatibility
Test on staging
Upgrade database
⭐ 11. mysqladmin Commands
mysqladmin status
mysqladmin shutdown
mysqladmin create testdb
mysqladmin processlist
⭐ 12. Types of Locking
Lock Type Meaning
Table-level Entire table locked
Row-level Single row locked
Internal locking Handled by MySQL
External locking OS-based
Deadlock Two queries wait for each other
🧭 UNIT – III : TABLE MAINTENANCE, BACKUP, REPLICATION
⭐ 13. Table Maintenance
CHECK TABLE tablename;
REPAIR TABLE tablename;
OPTIMIZE TABLE tablename;
ANALYZE TABLE tablename;
CHECKSUM TABLE tablename;
⭐ 14. Information Schema
Stores metadata like:
Tables
Columns
Views
Constraints
Privileges
⭐ 15. Performance Schema
Tracks:
Query performance
Memory usage
CPU usage
Threads
⭐ 16. Backup & Recovery
Backup Types
Logical → mysqldump
Physical → copy data files
Binary log backup
Recovery Tools
Restore SQL file
Apply binlog
LOAD DATA INFILE
⭐ 17. Replication
What is Replication?
Copying data from Master → Slave automatically.
Types
Master–Slave
Master–Master
Multi-source
Files Used
Binary Log
Relay Log
⭐ 18. User Management
Create User
CREATE USER 'abhi'@'localhost' IDENTIFIED BY '123';
Grant Privileges
GRANT ALL PRIVILEGES ON db.* TO 'abhi'@'localhost';
Revoke
REVOKE SELECT ON db.* FROM 'abhi'@'localhost';
Drop
DROP USER 'abhi'@'localhost';
⭐ 19. Routines & Triggers
Stored Procedure
Predefined SQL block
Reusable
Trigger
Runs automatically on INSERT/UPDATE/DELETE
🧭 UNIT – IV : MEMORY, SECURITY, OPTIMIZATION
⭐ 20. MySQL Resource Allocation (Memory Use)
MySQL uses RAM for:
Buffer pool
Query cache
Sort buffer
Join buffer
Temporary tables
⭐ 21. Securing MySQL
Use strong passwords
Restrict remote access
Enable firewall
Secure log files
Apply OS security
Use least privileges
Enable SSL
⭐ 22. Query Optimization
Use indexes
Avoid SELECT *
Use LIMIT
Normalize tables
Avoid subqueries
Use EXPLAIN
Use proper joins
⭐ 23. Using EXPLAIN
Shows:
Index used
Rows scanned
Query execution plan
⭐ 24. SHOW WARNINGS
Displays warnings after SQL execution.
🧭 UNIT – V : DOMAIN, HOSTING, FTP
⭐ 25. What is a Domain?
Human-readable name for websites
Example: google.com
Maps to IP address
⭐ 26. What is DNS?
Converts domain → IP
Works like phonebook of internet
⭐ 27. What is Web Hosting?
Service that stores website files.
Types
Shared
VPS
Dedicated
Cloud
⭐ 28. What is FTP?
Protocol to transfer files to web server.
Commands
ls # list files
cd # change directory
get # download
put # upload
Tools
FileZilla
CuteFTP
WS_FTP
🎯 IMPORTANT EXAM QUESTIONS (Write These!)
What is MySQL? Explain features.
Explain DML, DDL, TCL with examples.
Write types of joins with examples.
Explain MySQL logs.
What is replication? Explain types.
What is Information Schema?
Explain table maintenance commands.
What is locking? Explain deadlock.
What is DNS?
What is web hosting and FTP?
Top comments (0)