< BACK TO BLOG

Backup and restore MySQL databases in an efficient way

Published 31 August, 2021

mysqldump allows us to create logical backups of our databases, as well as restoring them.

A logical backup uses SQL statements to create the contents of the database, such as tables, views, triggers and inserts the data using the INSERT statement.

For example:

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `two_factor_secret` text COLLATE utf8mb4_unicode_ci,
  `two_factor_recovery_codes` text COLLATE utf8mb4_unicode_ci,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `current_team_id` bigint unsigned DEFAULT NULL,
  `profile_photo_path` varchar(2048) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_admin` tinyint(1) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `users` (`id`, `name`, `email`, `email_verified_at`, `password`, `two_factor_secret`, `two_factor_recovery_codes`, `remember_token`, `current_team_id`, `profile_photo_path`, `is_admin`, `created_at`, `updated_at`) VALUES
(1, 'Prof. Norwood Moen', 'moen@example.com', '2021-08-28 20:39:31', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', NULL, NULL, 'Ji0Eqw70n1', NULL, NULL, 1, '2021-08-28 20:39:31', '2021-08-28 20:39:31');

In this post, I will show you how to backup MySQL using mysqldump in an efficient way.

Basic of mysqldump

You can easily backup your database using the following command:

mysqldump your_database > database.sql

To make the backup file smaller, we may compress it using gzip :

mysqldump mysql | gzip > database.sql.gz

gzip is a great tool, but it doesn't work properly on a multi-core system.

pigz

An alternative tool to gzip is pigz which stands for parallel Implementation of gzip.

pigz exploits multiple processors and multiple cores to the hilt when compressing data [Source]:

sudo apt-get install -y pigz

mysqldump mysql | pigz > database.sql.gz

Data Inconsistency

Let's say that your database has users and posts tables, then what happens if the users table is dumped out, a new user is created before the posts table is dumped?

As you can probably tell, mysqldump will export inconsistent data, it will have a post related to a user that doesn't exist.

We can easily get around this issue by running the export operation within a transaction, which keeps our data inconsistent:

mysqldump -u your_user -p --single-transaction \
    --default-character-set=utf8mb4 \
    your_database | pigz > database.sql.gz

Restore database

Let's see how to restore the database:

gunzip < database_file.sql.gz | mysql -u your_user -p your_database

That's it 🥳

Edit on Github