はじめに
MySQL 8.0の環境にLOAD DATA LOCAL文を使用してデータをロードする手順を紹介します。
本記事は、MySQL 8.0の下記マニュアルを参考に作成しました。
3.3.3 テーブルへのデータのロード
動作確認環境
mysql> status
--------------
mysql Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 14
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.32 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 26 days 22 hours 19 min 37 sec
Threads: 2 Questions: 41 Slow queries: 0 Opens: 148 Flush tables: 3 Open tables: 67 Queries per second avg: 0.000
--------------
データロード
下記内容のテキストファイルをロードします。
- \Nは、NULLである事を示しています。
- データはタブで区切っています。
[root@mdb01 ~]# cat /tmp/pet.txt
Fluffy Harold cat f 1993-02-04 \N
Claws Gwen cat m 1994-03-17 \N
Buffy Harold dog f 1989-05-13 \N
Fang Benny dog m 1990-08-27 \N
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11 \N
Whistler Gwen bird \N 1997-12-09 \N
Slim Benny snake m 1996-04-29 \N
LOAD DATA LOCAL文を使用して、pet.txtのデータをロードします。
実行したところ、LOCA DATA LOCALの機能が無効化されているため、実行できない事が確認できます。
mysql> LOAD DATA LOCAL INFILE '/tmp/pet.txt' INTO TABLE pet;
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
マニュアル「6.1.6 LOAD DATA LOCAL のセキュリティー上の考慮事項」に下記の記述があり、デフォルトではローカルデータロードは無効化されているようです
LOAD DATA LOCALには、セキュリティ上の潜在的な問題があるため、クライアント側で適切な予防措置が講じられない限り使用するべきではない。
上記を認識した上で、LOAD DATA LOCALを一時的に有効化し、データロードを実行します。
有効化するためにはサーバ側とクライアント側の両方に設定が必要となるため、それぞれ確認します。
- サーバ側は、local_infile システム変数の値をONに設定する必要があります。
local_infileはグローバルスコープで、また動的変更が可能なシステム変数です。
現在の値を確認します。
mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set (0.00 sec)
今回は、SET GLOBAL文を使用して、一時的(インスタンスを再起動すると変更前の値に戻る)に変更します。
SET GLOBAL文を実行可能なrootアカウントで再接続します。
[root@mdb01 ~]# mysql -uroot -p
Enter password:
mysql>
local_infileをONに変更します。
mysql> SET GLOBAL local_infile = ON;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> exit
Bye
続いて、クライアント側ですが、mysqlクライアントの起動オプションに、--local-infile=ONを指定する事で有効化されます。
[root@mdb01 ~]# mysql -uchibatty -p menagerie --local-infile=ON
Enter password:
mysql>
LOAD DATA LOCALを実行します。
今度は正常に終了し、8行のデータがロードされました。
mysql> LOAD DATA LOCAL INFILE '/tmp/pet.txt' INTO TABLE pet;
Query OK, 8 rows affected (0.01 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
petテーブルの中身を確認します。
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)
mysql> exit
Bye
最後に変更したlocal_infileを元の値に戻します。
mysql> SET GLOBAL local_infile = OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set (0.00 sec)