Backing up MySQL with mysqldump

A simple way to back up MySQL databases is with the mysqldump command line tool. Mysqldump can be used to back up a single database or multiple databases, and can backup MySQL databases into a text file containing multiple SQL statements, or into CSV or tab delimited text files.

The simplest way to use mysqldump is like so, substituting [username] for the username you use to connect to MySQL, and [database] for the MySQL database you wish to backup:

mysqldump -u [username] -p [database] > [database].sql

The above example will output the dump to a file called [database].sql, but you can instead dump the data to standard output (ie a scrolling list on the command line) by omitting the > [database].sql part.

You will be prompted for the password (that's what the -p flag is for), and an example dump with a database containing a couple of basic tables with a couple of rows of data each might look like this:

-- MySQL dump 10.11
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.0.45

/*!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 */;

--
-- Table structure for table `something`
--

DROP TABLE IF EXISTS `something`;
CREATE TABLE `something` (
  `something_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `value` varchar(50) NOT NULL,
  `ts_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`something_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `something`
--

LOCK TABLES `something` WRITE;
/*!40000 ALTER TABLE `something` DISABLE KEYS */;
INSERT INTO `something` VALUES (1,'foo1','bar','2007-12-14 04:20:43');
INSERT INTO `something` VALUES (2,'foo2','baz','2007-12-14 04:20:43');
/*!40000 ALTER TABLE `something` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `something_else`
--

DROP TABLE IF EXISTS `something_else`;
CREATE TABLE `something_else` (
  `else_id` int(11) NOT NULL auto_increment,
  `somevalue` varchar(20) NOT NULL,
  PRIMARY KEY  (`else_id`),
  KEY `somevalue` (`somevalue`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `something_else`
--

LOCK TABLES `something_else` WRITE;
/*!40000 ALTER TABLE `something_else` DISABLE KEYS */;
INSERT INTO `something_else` VALUES (1,'blah');
INSERT INTO `something_else` VALUES (2,'blah blah');
INSERT INTO `something_else` VALUES (3,'blah blah blah');
/*!40000 ALTER TABLE `something_else` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2007-12-14  4:22:26

Dumping a single table, or a selection of tables

If you wanted to back up a single table or a selection of tables from the database, you can specify these on the command line after the database name as shown in the following examples. The first example below would dump the table "foo", the second "foo" and "bar", and the third "foo", "bar" and "baz".

mysqldump -u [username] -p testdb foo > dump1.sql
mysqldump -u [username] -p testdb foo bar > dump2.sql
mysqldump -u [username] -p testdb foo bar baz > dump3.sql

Loading the data from the file

Now that you've successfully dumped the data from the database into a text file, how do you get it back into the database again? It's really easy, again doing it from the command line. By default, the mysqldump command adds "DROP TABLE IF EXISTS `tablename`" to the query, so you can simply run the contents of the file against the database and it will delete the table if it exists, and then load the data into the database.

mysql -u [username] -p [database] < dumpfile.sql

This can be useful for a) backing up databases on a regular basis and b) when copying the contents of the database or table(s) from a remote server to your local development machine.

There are a lot of additional flags for dumping data from MySQL using mysqldump. I will explore some of these in later posts and there's also an excellent man page (man mysqldump) and running mysqldump --help will list the many options available.