The World of MySQL & MariaDB

mysqldump Command Guide: Usage, Automation & Error Handling

Backup and Restore
目次

1. Introduction

What is the mysqldump command?

Backing up and migrating databases are essential tasks in system administration and development. The “mysqldump” command is useful for that. mysqldump is a utility for exporting and saving the contents of a MySQL database, and it is used in many environments that rely on database management systems (DBMS).

Features of mysqldump

  1. Backup capability – Outputs the database contents as an SQL script, allowing data recovery in case of disasters or issues.
  2. Migration capability – Enables smooth data migration to different environments or servers.
  3. Flexibility – Allows export on a per-table basis or with conditions, supporting partial backups.
Thus, the mysqldump command is a powerful tool that supports data safety and management efficiency.

Purpose of this article and target audience

This guide explains everything from the basic usage of the mysqldump command to advanced option techniques.

Target audience

  • Beginners: Those who are not yet comfortable with MySQL operations but want to learn the basics of backup and restoration.
  • Intermediate users: Those who want to master practical mysqldump command usage and improve workflow efficiency.
  • Developers & operations staff: Those who want to deepen their database management knowledge and respond quickly when issues arise.

What you will learn in this article

  1. Basic syntax and examples of the mysqldump command
  2. How to export and import databases and tables
  3. Troubleshooting and error resolution
  4. Backup automation and security measures
Through these topics, you will be able to use mysqldump effectively and achieve safe, efficient data management.

2. Basics of the mysqldump command and what it can do

Overview of mysqldump

mysqldump is a command-line tool used for backing up and migrating MySQL and MariaDB databases. This tool exports the database structure and data in SQL format or plain text.

Key features of mysqldump

  1. Full database backup: Obtain a complete backup that includes both data and schema.
  2. Partial backup: Export only specific tables, allowing efficient management even for large databases.
  3. Data migration: Useful when moving a database to another server or environment.
  4. Export of settings and privileges: Stored procedures, triggers, views, etc., can also be exported, enhancing environment reproducibility.

Examples of use cases by scenario

  • Data replication to development environment: Used when moving data from production to development for testing.
  • Data archiving: Back up old data to save disk space.
  • Disaster recovery: Regularly save backups for recovery in case of hardware failure or data corruption.

Installation and basic configuration

Checking mysqldump installation

mysqldump is included in the standard packages of MySQL or MariaDB. You can check its installation status with the following command:
mysqldump --version
Example output:
mysqldump  Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)

If not installed

Some systems may not include mysqldump. In that case, install it with the following commands: For Ubuntu/Debian-based systems:
sudo apt-get install mysql-client
For CentOS/RHEL-based systems:
sudo yum install mysql

Connection configuration tips

To use mysqldump, connection information is required. The basic connection settings are as follows:
mysqldump -u username -p password database_name > backup.sql
  • -u: Specify the MySQL username.
  • -p: Option that prompts for password input.
  • database_name: Specify the name of the database you want to back up.
  • > backup.sql: Specify the output file name.

How to handle connection errors

  1. In case of authentication error:
   ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)
→ Check for incorrect username or password and grant appropriate privileges.
  1. In case of host specification error:
   ERROR 2003 (HY000): Can't connect to MySQL server on 'hostname' (111)
→ Verify firewall settings and the status of the MySQL service.

3. Basic Usage of mysqldump

Backup Procedure for a Single Database

By using the mysqldump command, you can easily back up a specific database. In the example below, we back up the database named “example_db”.

Basic Command Example

mysqldump -u username -p example_db > backup.sql

Command Explanation

  • -u username: Specify the user with access rights to the database.
  • -p: Prompts for password entry (input is hidden).
  • example_db: Specify the name of the database you want to back up.
  • > backup.sql: Specify the destination and name of the backup file.

Verifying the Backup Result

If you open the generated “backup.sql” file in a text editor, you can verify that it contains table creation statements and data insertion commands in SQL format.

