The World of MySQL & MariaDB

MySQL Backup & Restore: Complete mysqldump Guide

Backup and Restore
目次

1. What is mysqldump? – Basics and Main Uses –

mysqldump is a command-line tool for exporting MySQL databases in text format. The exported file is saved in SQL format, and you can restore the original database by importing it during a restore.

Main Uses of mysqldump

UseDescription
Database backupUsed to perform regular backups to guard against data loss risk.
Database migration between serversWhen migrating to another server, you can smoothly transfer data using mysqldump’s export file.
Backup of specific tablesUseful for backing up only specific tables rather than the entire database.

2. Basic Usage: Backing Up with mysqldump

First, let’s see how to back up a database using mysqldump. Below, we introduce common backup methods and options.

Backing Up a Single Database

This is the basic command to back up a specific database.
mysump -u [username] -p [database name] > [output file name].sql
Example: when backing up my_database and saving it to my_database_backup.sql
mysqldump -u root -p my_database > my_database_backup.sql
Tip: After running the command, you will be prompted for a password. The backup starts once you enter the correct password.

Backing Up Multiple Databases

When backing up several databases together, use the --databases option.
mysqldump -u root -p --abases database1 database2 > multi_database_backup.sql

Backing Up All Databases

To back up every database on a MySQL server, use the --all-databases option.
mysqldump -u root -p --all-databases > all_databases_backup.sql

Commonly Used Options with mysqldump

OptionDescription
--single-transactionCreates a backup while preserving data consistency during a transaction in InnoDB (not recommended for MyISAM).
<code–quickRetrieves data row by row while minimizing memory usage.
--routinesAllows backing up stored procedures and functions.
--triggersIncludes triggers in the backup.</>

3. Restore Method: Import Backup File into MySQL

This introduces how to restore a database using a backup file obtained with mysqldump.

Restoring a Single Database

Use the following command to restore the backup file to an existing database.
mysql -u [username] -p [database name] < [backup file name].sql
Example: Restoring my_database_backup.sql to my_database
mysql -u root -p my_database < my_database_backup.sql
Verification method: After the restore completes, log into MySQL and verify that the tables in the database have been correctly restored.

Restoring Multiple Databases

If the backup file contains multiple databases, use the following command.
mysql -u root -p < multi_database_backup.sql

Common Restore Errors and Solutions

ErrorSolution
Database already exists errorDelete the CREATE DATABASE statement from the export file, or drop the existing database before restoring.
Insufficient privileges errorPerform the operation with MySQL admin privileges and set up a user with appropriate access rights.
File size errorIncrease the max_allowed_packet size in the MySQL configuration file and restart the server.
Character set errorSpecify the same character set during export and import (e.g., --default-character-set=utf8).

4. Advanced: Flexible Ways to Use mysqldump

mysqldump can be used flexibly, such as exporting only specific tables, only data, or only the schema.

Backing Up Only Specific Tables

To back up only specific tables, specify the table name as shown below.
mysqldump -u root -p my_database my_table > my_table_backup.sql

Backing Up Data Only or Schema Only

  • Data only: Use the --no-create-info option to export only data without the schema (structure).
  mysqldump -u root -p --no-create-info my_database > my_database_data_only.sql
  • Schema only: Use the --no-data option to export only the schema without data.
  mysqldump -u root -p --no-data my_database > my_database_schema_only.sql

Backup and Restore Using Compression

Compressing backups is convenient for large databases.
  • Backup with compression
  mysqldump -u root -p my_database | gzip > my_database_backup.sql.gz
  • Restore from a compressed file
  gunzip < my_database_backup.sql.gz | mysql -u root -p my_database

5. Best Practices for Using mysqldump

Here are best practices for using mysqldump efficiently and safely.

Backup Emphasizing Data Consistency (–single-transaction)

For databases using InnoDB, the --single-transaction option is handy for taking backups while preserving data consistency.
mysqldump -u root -p --single-transaction my_database > my_database_backup.sql
Note: This option assumes InnoDB usage and is not recommended for MyISAM, as data consistency cannot be guaranteed.

Reducing Memory Consumption (–quick)

When handling large amounts of data, you can use the --quick option to keep memory usage low while backing up. It reads data row by row, making it effective for backing up large databases.
mysqldump -u root -p --quick my_database > my_database_backup.sql

Regular Backups Through Automation

Backups with mysqldump can be automated by setting up a cron job. The example below configures a backup to run daily at 2 AM and store it in a compressed format.
0 2 * * * mysqldump -u root -p[パスワード] my_database | gzip > /path/to/backup/my_database_$(date +%Y%m%d).sql.gz
Note: Pay attention to password security management, and if possible, use MySQL configuration files or similar methods to manage them safely.

Regular Verification of Backups

It is important to regularly verify that the backup files can be restored correctly. Confirming restore capability in advance ensures rapid recovery in case of a failure.

6. Summary: Best Practices for Data Protection Using mysqldump

mysqldump is a powerful tool for efficiently and safely backing up and restoring MySQL databases. By leveraging mysqldump, you can maintain data integrity and minimize the risk of system failures and data loss.

Article Key Points Summary

  1. Overview and Uses of mysqldump: For backing up and migrating MySQL databases, mysqldump is extremely convenient and versatile.
  2. Basic Backup and Restore Methods: Clearly understand the procedures for backing up and restoring single or multiple databases, as well as specific tables.
  3. Advanced Usage: You can export data only, schema only, perform compressed backups, and more, to meet various needs.
  4. mysqldump Best Practices: Settings that ensure consistency, scheduling regular automated backups, and verifying backups are essential.
By using mysqldump properly, you can improve the reliability of MySQL databases and strengthen your data protection strategy. Refer to the contents of this article to leverage mysqldump and achieve robust data protection.