Complete Guide: Export MySQL Safely with mysqldump & Restore

1. Introduction

MySQL is a representative relational database management system used in various web services and business systems. Databases built with MySQL often serve as the core of daily operations and services, and if data is lost due to failures or errors, it can have a significant impact on the business.

Therefore, “backups” become important. MySQL provides an official command-line tool called “mysqldump” that allows you to easily export the database contents and save them as backup files.

By utilizing this tool, you can meet various operational needs, such as recovery during system failures, migration to other environments, and periodic archive storage.

In this article, we will explain in detail how to export MySQL databases using “mysqldump,” from basic usage to advanced settings. Even for beginners, we will introduce it carefully with specific command examples so that you can put it into practice, so please refer to it.

2. Basic Usage of mysqldump

mysqldumpis a command-line tool for exporting the contents of a MySQL database as an SQL file. The basic syntax is very simple, and backups can be performed with just a few lines of commands. In this section, we will explain commonly used basic export methods.

Exporting a Single Database

The most common case is exporting an entire single database.

mysqldump -u username -p database_name > backup.sql

After execution, you will be prompted for a password, and the contents of the specified database will be output to a file calledbackup.sql.

Exporting Multiple Databases

If you want to back up multiple databases at the same time, use the--databasesoption.

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

In this format,CREATE DATABASEstatements are included for each target database, making it highly reproducible and convenient during import.

Exporting All Databases

If you want to back up all databases on the server, use the--all-databasesoption.

mysqldump -u username -p --all-databases > all_backup.sql

This command exports all databases existing on the MySQL server (includingmysql,information_schema,performance_schema, etc.) in bulk.

Example of Including Date in Output File Naming

For periodic backups, etc., including the date in the output file name makes management easier. The following is an example of date-included output in a UNIX shell.

mysqldump -u username -p database_name > backup_$(date +%F).sql

By doing this, files likebackup_2025-04-13.sqlare automatically generated, making backup management easier.

3. Export Variations

mysqldumpnot only outputs the entire database but also allows flexible exports depending on the situation. In this chapter, we introduce various advanced uses, such as methods to extract only specific tables, output only the schema or only the data, and export data with specified conditions.

Exporting Only Specific Tables

If you want to back up only specific tables within a database, you can achieve this by explicitly specifying the table names.

mysqldump -u username -p database_name table1 table2 > selected_tables.sql

Example:

mysqldump -u root -p mydb users orders > users_orders.sql

This command exports only theusersandorderstables from themydbdatabase.

Exporting Only Data or Only Schema

mysqldumphas options to output only the schema definition or only the data.

• Output Only Schema (Structure):

  mysqldump -u username -p --no-data database_name > schema_only.sql

• Output Only Data (Excluding CREATE TABLE Statements):

  mysqldump -u username -p --no-create-info database_name > data_only.sql

These are useful when you want to share only the schema between development and production environments, or when you want to insert differential data.

Exporting Data Conditionally (–where)

If you want to extract and export only part of the data, you can use the--whereoption. It uses the same syntax as SQL’s WHERE clause.

mysqldump -u username -p database_name table_name --where="condition" > filtered_data.sql

Example:

mysqldump -u root -p mydb users --where="created_at >= '2025-01-01'" > users_2025.sql

In this example, only the records created from 2025 onwards in theuserstable are exported.

4. Commonly Used Options and Their Explanations

mysqldumpis a simple command, but by combining numerous options, you can perform safer and more efficient backups. In this chapter, we select and explain the options that are particularly frequently used in practice, along with their roles and usage scenarios.

–single-transaction: Maintaining Transaction Consistency

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

This option is effective when using transaction-supporting storage engines like InnoDB. By executing the dump process as a single transaction, it maintains consistency during export without needing read locks. It’s very convenient when you want to continue operations without stopping the service during backup.

Note: It has no effect on non-transactional engines such as MyISAM.

–quick: Reducing Memory Usage

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

