How to use mysqldump command to backup mysql 5 database?

What’s mysqldump command?

mysqldump is a MySQL utility for taking logical backup and restore of MySQL tables and database. Logical backup means it consist of SQL statements like create table and insert statements.  Output format could be SQL, CSV, tab delimited or XML.

Where to find this utility?

It’s shipped with your MySQL server setup. So, if you are on Windows, just add MySQL’s bin directory path to your Windows environment variables.

How to run this utility?

Just go to command prompt and type in:

mysqldump --help

All the available options will be printed on screen. Now to run this command for mysql server, mysql login credentials needed and moreover that login must be privileged to select query.

Basic command syntax

mysqldump -uUSERNAME -pPASSWORD DATABASE_NAME > backup.sql

For and example:  default root login

mysqldump -uUSERNAME -pPASSWORD DATABASE_NAME > backup.sql

Now if you open test.sql you could see output like this

-- MySQL dump 10.13  Distrib 5.5.16, for Win32 (x86)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.5.27

/*!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 `gift`
--

DROP TABLE IF EXISTS `gift`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `gift` (
  `Name` varchar(255) DEFAULT NULL,
  `Amount` int(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `gift`
--

So, this output – test.sql contains drop/create table and insert statements. which you could use to restore by running this command again using mysqldump. Let’s explore abilities of this utility.

MySQL data exporting using mysqldump.

• How to take backup of multiple databases?
  Tip: Supply database names separated by spaces.

  mysqldump -uUSERNAME -p database_name1 database_name2 > combined_dump.sql
  Enter Password:

• How to take backup of all databases?
  Tip: Supply –all-databases as an argument.

  mysqldump -uUSERNAME -p -all-databases > all_db_dump.sql
  Enter Password:

• How to take backup of specific table?
  Tip: Supply table name as an argument after database name

  mysqldump -uUSERNAME -p test gift > gift.sql
  Enter Password:

  So in above case gift table from test database will be exported to gift.sql

• How to take backup in csv/tsv format?
  Tip: Supply –fields-terminated-by=’,’ as an argument along with csv filename path with -T option

  mysqldump -uUSERNMAE -p -TC:\ test gift --fields-terminated-by=',' --lines-terminated-by='\n'
  Enter password:
  
  # this will output gift.txt as csv file at C:\ drive location

• How to take backup in XML format?
  Tip: supply –-xml as an argument.

  mysqldump --xml -uUSERNAME -p test gift > gift.xml

MySQL data restoring using mysqldump

• Restore single database.

  mysql --uUSERNAME --p test < test.sql

• Restore all databases

  mysql -uUSERNAME -p < all_databases.sql

• Restore a specific table

  mysql -uUSERNAME -p test
  source gift.sql

• There is one more utility called mysqlimport, explore it here
  .
• Restore TSV (tab separated backup) file

  mysqlimport -uUSERNAME -p --local test gift.txt

• Restore xml formatted dump.Try this utility – https://code.google.com/p/mysqldump-x-restore/

MySQL data backup and restoring using data files.

Just navigate to your mysql directory

• E:\xampp\mysql

  Copy folder – data and archive (zip) it.

  Install mysql on new machine and overwrite it in its mysql directory.  Then use this backup db server’s credentials to get access and alter privileges on new machine.