Mysqldump

MySQL backup management is a fundamental skill for any professional working with databases. Having reliable backups not only protects against data loss, but also facilitates the migration and replication of environments. In this guide, we will explore how to use MySQLdump, a powerful tool that allows us to create complete and customized backups of our databases.

What is MySQLdump and why is it important?

MySQLdump is a standalone program that comes bundled with most MySQL distributions. This tool allows us to create full or partial database backups, including:

• Table structure
• Stored data
• Views
• Triggers
• Stored procedures

The importance of MySQLdump lies in its flexibility and reliability. Through the command line, we can specify exactly what we want to back up and in what format, generating SQL files that can be used to restore the information when necessary.

Difference between versioning and backup

It is crucial to understand the difference between these two concepts:

• Structure (tables, views, triggers): Versioned, can be included in code repositories.
• Data: Not versioned, backed up to secure locations (external disks, cloud services, etc.).

This distinction is fundamental to maintain proper database management practices.

How to create backups with MySQLdump?

To use MySQLdump, we need to access the command line and run the program with the appropriate parameters. Let's look at some practical examples:

Full backup (structure and data)

mysqldump -u root -p platzi_sql > platzi_sql_complete.sql

This command will create a file named platzi_sql_complete.sql containing:

• DROP instructions to delete existing objects.
• CREATE statements to recreate tables, views and triggers
• INSERT statements to populate the tables with data.

The resulting file is a complete SQL script that can be run to recreate the database in its entirety.

Structure-only backup (no data)

mysqldump -u root -p --no-data platzi_sql > platzi_sql_schema.sql

This command generates a file containing only the database structure:

• Table definitions
• View definitions
• Triggers
• Other database objects

This type of backup is ideal for versioning, as it is compact and contains only the structural logic of the database.

What does a MySQLdump backup file contain?

A file generated by MySQLdump is a SQL script that contains instructions to recreate the database. Some interesting elements we can find:

Version compatibility comments.

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

These special comments (delimited by /*! and */) contain code that will only run on specific MySQL versions. For example:

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER....

This code will only run on MySQL 5.00.03 or higher, which ensures compatibility between different versions.

Table structure

DROP TABLE IF EXISTS `build_products`;
CREATE TABLE `build_products` ( `id` int NOT NULL AUTO_INCREMENT, `bill_id` int NOT NULL, `product_id` int NOT NULL, `price` decimal(10,2) NOT NULL, `quantity` int NOT NULL, `total` decimal(10,2) GENERATED ALWAYS AS ((`price` * `quantity`)) VIRTUAL, PRIMARY KEY (`id`), KEY `bill_id` (`bill_id`), KEY `product_id` (`product_id`)) ENGINE=InnoDB AUTO_INCREMENT=1016 DEFAULT  CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Data (in full backups)

LOCK TABLES `build_products` WRITE;/*!40000 ALTER TABLE `build_products` DISABLE KEYS */;INSERT INTO `build_products` VALUES (1,87,5346,7406.35,15,'2025-02-10 01:30:00',...);/* ... more data ...*//*!40000 ALTER TABLE `build_products` ENABLE KEYS */;UNLOCK TABLES;

Notice how MySQLdump:

1. Locks the table for writing.
2. Disables keys (to speed up insertion)
3. Inserts the data
4. Restores the keys
5. Unlocks the table

How to restore a database from a backup?

Restoring a database from a backup file is a simple process:

mysql -u root -p -d platzi_dos < platzi_sql_schema.sql

This command will execute all SQL statements contained in the backup file on the specified database.

Important considerations when restoring

When restoring a database, we must keep a few things in mind:

• Views may reference the original database.
• We may need to modify these references so that they point to the new database.

For example, if a view was created with:

CREATE VIEW db_status ASSELECT * FROM information_schema.tablesWHERE table_schema = 'platzi_sql';

When restoring it to a database named platzi_dos, we will need to modify it:

CREATE OR REPLACE VIEW db_status ASSELECT * FROM information_schema.tablesWHERE table_schema = 'platzi_dos';

Recommendations for an effective backup strategy

To keep your databases secure and recoverable:

• Perform full backups periodically (structure + data).
• Version the structure in your code repository
• Store data backups in secure, redundant locations
• Compress backup files from the operating system, not from MySQLdump
• Regularly test the restoration of your backups
• Document the backup and restoreprocess

MySQLdump is a powerful tool that every database professional should master. Its flexibility and reliability make it an indispensable ally for managing backups in MySQL environments. Take advantage of its capabilities to keep your data safe and your database structure correctly versioned.

Have you had any interesting experience with database backups? Do you use any particular strategy to manage your backups? Share your experience in the comments.