Comprehensive Linux & MySQL Backend
Engineering Study Guide
Structured Reading Material for Exam Preparation
PART 1: Linux Installation (Dual Boot)
Dual boot allows two operating systems (e.g., Windows and Linux) to coexist on one machine.
GRUB (bootloader) presents a menu at startup to choose the OS. BIOS/UEFI loads first, then
GRUB intercepts startup.
Partitioning divides a hard drive into sections. Windows typically uses NTFS; Linux uses ext4.
Shrinking Windows creates unallocated space for Linux.
Installation Steps:
1. Create bootable USB using Rufus (writes ISO to USB).
2. Boot from USB via BIOS/UEFI.
3. Choose 'Install alongside Windows'.
4. GRUB is installed.
5. Reboot and select OS.
After Installation:
sudo apt update
sudo apt upgrade
sudo = temporary admin rights. apt = package manager. update = refresh list. upgrade = install
updates.
PART 2: Linux CLI Fundamentals
Command syntax: Command + Options + Arguments.
ls -la
pwd
cd ..
mkdir -p dir1/dir2
touch [Link]
rm -r folder
cp [Link] /path/
mv [Link] [Link]
PART 3: Linux Permissions
Each file has Owner, Group, Others. Permissions: r=4, w=2, x=1.
chmod 760 [Link]
sudo chown user [Link]
760 = owner(rwx=7), group(rw-=6), others(---=0).
PART 4: MySQL Server & XAMPP
XAMPP bundles Apache (web server), MySQL (database), PHP (backend). MySQL listens on port
3306.
systemctl status mysql
ss -tulnp | grep 3306
PART 5: InnoDB Storage Engine
InnoDB supports transactions, foreign keys, crash recovery.
Key Components: Buffer Pool (RAM cache), Redo Log (durability), Undo Log (rollback),
Doublewrite Buffer (corruption protection).
PART 6: Schema Design (University Example)
CREATE DATABASE university;
USE university;
CREATE TABLE Student (
student_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Course (
course_id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
credits INT NOT NULL
);
CREATE TABLE Enrollment (
enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_id INT,
enrollment_date DATE,
FOREIGN KEY (student_id) REFERENCES Student(student_id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES Course(course_id) ON DELETE CASCADE
);
Normalization reduces duplication. Enrollment resolves many-to-many relationships.
PART 7: Transactions & ACID
START TRANSACTION;
UPDATE Student SET first_name='John' WHERE student_id=1;
COMMIT;
ROLLBACK;
ACID: Atomicity, Consistency, Isolation, Durability.
PART 8: Indexing & EXPLAIN
CREATE INDEX idx_email ON Student(email);
EXPLAIN SELECT * FROM Student WHERE email='a@[Link]';
type=ALL means full scan. ref/const means index used. Indexes speed reads but slow writes.
PART 9: Backup & Recovery
mysqldump --single-transaction -u root -p university > [Link]
SHOW VARIABLES LIKE 'log_bin';
Binary logs enable point-in-time recovery.
PART 10: Secure Application Practices
Use prepared statements to prevent SQL injection.
PHP (PDO):
PDO::ATTR_EMULATE_PREPARES = false
[Link]: Use connection pooling for performance.
Final Advice: This material covers operating systems, permissions, database architecture,
transactions, indexing, backups, and security — core backend engineering principles.