Overview

This documentation outlines the steps to back up and restore a MySQL database using the command-line interface (CLI). Regular backups are essential for data integrity and continuity, ensuring that your information is protected against data loss.

---

Table of Contents

1. Prerequisites

2. Step 1: Perform a Full Backup of Your MySQL Database

3. Step 2: Restore the Database from the Backup File

4. Step 3: Verify the Restoration

5. Conclusion

---

1. Prerequisites

Before proceeding, ensure you have the following:

• Basic understanding of MySQL and command-line interface (CLI).

• MySQL installed on your machine (either locally or on a server).

---

2. Step 1: Perform a Full Backup of Your MySQL Database

To create a backup of a MySQL database, you will use the mysqldump command. This command generates a dump file containing the SQL statements necessary to recreate the database.

Backup Command

To perform a full backup of a database named my_database, use the following command:

mysqldump -u [username] -p my_database > my_database_backup.sql

Command Breakdown

• -u [username]: Replace [username] with your MySQL username (e.g., root).

• -p: This flag will prompt you to enter your MySQL password.

• my_database: Replace this with the name of the database you wish to back up.

• > my_database_backup.sql: Specifies the output file where the backup will be saved.

Example

If your MySQL username is root, the command would be:

mysqldump -u root -p my_database > my_database_backup.sql

Upon executing this command, you will be prompted to enter your MySQL password. After entering it, the backup process will begin. Once complete, you will have a file named my_database_backup.sql, which contains your database's data and structure.

---

3. Step 2: Restore the Database from the Backup File

To restore your MySQL database from the backup file created earlier, you will use the mysql command.

Restore Command

Use the following command to restore the database from the backup file:

mysql -u [username] -p my_database < my_database_backup.sql

Command Breakdown

• -u [username]: Replace [username] with your MySQL username.

• -p: This flag prompts you to enter your MySQL password.

• my_database: The name of the database you want to restore to.

• < my_database_backup.sql: Specifies the backup file to read from.

Example

For example, if your MySQL username is root, the command would be:

mysql -u root -p my_database < my_database_backup.sql

After executing this command, enter your password when prompted. This will restore the database using the data and structure defined in the my_database_backup.sql file.

---

4. Step 3: Verify the Restoration

To ensure that your database has been restored successfully, you can check the tables and data within your database.

Verify Tables

Run the following command to list all tables in your database:

SHOW TABLES;

Verify Data

To verify that the data has been restored correctly, execute a simple query to check the contents of one of the tables:

SELECT * FROM some_table LIMIT 10;

Note: Replace some_table with the actual name of a table in your database.

---

5. Conclusion

In this documentation, we covered the essential steps for performing a full backup of a MySQL database using the mysqldump command and restoring it using the mysql command. Regularly backing up your database is a crucial practice to protect against data loss and ensure business continuity. Following these steps will help you safeguard your data effectively.