By adding this option, mysqldump fetches data row by row sequentially instead of loading all data at once. This allows exporting while suppressing memory consumption, making processing more stable even for large tables.

–routines, –events: Exporting Stored Procedures and Events

By default, stored procedures and events are not included in the export. By using the following options, you can include them in the backup.

mysqldump -u username -p --routines --events database_name > backup_with_logic.sql

• --routines: Include stored procedures and functions
• --events: Include scheduled events

If much of the business logic is implemented on the database side, be sure not to forget to export these.

–add-drop-table: Convenient for Overwriting Tables

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

Adding this option appends aDROP TABLE IF EXISTSstatement before exporting each table. If a table with the same name already exists at the import destination, it is automatically deleted and recreated, making it very useful for overwrite imports.

–lock-tables: Effective for Lock Control in MyISAM

mysqldump -u username -p --lock-tables database_name > backup.sql

It is not usually used in InnoDB, but when using MyISAM, it locks the tables to prevent writes before exporting. Use it for backups that prioritize consistency.

5. How to Import Exported Files

mysqldump exported SQL files can be restored using MySQL’s standard import method. This chapter covers the basics of importing backup files, practical restore use cases, and important cautions.

Basic Import Command

Exported SQL files can be easily imported using the mysql command. The basic syntax is as follows.

mysql -u username -p database_name < backup.sql

Example:

mysql -u root -p mydb < backup.sql

When you run this command, the SQL statements contained in backup.sql are executed sequentially, restoring the database to its original state.

Importing into a New Database

Because backup files may not contain a CREATE DATABASE statement, you need to create a new database beforehand if you want to import into a database with a different name.

Example: Create a new database “mydb_restore” and import

CREATE DATABASE mydb_restore;

mysql -u root -p mydb_restore < backup.sql

Note: SQL exported with the --databases option includes a CREATE DATABASE statement, so the steps above differ in that case. Please be aware of this.

Importing Compressed Files (.gz)

If the backup file is compressed with gzip or similar, you can import it directly while decompressing.

gunzip < backup.sql.gz | mysql -u username -p database_name

Using this method saves disk space while allowing a fast restore.

Common Import Errors and How to Resolve Them

Summary: Treat Import and Export as a Pair

A backup file obtained with mysqldump is meaningless if you only create it. Ensuring that you can restore it when needed is the true value of a backup. Therefore, we recommend regularly testing imports to verify that the file can be read correctly.

6. Practical Tips and Precautions

mysqldumpis convenient to use, but for large-scale databases or production environments, careful operation and ingenuity may be required in some cases. This chapter introduces techniques useful in real-world work and precautions to prevent troubles in advance.

Compress Large Databases with gzip

mysqldumpoutputs SQL files in text format, which can become extremely large. Especially for large databases exceeding several GB, it’s common to combine withgzipto compress the output files.

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

This method not only significantly saves disk space but also reduces the load during remote transfers.

Make Verification of Export and Import a Habit

Even if you can take backups, if you can’t import them when needed, they’re meaningless. We recommend the following practices:

• Periodically restore and test backup files in a separate environment
• Check files for corruption using md5sum or sha256sum
• For important databases, keep backups for multiple generations

Be Careful of Inconsistencies Due to Version Differences

If the MySQL version differs between the export source and import destination, the SQL file may not execute properly due to differences in syntax or internal specifications.

• If possible, operate with the same version
• When crossing versions, control with options (e.g., --set-gtid-purged=OFF)
• Before and after version upgrades, always check schema definition compatibility

Utilize cron and Scripts for Automation

If you want to automatically take backups daily or weekly, you can manage them efficiently by using shell scripts and cron.

#!/bin/bash
DATE=$(date +%F)
mysqldump -u root -p[password] mydb | gzip > /backup/mydb_$DATE.sql.gz

By placing such a script in/etc/cron.daily/, you can automatically take backups every day.

Note: From a security perspective, avoid directly specifying passwords; it’s recommended to manage them securely using a .my.cnf file.

