mysqldump Guide: Backup, Restore, Options, and Automation for MySQL & MariaDB

1. Introduction

What Is the mysqldump Command?

Database backup and migration are essential tasks in system administration and development. One tool that helps with these tasks is the mysqldump command.
mysqldump is a utility for exporting and saving the contents of a MySQL database. It is widely used in many environments where database management systems (DBMS) are in operation.

Key Features of mysqldump

1. Backup capability – Outputs database contents as an SQL script so you can restore data in the event of disasters or incidents.
2. Migration capability – Enables smooth data migration to different environments or servers.
3. Flexibility – Supports exporting by table or with conditions, making partial backups possible.

In this way, the mysqldump command is a powerful tool that supports data safety and efficient management.

Purpose of This Article and Intended Audience

This guide explains everything from basic mysqldump usage to leveraging advanced options in detail.

Intended Audience

• Beginners: Those who are not yet comfortable with MySQL operations but want to learn the basics of backup and restore.
• Intermediate users: Those who want to master practical mysqldump usage and improve operational efficiency.
• Developers / Operations engineers: Those who want deeper database management knowledge and the ability to respond quickly when issues occur.

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 solutions
4. Backup automation and security measures

By working through these topics, you will be able to use mysqldump effectively and achieve safe and efficient data management.

2. mysqldump Basics and What It Can Do

Overview of mysqldump

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

Main Capabilities of mysqldump

1. Full database backup:
   Captures a complete backup including both data and schema.
2. Partial backup:
   Exports only specific tables, enabling efficient management even for large databases.
3. Data migration:
   Useful when migrating a database to another server or environment.
4. Exporting settings and privileges:
   Can export stored procedures, triggers, views, and more, improving environment reproducibility.

Examples by Use Case

• Copying data to a development environment: Used when moving data from production to development for testing.
• Archiving data: Backup old data to save disk space.
• Disaster recovery: Store backups regularly for recovery from hardware failures or data corruption.

Installation and Basic Setup

Verify mysqldump Installation

mysqldump is included in the standard MySQL or MariaDB packages. You can verify installation with the following command:

mysqldump --version

Example output:

mysqldump  Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)

If mysqldump Is Not Installed

Depending on the system, mysqldump may not be installed. In that case, install it with the following commands:

For Ubuntu/Debian:

sudo apt-get install mysql-client

For CentOS/RHEL:

sudo yum install mysql

Connection Setup Tips

To use mysqldump, you need connection information. Basic connection usage is as follows:

mysqldump -u username -p password database_name > backup.sql

• -u: Specifies the MySQL username.
• -p: Prompts for a password.
• database_name: Specifies the database name to back up.
• > backup.sql: Specifies the output file name.

How to Handle Connection Errors

1. If it is an authentication error:

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

→ Confirm that the username and password are correct, and grant the appropriate privileges.

2. If it is a host-related error:

   ERROR 2003 (HY000): Can't connect to MySQL server on 'hostname' (111)

→ Check firewall settings and the status of the MySQL service.

3. Basic mysqldump Usage

How to Back Up a Single Database

With mysqldump, you can easily back up a specific database. The example below creates a backup of the database named example_db.

Basic Command Example

mysqldump -u username -p example_db > backup.sql

Command Breakdown

• -u username: Specifies a user with access privileges to the database.
• -p: Prompts for the password (it will not be displayed while typing).
• example_db: Specifies the database name to back up.
• > backup.sql: Specifies the backup file location and name.

Verify the Backup Output

Open the generated backup.sql file in a text editor to confirm that it contains SQL statements for creating tables and inserting data.

How to Back Up Multiple Databases

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

Multiple Database Example

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

Option Explanation

• –databases: Required when specifying multiple database names.
• db1 db2: Specify the databases to back up, separated by spaces.

This method exports the specified databases into a single file.

How to Back Up Only Specific Tables

To back up only specific tables from a large database, use the following command.

Specific Table Example

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

Option Explanation

• example_db: Specifies the target database.
• table1 table2: Specifies the tables to back up, separated by spaces.

This method is useful when you want to efficiently back up only certain data.

How to Compress a Backup File

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

Compressed Backup Example

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

Command Breakdown

• | gzip: Compresses mysqldump output with gzip.
• backup.sql.gz: The name of the compressed backup file.

This method can save storage space and improve backup transfer speeds.

How to Restore a Database

You can easily restore a mysqldump backup with the following command.

Basic Restore Example

mysql -u username -p example_db < backup.sql

Command Breakdown

• mysql: Invokes the MySQL client.
• example_db: Specifies the destination database name.
• < backup.sql: Imports data from the backup file.

Notes and Recommendations

