COURSE MODULE 7 OUTLINE
COURSE OVERVIEW
• Topic: Database Backup and Recovery
• Lesson Description:
This lesson covers the fundamental concepts, types, methods, and strategies of database
backups and recoveries. Students will learn how to perform and manage backup
operations using MySQL tools and apply recovery techniques to restore data integrity in
case of data loss or corruption.
LEARNING OBJECTIVES
After completing this module, students should be able to:
1. Explain the types and methods of backups and recoveries.
2. Perform different strategies for database backup and recovery.
3. Demonstrate the use of MySQL Workbench and WAMP for creating and restoring
database backups.
4. Analyze the causes of database failure and propose effective recovery strategies.
LESSON TOPICS
A. Database Administration
• Roles of Data Administrator and Database Administrator
• Key responsibilities and functions
B. Security Threats and SQL Security Concepts
• Types of security threats (Spoofing Identity, Denial of Service, etc.)
• Concepts: Threat Actor, Threat Action, Threat Consequence
• MySQL user management, roles, privileges, and permissions
C. Backup and Recovery Overview
• Importance of database backup
• Reasons for database failure:
o User Error
o Hardware Failure
o Catastrophic Events (e.g., natural disasters, hacking)
TYPES OF DATABASE BACKUPS
1. Physical (Raw) Backup
o Direct copy of database files and directories
o Faster, more space-efficient
o Tools: mysqlbackup, cp, rsync, tar
2. Logical Backup
o Uses SQL statements to recreate the database
o More portable and flexible
o Tools: mysqldump, SELECT ... INTO OUTFILE, LOAD DATA
3. Online vs. Offline Backups
o Online (Hot): Performed while the server is running
o Offline (Cold): Performed when the server is stopped
4. Local vs. Remote Backups
o Local: Performed on the same host
o Remote: Performed from a different host via client connection
DATABASE BACKUP METHODS
1. Full Backup – Complete copy of data and transactions
2. Transaction Log Backup – Saves only transaction logs
3. Differential Backup – Copies data changed since the last full backup
DATABASE RECOVERY METHODS
1. Log-Based Recovery – Uses transaction logs to restore consistency
2. Shadow Paging – Uses a duplicate “shadow” copy for quick rollback
BACKUP AND RECOVERY STRATEGIES
1. Review Existing Legacy Systems
o Assess current infrastructure
o Identify hardware/software limitations
2. Adopt a 3-2-1 Data Backup Plan
o 3 copies of data
o 2 different storage types
o 1 copy stored offsite
3. Perform Regular Testing of Backups and Recovery
o Ensure accuracy, consistency, and data integrity
o Update recovery plans after new deployments
4. Document Policies and Procedures
o Backup schedules
o Access control and encryption
o Disaster recovery protocols
USING MYSQLDUMP FOR BACKUPS
• Basic Commands:
o Backup all databases:
o mysqldump --all-databases > [Link]
o Backup specific databases:
o mysqldump --databases db1 db2 > [Link]
o Backup a single table:
o mysqldump test t1 t3 > [Link]
• Reloading Backups:
o Restore all databases:
o mysql < [Link]
o Restore single database:
o mysql db1 < [Link]
o Alternative (within MySQL CLI):
o mysql> source [Link]
SUMMARY
• Database backup ensures data integrity and availability.
• Recovery strategies protect organizations from data loss due to user error, hardware
failure, or natural disasters.
• MySQL provides flexible tools for logical and physical backups.
• Implementing a 3-2-1 backup plan and regular testing are essential for data resilience.