Backing Up Multiple Databases

To back up multiple databases at once, use the following command.

Example for Multiple Databases

mysqldump -u username -p --databases db1 db2 > multi_backup.sql

Option Explanation

  • –databases: Required when specifying multiple database names.
  • db1 db2: List the database names to back up, separated by spaces.
This method exports the specified multiple databases into a single file.

Backing Up Specific Tables Only

When you want to back up only specific tables from a large database, use the following command.

Example for Specific Tables

mysqldump -u username -p example_db table1 table2 > tables_backup.sql

Option Explanation

  • example_db: Specify the target database name.
  • table1 table2: Specify the table names to back up, separated by spaces.
This approach is useful when you want to efficiently back up only specific data.

Compressing Backup Files

If the backup file becomes large, it is recommended to compress it with gzip.

Example of Compressed Backup

mysqldump -u username -p example_db | gzip > backup.sql.gz

Command Explanation

  • | gzip: Compress the output of mysqldump using gzip.
  • backup.sql.gz: The name of the compressed backup file.
This method can save storage space and improve backup transfer speeds.

Database Restore Procedure

Backups obtained with mysqldump can be easily restored using the following command.

Basic Restore Example

mysql -u username -p example_db < backup.sql

Command Explanation

  • mysql: Invokes the MySQL client.
  • example_db: Specify the name of the database to restore into.
  • < backup.sql: Imports data from the backup file.

Cautions and Recommendations

  1. Create the database in advance: If the target database does not exist, you need to create it beforehand.
   CREATE DATABASE example_db;
  1. Split import for large data sets: When the data volume is large, improve efficiency by combining file splitting and decompressing compressed data.
  2. Check character set: Verify the character set settings to prevent garbled text during backup and restore.
   mysqldump --default-character-set=utf8 -u username -p example_db > backup.sql

4. Useful mysqldump Options Explained

mysqldump command offers many options, allowing you to streamline data export and management to meet specific needs. In this section, we’ll dive into the most practical options.

Options for Ensuring Data Consistency

–single-transaction

mysqldump --single-transaction -u username -p example_db > backup.sql

Explanation

  • Creates a backup while maintaining transaction consistency.
  • Especially effective when using the InnoDB storage engine.
  • Helps minimize locking during backups of large databases.

Example Use

Useful for taking backups without shutting down online services.

Options to Reduce Memory Usage

–quick

mysqldump --quick -u username -p example_db > backup.sql

Explanation

  • Fetches data row by row to keep memory usage low.
  • Ideal for backing up large databases.

Caution

  • While it improves performance, the operation may take slightly longer.

Backing Up Stored Procedures and Triggers

–routines and –triggers

mysqldump --routines --triggers -u username -p example_db > backup.sql

Explanation

  • –routines: Backs up stored procedures and functions.
  • –triggers: Also exports triggers.

Example Use

Use when you need to back up and migrate while preserving complex business logic and automated processes.

Option to Separate Data and Schema

–no-data

mysqldump --no-data -u username -p example_db > schema.sql

Explanation

  • Exports only the table structure, omitting the data.
  • Useful for validating or rebuilding schemas in development environments.

Safety Options When Adding Data

–add-drop-table

mysqldump --add-drop-table -u username -p example_db > backup.sql

Explanation

  • Includes SQL to drop existing tables before creating new ones.
  • Helps when you need to completely overwrite existing data.

Caution

Since it may delete existing data during restore, be sure to test thoroughly before running.

Data Filtering Options

–where

mysqldump -u username -p example_db --where="created_at >= '2023-01-01'" > filtered_backup.sql

Explanation

  • Exports only data that matches a specific condition.
  • Useful for extracting subsets of data from large databases.

Option to Compress Backups

–compress

mysqldump --compress -u username -p example_db > backup.sql

Explanation

  • Compresses data transfer between server and client.
  • Improves transfer speed when backing up over a network.

Summary of Other Useful Options