1. Create the database in advance:
   If the destination database does not exist, you must create it beforehand.

   CREATE DATABASE example_db;

2. Split imports for large data:
   If the data size is large, combine file splitting and decompression to improve efficiency.
3. Check character encoding:
   To prevent garbled text during backup and restore, verify your character set settings.

   mysqldump --default-character-set=utf8 -u username -p example_db > backup.sql

4. Helpful mysqldump Options Explained

mysqldump provides many options that help you export and manage data more efficiently depending on your needs. This section explains especially practical options in detail.

Options to Ensure Data Consistency

–single-transaction

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

Explanation

• Creates a backup while preserving transactional consistency.
• Especially effective when using the InnoDB storage engine.
• Minimizes locking during large database backups.

Use Case

Useful when you want to take a backup without stopping an online service.

Option to Reduce Memory Usage

–quick

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

Explanation

• Fetches data row by row to reduce memory usage.
• Well-suited for large database backups.

Notes

• While it improves memory usage, total execution time may become slightly longer.

Backing Up Stored Procedures and Triggers

–routines and –triggers

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

Explanation

• –routines: Includes stored procedures and functions in the backup.
• –triggers: Exports triggers as well.

Use Case

Use this when you want to back up or migrate while preserving complex business logic and automated processing.

Option to Save Data and Schema Separately

–no-data

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

Explanation

• Exports only the table structure and does not include data.
• Useful when validating or rebuilding schemas in a development environment.

Safety Option When Overwriting Data

–add-drop-table

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

Explanation

• Includes SQL statements to drop existing tables before creating them.
• Useful when completely overwriting existing data.

Notes

Because this may delete existing data during restore, thoroughly validate before execution.

Option for Data Filtering

–where

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

Explanation

• Exports only data that matches specific conditions.
• Helpful for extracting a subset of data from a large database.

Option to Compress During Transfer

–compress

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

Explanation

• Compresses data transfer between the server and client.
• Improves transfer speed when taking backups over a network.

Other Useful Options Summary

Summary

In this section, we explained helpful mysqldump options. Using these options appropriately can significantly improve the efficiency and safety of backups and migrations.

5. Practical Example: Backing Up and Restoring WordPress

WordPress uses a MySQL database to manage site information. This section explains the concrete steps to back up and restore a WordPress database using mysqldump.

How to Back Up a WordPress Site

1. Check Database Information

First, check the database name, username, and password in the WordPress configuration file (wp-config.php).

Example configuration:

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 details:

• -u wp_user: The database user used by WordPress.
• -p: Prompts for a password.
• wordpress_db: The database name.
• > wordpress_backup.sql: The backup file name.

3. Example: Compressed Backup

To reduce file size by compressing with gzip:

mysqldump -u wp_user -p wordpress_db | gzip > wordpress_backup.sql.gz

4. Recommended File Transfer Method

Backups must be stored in a safe location. The example below uses the SCP command to transfer the file to a remote server.

scp wordpress_backup.sql.gz user@remote_host:/backup/

Restore Procedure and Notes

1. Create a New Database

Create a new database on 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

To restore from a gzip-compressed backup file, use the following command.

gunzip < wordpress_backup.sql.gz | mysql -u wp_user -p wordpress_db

4. Validate Operation

After restoring, check the following points:

• Can you log in to the WordPress dashboard?
• Are posts and pages displayed correctly?
• Are plugins and themes working properly?

How to Handle Errors

1. Unknown Database Error

ERROR 1049 (42000): Unknown database 'wordpress_db'

Solution:
Create the database in advance, then restore.

2. Permission Error

ERROR 1045 (28000): Access denied for user 'wp_user'@'localhost'

Solution:
Grant appropriate privileges to the user.

GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wp_user'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

3. Preventing Garbled Characters

If text becomes garbled, verify 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. Automate with a cron Job

Configure a cron job to automate backups.

Example: Backup daily at 2:00 AM

0 2 * * * mysqldump -u wp_user -p'wp_password' wordpress_db | gzip > /backup/wordpress_backup_$(date +\%F).sql.gz

2. Manage 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

In this section, we explained specific steps to back up and restore a WordPress database. By using mysqldump, you can protect and restore your data easily and safely.

6. Troubleshooting and Error Handling

When using mysqldump, various errors can occur depending on your environment and database settings. This section explains common causes and solutions in detail.

1. Connection Errors

Example Error Message

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

Causes

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

Solutions

1. Verify that the username and password are correct.
2. Grant privileges.

GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

3. If you want to automate password input, consider using a .my.cnf file.

2. Unknown Database Error

Example Error Message

ERROR 1049 (42000): Unknown database 'database_name'

Cause

