The data in an enterprise database is the lifeblood of the company's daily operations. It is at risk of becoming corrupted due to bugs introduced during the regular iterations of software, or it may be deliberately deleted by an employee with malicious intent, putting the company at significant legal risk. Whether it is an accidental event or intentional sabotage, either could render this core asset unusable, causing a failure in daily operations.
Database backups are an effective means of protecting this core data asset. By establishing a rigorous backup plan, you can elegantly manage unexpected events. Typically, the database backups provided by vendors are physical backups. In fact, depending on the method used to obtain the backup data, database backups can be categorized into logical backups and physical backups.
What Is Physical Backup?
Physical backup refers to directly backing up the binary files of the database, including data files, log files, and index files or backing up through file system tools (such as cp or rsync). The advantage of physical backup is that the backup and recovery speed is faster, especially when the amount of data is large, and the backup process is simpler than logical backup. The disadvantage is that the backup file is large and cannot be backed up and restored across platforms.
3 Ways of Physical Backup
File system level backup: Directly copy the binary files of the database, including data files, log files, and index files. You can use file system tools (such as cp, rsync) for backup.
Storage level backup: Use the snapshot or mirroring function provided by the storage device to back up the database storage. This method can quickly create a copy of the database without copying the entire database file.
Database backup tool: Use the backup tools provided by the database management system, such as Oracle's RMAN (Recovery Manager), MySQL's XtraBackup, etc., to physically back up the database.
What Is Logical Backup?
A logical database backup refers to exporting and saving the data in a logical format from the database, which can be understood as a backup method at an abstract level. Specifically, logical backups involve exporting the data of database tables, structures (such as table definitions, indexes, etc.), views, stored procedures, triggers, and other elements into SQL script files, which can be used to reconstruct the database when necessary. This backup method differs from physical backups, which directly copy the database files, whereas logical backups take a snapshot of the database through SQL commands.
The core of a logical database backup lies in exporting the database content using DDL and DML. DDL includes SQL commands for creating tables, views, indexes, etc., while DML includes INSERT statements used to reconstruct the data in the tables. This backup method makes the backup data more flexible and easier to migrate between different database systems.
Common tools for logical backups include “mysqldump” (for MySQL), “pg_dump” (for PostgreSQL), and “expdp/impdp” (for Oracle). These tools generate SQL script files or other export formats, which can be executed in other database instances to rebuild the database.
Advantages of Logical Backups
1. Cross-Platform and Cross-Version Migration
Since logical backups generate SQL script files, in theory, these files can be executed on any database system that supports SQL, enabling cross-platform migration. Additionally, logical backup files can be used on different versions of the same database system, making database upgrades and migrations more convenient.
2. Smaller Backup File Size
Compared to physical backups, logical backups only export the table structure and data, not the internal database files, meaning that the generated backup files are typically much smaller. This is especially advantageous for remote backups or when transferring backup files over bandwidth-limited networks.
3. Ease of Management and Modification
Since the backup files are text files, they can be viewed and edited using any text editor. This allows administrators to easily edit backup files when necessary, such as removing sensitive data or changing table structures.
Limitations of Logical Backups
Despite the many advantages, logical backups also have some limitations:
1. Slower Execution and Restoration
Logical backups are typically slower to execute and restore compared to physical backups. This is because logical backups involve generating and parsing SQL scripts, while physical backups simply copy files, which provides a clear speed advantage for physical backups.
2. Lack of Transaction Logs
Logical backups generally do not include the database's transaction logs, which means that in some cases, logical backups may not provide the same level of recovery consistency as physical backups. For example, when restoring to a specific point in time, a physical backup combined with transaction logs can restore data more accurately, while logical backups may not achieve this.
3. Performance Bottlenecks with Large Data Volumes
Logical backups can encounter performance bottlenecks when dealing with large datasets. For large databases containing vast amounts of data, generating and parsing SQL scripts can require substantial computing resources and time, which may lead to system performance degradation in some cases.
Key Differences Between Physical Backup and Logical Backup
| Physical Backup | Logical Backup |
Backup Object | Physical files of the database (data files, control files, archive log files, etc.) | Database objects (such as users, tables, stored procedures, etc.) |
Portability | Weak, or even not portable | Strong |
Space Usage | Large | Smaller |
Restore Efficiency | High | Lower |
Full Backup | Install the DBS backup gateway on the database server, then back up (copy) the database files through the backup gateway. | Split the data of each table, then run SQL statements on the database, with multi-threaded parallel data reading. |
Incremental Backup | Supports real-time capture of database memory logs, enabling real-time incremental backups to avoid data accumulation, thus reducing the impact on database IO performance. The log reading speed adjusts with the speed of log generation. |
Features | Only supports backing up the entire database instance. Reads data from the database disk, which can have some impact on database IO performance. Generally, physical backup and restore speed is faster than logical backup and restore.
| Supports finer-grained backup at the database and table level. Does not lock the database, with minimal impact on database performance.
|
Recommended Database Scale | TB level | MB to hundreds of GB level. |
The most Useful Way to Back Up Database
After discussing the differences between logical and physical backups, it's important to highlight how Vinchin Backup & Recovery addresses some of the limitations of both approaches with its advanced database backup features. Vinchin provides agent-based application-level database backup and restore functionalities for popular database platforms, including Oracle, MS SQL Server, MySQL, MariaDB, and PostgreSQL.
It simplifies backup management through a single web console and supports hot backups without disrupting database operations. The solution reduces backup size by up to 70% without affecting production resources and offers flexible backup strategies like full, differential, incremental, and transaction log backups. It also ensures database consistency and optimizes Oracle RAC backup processes.
It only takes you 4 steps to backup databases:
1.Select the backup object.
2.Select backup destination.
3.Configure backup strategies.
4.Review and submit the job.
Vinchin Backup & Recovery offers a 60-day full-featured free trial. Click the button below to experience all its powerful backup and recovery capabilities.
Conclusion
Both logical and physical backups play crucial roles in a robust data protection strategy. Logical backups offer flexibility and portability, making them ideal for application data that may need to be migrated or restored with granularity. On the other hand, physical backups provide full system-level protection, ensuring quick and complete recovery of entire systems or disks.
