The World of MySQL & MariaDB

Mastering mysqldump: MySQL Backup, Restore, & Best Practices

Backup and Restore
佐川 直弘 | Naohiro Sagawa
By 佐川 直弘 | Naohiro Sagawa (運営者)
※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　
目次

How to Use mysqldump and Best Practices

1. Introduction

Database backup and restoration are fundamental aspects of data management and essential for reliable operations. MySQL’s “mysqldump” is a widely used tool for efficiently and flexibly backing up databases. This guide provides a detailed explanation of everything from basic mysqldump usage to leveraging advanced options, restoration methods, and troubleshooting. We also introduce best practices and reference resources at the end of the article, so please use this as a guide to master mysqldump.

2. What is mysqldump?

2.1 Overview of mysqldump

mysqldump is a command-line tool used to create backups of MySQL databases. It can dump an entire database, specific tables, or data matching specific criteria as an SQL script. This dump file is used for restoring data or migrating data to a new server.

2.2 Use Cases

  • Backup: Regularly obtain backups to prepare for system failures or data loss.
  • Data Migration: Used for database migration between servers or copying data to development environments.
  • Data Analysis: Extract specific datasets for analysis and verification.

3. Basic Usage

3.1 Basic Command Syntax

The basic command syntax for mysqldump is as follows:

mysqldump -u username -p database_name > output_file_name.sql
  • -u username: The username to access the database.
  • -p: Prompts for the password.
  • database_name: The name of the database to back up.
  • > output_file_name.sql: The destination for the dump file.

3.2 User Authentication Options

  • -h hostname: The hostname of the database server (default is localhost).
  • -P port_number: The port number to connect to (default is 3306).

3.3 Example: Backing Up an Entire Database

mysqldump -u root -p mydatabase > backup.sql

This command backs up all data from mydatabase to the backup.sql file. Including the date in the backup filename facilitates version management and historical tracking.

4. Explanation of Key Options

4.1 --all-databases (-A)

This option allows you to back up all databases at once. It is useful when you want to get a backup of the entire server.

mysqldump -u root -p --all-databases > all_databases_backup.sql

4.2 --no-data (-d)

Use this option to back up only the table schema and not include data. For example, you can use this to set up a development environment by obtaining only the table structure.

mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql

4.3 --where (-w)

Use this option when you want to back up only the data that matches specific conditions. For instance, to back up only records where the is_active column is 1:

mysqldump -u root -p mydatabase --where="is_active=1" > filtered_data_backup.sql

4.4 --ignore-table

Use this option to exclude specific tables from the backup. This is useful if there are tables you do not want to back up.

mysqldump -u root -p mydatabase --ignore-table=mydatabase.table1 > partial_backup.sql

5. Practical Examples

5.1 Dumping Only Specific Tables

To back up only specific tables, specify the table names after the database name.

mysqldump -u root -p mydatabase table1 > table1_backup.sql

This command saves only the data from table1 to table1_backup.sql.

5.2 Dumping Only Data / Only Schema

  • Data Only: mysqldump -u root -p mydatabase --no-create-info > data_only_backup.sql Backs up only the data, without including the table structure.
  • Schema Only:
    bash mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql
    Backs up only the table schema.

5.3 Conditional Dumping

To back up only the data that meets specific criteria, use the --where option.

mysqldump -u root -p mydatabase --where="created_at >= '2023-01-01'" > recent_data_backup.sql

This command backs up only the data where created_at is on or after January 1, 2023.

6. Restoration Method

To restore a database backed up with mysqldump, use the mysql command. Restoration is the process of restoring the database state using a backup file.

6.1 Basic Restoration Syntax

mysql -u username -p database_name < dump_file.sql
  • -u username: The username to connect to the database.
  • -p: Prompts for the password.
  • database_name: The name of the database to restore to.
  • < dump_file.sql: The dump file to use for restoration.

6.2 Example: Executing Restoration

mysql -u root -p mydatabase < backup.sql

This command restores the data to mydatabase from the backup.sql file.

6.3 Restoration Notes

  • If the database you are restoring to does not exist, you must create it first.
  • Restoring large amounts of data can take time, so it is important to plan ahead.

7. mysqldump Best Practices

7.1 Scheduling Backups

Automate regular backups by scripting mysqldump and using a scheduler like cron. The following shell script example obtains a backup of all databases nightly.

#!/bin/bash
mysqldump -u root -p'password' --all-databases > /path/to/backup/all_databases_$(date +%F).sql

7.2 Encrypting Backup Files

Since backup files contain sensitive information, it is recommended to encrypt them using tools like gpg.

gpg -c /path/to/backup/all_databases_$(date +%F).sql

7.3 Version Compatibility

When migrating data between different MySQL versions, be careful of incompatibility issues. Simulate the backup and restore process in a test environment before upgrading to verify compatibility.

  1. Restoring Table Definitions: mysqldump --all-databases --no-data --routines --events > dump-defs.sql Dump only the table structure with this command and restore it in the new version environment to check compatibility.
  2. Restoring Data: mysqldump --all-databases --no-create-info > dump-data.sql Once table definitions are confirmed to be compatible, restore only the data.
  3. Verification in a Test Environment:
    To confirm compatibility between versions, perform backup and restore in a test environment and verify that it functions correctly before migrating in the production environment.

7.4 Backup Storage and Verification

  • Secure Backup Storage:
    Store backup files on external storage or in the cloud and update them regularly. Off-site storage protects data from physical damage.
  • Regular Restoration Verification:
    Periodically perform restoration tests to confirm that backups can be restored correctly. It is important not to neglect restoration verification in case a backup is invalid.

8. Troubleshooting

8.1 Common Errors and Solutions

  • Error: @@GLOBAL.GTID_PURGED cannot be changed:
    This error appears when GTID-related issues occur in MySQL 8.0. It can be avoided by commenting out the GTID setting using the --set-gtid-purged=COMMENTED option. mysqldump -u root -p mydatabase --set-gtid-purged=COMMENTED > backup.sql
  • Error: Insufficient Disk Space:
    If disk space is insufficient for backing up a large database, compress the backup or change the save location. Back up by compressing with gzip as follows: mysqldump -u root -p mydatabase | gzip > backup.sql.gz
  • Error: Insufficient Permissions:
    If the database user does not have sufficient privileges, backup or restoration will fail. Grant the necessary privileges (SELECT, LOCK TABLES, SHOW VIEW, etc.) and try again.

8.2 Version Compatibility Issues

Version compatibility issues between different MySQL versions are resolved through testing before upgrading. Especially when migrating from MySQL 5.7 to 8.0, it is recommended to restore only table definitions with the --no-data option and verify compatibility.

  • Incompatibility Testing:
    Simulate the migration in a test environment before upgrading to identify potential issues. Pay attention to incompatible features and syntax, and modify the SQL script as needed.

9. Conclusion

mysqldump is a reliable and powerful tool for backing up and restoring MySQL databases. This article has provided a comprehensive explanation covering basic usage, advanced options, best practices, and troubleshooting. By utilizing this knowledge, you can efficiently protect and manage your databases using mysqldump.

Incorporating best practices such as scheduling backups and encrypting files enhances data security and improves the reliability of your database operations. Properly utilizing mysqldump will help you prepare for database issues.

10. References and Additional Resources

Refer to this resource to learn more about mysqldump and apply it in practice. Also, regularly performing backup and restoration verification will maintain database security and prepare for potential data loss.