OptionDescription
–skip-lock-tablesAvoids table locks to speed up export.
–default-character-setSpecifies the character set (e.g., utf8).
–result-fileWrites directly to the output file, improving performance.
–hex-blobExports binary data in hexadecimal format.
–no-create-infoExports only data, omitting table definitions.

Conclusion

In this section we covered the useful mysqldump options in detail. By using these options effectively, you can greatly improve the efficiency and safety of backups and data migrations.

5. Practical Example: WordPress Backup and Restore

WordPress uses a MySQL database to manage site information. This section explains the concrete steps for backing up and restoring a WordPress database using the mysqldump command.

WordPress Site Backup Procedure

1. Verify Database Information

First, check the database name, username, and password from WordPress’s configuration file (wp-config.php). Example configuration file:
define('DB_NAME', 'wordpress_db');
define('DB_USER', 'wp_user');
define('DB_PASSWORD', 'wp_password');
define('DB_HOST', 'localhost');

2. Database Backup Command

Run the following command to back up the WordPress database.
mysqldump -u wp_user -p wordpress_db > wordpress_backup.sql
Option explanations:
  • -u wp_user: The database user used by WordPress.
  • -p: Option that prompts for a password.
  • wordpress_db: The database name.
  • > wordpress_backup.sql: The backup file name.

3. Example of Compressing the Backup

To reduce file size, you can compress using gzip:
mysqldump -u wp_user -p wordpress_db | gzip > wordpress_backup.sql.gz

4. Recommended File Transfer Method

Backup files need to be stored in a secure location. The example below uses the SCP command to transfer to a remote server.
scp wordpress_backup.sql.gz user@remote_host:/backup/

Restore Procedure and Considerations

1. Create a New Database

Create a new database at the restore destination.
mysql -u root -p -e "CREATE DATABASE wordpress_db;"

2. Restore the Database

Restore data from the backup file.
mysql -u wp_user -p wordpress_db < wordpress_backup.sql

3. Restoring Compressed Data

If restoring a backup file compressed with gzip, use the following command.
gunzip < wordpress_backup.sql.gz | mysql -u wp_user -p wordpress_db

4. Verify Operation

After restoration, check the following points.
  • Whether you can log in to the WordPress dashboard.
  • Whether posts and pages display correctly.
  • Whether plugins and themes function without issues.

Troubleshooting Errors

1. Database Does Not Exist Error

ERROR 1049 (42000): Unknown database 'wordpress_db'
Solution: Create the database beforehand before restoring.

2. Permission Error

ERROR 1045 (28000): Access denied for user 'wp_user'@'localhost'
Solution: Grant the appropriate privileges to the user.
GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wp_user'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

3. Fixing Garbled Characters

If garbled characters appear, check the character set settings. During backup:
mysqldump --default-character-set=utf8 -u wp_user -p wordpress_db > wordpress_backup.sql
During restore:
mysql --default-character-set=utf8 -u wp_user -p wordpress_db < wordpress_backup.sql

Automating Backup Operations

1. Automation with Cron Jobs

Set up a cron job to automate backups. Example: Backup daily at 2 AM
0 2 * * * mysqldump -u wp_user -p'wp_password' wordpress_db | gzip > /backup/wordpress_backup_$(date +%F).sql.gz

2. Managing Backup Retention

Example script to automatically delete old backup files:
find /backup/ -type f -name "*.sql.gz" -mtime +30 -exec rm {} ;
This script deletes files older than 30 days.

Summary

This section detailed the backup and restore procedures for a WordPress database. By using the mysqldump command, you can protect and recover data easily and safely.

6. Troubleshooting and Error Handling

mysqldump command can produce various errors depending on the environment and database settings. This section provides detailed explanations of common error causes and their solutions.

1. Connection Error

Example Error Message

ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

Cause

  • Incorrect username or password.
  • The user does not have sufficient privileges.

