5 Ways of MySQL Database Export

 I believe that everyone who has used MySQL would like to know how to export databases. This article will introduce some common database export operations of MySQL database in detail and look forward to helping you!

Use SELECT… INTO OUTFILE sentences to export data information

1. In the following cases, the data analysis table is shulanxt_ Tbl data information is exported to the/tmp/shulanxt.txt file:

MySQL>SELECT * FROM shulanxt_tbl

->INTO OUTFILE ‘/tmp/shulanxt.txt’;

You can set the format of data output according to the command options. The following example is the export CSV format:

MySQL> SELECT * FROM passwd INTO OUTFILE ‘/tmp/shulanxt.txt’

-> FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘“‘

-> LINES TERMINATED BY ‘\r\n’;

2. Export table as original record

MySQLDump is an application used by MySQL to convert and store databases. It can generate an SQL script, including the commands CREATE TABLE, INSERT, and so on, which are necessary for building a database from scratch.

When MySQLDump is used to export data information, the address directory of the exported file must be selected through the — tab option. The directory must be writable.

In the following cases, the data analysis table shulanxt_ Tbl export to/tmp file directory:

$ MySQLDump -u root -p — no-create-info \

— tab=/tmp shulanxt shulanxt_tbl

password ******

Export data information in SQL format

On the original database server, you can use the phpmyadmin special tool or the MySQLDump (the MySQLDump command is located in the MySQL/bin/file directory) command line to export the sql script.

1. phpmyadmin exports MySQL database

In the export options, select Structure and Data.

Note that “drop database” and “drop table” options cannot be added.

Select the Save Document As option. If the data volume is relatively large, you can select the gzipped option to save the exported SQL document.

2. The MySQLDump command line exports the MySQL database

MySQLDump is an application used by MySQL to convert and store databases.

Syntax format

MySQLDump-uLogin Name-pDatabase Name>Database Name.sql

When prompted for a password, enter the password that matches the database user name.

The contents of the file created by the above command are as follows:

— MySQL Dump 8.23

—

— Host: localhost Database: shulanxt

— — — — — — — — — — — — — — — — — — — — — — — — — — — — -

— Server version 3.23.58

—

— Table structure for table `shulanxt_tbl`

—

CREATE TABLE shulanxt_tbl (

shulanxt_id int(11) NOT NULL auto_increment,

shulanxt_title varchar(100) NOT NULL default ‘’,

shulanxt_author varchar(40) NOT NULL default ‘’,

submission_date date default NULL,

PRIMARY KEY (shulanxt_id),

UNIQUE KEY AUTHOR_INDEX (shulanxt_author)

) TYPE=MyISAM;

—

— Dumping data for table `shulanxt_tbl`

—

INSERT INTO shulanxt_tbl

VALUES (1,’Learn PHP’,’John Poul’,’2007–05–24');

INSERT INTO shulanxt_tbl

VALUES (2,’Learn MySQL’,’Abdul S’,’2007–05–24');

INSERT INTO shulanxt_tbl

VALUES (3,’JAVA Tutorial’,’Sanjay’,’2007–05–06');

If you want to export the data information of all databases, please enter the following command:

$ MySQLDump -u root -p shulanxt > database_Dump.txt

password ******

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

This mode can be used to back up the database.

$ MySQLDump -u root -p — all-databases > database_Dump.txt

password ******

–The all databases option will be added in MySQL 3.23.12 and later versions.

3.Use the MySQL command line

MySQL-uLogin Name-pDatabase Name<Database Name.sql