Reader Level:
ARTICLE

How to Save "Backup Records and Dump File" Into Text File in MySQL

Posted by Arjun Panwar Articles | Databases & DBA January 30, 2012
This article explains how to backup and restore the MySQL records and export tables into a text file.
  • 0
  • 0
  • 7699

In MySQL the database backups are stored as a large file and it is very necessary to backup our data. Here we have to show the backup data and export the tables into text files. The MySQL dump is used to quickly backup the MySQL database to text files.

In restoring the MySQL database or tables, we have to use the following commands.

Backup a selected table records into text file

It indicates the terminator by enclosing in a table with the where condition.

Command

mysql-> SELECT tablename, InStock, Category INTO OUTFILE 'Backup2.sql'
->   FIELDS
->   TERMINATED BY ','
->    ENCLOSED BY '*' FROM tables WHERE Category='Classical';

backup1.gif

Backup records into a text file 

By the use of this command, records are converted into the text file.

backupsql.gif

Set OPTIONALLY ENCLOSED

Here we have to select the table into text file and set it to the optionally enclosed.

Set OPTIONALLY ENCLOSED.gif

Location

The player text file is stored in the C drive and we find it as per this location.
                                                       
c.gif

Set enclosed terminator on a table

We have to set the enclosed terminator, it is used for specifying the line termination. It is optional.

enclosed.gif

Escape sign

It works in standard mode but not in NO_BACKSLASH_ESCAPES mode.

escape sign.gif

Create dump file

Here we have to create a dump file by the use of the following command.

dumpfile.gif

Location: The dump file 'player.DUMP' is stored in the C drive.

c-dumpfile.gif

Dump data into a text file

Here we have to export dump data into a text file.

dumpdata into text file.gif

Location: In this figure we have to see that the location of the ' Players9.TXT ' file is stored in the C drive.

location of dumpdata into text file in c.gif

Resources

Here are some useful resources:

COMMENT USING

Trending up