The specified database does not exist.

Solutions

1. Create the database.

CREATE DATABASE database_name;

2. Check for typos in 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 privileges to the specified database.

Solutions

1. Check the current privileges.

SHOW GRANTS FOR 'user'@'localhost';

2. Grant the required privileges.

GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON database_name.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

4. Backup Errors with Large Databases

Example Error Message

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

Causes

• The database is too large and the connection timed out.
• Insufficient network or server resources.

Solutions

1. Update the configuration file (my.cnf)
   Increase the following parameters.

[mysqld]
max_allowed_packet=512M
net_read_timeout=600
net_write_timeout=600

2. Use helpful options

mysqldump --quick --single-transaction -u user -p database_name > backup.sql

This helps back up large amounts of data efficiently.

5. Garbled Characters

Symptoms

• After restore, multibyte characters such as Japanese appear garbled.

Cause

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

Solutions

1. Specify the character set during backup

mysqldump --default-character-set=utf8 -u user -p database_name > backup.sql

2. Specify the character set during restore

mysql --default-character-set=utf8 -u user -p database_name < backup.sql

6. Duplicate Table 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 destination database.

Solutions

1. Add DROP TABLE IF EXISTS statements by backing up with this option.

mysqldump --add-drop-table -u user -p database_name > backup.sql

2. 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 locking occurs and privileges are insufficient.

Solutions

1. Add options to avoid locks.

mysqldump --single-transaction --skip-lock-tables -u user -p database_name > backup.sql

2. Expand privileges if necessary.

Summary

In this section, we explained common errors that occur when using mysqldump and how to address them. Understanding these troubleshooting steps will help you respond quickly if issues arise.

7. Automation and Building a Backup Strategy

Database backups using mysqldump are essential for improving system safety. This section explains how to automate backups and manage them strategically.

1. Benefits of Automation

Why Backup Automation Matters

• Avoid human error: Prevent mistakes caused by manual operations.
• Consistent protection: Backups run reliably on a defined schedule.
• Faster recovery: Restore the latest data quickly when failures occur.

Common Scenarios

• Backing up before site updates.
• Daily/weekly scheduled backups.
• Data protection during server maintenance and upgrades.

2. Scheduled Backups with cron

Basic cron Configuration Example

1. Start editing your cron jobs.

crontab -e

2. Add the following schedule.

Example: Take a backup daily at 2:00 AM

0 2 * * * mysqldump -u user -p'password' database_name | gzip > /backup/backup_$(date +\%F).sql.gz

Configuration Tips

• Password management: If you specify the password directly, wrap it in quotes.
• Add the date to the filename: $(date +\%F) is a convenient way to append the date in YYYY-MM-DD format.
• Compression: Using gzip saves storage space.

3. Automatically Deleting Old Backups

Keeping backup files for a long time can consume disk space. Configure automatic deletion for backups older than a certain period.

Example File Deletion Script

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

Command Breakdown

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

4. Remote Backup Storage

Strengthening Security and Risk Management

Storing backups not only on the local server but also on a remote server or cloud storage strengthens disaster recovery measures.

Example Transfer Using SCP

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

Example Incremental Transfer Using rsync

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

Example Upload to Cloud Storage

If you use AWS CLI to upload to an S3 bucket:

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. Improve efficiency by combining incremental backups.

Incremental Backups Using binlog

1. Enable binary logs
   Add the following to my.cnf:

[mysqld]
log_bin=mysql-bin
expire_logs_days=10

2. Back up binary logs

mysqlbinlog mysql-bin.000001 > binlog_backup.sql

3. Restore procedure

mysql -u user -p database_name < binlog_backup.sql

6. Security and Data Protection Measures

1. Protect with Encryption

Encrypt backup files to improve safety.

Example: Encrypt with gpg

gpg --output backup.sql.gz.gpg --encrypt --recipient user@example.com backup.sql.gz

2. Password-Protected Archive

zip -e backup.zip backup.sql.gz

3. Set Access Permissions

Restrict access permissions for the backup directory.

chmod 700 /backup/

Summary

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

8. Frequently Asked Questions (FAQ)

This section summarizes frequently asked questions about mysqldump and their solutions. Use these practical tips to help resolve common issues.

1. How Can I Speed Up mysqldump?

Q. What causes slow backups?

A. If the database is large, or if table locks occur, the process can be slow.

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: Processes data row by row while reducing memory usage.

1. Increase packet size
   Edit the configuration file (my.cnf):

max_allowed_packet=512M

3. Use parallel processing
   Use a tool that backs up multiple tables in parallel (for example, mydumper).

2. How Do I Save a Compressed Backup File?

Q. The database is large—how can I save storage space?

