.SQL File Format – MySQL dumps

The start of the file

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

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

The comment names the tool, the host, the database and the server version. A MariaDB server’s mysqldump says MariaDB dump. The settings are wrapped in executable comments, each saving the old value so the end of the file can restore it.

Each table

For each table, mysqldump writes a comment, the structure and then the rows.

DROP TABLE IF EXISTS `fruit`;
CREATE TABLE `fruit` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOCK TABLES `fruit` WRITE;
/*!40000 ALTER TABLE `fruit` DISABLE KEYS */;
INSERT INTO `fruit` VALUES (1,'apple'),(2,'pear');
/*!40000 ALTER TABLE `fruit` ENABLE KEYS */;
UNLOCK TABLES;

By default many rows go in each INSERT, and the statement stays as a single line. See INSERT. Foreign keys are part of the CREATE TABLE, and FOREIGN_KEY_CHECKS=0 at the top allows tables to be loaded in any order.

Options that change the file

Views, triggers and routines

A view is first written as a stand-in, so views can refer to each other, and replaced at the end of the dump with its real definition. Triggers and routines are in DELIMITER blocks, with their keywords split across executable comments so that older servers ignore the parts they do not know.

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`app`@`localhost`*/ /*!50003 TRIGGER `fruit_ai` AFTER INSERT ON `fruit` FOR EACH ROW BEGIN
  UPDATE stats SET n = n + 1;
END */;;
DELIMITER ;

phpMyAdmin

phpMyAdmin’s file starts -- phpMyAdmin SQL Dump, names its version and the server, and writes rows with the columns named, a row to a line. It leaves keys out of the CREATE TABLE, and adds them near the end of the file.

ALTER TABLE `fruit`
  ADD PRIMARY KEY (`id`),
  ADD KEY `name` (`name`);

ALTER TABLE `fruit`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;