Taking backups using mysqldump

mysqldump is a widely used logical backup tool. It gives a variety of options to include or exclude databases, select specific data to be backed up, back up only the schema without data, or just take a backup of stored routines without anything else, and more.

Full backup of all databases

mysqldump -u root -p --all-databases > dump.sql

You can open the dump.sql file to see how it is structured. Since the data is stored as SQL statements, this is called logical backup.

less dump.sql

-- MySQL dump 10.13  Distrib 8.0.12, for Linux (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version       8.0.12

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 SET NAMES utf8mb4 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `bank`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bank` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */;

USE `bank`;

--
-- Table structure for table `account`
--

DROP TABLE IF EXISTS `account`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `account` (
  `account_number` varchar(10) NOT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`account_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `account`
--

LOCK TABLES `account` WRITE;
/*!40000 ALTER TABLE `account` DISABLE KEYS */;
INSERT INTO `account` VALUES ('A',400),('B',600);
/*!40000 ALTER TABLE `account` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Current Database: `company`
--
...

You will notice that when you restore the dump, the DROP TABLE statement will wipe off all the tables before creating the tables.

Point-in-time recovery

To get point-in-time recovery, you should specify --single-transaction and --master-data.

The --single-transaction option provides consistent backup by changing the transaction isolation mode to REPEATABLE READ and executing START TRANSACTION before taking a backup. It is useful only with transactional tables, such as InnoDB, because then it dumps the consistent state of the database at the time START TRANSACTION was issued without blocking any applications.

The --master-data option prints the binary log coordinates of the server to the dump file. If --master-data=2, it prints as a comment. This also uses the FLUSH TABLES WITH READ LOCK statement to get a snapshot for the binary logs.

mysqldump -u root -p --all-databases --routines --events --single-transaction > dump.sql

Dumping master binary coordinates

Backups are always taken on slaves. To get the binary log coordinates of the master when the backup was taken, you can use the --dump-slave option. If you are taking the binary log backup from the master, use this option. Otherwise, use the --master-data option:

mysqldump -u root -p --all-databases --routines --events --single-transaction --dump-slave > dump.sql

mysqldump: Couldn't execute 'START SLAVE': The server is not configured as slave; fix in config file or with CHANGE MASTER TO (1200)

mysqldump -u root -p --all-databases --routines --events --single-transaction --master-data > dump.sql