MySQL Dump Example

In this tutorial you will come to know about mysqldump command and how to use this command, how to execute, the option it supports etc. Example will make this command more clear.

  MySQLDUMP:
Introduction:
In database we need to store our important data periodically or after certain period of time. MySQL provides several techniques to keep the backup of our important data. In the current and next tutorials we will study about MySQL dump and it's procedures to store the data. You will get several examples in this topic.
In the current tutorial we will study about how to store the data using MySQL dump.
We are assuming that you already have a little knowledge on MySQL and SQL language syntax, if you did not installed MySQL in your system then goto www.mysql.org and download the latest version.
What is MySQLDUMP?
The mysqldump is console based executable utility and it allows us to assign a host of options to get the backup of a database to a file, a different MySQL server running anywhere in the world .
In fact MySQL does not backup our data instead of that it executes a set of basic sql syntaxes like "create table" and "insert into" in the MySQL server to re-create the database/s.
The mysqldump utility is present in the root directory of mysql, let us assume it is c:\mysql and you could find a folder bin in that directory. Using the mysqldump utility we can provide several commands so that we can change the way of taking backups.
There are following ways to invoke mysqldump. Use the following command to take backups:

       prompt/shell> mysqldump [options] db_name [tables]
       prompt/shell> mysqldump [options] --databases DB1 [DB2 DB3...]
       prompt/shell> mysqldump [options] --all-databases

As in the above example  we can see that how to use the command, first of all goto the root directory of MySQL, as in the example it is under xampp directory, then type mysqldump followed by a space and --opt followed by a space --user=root(default user, type the valid username you want ) followed by a space, --password, if you have set the password previously then write the password after equal sign, put a space after that write down the database name, followed by a space greater than sign (>) specify a file name, in which the backup will be taken. The file will be stored in the same folder in which the mysqldump persists. In my example the rose database does not contain any table, and the output of the rose.sql file is as below:


-- MySQL dump 10.13 Distrib 5.1.41, for Win32 (ia32)
--
-- Host: localhost Database: rose
-- ------------------------------------------------------
-- Server version 5.1.41
/*!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 */;
/*!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 2010-04-07 17:10:52
Let us create a simple table and check the output, consider a table userdetails as follows:
`user_id` int(20) NOT NULL AUTO_INCREMENT,
`user_login` varchar(32) NOT NULL,
`password` varchar(64) NOT NULL,
PRIMARY KEY (`user_id`)
Now again execute the command as mentioned above with following modification:
C:\xampp\mysql\bin>mysqldump --opt --user=root --password rose userdetails> rose.sql
Enter password:

Note: Enter password is not a command, as you type the command and press the return key, it will ask for the password of the current user.
If the table is previously build then the above command will rewrite the whole file.
If you check the output of the file rose.sql then it will display the following differences:
-- MySQL dump 10.13 Distrib 5.1.41, for Win32 (ia32)
--
-- Host: localhost Database: rose
-- ------------------------------------------------------
-- Server version 5.1.41
/*!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 `userdetails`
--
DROP TABLE IF EXISTS `userdetails`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `userdetails` (
`user_id` int(20) NOT NULL AUTO_INCREMENT,
`user_login` varchar(32) NOT NULL,
`password` varchar(64) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `userdetails`
--
LOCK TABLES `userdetails` WRITE;
/*!40000 ALTER TABLE `userdetails` DISABLE KEYS */;
INSERT INTO `userdetails` VALUES (1,'rose','india'),(2,'nie','jackson');
/*!40000 ALTER TABLE `userdetails` 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 2010-04-07 17:46:32
To get the output in the command prompt type the following code:
C:\xampp\mysql\bin>mysqldump --user=root --password rose < rose.sql
Enter password:
Note: Enter password is not a command, as you type the command and press the return key, it will ask for the password of the current user.

If we use --databases option or --all-databases option or do not specify the name of the table then entire databases are dumped. To get any kind of help or to check the options of the version you are currently using, execute mysqldump --help. Output would be as follows:

If mysqldump runs without --quick or --opt option, before dumping the result mysqldump loads the whole result into the system's memory. In MySQL 4.1, --opt is enabled by default  and it can be disabled by --skip-opt. It is recommended that if you are using  recent copy of mysqldump program to generate a dump which will be reloaded in the older version of MySQL, then you should not use the option --opt or -e options.
Few Options supported by mysqldump:

• mysqldump -? = It displays a long list of options which are supported by mysqldump. To check the options one by one, type mysqldump -?|more.
• mysqldump --version displays the version of the mysqldump which is in use. The command is as below: C:\xampp\mysql\bin>mysqldump --version
  mysqldump Ver 10.13 Distrib 5.1.41, for Win32 (ia32)