Posted inMySQL

High-Speed MySQL Backup & Restore: Reducing Time from 15 Hours to 2 Hours with Mydumper

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

When mysqldump becomes a burden on the system

Early in my career, I always used mysqldump because it was the default tool and extremely easy to use. Everything went smoothly until the project I was managing hit the 200GB data mark. At this point, log tables had exceeded 50 million records, turning nightly backups into a real challenge.

The biggest problem with mysqldump is that it is single-threaded. It only utilizes a single CPU core to process massive amounts of data. As a result, it took me more than 6 hours to finish a backup. Worse, when I tried a test restore, the waiting time stretched to nearly 15 hours. In a production environment, if a server fails and it takes half a day to recover, the technical team’s reputation would surely evaporate.

After switching to mydumper and myloader, the backup time dropped to 40 minutes. The restore process also took less than 2 hours. Here are the practical experiences to help you master this duo.

Why is mydumper superior?

The difference lies in the approach to data. If mysqldump is like moving a house using a single small truck driving back and forth, then mydumper is an entire fleet of trucks starting at the same time.

  • Multi-threading Power: You can mobilize 8, 16, or 32 threads to read data in parallel.
  • Chunking Mechanism: Instead of exporting a single SQL file weighing hundreds of GBs, mydumper splits data by table. For large tables, it automatically cuts them into multiple parts (chunks). This allows myloader to load multiple files into the database simultaneously, maximizing server resources.
  • Instant Compression: Compression happens right during the dump, saving storage space without causing a CPU bottleneck.
  • Consistency Assurance: This tool uses FLUSH TABLES WITH READ LOCK momentarily to get binlog coordinates, then uses transactions for InnoDB tables to ensure data consistency (snapshot).

Quick Installation on Linux

Mydumper does not come pre-installed with MySQL. However, installation on popular distros like Ubuntu or CentOS is very quick:

# For Ubuntu/Debian
sudo apt update
sudo apt install mydumper -y

# For CentOS/RHEL
sudo yum install epel-release -y
sudo yum install mydumper -y

If your system requires the latest features, prioritize downloading the pre-built binaries from the project’s official GitHub page.

How to Backup Effectively with mydumper

Suppose you need to backup the prod_db database. Instead of simple commands, use these optimized parameters:

mydumper \
  --host=127.0.0.1 \
  --user=admin_user \
  --password='your_password' \
  --database=prod_db \
  --threads=8 \
  --rows=500000 \
  --compress \
  --build-empty-files \
  --outputdir=/data/backups/$(date +%F) \
  --verbose=3

Decoding key parameters:

  • --threads=8: Utilizes 8 CPU cores. My experience is to set the number of threads to about 70% of the server’s total cores.
  • --rows=500000: This is the most important “weapon.” If an orders table has 10 million rows, it will be split into 20 small files instead of one giant file. This makes future restores many times faster.
  • --compress: Compresses output files using gzip, significantly reducing the burden on the hard drive.

Blazing Fast Recovery with myloader

When you need to rescue data, myloader will automatically read the metadata in the backup directory to perform the most optimal recovery process.

myloader \
  --host=127.0.0.1 \
  --user=admin_user \
  --password='your_password' \
  --directory=/data/backups/2023-10-27 \
  --queries-per-transaction=50000 \
  --threads=8 \
  --database=prod_db_restored \
  --overwrite-tables \
  --verbose=3

Pro tip: Before running this command, temporarily increase <a href="https://itfromzero.com/en/mysql-en/optimizing-my-cnf-for-mysql-8-dont-let-your-server-choke-on-default-settings.html">innodb_buffer_pool_size</a> to the highest possible level to give MySQL more processing space.

4 Critical Lessons for Real-World Operation

After 6 months of applying this to large systems, I’ve drawn 4 lessons to help you avoid unfortunate incidents:

1. Control Disk IOPS

Due to multi-threading, mydumper consumes significant read/write resources. If you run a backup directly on the disk containing the database, the application may experience lag or hang (slow queries spike). Ideally, backup to a separate drive or perform the dump on a Slave (Read Replica).

2. Don’t Forget the –rows Parameter

Many people forget --rows, leading to a 100GB table being processed by only 1 thread. In this case, even if you set 64 threads, the speed will still be snail-paced due to a bottleneck at the largest table.

3. Temporary Directory Capacity

Ensure the partition containing the backup data has free space at least 1.5 times the actual database size. Running out of memory midway through the dump will corrupt the entire backup.

4. Optimize MySQL Configuration During Restore

To achieve maximum data loading speed, temporarily disable constraint checking mechanisms with these commands:

SET GLOBAL autocommit = 0;
SET GLOBAL unique_checks = 0;
SET GLOBAL foreign_key_checks = 0;

Don’t forget to re-enable these parameters after the restore process is complete to ensure data integrity.

In summary, if your database has exceeded the 20GB threshold, move mysqldump aside. Switching to mydumper not only saves time but is also the safest insurance policy for your system when facing the risk of data loss.

Share:
Tags: