The World of MySQL & MariaDB

Safe Backups Without Downtime: mysqldump –single-transaction Guide

Backup and Restore
目次

1. The Importance of Backups to Maintain Database Consistency

Database Backups Are “Insurance”

In business, the database is like the heart of operations. Customer information, transaction history, inventory data, and more—all business activities depend on the database. If data is lost due to a failure or human error, it could lead to serious risks such as business suspension or loss of trust. Therefore, regular backups are an essential operation, serving as “insurance”.

How to Ensure Data Integrity During Backups?

However, a challenge when taking backups is ensuring “consistency (integrity).” Especially when you want to perform backups without stopping the system while it’s running, maintaining data consistency is a very important point. For example, if someone updates a table during the backup, there could be a discrepancy between the dumped content and the actual database.

What is mysqldump? And What is –single-transaction?

The widely used backup tool for MySQL and MariaDB is “mysqldump.” With simple command-line operations, you can export the entire database or specific tables as SQL files. This mysqldump option, used to obtain backups while maintaining consistency, safely and efficiently, is the theme of this article: --single-transaction. This option uses transactions (processing units) to save a “snapshot-like state at one point in time” while allowing backups without blocking other processes; it’s a very convenient feature.

What You Can Gain from This Article

In this article, we thoroughly explain the usage, precautions, and combinations with other options for the mysqldump --single-transaction option from a practical perspective.
  • People who want to understand the basics of mysqldump from the ground up
  • People who want to correctly understand the meaning of --single-transaction
  • People who want to build safe backup operations in practice
For such people, we will explain clearly, including actual command examples. Please read to the end.

2. Basic Usage of mysqldump

What is mysqldump?

mysqldump is a standard command-line tool for backing up MySQL or MariaDB databases. It can export table structures and data in SQL format, and during restoration, you can easily return to the original state by simply executing that SQL. As a backup method, it is very convenient and powerful, and is widely used regardless of development or production environments.

Basic Syntax of mysqldump

The following is the simplest form of the mysqldump command.
mysqldump -u username -p database_name > output_file.sql
  • -u: Username to log in to MySQL
  • -p: Flag to prompt for password (input cannot be omitted)
  • database_name: Target database for backup
  • >: Specify output file (redirection)
After executing the command, you will be prompted for the password, and if there are no issues, an SQL format backup file will be generated.

Backing Up Only Specific Tables

If you want to back up only specific tables instead of the entire database, specify the table names as follows.
mysqldump -u username -p database_name table_name1 table_name2 > output_file.sql
For example, if you want to back up only the users and orders tables, it would be like this.
mysqldump -u root -p shop_db users orders > users_orders.sql

Backing Up Multiple Databases

Using the -B option, you can back up multiple databases at once.
mysqldump -u username -p -B database1 database2 > multi_backup.sql
With this method, since it includes CREATE DATABASE statements, it’s convenient because you can use it as-is during restore.

Bulk Backup of All Databases

If you need a backup of the entire system, use -A (or --all-databases).
mysqldump -u username -p -A > all_databases.sql
With this command, all databases on the MySQL server are dump targets. Since it also includes MySQL’s system databases (mysql,information_schema, etc.), it is suitable for environment migrations and such.

How to Restore a Backup

The SQL file obtained with mysqldump can be restored with a command like the following.
mysql -u username -p database_name < output_file.sql
In this way, you can restore backup data to a new environment or in case of damage.

3. –single-transaction Option Details

The Key to Maintaining Consistency During Backup

mysqldump command, by default, dumps each table in sequence. Therefore, if another user modifies data during the backup process, an intermediate state may be saved, potentially leading to data inconsistency upon restoration. This issue is resolved by the --single-transaction option.

Basic Mechanism of –single-transaction

When using this option, mysqldump executes a BEGIN statement at the start of the dump process to begin a transaction. This creates a snapshot (static state) of the database at that point, allowing the backup to complete without being affected by changes from other transactions during the dump. In other words, since the entire state at the “initial instant of the dump” can be backed up, consistency is maintained.

Available Only for InnoDB