Solution

  1. Verify that the username and password are correct.
  2. Grant the necessary privileges.
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
  1. If you want to automate password entry, consider using a .my.cnf file.

2. Database Does Not Exist Error

Example Error Message

ERROR 1049 (42000): Unknown database 'database_name'

Cause

The specified database does not exist.

Solution

  1. Create the database.
CREATE DATABASE database_name;
  1. Check for any misspelling of the database name.

3. Insufficient Privileges Error

Example Error Message

mysqldump: Got error: 1044: Access denied for user 'user'@'localhost' to database 'database_name'

Cause

The user does not have access rights to the specific database.

Solution

  1. Check the privileges.
SHOW GRANTS FOR 'user'@'localhost';
  1. Grant the required privileges.
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON database_name.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

4. Large Database Backup Error

Example Error Message

mysqldump: Error 2006: MySQL server has gone away when dumping table 'table_name'

Cause

  • The database is too large, causing the connection to time out.
  • Insufficient network or server resources.

Solution

  1. Modify the configuration file (my.cnf) Increase the following parameters.
[mysqld]
max_allowed_packet=512M
net_read_timeout=600
net_write_timeout=600
  1. Use options
mysqldump --quick --single-transaction -u user -p database_name > backup.sql
This backs up large amounts of data efficiently.

5. Garbled Character Issue

Symptoms

  • Multibyte characters such as Japanese become garbled after restore.

Cause

The character set settings during backup or restore do not match.

Solution

  1. Specify character set during backup
mysqldump --default-character-set=utf8 -u user -p database_name > backup.sql
  1. Specify character set during restore
mysql --default-character-set=utf8 -u user -p database_name < backup.sql

6. Table Duplicate Error During Restore

Example Error Message

ERROR 1050 (42S01): Table 'table_name' already exists

Cause

A table with the same name already exists in the target database.

Solution

  1. Add the “DROP TABLE IF EXISTS” option to the backup file.
mysqldump --add-drop-table -u user -p database_name > backup.sql
  1. Manually drop the target table.
DROP TABLE table_name;

7. Backup Failure Due to Locks

Example Error Message

mysqldump: Error 1227: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Cause

Table locks occur and the required privileges are missing.

Solution

  1. Add options to avoid locking.
mysqldump --single-transaction --skip-lock-tables -u user -p database_name > backup.sql
  1. Expand privileges as needed.

Summary

In this section, we explained the common causes of errors when using the mysqldump command and how to address them. Understanding these troubleshooting steps will enable you to respond quickly to any issues that arise.

7. Building Automation and Backup Strategies

Using the mysqldump command for database backups is essential for enhancing system security. This section explains how to automate backups and manage them strategically.

1. Benefits of Automation

Importance of Backup Automation

  • Avoiding Human Error: Prevents mistakes caused by manual operations.
  • Regular Protection: Ensures backups run reliably on the configured schedule.
  • Reduced Recovery Time: Allows immediate restoration of the latest data when a failure occurs.

Use Cases

  • Backup before site updates.
  • Daily and weekly scheduled backups.
  • Data protection during server maintenance or upgrades.

2. Setting Up Scheduled Backups with Cron Jobs

Basic Cron Configuration Example

  1. Start editing the cron job.
crontab -e
  1. Add the following schedule configuration.
Example: Capture backups daily at 2 AM
0 2 * * * mysqldump -u user -p'password' database_name | gzip > /backup/backup_$(date +%F).sql.gz

Key Configuration Points

  • Password Management: If specifying the password directly in the command, enclose it in quotes.
  • Appending Date to Filenames: $(date +%F) is a convenient way to add the date in YYYY‑MM‑DD format.
  • Compression: Using gzip saves storage space.

3. Automatic Deletion of Old Backups

Keeping backup files for a long time can consume disk space. Therefore, you should configure automatic deletion of backups that are older than a certain period.

Example File Deletion Script

find /backup/ -type f -name "*.sql.gz" -mtime +30 -exec rm {} ;

