How to export and import Mysql Data...

How to export and import Mysql Data...

 

1. load data

(Windows system .. When the table structure is consistent)

Run the following command in the DOS MySQL/bin directory:

Use test

Export: Select * from AAA into OUTFILE "E:/a.txt ";

Import: load data local infile "E:/a.txt" into Table AAA;

----------------------------------

 

2. Dump SQL files & run SQL files

----------------------------------

 

3. Practical Use of mysqldumpProgram
Example:
3.1 run -- cmd -- mysqldump
3.2 mysqldump-uroot-proot-hlocalhost test AA> E: \ testaa1. SQL(-Hlocalhost can be added and ignored;-proot can be input as-P, followed by password input; test is the database name; AA is the table name;> is the output identifier)
----------------
3.3(Dump. SQL -- directly store the path to the current user, for example, C: \ Documents and Settings \ Administrator)

(1) ExportEntire database structure and data 
Mysqldump-H localhost-uroot-p123456 database> dump. SQL

 

(2)ExportStructure and data of a single data table
Mysqldump-H localhost-uroot-p123456 database table> dump. SQL

 

 

(3)ExportEntire database structure (excluding data)
Mysqldump-H localhost-uroot-p123456-DDatabase> dump. SQL

 

(4)ExportSingle data table structure (excluding data)
Mysqldump-H localhost-uroot-p123456-DDatabase Table> dump. SQL

(5)ImportRequiredSource command:
MySQL command line --

 
Mysql-u root-p123 MySQL prompt> source C: \ m1_83. SQL
 

---------------------------------

 

4. Intermediate media. Export and Import (access/TXT/Excel)

-------------------------------------------------------------------

In other operating systems, MySQL:

Backup: select into OUTFILE
Select * From hx_9enjoy into OUTFILE '/tmp/9enjoy.txt'
By default, fields are separated by tab, and records are separated by/n. You can customize three parameters.
Select * From hx_9enjoy into OUTFILE '/tmp/9enjoy_hx.txt 'fields terminated by', 'optionally enclosed by' "'Lines terminated by '/N ';
That is, use. separate fields. Double quotation marks are used to limit the content of fields./N is used to separate records.
You can also specify the record output for certain conditions:
Select * From hx_9enjoy where ID <100 into OUTFILE '/tmp/9enjoy_100.txt'

Restore: load data infile
Default:
Load data infile '/tmp/9enjoy.txt' into Table hx_9enjoy
If a Delimiter is specified, the Delimiter is added:
Load data infile '/tmp/9enjoy.txt' into Table hx_9enjoy fields terminated by ', 'optionally enclosed by' "'Lines terminated by '/N'

If the database is GBK or UTF-8 encoded, you must specify the character set parameter during restoration. Otherwise, the restored data is garbled.
For example, load data infile '/tmp/9enjoy.txt' into Table hx_9enjoy Character Set GBK;
Notes
1. MySQL must have the permission to access the directory of the generated file
2. For security reasons, the existing file cannot be overwritten, no matter how permissions are assigned to the file.
3. You cannot export a compressed file directly.