A. Compress the backup with gzip to reduce file size.

Solution

mysqldump -u user -p database | gzip > backup.sql.gz

This method improves storage efficiency while maintaining a good compression ratio.

3. How Can I Prevent Data Conflicts During Restore?

Q. Can conflicts occur when restoring a database?

A. Yes. Conflicts can occur when tables or data already exist.

Solution

1. When you want to delete existing data before restoring

mysqldump --add-drop-table -u user -p database > backup.sql

This option drops existing tables before creating them.

2. When you want to import while keeping existing data

mysql -u user -p database < backup.sql

If you want to overwrite only specific data based on conditions, consider the --replace option as well.

4. How Do I Migrate Data Between Different Servers?

Q. What should I pay attention to when moving data to another server?

A. Watch out for compatibility issues caused by different character sets or versions.

Solution

1. Specify the character set during export

mysqldump --default-character-set=utf8 -u user -p database > backup.sql

2. Verify the destination character set settings

mysql --default-character-set=utf8 -u user -p database < backup.sql

3. Ensure version compatibility

mysqldump --compatible=mysql40 -u user -p database > backup.sql

This option helps ensure 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. Yes. Use the --where option to extract data conditionally.

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 Do I Fix Garbled Characters After Restore?

Q. Why do Japanese characters become garbled after restoring?

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

Solution

1. Specify the character set during backup

mysqldump --default-character-set=utf8 -u user -p database > backup.sql

2. Specify the character set during restore

mysql --default-character-set=utf8 -u user -p database < backup.sql

Unifying the character set prevents garbled text.

7. What Are Common Reasons Backups Fail?

Q. If mysqldump stops midway, what is likely wrong?

A. It is often caused by database size, configuration issues, or connection timeouts.

Solution

1. Adjust memory-related settings:

max_allowed_packet=512M

2. Use options to avoid locks:

mysqldump --single-transaction --skip-lock-tables -u user -p database > backup.sql

3. Export data in smaller chunks:

mysqldump -u user -p database table_name > table_backup.sql

Exporting per table helps reduce load.

Summary

This section explained frequently asked questions about mysqldump and practical solutions. It covers everything from basic usage to troubleshooting, so use it as a reference when mastering mysqldump.

9. Summary

In the previous sections, we covered mysqldump from basics to advanced usage. In this section, we review the content and reaffirm the key points for using mysqldump effectively.

1. The Role and Features of mysqldump

mysqldump is an essential tool for backing up and migrating MySQL databases. It helps ensure data safety and can be used for many purposes, including disaster recovery and copying data into development environments.

Main Features

• Versatility: Export one or multiple databases.
• Flexibility: Back up per table or using conditions.
• Compatibility: Rich options for character sets and older versions.

By understanding these capabilities and combining them appropriately, you can achieve safe and efficient data management.

2. Basic Usage and Advanced Techniques

Basic Backup and Restore

mysqldump supports backup and restore with simple commands.
Backup example:

mysqldump -u user -p database > backup.sql

Restore example:

mysql -u user -p database < backup.sql

Using Helpful Options

• –single-transaction: Avoid locks while maintaining consistency.
• –quick: Efficient backups for large datasets with reduced memory usage.
• –routines and –triggers: Export stored procedures and triggers too.

Using options appropriately allows you to respond flexibly to different data volumes and environments.

3. Practical Use Cases

Backing Up and Restoring WordPress

As a real-world example, we used WordPress database management.

• Backup: Save the database before updates or migrations.
• Restore: Use for site recovery or copying to development environments.

Referencing system-specific examples will further strengthen your practical mysqldump skills.

4. The Importance of Troubleshooting and Automation

Error Handling and Troubleshooting

With mysqldump, you may encounter issues such as connection errors, garbled characters, or insufficient privileges.

• Check error messages and apply the appropriate fix quickly.
• Adjust character set and packet size settings to improve large-database readiness.

Efficiency and Safety Through Automation

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

5. Looking Ahead to Better Data Management

Optimizing Your Backup Strategy

• Combine full and incremental backups: Aim for efficient operations.
• Adopt remote storage and encryption: Strengthen against data leaks and disasters.

Integrating Tools Beyond mysqldump

• Percona Xtrabackup: Supports fast backups and restores.
• mydumper: Enables faster exports with parallel processing.

Using these tools as needed can complement mysqldump and help optimize your overall data management approach.

6. Closing

The mysqldump command is a powerful tool for strengthening database management and protection. By understanding both basic operations and advanced options—and by automating your backup strategy—you can significantly improve operational efficiency and safety.

Use the content in this article as a reference to apply mysqldump in practice and achieve reliable, safe data management.