MySQL file import and export under Linux

Recently in doing MySQL data import and export get some experience, recorded. 1. First to open the import and export function, you need to set up a MySQL configuration can be added at the end of the my.conf file configuration secure-file-priv= "" With this command to check the current settings SHOW VARIABLES like '%secure_file _priv% '; If NULL indicates that import export is prohibited, if a specific path is specified, the import export must be under this path, and if it is an empty string, it can be exported to any path (you must have sufficient permissions to write any path permission) If you have insufficient permissions, you can default to the MySQL data file directory No error message set, error code: 1290The MySQL Server is running with the--SECURE-FILE-PRIV option so it cannot execute this statement2. Right Problem, the permissions problem is resolved under Windows. MySQL has limited user rights under Linux, so I can only export the MySQL data file by default. --If the specified path requires that the current user has a high level of permissions, the default can be exported--the default path to the MySQL data file storage path can be used to view the file path show VARIABLES like '%datadir% ' SELECT * Into OUTFILE ' 2.txt ' of TERMINATED by ', ' enclosed by ' \ ' LINES TERMINATED by ' \ n ' from Mtdwgl_syn.irms_trans_pipehol E LOAD DATA INFILE '/data/mysql/2.txt ' into TABLE mtdwgl_res.tb_irms_trans_pipehole CHARACTER SET UTF8 fields TERMINATED by ', ' enclosed by ' \ ' LINES TERMINATED by ' \ n '; 3. Tested 2300W records export took 1 minutes 46 seconds select * into OUTFILE ' 2.txt ' fields TERMINATED by ', ' Enclosed by ' \ ' LINES TERMINATED by ' \ n ' from Mtdwgl_syn.irms_trans_pipeHole Query OK, 23040110 rows affected (1 min 46.01 sec) import took 3 minutes 22 seconds load DATA INFILE '/data/mysql/2.txt ' into TABLE mtdwgl_res.tb_ Irms_trans_pipehole CHARACTER SET UTF8 fields TERMINATED by ', ' enclosed by ' \ ' LINES TERMINATED by ' \ n '; Query OK, 23040110 rows affected (3 min 22.10 sec) records:23040110 deleted:0 skipped:0 warnings:0

File size, tested 2.txt file, 4.8G

sudo du-h/data/mysql/*.txt
480m/data/mysql/1.txt
4.8g/data/mysql/2.txt

　　

MySQL file import and export under Linux