MySQL data import and export (i)

Today, there is a requirement to import data from one table to another table using automatic tasks. Specific scenarios and limitations: Import data from Table a in database a into table B of database B (either incremental or full data); System 1 and System 2 can only access database a and database bseparately. Figures:

Implementation Mode : System 1 to the table a data export into a file, MongoDB, after the successful notification System 2, System 2 for data import.

The point is: in the data export and data import, there are several ways, here are specific to the method three

Method One:mysqldump, but mysqldump is run under the operating system command line and does not satisfy this scenario .

Method two: Query through SQL, and then write the file through IO. This method does not do the evaluation ... PS: Import and export of various tools even if, interested in their own understanding

method Three : Export = = "SELECT statement into OUTFILE path + target file [option]
Import = = "LOAD DATA LOCAL INFILE path + target file into table name [option]

Among the 5 options commonly used in the option parameter:
Field TERMINATED by ' string ': Sets the string as the delimiter for the fields, the default value is \ t;
Fields enclosed by ' character ': Sets the value of the field in the string, the default value is no symbol;
field optionally enclosed by ' character ': set string enclosed by char-varchar text and other character fields, the default value is no symbol;
LINES starting by ' string ': Sets the character at the beginning of each line, and the default value is no character;
Fields escaped by ' character ': Sets the escape character, the default value is \;
LINES TERMINATED by ' string ': Sets the end of each line, the default value is \ n;

Note: 1. When importing, the data file should correspond to the structure of the imported table (field length, type, column, etc.)

2. The exported file is not SQL, does not contain a table structure, only a pure data file, and each piece of data occupies one row

3. Path problem: Sometimes choose a path can not export data files, will be error
For example: The MySQL server is running with the--SECURE-FILE-PRIV option so it cannot execute this statement
How to resolve:
　　A) first through show VARIABLES like '%secure% ' query out the default path, using the default path to export, execution results such as

　　b) Modify the MySQL my.ini configuration file in Secure_file_priv to manage,
I. Do not allow MySQL to import and export
# Secure File Priv.
Secure-file-priv = null
II. Allow only MySQL to import and export operations under the specified directory
# Secure File Priv.
Secure-file-priv =/Directory/
III. Do not restrict MySQL import and export (comment or remove secure-file-priv configuration)
# Secure File Priv.
#secure-file-priv= "C:/programdata/mysql/mysql Server 5.7/uploads"

finally attach eg:

SELECT *  from  into ' c:\\programdata\\mysql\\mysql Server 5.7\\uploads\\test8.sql '

SELECT * fromPub_parameter intoOUTFILE'c:\\programdata\\mysql\\mysql Server 5.7\\uploads\\test5.sql'Fields TERMINATED by '|'

MySQL data import and export (i)