Command Explanation

  • find /backup/: Searches within the backup folder.
  • -type f: Targets files only.
  • -name “*.sql.gz”: Looks for files with the .sql.gz extension.
  • -mtime +30: Targets files older than 30 days.
  • -exec rm {} ;: Deletes the found files.

4. Remote Storage of Backups

Enhancing Security and Risk Management</h4By storing backups not only on the local server but also on remote servers or cloud storage, you can strengthen disaster recovery measures.

Transfer Example Using SCP

scp /backup/backup_$(date +%F).sql.gz user@remote_host:/remote/backup/

Incremental Backup Example with rsync

rsync -avz /backup/ user@remote_host:/remote/backup/

Upload Example to Cloud Storage

When uploading to an S3 bucket using the AWS CLI:
aws s3 cp /backup/backup_$(date +%F).sql.gz s3://my-bucket-name/

5. Incremental Backup Strategy

For large databases, full backups consume time and resources. Therefore, combining incremental backups improves efficiency.

Incremental Backup Using Binlog

  1. Enable Binary Logging – add the following to my.cnf:
[mysqld]
log_bin=mysql-bin
expire_logs_days=10
  1. Backup the Binary Log
mysqlbinlog mysql-bin.000001 > binlog_backup.sql
  1. Restore Procedure
mysql -u user -p database_name < binlog_backup.sql

6. Security and Data Protection Measures

1. Protection via Encryption

Encrypt backup files to enhance security. Example: Encryption with gpg
gpg --output backup.sql.gz.gpg --encrypt --recipient user@example.com backup.sql.gz

2. Password-Protected Archives

zip -e backup.zip backup.sql.gz

3. Setting Access Permissions

Restrict access permissions on the backup directory.
chmod 700 /backup/

Conclusion

In this section, we explained how to automate and strategically manage backups using the mysqldump command. By combining regular backups, remote storage, and incremental backups, you can significantly improve data security.

8. Frequently Asked Questions (FAQ)

This section compiles common questions about the mysqldump command and their solutions. Use it as a practical troubleshooting guide.

1. How to speed up mysqldump execution?

Q. What causes slow backups?

A. Large database size or table locks can slow down the process.

Solution

  1. Optimize options
mysqldump --single-transaction --quick -u user -p database > backup.sql
  • –single-transaction: Uses a transaction to maintain consistency while avoiding locks.
  • –quick: Reduces memory usage by processing rows one at a time.
  1. Increase packet size Edit the configuration file (my.cnf):
max_allowed_packet=512M
  1. Leverage parallel processing Use a tool that backs up multiple tables in parallel (e.g., mydumper).

2. How to compress and store backup files?

Q. What if the database is large and I want to save storage space?

A. Using gzip to compress the backup reduces the file size.

Solution

mysqldump -u user -p database | gzip > backup.sql.gz
This method improves storage efficiency by increasing compression.

3. How to prevent data conflicts during restore?

Q. Is there a risk of conflicts with existing data when restoring a database?

A. Conflicts can occur if tables or data overlap.

Solution

  1. When restoring by deleting existing data
mysqldump --add-drop-table -u user -p database > backup.sql
This option drops existing tables before creating new ones.
  1. When importing while preserving existing data
mysql -u user -p database < backup.sql
If you need to overwrite only specific rows, consider the --replace option.

4. How to migrate data between different servers?

Q. What should I watch out for when moving data to another server?

A. Be aware of compatibility issues due to character set or version differences.

Solution

  1. Specify character set during export
mysqldump --default-character-set=utf8 -u user -p database > backup.sql
  1. Verify character set settings on the target
mysql --default-character-set=utf8 -u user -p database < backup.sql
  1. When ensuring version compatibility
mysqldump --compatible=mysql40 -u user -p database > backup.sql
This option ensures compatibility with older versions.

5. Can I back up only part of the data with mysqldump?

