Backing Up and Restoring Databases - Learning MySQL and MariaDB - page 336

Understanding Dump Files
After the mysqldump commandin the previous section has finished running, use a
simple text editor to open the dump file that it generated. Scroll through the file to exam-
ine the contents. You'll notice quite a few things: the utility annotates the dump file, sets
certain variables, then lists CREATE DATABASE , CREATE TABLE , and many INSERT
statements. Let's review a few of those entries so you'll have a better understanding of
dump files. This will be useful later when you need to restore a database.
First, let's look at the header. Here's an example of the first few lines in a dump file gener-
ated by mysqldump using the settings from the previous example:
-- MySQL dump 10.14 Distrib 5.5.39-MariaDB, for Linux (i686)
--
-- Host: localhost Database: rookery
-- ------------------------------------------------------
-- Server version 5.5.39-MariaDB
The first line of the dump file lists the version of mysqldump that was used and the distri-
bution of MySQL, or in this case, MariaDB, and on which operating system the command
was executed. Next, we see that the dump was executed while logged into the server, from
the local host. On the same line, we find the name of the first database to be backed up.
The next line, after some dashes for nicer formatting, is the version number of MariaDB
— that was given in the line showing the distribution, but here it's more clearly listed.
Next in the dump file come abatch of SET statements that look something like
Example14-1 .
Example 14-1. Conditional SET commands in dump file
/*!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 */ ;
/*!40101 SET NAMES utf8 */ ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */ ;
/*!40103 SET TIME_ZONE='+00:00' */ ;
/*!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 */ ;
The way these SET statements are enclosed between /* and */ , they may seem to be
comments that won't be processed. However, they're SQL statements or tokens that will