MySQL uses LOAD DATA to synchronize data to Alibaba cloud database regularly

Posted by sarun on Tue, 23 Jun 2020 04:31:16 +0200

Before  , there was a small test project that needed to synchronize some local data to Alibaba cloud database regularly. It was implemented by executing LOAD DATA with the most mechanical script. Here is a brief introduction.

LOAD DATA can quickly read text file data into the table (LOAD DATA is usually 20 times faster than INSERT statement when importing data), LOAD DATA and SELECT To export data from a table to a text file, SELECT INTO OUTFILE, otherwise LOAD DATA is used. The syntax of the FIELDS and LINES clauses of the two is the same.

    [LOW_PRIORITY | CONCURRENT] [LOCAL] --LOCAL: Read text file from client host
    INFILE 'file_name'
    [REPLACE | IGNORE] --REPLACE: The input line replaces the existing duplicate line, IGNORE: Skip duplicate lines
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name] --Specifies that all fields in the file are interpreted to have the same character set, regardless of the data type of the column to which the field value is loaded
    [{FIELDS | COLUMNS} --FIELDS Must be on LINES before
        [TERMINATED BY 'string'] --Column field separator, default\t
        [[OPTIONALLY] ENCLOSED BY 'char'] --Specifies the inclusion character of the column value, which is empty by default
        [ESCAPED BY 'char'] --Escape character, default\
        [STARTING BY 'string'] --Line start
        [TERMINATED BY 'string'] --Line break, default\n
    [IGNORE number {LINES | ROWS}] --Skip the first few lines specified when importing
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

FIELDS ESCAPED BY controls how special characters are read or written:

Several official examples:
# Fields are separated by commas, field values are enclosed in double quotation marks, the first line of the data file begins with the column name, and the line ends with a carriage return / line feed pair.
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
# Lines ending with a carriage return / line feed pair
LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
  LINES TERMINATED BY '\n%%\n' (joke);

                         . Here's an explanation: LOCAL can only be used when both the server and the client are configured to allow, for example, if mysqld is in LOCAL_ If the system variable is disabled by infile, LOCAL cannot be used.

  • If LOCAL is specified, the file will be read by the client program on the client host and sent to the server; the file can be specified as a full path name to specify its exact location; if given as a relative path name, the name will be interpreted relative to the directory where the client program is started;
  • If LOCAL is not specified, the file must be on the server host and can be read directly by the server. The server uses the following rules to locate files: if the file name is an absolute path name, the server will use it according to the given name; if the file name is a relative path name with one or more leading components, the server will search for files relative to the server data directory; if the given file name does not have a leading component, the server will search for files in the default database Find the file in the database directory.

There are two potential security problems when using LOCAL with LOAD DATA:

  • The database server will tell the client program to transfer the file selected by the server instead of the file named in the statement. The database server can access any file on the client host that the client user has read access rights;
  • In the web environment, the client connects from the web server, and the user can use LOAD DATA LOCAL to read any file that the web server process has read permission (assuming that the user can run any statement against the SQL Server). In this environment, the client of MySQL server is actually a web server, not a remote program run by the user connected to the web server.
       to avoid connecting to untrusted servers, clients can establish a secure connection by using – SSL mode = verify_ The identity option connects with the appropriate CA certificate to verify the identity of the server. In order to control the LOCAL data loading, MySQL allows to enable or disable the LOCAL function. Starting from MySQL 8.0.21, MySQL clients can only load data from the data files in the LOCAL specified directory.

[root@mysqlplus2 ~]# crontab -e

01 00 * * 1-6 /home/mysqlbak/script/

   export the required data first:

SELECT insId,areaNo,jointId,contractId,sold
FROM tb_jyx_ins_bizarea into outfile "/home/databak/tb_bizarea.txt" fields terminated by ',';

SELECT tb_member.mid,openid,unionid,cellPhone,nickname,
FROM tb_member where TO_DAYS(NOW())-TO_DAYS(createDatetime)=1 into outfile "/home/databak/tb_member.txt" fields terminated by ',';

SELECT orgID,orgname,disno,province,city,orgnum,fullname,orgtype
 FROM tb_organization into outfile "/home/databak/tb_organization.txt" fields terminated by '|';

Take a look at LOAD DATA:

set autocommit=0;
set unique_checks=0;
load data local infile "/home/databak/tb_bizarea.txt" into table tb_test_bizareafields terminated by ',';
load data local infile "/home/databak/tb_member.txt" replace into table tb_test_member fields terminated by ',';
load data local infile "/home/databak/tb_organization.txt" into table tb_test_organization(orgID,orgname,disno,province,city,orgnum,fullname,orgtype) fields terminated by '|';
set unique_checks=1;

Topics: Database Web Server MySQL SQL