Physical and logical backups are two different methods of backing up MySQL databases on AWS RDS. Here are the key differences:
Physical Backup
1. Type of Backup: Physical backups are raw copies of the directories and files that store database contents. They are often used for larger datasets and can be faster and less resource-intensive.
2. Tools Used: Tools like Percona XtraBackup can be used to create physical backups of MySQL databases on self-managed instances or on Amazon EC2 instances. However, these tools cannot be used to create physical backups of RDS for MySQL instances.
3. Advantages:
- Faster and less resource-intensive.
- Captures the complete state and structure of the database.
- Ensures consistency and recoverability of the database.
4. Disadvantages:
- Not portable and can only be restored to the same platform and version of the database software.
- Not suitable for selective backup or restoration of specific objects or schemas within the database.
Logical Backup
1. Type of Backup: Logical backups save the content and structure of the database in a logical form. They are often used for smaller datasets and can be used to transfer data between different database systems or formats.
2. Tools Used: Tools like mysqldump or MyDumper can be used to create logical backups of MySQL databases.
3. Advantages:
- Portable and can be restored to different platforms or versions of the database software.
- Allows selective backup or restoration of specific objects or schemas within the database.
- Can be used to transfer data between different database systems or formats.
4. Disadvantages:
- Slower and more resource-intensive.
- May not capture all information or dependencies of database objects.
- May not be consistent or recoverable if the database is modified during the backup process.
Conclusion
Physical backups are suitable for larger datasets and provide faster and less resource-intensive backup and recovery. Logical backups are suitable for smaller datasets and provide more flexibility in terms of portability and selective backup or restoration.
Citations:[1] https://www.linkedin.com/advice/0/how-can-you-compare-logical-physical-backups
[2] https://aws.amazon.com/blogs/database/improve-performance-of-your-bulk-data-import-to-amazon-rds-for-mysql/
[3] https://sudoconsultants.com/how-to-backup-mysql-databases-on-amazon-rds/
[4] https://repost.aws/questions/QUKZyq-gcETA6ly4rFEyTcyg/single-db-backup-restore-for-rds
[5] https://www.reddit.com/r/Database/comments/9uzxtl/logical_vs_physical_backup_and_online_vs_offline/