What requires attention here is that the --single-transaction option is effective only for tables using the InnoDB storage engine. Since InnoDB supports transactions, it is possible to create and maintain snapshots. On the other hand, if using storage engines that do not support transactions, such as MyISAM or MEMORY, the effect of --single-transaction cannot be obtained. In such cases, lock options such as --lock-tables or --lock-all-tables are necessary as alternatives.

Differences from Table Locking

By default, mysqldump locks tables to maintain data consistency (--lock-tables is automatically enabled). However, this method has the drawback that other users cannot update data = service downtime occurs. Using --single-transaction allows backups without locking, enabling backups without stopping the service. This is a highly valuable feature, especially in production environments.

Image Diagram (Text Explanation)

[Normal mysqldump]
Time progression → [users table dump starts] → [data change midway] → [orders table dump starts] → Data inconsistency occurs

[Using --single-transaction]
Time progression → [Snapshot creation with BEGIN] → [Dump all users, orders at a consistent point] → Safe backup completion

Note: Ineffective Against DDL Operations

However, while --single-transaction is strong against data changes (INSERT, UPDATE, DELETE), it is ineffective against table definition changes (CREATE, DROP, ALTER, etc.). If DDL operations occur during backup, errors or lack of consistency may arise. Therefore, it is ideal to schedule backups during maintenance windows or at times avoiding DDL as much as possible.

Summary

--single-transaction is a highly effective means in MySQL environments using InnoDB to obtain consistent backups without stopping the service. By understanding its mechanism and using it correctly, safe and efficient data protection can be achieved.

4. How to Use the –single-transaction Option

Basic Command Example

Let’s first check the simplest way to use --single-transaction.
mysqldump --single-transaction -u username -p database_name > output_file.sql
This command saves the state of the database using the InnoDB engine at the time the transaction starts as a backup. A major advantage is that it does not affect other processes during the backup and allows work without stopping the service.

Backing Up by Specifying Multiple Tables

You can also apply --single-transaction to back up only specific tables.
mysqldump --single-transaction -u root -p shop_db users orders > users_orders.sql
Even if you specify tables individually like this, it creates a consistent snapshot, and data from the same point in time is exported.

Backing Up Multiple Databases Together

To target multiple databases, use it in combination with the -B (or --databases) option.
mysqldump --single-transaction -u root -p -B db1 db2 > multi_db_backup.sql
In this format, each database is dumped including the CREATE DATABASE statement, which increases convenience during restore.

Backing Up All Databases

If you need a system-wide backup, use it in combination with the -A (or --all-databases) option.
mysqldump --single-transaction -u root -p -A > full_backup.sql
This command dumps all databases on the MySQL server (including mysql, information_schema, performance_schema, etc.), making it effective for server migration or full restore.

Points to Check During Execution

  • Storage Engine Must Be InnoDB --single-transaction is only effective for InnoDB. It will not behave as expected with transaction-unsupported engines like MyISAM.
  • Do Not Use with --lock-tables Since --single-transaction and --lock-tables have conflicting behaviors, the consistency guarantee is broken. It’s safer to explicitly add --skip-lock-tables.

Common Execution Example (Recommended Configuration)

mysqldump --single-transaction --quick --skip-lock-tables -u root -p production_db > backup.sql
This configuration has the following features:
  • --quick: Outputs row by row without loading large amounts of data into memory at once, reducing memory usage
  • --skip-lock-tables: Explicitly avoids automatic locking to ensure safe behavior

Example of Automation in Shell Script

In actual operations, it’s common to script regular backups and automate execution with cron or similar.
#!/bin/bash
DATE=$(date +%F)
mysqldump --single-transaction --quick --skip-lock-tables -u root -pYourPassword production_db > /backups/production_$DATE.sql
※It is recommended to manage passwords with environment variables or configuration files.

5. Notes on the –single-transaction Option

No Effect on Non-Transaction-Supporting Engines (e.g., MyISAM)

This option is only effective for transaction-supporting storage engines (mainly InnoDB). Engines such as MyISAM and MEMORY do not have transaction functionality, so specifying --single-transaction does not guarantee consistency.Example:
  • users table is InnoDB
  • logs table is MyISAM