Q. Is there a way to back up only specific data?

A. Using the --where option allows conditional data extraction.

Solution

mysqldump -u user -p database --tables table_name --where="created_at >= '2023-01-01'" > filtered_backup.sql
This command backs up only data created on or after January 1, 2023.

6. How to fix garbled characters after restore?

Q. Why do Japanese characters become garbled after a restore?

A. The character set settings may not match between backup and restore.

Solution

  1. Specify character set during backup
mysqldump --default-character-set=utf8 -u user -p database > backup.sql
  1. Specify character set during restore
mysql --default-character-set=utf8 -u user -p database < backup.sql
Unifying the character set prevents garbled text.

7. Common reasons for backup failures?

Q. What could cause mysqldump to stop midway?

A. Database size, configuration, or connection timeouts are common causes.

Solution

  1. Adjust memory settings:
max_allowed_packet=512M
  1. Use options that avoid locks:
mysqldump --single-transaction --skip-lock-tables -u user -p database > backup.sql
  1. Split data for export:
mysqldump -u user -p database table_name > table_backup.sql
Exporting table by table reduces load.

Summary

This section covered common questions and solutions for the mysqldump command. It spans basic usage to troubleshooting, serving as a reference for mastering mysqldump.

9. Summary

In the previous sections, we have covered the basics to advanced usage of the mysqldump command in depth. In this section, we review the article’s content and reconfirm the key points for using mysqldump.

1. Role and Features of the mysqldump Command

mysqldump is an essential tool for backing up and migrating MySQL databases. It ensures data safety and can be used for disaster recovery, copying to development environments, and many other purposes.

Main Features

  • Versatility: Can export a single or multiple databases.
  • Flexibility: Allows table‑level or conditional backups.
  • Compatibility: Offers options for character sets and support for older versions.
By understanding and combining these capabilities appropriately, you can achieve safe and efficient data management.

2. Basic Usage and Advanced Techniques

Basic Backup and Restore

mysqldump can perform backup and restore with the simple commands below. Backup example:
mysqldump -u user -p database > backup.sql
Restore example:
mysql -u user -p database < backup.sql

Using Handy Options

  • –single-transaction: Maintains consistency while avoiding locks.
  • –quick: Reduces memory usage while efficiently backing up large datasets.
  • –routines and –triggers: Enables exporting stored procedures and triggers as well.
By using these options appropriately, you can flexibly handle varying data volumes and environments.

3. Practical Use Cases

WordPress Backup and Restore

We illustrated the concepts with a real‑world example of managing a WordPress site’s database.
  • Backup: Save the database before site updates or migrations.
  • Restore: Use it for site recovery or copying to a development environment.
Referencing these system‑specific examples can further enhance your practical mysqldump skills.

4. Troubleshooting and the Importance of Automation

Error Handling and Troubleshooting

With mysqldump, you may encounter connection errors, garbled characters, and insufficient privileges.
  • Check error messages and promptly apply the appropriate fixes.
  • Adjust character set and packet size settings to better handle large datasets.

Efficiency and Safety Improvements Through Automation

By combining scheduled backups with cron jobs, remote server transfers, and automatic deletion of old backups, you can further improve data protection efficiency.

5. Looking Ahead to Data Management

Optimizing Backup Strategies

  • Combining full and incremental backups: Aims for efficient operations.
  • Adopting remote storage and encryption: Strengthens protection against data leaks and disasters.

Integrating with Tools Beyond mysqldump

  • Percona Xtrabackup: Supports fast backup and restore.
  • mydumper: Enables high‑speed export via parallel processing.
By selecting the appropriate tool for each situation, you can complement mysqldump and optimize data management.

6. Conclusion

The mysqldump command is a powerful tool for enhancing database management and protection. Understanding everything from basic operations to advanced options and automating your backup strategy can significantly improve system efficiency and safety. Use the information covered in this article as a guide, and put mysqldump into practice to achieve reliable and secure data management.