MySQL Export Data

MySQL Export Data

In MySQL you can use SELECT ... into OUTFILE statement to simply export the data to a text file.

Use SELECT ... into OUTFILE statement to export data

In the following example we export the data table TUTORIALS_TBL data to the/tmp/tutorials.txt file:

MySQL>* from tutorials_tbl     '/tmp/tutorials.txt ';     

You can set the specified format for the data output by using the command option, and the following instance is exported in CSV format:

MySQL>*'/tmp/tutorials.txt ', ' ', ', '\ r \ n ';    

In the following example, a file is generated and the values are separated by commas. This format can be used by many programs.

Select a,b,a+'/tmp/result.text ', ' '\ n ' fromtest_table;  

SELECT ... The into OUTFILE statement has the following properties:

• LOAD DATA infile is select ... The inverse operation of into outfile, select syntax. To write data from a database to a file, use Select ... into OUTFILE, in order to read the file back to the database, use load DATA INFILE.
• SELECT ... Into OUTFILE ' file_name ' form select to write the selected line to a file. The file is created on the server host, so you must have file permissions to use this syntax.
• The output cannot be a file that already exists. Prevents file data from being tampered with.
• You need to have a login server account to retrieve the files. otherwise SELECT ... Into OUTFILE does not play any role.
• In Unix, the file is created and readable, and the permissions are owned by the MySQL server. This means that although you can read the file, you may not be able to delete it.

Export tables as raw data

Mysqldump is a utility that MySQL uses to store databases. It primarily produces a SQL script that contains the commands necessary to recreate the database from scratch, create TABLE insert, and so on.

Exporting data using mysqldump requires the--TAB option to specify the directory specified by the export file, which must be writable.

The following example exports the data table Tutorials_tbl to the/tmp directory:

-----No-Create---tab=/******      

Export data in SQL format

Export the data in SQL format to the specified file as follows:

-->dump.  ******

The above command creates a file with the following contents:

-- Mysql Dump 8.23---- Host:localhostDatabase:Tutorials----------------------------------------------------------- ServerVersion3.23.58---- TableStructureForTable' Tutorials_tbl '--CREATE TABLE Tutorials_tbl(tutorial_idInt(11)Not NULL auto_increment,Tutorial_title varchar(100)Not NULLDefault ‘‘,Tutorial_author varchar(40)Not NULLDefault ‘‘,Submission_date DateDefaultNull,PRIMARY KEY(tutorial_id),UNIQUE KEY Author_index(Tutorial_author))TYPE=MyISAM;---- DumpingDataForTable' Tutorials_tbl ' --insert into tutorials_tbl VALUES  (1, ' learn PHP ' , ' John Poul ' ,< span class= "str" > ' 2007-05-24 ' ); insert into tutorials_tbl VALUES  (2, ' learn MySQL ' , ' Abdul S ' ,insert into tutorials_tbl VALUES  (3, ' JAVA Tutorial ' , ' Sanjay ' ,             

If you need to export data for an entire database, you can use the following command:

--> database_dump.  ******

If you need to back up all databases, you can use the following command:

----all-> database_dump.  ******

The--all-databases option is added in MySQL 3.23.12 and later versions.

This method can be used to implement a database backup strategy.

Copy data tables and databases to other hosts

If you need to copy the data to a different MySQL server, you can specify the database name and data table in the mysqldump command.

Perform the following command on the source host to back up the data to the Dump.txt file:

-->dump.  *****

If you are backing up the database completely, you do not need to use a specific table name.

If you need to import the backed up database into a MySQL server, you can use the following command, using the following commands you need to confirm that the database has been created:

--<dump.  *****

You can also use the following command to import the exported data directly to a remote server, but make sure that the two servers are interlinked and accessible to each other: </P>--|  -H Other-host.  COM database_name          

Pipelines are used in the above command to import the exported data to the specified remote host.

MySQL Export Data