In such a mixed environment, when taking a backup, the users table will maintain consistency, but the logs table may be affected by other operations.Countermeasures:
  • Uniformly use InnoDB as much as possible.
  • If MyISAM etc. are mixed, consider using --lock-all-tables.

Powerless Against DDL Operations (Table Definition Changes)

--single-transaction is strong for data operations (SELECT, INSERT, UPDATE, DELETE) during the dump, but it cannot handle DDL (CREATE, DROP, ALTER, etc.). If the table definition is changed during the dump, there are risks such as the following.
  • Table deleted midway through the dump → Error occurs
  • Table definition changed during the dump → Schema inconsistency
Countermeasures:
  • Establish a rule not to perform DDL during backup execution.
  • If possible, perform backups during maintenance time.

Cannot Be Used in Combination with --lock-tables

mysqldump enables the --lock-tables option by default, but this performs conflicting operations with --single-transaction. Since table locks are executed before the transaction starts, the consistency guarantee is broken. Therefore, when using --single-transaction, it is recommended to explicitly use --skip-lock-tables in combination.
mysqldump --single-transaction --quick --skip-lock-tables -u root -p dbname > backup.sql

Consistency Guarantee Only at the “Start Point”

--single-transaction captures the state at the point when mysqldump starts the transaction as a snapshot. Changes made afterward are naturally not included in the dump. This avoids lock contention due to changes during the dump, but it is necessary to recognize that it is merely a “snapshot at a certain point”.

For Large Amounts of Data, Use in Combination with –quick

When backing up large amounts of data, mysqldump tries to load the entire table into memory by default, which may cause memory shortages or swapping. In such cases, by using the --quick option in combination as below, and adopting a method of reading and outputting one row at a time, you can significantly reduce memory usage.
mysqldump --single-transaction --quick --skip-lock-tables -u root -p dbname > backup.sql

Summary

--single-transaction is a powerful option that allows for consistent backups without stopping the service, but to use it correctly, it is necessary to understand several precautions. When using it in a production environment, consider compatibility with storage engines and DDL operations, and perform backups at appropriate times and settings.

6. Combining the –single-transaction Option with Other Options

–quick: The Strongest Partner for Reducing Memory Consumption

mysqldump --single-transaction --quick -u root -p dbname > backup.sql
--quick option causes mysqldump to read data into memory not all at once, but outputs while reading one row at a time. This is particularly effective for handling large tables and can significantly reduce memory usage.Benefits:
  • Can suppress memory usage during backup processing
  • Prevents swap occurrences and processing failures
  • Improves stability in large data environments
Recommendation: If using --single-transaction, it’s best practice to almost always combine it with --quick.

–skip-lock-tables: Explicitly Avoiding Automatic Locking

mysqldump tries to enable the --lock-tables option by default, but this becomes an action that conflicts with --single-transaction. To avoid conflicts, explicitly specify --skip-lock-tables for peace of mind.
mysqldump --single-transaction --quick --skip-lock-tables -u root -p dbname > backup.sql
Benefits:
  • Makes the command’s intent clear and easier to avoid troubles
  • Prevents errors or warnings due to option conflicts

–master-data: Optimal for Replication Purposes

In MySQL’s replication configuration, to perform accurate synchronization on the slave side, it’s common to use the --master-data option.
mysqldump --single-transaction --quick --master-data=2 -u root -p dbname > repl_backup.sql
Specifying --master-data=2 records the current binary log file name and position in the dump file in a commented-out form. When starting up the slave, synchronization is possible using this information.Notes:
  • --master-data should also be used under the premise of InnoDB, like --single-transaction.
  • It is sometimes used in combination with --flush-logs to rotate binary logs.

–set-gtid-purged=OFF: When You Want to Disable GTID

In environments using GTID (Global Transaction ID), mysqldump tries to automatically write the GTID definition, but there are cases where you want to disable this.
mysqldump --single-transaction --quick --set-gtid-purged=OFF -u root -p dbname > no_gtid.sql
Uses:
  • Temporary backup outside of slave configuration
  • When wanting to move data to another environment

