How to use mysqldump command to backup mysql 5 database?

What’s mysqldump command?

mysqldump is 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.

mysql_path

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 -uroot -p test > test.sql
Enter Password:

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 @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@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.

Total
0
Shares
Leave a Reply

Your email address will not be published.

 
Previous Post

Tutorial – Inserting text and image content using CSS

Next Post

jQuery Fake AJAX requests for ajax testing using mockjax plugin

Related Posts