Databases Reference
In-Depth Information
Backing Up Data
As with most things, there are better and worse ways to actually make a backup—and
the obvious ways are sometimes not so good. The trick is to maximize your network,
disk, and CPU capacity to make backups as fast as possible. This is a balancing act,
and you'll have to experiment to find the “sweet spot.”
Making a Logical Backup
The first thing to realize about logical backups is that they are not all created equal.
There are actually two kinds of logical backups: SQL dumps and delimited files.
SQL dumps
SQL dumps are what most people are familiar with, because they're what
mysqldump
creates by default. For example, dumping a small table with the default options will
produce the following (abridged) output:
$
mysqldump test t1
-- [Version and host comments]
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
-- [More version-specific comments to save options for restore]
--
-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
-- [More option restoration]
The dump file contains both the table structure and the data, all written out as valid
SQL commands. The file begins with comments that set various MySQL options. These
are present either to make the restore work more efficiently or for compatibility and
correctness. Next you can see the table's structure, and then its data. Finally, the script
resets the options it changed at the beginning of the dump.
The dump's output is executable for a restore operation. This is convenient, but
mysql-
dump
's default options aren't great for making a huge backup (we delve into
mysql-
dump
's options in more detail later).