Examples of Combinations with Other Options (Comprehensive)

mysqldump   --single-transaction   --quick   --skip-lock-tables   --master-data=2   --set-gtid-purged=OFF   -u root -p production_db > production_backup.sql
In this way, by combining multiple options, you can build a practical backup script that covers “consistency,” “memory efficiency,” “replication support,” and “GTID management.”

Summary

--single-transaction is powerful on its own, but by combining it with other options, you can achieve an optimal backup strategy suited to your environment and purpose. In particular, combining with --quick or --skip-lock-tables is almost essential, and if operating replication, you should also consider using --master-data. To make the most of mysqldump, selecting options according to your purpose is key.

7. Frequently Asked Questions (FAQ)

Here, we have compiled questions and answers that are frequently asked in practice regarding mysqldump --single-transaction. Please use this as a reference for preventing troubles on site and for operational design.

Q1. In what cases is it appropriate to use the --single-transaction option?

A1.It is optimal when using the InnoDB storage engine and wanting to obtain a consistent backup without stopping the service. It is particularly useful in production environments like e-commerce sites or reservation systems where there is constant user access.

Q2. Can --single-transaction be used even if MyISAM tables are included?

A2.It can be used, but the data integrity of MyISAM tables is not guaranteed. Since MyISAM does not support transactions, if there are updates during the backup, the dump content may become inconsistent. If MyISAM tables are included, consider using --lock-all-tables instead.

Q3. What happens if --single-transaction and --lock-tables are used together?

A3.Since they have opposing behaviors, mysqldump automatically disables one of them. However, it is safer to explicitly specify --skip-lock-tables to avoid unintended behavior or warnings.

Q4. What happens if DDL operations such as CREATE TABLE or ALTER TABLE are performed during the dump?

A4.--single-transaction is powerless against DDL operations. If the table definition is changed during the dump, there is a risk of dump failure or inconsistent backup. Therefore, it is ideal to schedule backups during maintenance windows or at times when DDL operations are avoided as much as possible.

Q5. Are there any options recommended to use in combination when using --single-transaction?

A5.Yes, combining the following options can improve the safety and efficiency of backups.
  • --quick: Suppresses memory usage for stable dumps
  • --skip-lock-tables: Explicitly avoids table lock conflicts
  • --master-data=2: Supports backups for replication
  • --set-gtid-purged=OFF: Allows flexible handling even in environments without GTID support

Q6. Are there ways to shorten the backup time?

A6.The following measures are effective:
  • Speed up while suppressing memory usage with the --quick option
  • Backup only specific tables (partial backup)
  • Slim down the backup target data in advance by archiving or deleting
  • Output backup files to SSD environments or high-speed storage

Q7. How to restore a backup using --single-transaction?

A7.Just like regular mysqldump files, it can be restored with the following command.
mysql -u username -p database_name < backup.sql
For backups that include binary logs or GTID, adjustments or settings may be necessary before restoration (e.g., CHANGE MASTER TO command, etc.).

8. Summary

mysqldump --single-transaction is a powerful method for obtaining consistent backups without stopping the service in MySQL or MariaDB environments. In this article, we have comprehensively explained everything from its mechanism to usage methods, precautions, combinations with other options, and even FAQs.

Let’s Review the Key Points of This Article

  • --single-transaction utilizes InnoDB’s transaction functionality to achieve snapshot-based consistent backups.
  • By using it, you can safely obtain dumps in production environments while avoiding table locks.
  • However, it does not support non-transactional engines like MyISAM or DDL operations, so it is essential to operate with a full understanding of the use case and environment.
  • Combinations with options such as --quick, --skip-lock-tables, and --master-data, tailored to the purpose, enhance quality and efficiency.
  • The FAQ covers content that addresses common practical questions and supports peace of mind on-site.

For Safe and Efficient Backup Strategies

In business or service operations, data loss directly leads to credibility issues. By utilizing mysqldump and --single-transaction, you can achieve backup operations that balance reliability and practicality. The key is to advance one step beyond ‘just backing up’ to the mindset of obtaining consistent backups that are reliable during failures. To that end, please apply the knowledge from this article to your daily operations.