Also Consider Security

Backup files may contain sensitive information. Take the following measures:

• Set appropriate access restrictions on the storage location of files
• Encrypt for storage and transfer (e.g., use GPG or SFTP)
• When storing on the cloud, also consider automatic backup settings and lifecycle management

7. Frequently Asked Questions (FAQ)

Here, we summarize in Q&A format the points that many people often wonder about when usingmysqldump, as well as common troubles that are likely to occur.Q1. Why does the “Access denied” error occur during export?A.The specified MySQL user may lack permissions such as “SELECT” or “LOCK TABLES” for the target database. Check the necessary permissions and have an administrator add them if they are insufficient. Additionally, if table locking fails, you can avoid it by using the--single-transactionoption.Q2. The backup file size becomes very large. Is there a way to reduce it?A.If there are large tables or a large amount of data, the SQL file can reach sizes in the GB range. The following methods can reduce the size:

• Compress and save using gzip etc. (e.g., mysqldump ... | gzip > backup.sql.gz)
• Output only the necessary parts using --no-data or --no-create-info options
• Filter and output data using the --where option (e.g., data for a specific period)

Q3. I want to export only specific tables. How can I do that?A.By listing the table names to export at the end of the command, you can output only the desired tables.

mysqldump -u root -p mydb users orders > selected.sql

It supports multiple tables, so it’s convenient when you want to back up only the necessary tables.Q4. Stored procedures or events are not exported. Why is that?A.mysqldumpdoes not include stored procedures (routines) or scheduled events by default. If you want to include them in the output, add the following options.

--routines --events

Also, make sure the target user has permissions to access those objects.Q5. What are the precautions when transferring a file to another server and importing it?A.The main precautions are as follows:

• Character Encoding: If the character encoding differs between servers, it can cause garbled text. It’s safer to specify it explicitly with --default-character-set=utf8 etc.
• Version Differences: Check if there is compatibility between the MySQL versions of the export source and import destination.
• Network Transfer: Use scp, rsync, or SFTP etc. to transfer securely.
• File Corruption Check: It is recommended to verify the integrity of the file after transfer using md5sum or sha256sum.

Q6. Are there differences in commands between Windows and Mac/Linux?A.The basic command syntax is the same, but there are differences in shell specifications, batch processing methods, date command formats, etc. Especially for automatic generation of date-attached filenames, use PowerShell or the%DATE%variable on Windows, and thedatecommand on Linux or Mac.

8. Summary

In this article, we have covered the toolmysqldump, which plays a key role in backing up and migrating MySQL databases, from basic usage to advanced techniques and troubleshooting methods.mysqldumphas a very simple syntax, but if you don’t use appropriate options or command tweaks tailored to your purpose, there can be significant differences in backup quality and restoration reliability.

✅ Key Points Learned in This Article

• Basic Syntax of mysqldump and 3 Export Methods (Single, Multiple, All Databases)
• Variety of Outputs Such as Schema Only, Data Only, Specific Tables Only, and More
• Explanation of Key Options Useful in Practice, Such as –single-transaction and –routines
• Basic Commands for Restoration and Handling Import Errors
• Tips for Practical Use, Such as Combining with gzip and cron Automation
• Practical Troubleshooting Knowledge via FAQ

🛡 Best Practices for Using mysqldump

1. Not Only Acquire Backups but Also Verify They Can Be Restored
2. Prepare for Issues Due to Environmental Differences, Such as Version Discrepancies and Character Encoding
3. In Operational Environments, Design Consistently Up to Compression, Automation, and Permission Management
4. Backup Including Stored Procedures, Events, and More in a Configuration Similar to Production

A properly designed and operated backup system usingmysqldumpenables rapid recovery even in the event of a failure and contributes to reliable system operations. Especially in scenarios like corporate systems or WordPress sites where data loss can be fatal, understanding and utilizing mysqldump is extremely important.

Using this article as a reference, please try building the optimal backup strategy for your company or your own operations.