The easiest way to export table data to a text file is to use the Select ... The query results into the OUTFILE statement are exported directly to a file on the server host.
Select for export Data ... into outfile declaration:
The syntax of this sentence combines the general select into OutFile file name end. The default output format is the same load DATA, so the following statement exports the TUTORIALS_TBL Table tab-delimited, newline-terminated file to/tmp/tutorials.txt:
Mysql> SELECT * from Tutorials_tbl
-> into outfile '/tmp/tutorials.txt ';
You can also change the output format, using options to indicate how columns and records are referenced and delimited. CRLF terminating line to export a table in TUTORIAL_TBL CSV format, use such a statement:
Mysql> SELECT * from passwd to outfile '/tmp/tutorials.txt '-> FIELDS terminated
by ', ' enclosed by ' "'
-& Gt LINES terminated by ' \ r \ n ';
SELECT ... into outfile has the following properties:
- The MySQL server creates an output file directly, such that the file name should specify the file you want to be written on the server host. A similar statement without a local version of the load DATA version.
- You must have MySQL file permission to execute the Select ... INTO statement.
- The output file must not exist. This prevents MySQL from being able to destroy files that may be important.
- It should be on the server host or some method to retrieve the file from that host's login account. Otherwise select ... into outfile may be worthless.
- Under UNIX, the world's readable files and MySQL servers are created. This means that while the file can be read, it cannot be deleted.
Export table as raw data:
Use the Mysqldump program to copy or back up tables and databases. It can write the original data file of the table output or recreate the records in the table for a group of INSERT statements.
To dump a table, you must specify a data file-the option on the tab that represents the directory where you want the MySQL server to write to the file.
Use the Mysqldump program to copy or back up tables and databases. It can write the original data file of the table output, or recreate the records in the table for a set of INSERT statements.
To dump a table, you must specify a data file-the option on the tab that represents the directory where you want the MySQL server to write to the file.
$ mysqldump-u root-p--no-create-info
--tab=/tmp Tutorials tutorials_tbl
To export the contents or definition of a table in SQL format:
The SQL format in a table is exported to a file, using a command such as:
$ mysqldump-u root-p Tutorials tutorials_tbl > Dump.txt
Password
This will create the contents of the file as follows:
--MySQL Dump 8.23--
host:localhost database:tutorials
--------------------------------------- --------------------
Server version 3.23.58--
table structure for table ' TUTORIALS_TBL '
--
CREATE TABLE tutorials_tbl (
tutorial_id Int (one) not NULL auto_increment,
tutorial_title varchar (100) Not null default ',
tutorial_author varchar (+) NOT null default ',
submission_date date default NULL,
P Rimary Key (tutorial_id),
UNIQUE key Author_index (tutorial_author)
) Type=innodb;
---
dumping data for table ' TUTORIALS_TBL '-
-
inserts into TUTORIALS_TBL
VALUES (1, ' Learn PHP ', ' John Poul ', ' 2007-05-24 ');
INSERT into Tutorials_tbl
VALUES (2, ' Learn MySQL ', ' Abdul S ', ' 2007-05-24 ');
INSERT into Tutorials_tbl
VALUES (3, ' JAVA Tutorial ', ' Sanjay ', ' 2007-05-06 ');
To dump multiple tables, they name all the database name parameters. To dump the entire database, do not specify a database after any tables, as follows:
$ mysqldump-u Root-p Tutorials > database_dump.txt
password
The following commands are used on all available database backup hosts:
$ mysqldump-u root-p--all-databases > Database_dump.txt
Password
To export the contents or definition of a table in SQL format:
These methods can be used to implement a backup strategy for a database.
To copy a table or database to another host:
If you want to copy a table or database from a MySQL server, then use the Mysqldump database name and table name.
Run the following command on the source host. This will dump the complete database to the Dump.txt file:
$ mysqldump-u ROOT-P database_name table_name > Dump.txt
Password * * *
Copy the complete database without using a specific table name, as explained above.
Now, the FTP dump.txt file is on another host, using the following command. Before you run this command, make sure that you have created the database name on the target server.
$ mysql-u root-p database_name < dump.txt
Password * * * *
Another method, without using an intermediate file, mysqldump output is sent directly over the network to the remote MySQL server. If you can connect to the host of the cookbook database for both servers, use the following command:
$ mysqldump-u root-p database_name mysql-h
States | other-host.com database_name
The command mysqldump is connected to the local server and writes to the dump output pipeline. Half of MySQL commands connect to remote MySQL server otherhost.com. It reads each statement sent by the input tube and other host.com servers.