Reliable data protection is essential in database operations, particularly under high-scale workloads. MySQL supports physical and logical backup strategies; logical backups are widely adopted due to their flexibility and zero cost. The primary tool for logical backups is mysqldump, which can export individaul tables, selected databases, or an entire server instance.

Exporting Data

Exporting a Single Table

mydumper -host srv-host -user admin -pass secret mydb customer_info > cust_backup.sql

This command extracts the customer_info table from mydb into a text file containing DDL and INSERT statements.

Exporting Multiple Databases

mydumper -host srv-host -user admin -pass secret --include-databases sales inventory > multi_db.sql

All tables and structures within the listed databases are serialized into the output file.

Exporting All Databases

mydumper -host srv-host -user admin -pass secret --all-databases > full_server.sql

Captures every accessible database on the server in a single script.

The generated .sql files are plain text and can be inspected or edited before restoration.

Restoring Data

Command-Line Import

mysql -host srv-host -user admin -pass secret mydb < cust_backup.sql

Feeds the dump into the specified database via standard input.

Using SOURCE Within MySQL Shell

USE mydb;
SOURCE cust_backup.sql;

Executes the script inside an active MySQL session, applying its contents to the current schema.

Trade-offs of Backup Strategies

Full Database Export

Benefits:

• Completeness: Every table and relationship is captured, ensuring no structural loss.
• Simplicity: One operation backs up all objects, reducing procedural complexity.

Drawbacks:

• Time-consuming: Large datasets extend execution time.
• Resource impact: High I/O and CPU usage may affect live transactions during export.

Example:

mydumper -host srv-host -user admin -pass secret --include-databases sales inventory > multi_db.sql

Single Table Export

Benefits:

• Targeted scope: Useful when only specific subsets require safeguarding.
• Speed: Faster than full export for large schemas.

Drawbacks:

• Isolation risk: Foreign key dependencies might be omitted, breaking referential integrtiy on restore.
• Manual assembly: Restoring multiple tables demands separate steps.

Example:

mydumper -host srv-host -user admin -pass secret mydb customer_info > cust_backup.sql

Trade-offs of Restoration Approaches

Restoring a Full Database

Benefits:

• Wholeness: Reinstates complete schema and data in one pass.

Drawbacks:

• Duration: Large imports take considerable time.
• Overwrite hazard: Existing data in the target may be replaced unintentionally.

Example:

mysql -host srv-host -user admin -pass secret mydb < multi_db.sql

Restoring a Single Table

Benefits:

• Precision: Alters only the intended table, preserving others.

Drawbacks:

• Integrity gaps: May disrupt inter-table consistency if related tables are not restored together.
• Repetitive effort: Scaling to many tables increases operational overhead.

Example:

USE mydb;
SOURCE cust_backup.sql;

Typical Use Cases

• Preventing data loss: Regular exports mitigate risks from hardware failure or accidental deletions.
• Disaster recovery: Enables rapid rebuilding of services after catastrophic events.
• Testing environments: Clone production state for validation without affecting live systems.
• Platform migration: Transfer datasets across servers or upgraded versions reliably.
• Undoing mistakes: Roll back unintended changes using recent backups.
• Regulatory compliance: Meet legal obligations for data retention and retrievability.
• Storage optimization: Archive infrequently accessed data to improve performance.

Backup and restoration granularity should align with operational needs, balancing speed, completeness, and system impact.