MySQL - LOAD DATA INFILE

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

Daten in Datei exportieren

> echo "SELECT * INTO OUTFILE '/var/lib/mysql-files/data.csv' FIELDS TERMINATED BY ';' FROM dbname.tabname" | mysql

secure_file_priv

> echo "SELECT * INTO OUTFILE 'data_1234567.txt' FIELDS TERMINATED BY ';' FROM dbname.tabname" | mysql
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

> echo "SHOW VARIABLES LIKE 'secure_file_priv';" | mysql -t
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

> echo "SELECT * INTO OUTFILE '/var/lib/mysql-files/data_1234567.txt' FIELDS TERMINATED BY ';' FROM dbname.tabname" | mysql

> ls -lha /var/lib/mysql-files/
total 8,0K
drwxrwx---  2 mysql mysql 4,0K Okt 30 11:20 .
drwxr-xr-x 55 root  root  4,0K Aug  9 10:47 ..
-rw-rw-rw-  1 mysql mysql    0 Okt 30 11:20 data_1234567.txt

Daten aus Datei importieren

eine CSV-Datei importieren, in der die Felder durch ";" getrennt werden und die erste Zeile (beispielsweise der Tabellenkopf) übersprungen wird:

> echo "LOAD DATA LOCAL INFILE '/var/lib/mysql-files/data.csv' INTO TABLE dbname.tabname FIELDS TERMINATED BY ';' IGNORE 1 LINES;" | mysql

Datenübertragung in die DB

Mit diesem Kommando kann man eine Datei auch übers Netz in die DB laden, wenn der DB-User das FILE-Recht besitzt:

> echo "SET GLOBAL local_infile=1; ..." | mysql --local-infile=1

LOAD DATA INFILE

test.csv

001C6CE64AFA01E95015ABAE848E45F6E01:4
001C6CE64AFA01E95015ABAE848E45F6E02:4
001C6CE64AFA01E95015ABAE848E45F6E03:4
001C6CE64AFA01E95015ABAE848E45F6E04:4
001C6CE64AFA01E95015ABAE848E45F6E05:4
001C6CE64AFA01E95015ABAE848E45F6E06:4
001C6CE64AFA01E95015ABAE848E45F6E07:4
001C6CE64AFA01E95015ABAE848E45F6E08:4
001C6CE64AFA01E95015ABAE848E45F6E09:4
001C6CE64AFA01E95015ABAE848E45F6E10:4

test.sql

SET GLOBAL local_infile=1;
 
START TRANSACTION;
LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE dbname.tabname
FIELDS TERMINATED BY ':'
LINES TERMINATED BY '\n'
(pwhash, count);
COMMIT;

root@haveibeenpwned-sftp:~# cat test.sql | mysql --local-infile=1 -udbadmin -p...
 
root@haveibeenpwned-sftp:~# echo "SELECT * FROM dbname.tabname LIMIT 12;" | mysql -t
+----+-------------------------------------+-------+
| id | pwhash                              | count |
+----+-------------------------------------+-------+
|  1 | 001C6CE64AFA01E95015ABAE848E45F6E01 | 4     |
|  2 | 001C6CE64AFA01E95015ABAE848E45F6E02 | 4     |
|  3 | 001C6CE64AFA01E95015ABAE848E45F6E03 | 4     |
|  4 | 001C6CE64AFA01E95015ABAE848E45F6E04 | 4     |
|  5 | 001C6CE64AFA01E95015ABAE848E45F6E05 | 4     |
|  6 | 001C6CE64AFA01E95015ABAE848E45F6E06 | 4     |
|  7 | 001C6CE64AFA01E95015ABAE848E45F6E07 | 4     |
|  8 | 001C6CE64AFA01E95015ABAE848E45F6E08 | 4     |
|  9 | 001C6CE64AFA01E95015ABAE848E45F6E09 | 4     |
| 10 | 001C6CE64AFA01E95015ABAE848E45F6E10 | 4     |
+----+-------------------------------------+-------+