The problem background

MySQL High availability cluster architecture , Applications need to use select ... into outfile and load data [local] infile Import and export data . among , Export data （ Only read operations are involved ） Occurs in read-only Slave node , adopt localhost Connect to database ; Data import （ Involving read and write operations ） It happened in Master node , By clustering vip Connect to database .

DB-Master (DB02)	DB-Slave (DB01)	vip
A.B.C.120	A.B.C.119	A.B.C.121

The current effective values of the two parameters related to data import and export are as follows ：

secure_file_priv='' #  Indicates a directory that does not restrict data export 
local_infile=OFF  #  Indicates that... Is not allowed load data local infile Import data from the client 

Export test data

Create a test library （ In the main library ）

--- DB-Master: A.B.C.120
[root@DB02 tmp]# mysql -uroot -p
Password:
mysql> create user 'apptest'@'%' identified by 'appPasswd';

mysql> create database apptest;
mysql> use apptest;
mysql> create table `test01` (id int not null,
    -> name varchar(20),
    -> country varchar(12),
    -> primary key pk_id(`id`)
    -> ) engine=innodb;

---  The process of inserting data is omitted here 
mysql> select * from apptest.test01;
+----+---------------+---------+
| id | name          | country |
+----+---------------+---------+
|  1 | Gu Eileen     | CN      |
|  2 | Lebron James  | USA     |
|  3 | Karim Benzema | FR      |
+----+---------------+---------+

---  The user authorization statement is omitted here 
mysql> show grants for 'apptest'@'%';
+------------------------------------------------------+
| Grants for apptest@%                                 |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'apptest'@'%'                  |
| GRANT ALL PRIVILEGES ON `apptest`.* TO 'apptest'@'%' |
+------------------------------------------------------+

Test data export （ From the library ）

Log in and export data from the library ：

--- DB-Slave: A.B.C.119 
[root@DB01 tmp]# mysql -uapptest -pappPasswd
mysql> select * from test01 into outfile '/tmp/apptest.txt';
ERROR 1045 (28000): Access denied for user 'apptest'@'%' (using password: YES)

You can see , Database error “ Access denied ”, It should be the lack of permissions related to data export .

Sign in Main library Add relevant permissions for application users ：

--- DB-Master: A.B.C.120
[root@DB02 tmp]# mysql -hA.B.C.121 -uroot -p
Password:
mysql> grant file on *.* to 'apptest'@'%';

Log in again and export data from the library ：

--- DB-Slave: A.B.C.119
[root@DB01 tmp]# mysql -uapptest -pappPasswd
mysql> select * from apptest.test01 into outfile '/tmp/apptest.txt';
Query OK, 3 rows affected (0.01 sec)

Data export successful .

[root@DB01 tmp]# ll /tmp
total 8
-rw-rw-rw- 1 mysql mysql 53 Feb 17 12:20 apptest.txt

[root@DB01 tmp]# cat /tmp/apptest.txt
1	Gu Eileen	CN
2	Lebron James	USA
3	Karim Benzema	FR

You can see , The owner of the exported file is mysql.

Let's say we're going to the app Directory /home/apptest Derived data ：

[root@DB01 tmp]# ll /home | grep apptest
drwxr-xr-x  2 apptest  apptest  62 Feb 17 12:24 apptest

mysql> select * from apptest.test01 into outfile '/home/apptest/apptest.txt';
ERROR 1 (HY000): Can't create/write to file '/home/apptest/apptest.txt' (Errcode: 13 - Permission denied)

You can see , Database error “ Unable to create or write file ”, as a result of mysql The user does not have access to the home directory of the application user .

We try to give mysql The user adds the read and write permission of the application subdirectory ：

[root@DB01 tmp]# setfacl -R -m u:mysql:rwx /home/apptest 
[root@DB01 tmp]# setfacl -R -d -m u:mysql:rwx /home/apptest

Export data to application subdirectory ：

mysql> select * from apptest.test01 into outfile '/home/apptest/apptest.txt';
Query OK, 3 rows affected (0.00 sec)

Test data import （ In the main library ）

Prepare the file to import into the database

# DB-Master: A.B.C.120
[apptest@DB02 tmp]$ ll test02.txt
-rw-r--r-- 1 apptest apptest 52 Feb 17 12:36 test02.txt
[apptest@DB02 tmp]$ cat test02.txt
1	Yao Ming	CN
2	Kobe Bryant	USA
3	Stephen Curry	USA

Create an empty table to import ：

[apptest@DB02 tmp]$ mysql -hA.B.C.121 -uroot -p
mysql> use apptest
mysql> create table `test02` (id int not null,
    name varchar(20),
    country varchar(12),
    primary key pk_id(`id`)
    ) engine=innodb;

Try to check whether there is Local Keyword to import data ：

mysql> load data local infile '/tmp/test02.txt' into table test02;
ERROR 1148 (42000): The used command is not allowed with this MySQL version

mysql> load data infile '/tmp/test02.txt' into table test02;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from apptest.test02;
+----+---------------+---------+
| id | name          | country |
+----+---------------+---------+
|  1 | Yao Ming      | CN      |
|  2 | Kobe Bryant   | USA     |
|  3 | Stephen Curry | USA     |
+----+---------------+---------+

mysql> show variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+

so , Due to the local_infile=OFF, Only use load data infile Import data , They can't be used load data local infile.

Suppose we turn on local_infile Parameters ：

mysql> set global local_infile=ON;

mysql> create table `test03` (id int not null,
    name varchar(20),
    country varchar(12),
    primary key pk_id(`id`)
    ) engine=innodb;
    
mysql> load data local infile '/tmp/test02.txt' into table test03;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
 
mysql> set global local_infile=OFF;  #  For safety reasons, it is generally closed 

It can be seen that local_infile Parameters affect the data import operation .

MySQL This parameter can also be specified in the configuration file .

[root@DB02 ~]# cat /etc/my.cnf | grep local-inf
local-infile=0