文章详细介绍了如何在MySQL中查看和修改默认的检索目录，以及如何进行数据的导入和导出操作。在导入数据时，涉及到文件路径、权限设置和数据格式的指定，如列以$分隔，行以换行符结束。导出数据则可以定制导出内容和格式。整个过程强调了安全性和文件权限的重要性。
数据库学习-数据导入导出
数据导入导出（批量处理数据）
检索目录（执行导入导出时，MySQL服务默认只在定义的目录下查找文件）
查看默认的检索目录
修改检索目录
.
数据导入（将系统文件的内容存储到数据库服务器的表里）
.
数据导出（将数据库服务器表里的内容存储到系统文件里）
查看默认检索目录
mysql> show variables like 'secure_file_priv';
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.99 sec)
mysql> system ls /var/lib/
alternatives dhclient initramfs mysql-files plymouth rhsm selinux tuned
authselect dnf logrotate mysql-keyring polkit-1 rpm sss unbound
dav games misc NetworkManager portables rpm-state systemd up2date
dbus httpd mysql os-prober private rsyslog tpm
模糊查询：show variables like '%XXXX%';
mysql> show variables like '%secure%';
+--------------------------+---------+
| Variable_name | Value |
+--------------------------+---------+
| require_secure_transport | OFF |
| secure_file_priv | /mylog/ |
+--------------------------+---------+
2 rows in set (0.00 sec)
修改检索目录路径（！！文件一定要有MySQL用户的 7的RWX 权限！）
1、改配置文件
[root@control-clone ~]# vim /etc/my.cnf
mysql> system cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
secure_file_priv='/myload'
# 末尾两行是要修改的内容
2、创建定义的文件，改所有者为mysql，重启服务mysqld
[root@control-clone ~]# mkdir /myload
[root@control-clone ~]# chown mysql /myload
[root@control-clone ~]# systemctl restart mysqld.service
3、验证：
mysql> show variables like 'secure_file_priv';
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| secure_file_priv | /myload/ |
+------------------+---------+
1 row in set (0.01 sec)
默认的检索目录和权限
[root@control ~]# ls -l /var/lib/mysql-files
total 4
-rw-r--r--. 1 root root 1241 May 16 17:07 passwd
[root@control ~]# ls -l /var/lib/
total 8
drwxr-x---. 2 mysql mysql 20 May 16 17:07 mysql-files
数据导入
例：把用户信息存到数据库里
建库、建表、建表结构
mysql> create table user01(
-> id int primary key auto_increment,
-> name char(50),
-> password char(10),
-> uid int,
-> gid int,
-> comment varchar(150),
-> homedir char(100),
-> shell char(80)
-> );
Query OK, 0 rows affected (0.17 sec)
mysql> desc user01;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | YES | | NULL | |
| password | char(10) | YES | | NULL | |
| uid | int(11) | YES | | NULL | |
| gid | int(11) | YES | | NULL | |
| comment | varchar(150) | YES | | NULL | |
| homedir | char(100) | YES | | NULL | |
| shell | char(80) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)
2,把文件内容放到定义的检索目录内：
MariaDB [studio]> system cp /etc/passwd /myload
sh: warning: setlocale: LC_ALL: cannot change locale (zh_CN.utf8): No such file or directory
MariaDB [studio]> system ls /myload
sh: warning: setlocale: LC_ALL: cannot change locale (zh_CN.utf8): No such file or directory
anaconda-ks.cfg passwd
3、导入数据
MariaDB [db1]> load data infile '/myload/passwd' into table user fields terminated by ":" lines terminated by "\n";
Query OK, 18 rows affected (0.006 sec)
Records: 18 Deleted: 0 Skipped: 0 Warnings: 0
":"#文件中列的分隔符
"\n"#文件中行的结束符
MariaDB [db1]> select * from user;
+-----------------+----------+------+------+----------------------------+-----------------+----------------+
| name | password | uid | gid | comment | homedir | shell |
+-----------------+----------+------+------+----------------------------+-----------------+----------------+
| root | x | 0 | 0 | root | /root | /bin/zsh |
| bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
| adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
| lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
| sync | x | 5 | 0 | sync | /sbin | /bin/sync |
| shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown |
| halt | x | 7 | 0 | halt | /sbin | /sbin/halt |
| mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin |
| operator | x | 11 | 0 | operator | /root | /sbin/nologin |
| games | x | 12 | 100 | games | /usr/games | /sbin/nologin |
| ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin |
| nobody | x | 99 | 99 | Nobody | / | /sbin/nologin |
| systemd-network | x | 192 | 192 | systemd Network Management | / | /sbin/nologin |
| dbus | x | 81 | 81 | System message bus | / | /sbin/nologin |
| sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |
| ntp | x | 38 | 38 | | /etc/ntp | /sbin/nologin |
| mysql | x | 999 | 997 | MySQL server | /var/lib/mysql | /sbin/nologin |
+-----------------+----------+------+------+----------------------------+-----------------+----------------+
18 rows in set (0.000 sec)
MariaDB [db1]>
4、完善表，添加行号
MariaDB [db1]> alter table user add id int primary key auto_increment first;
Query OK, 0 rows affected (0.022 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db1]> desc user;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | YES | | NULL | |
| password | char(10) | YES | | NULL | |
| uid | int(11) | YES | | NULL | |
| gid | int(11) | YES | | NULL | |
| comment | varchar(150) | YES | | NULL | |
| homedir | char(100) | YES | | NULL | |
| shell | char(80) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.001 sec)
MariaDB [db1]> select * from user;
+----+-----------------+----------+------+------+----------------------------+-----------------+----------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+-----------------+----------+------+------+----------------------------+-----------------+----------------+
| 1 | root | x | 0 | 0 | root | /root | /bin/zsh |
| 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
| 4 | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
| 5 | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
| 6 | sync | x | 5 | 0 | sync | /sbin | /bin/sync |
| 7 | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown |
| 8 | halt | x | 7 | 0 | halt | /sbin | /sbin/halt |
| 9 | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin |
| 10 | operator | x | 11 | 0 | operator | /root | /sbin/nologin |
| 11 | games | x | 12 | 100 | games | /usr/games | /sbin/nologin |
| 12 | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin |
| 13 | nobody | x | 99 | 99 | Nobody | / | /sbin/nologin |
| 14 | systemd-network | x | 192 | 192 | systemd Network Management | / | /sbin/nologin |
| 15 | dbus | x | 81 | 81 | System message bus | / | /sbin/nologin |
| 16 | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |
| 17 | ntp | x | 38 | 38 | | /etc/ntp | /sbin/nologin |
| 18 | mysql | x | 999 | 997 | MySQL server | /var/lib/mysql | /sbin/nologin |
+----+-----------------+----------+------+------+----------------------------+-----------------+----------------+
18 rows in set (0.000 sec)
数据导出
加个序号：
mysql> alter table user add id int primary key auto_increment first;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | YES | | NULL | |
| password | char(10) | YES | | NULL | |
| uid | int(11) | YES | | NULL | |
| gid | int(11) | YES | | NULL | |
| comment | varchar(150) | YES | | NULL | |
| homedir | char(100) | YES | | NULL | |
| shell | char(80) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
导出数据，到检索目录下，文件名自定义。不用提前在目录下创建文件，目录下没有的文件可自动创建！！！
mysql> select * from user into outfile '/var/lib/mysql-files/user.txt02';
Query OK, 25 rows affected (0.00 sec)
mysql> system cat /var/lib/mysql-files/user.txt02
1 root x 0 0 root /root /bin/bash
2 bin x 1 1 bin /bin /sbin/nologin
3 daemon x 2 2 daemon /sbin /sbin/nologin
4 adm x 3 4 adm /var/adm /sbin/nologin
5 lp x 4 7 lp /var/spool/lpd /sbin/nologin
6 sync x 5 0 sync /sbin /bin/sync
7 shutdown x 6 0 shutdown /sbin /sbin/shutdown
8 halt x 7 0 halt /sbin /sbin/halt
9 mail x 8 12 mail /var/spool/mail /sbin/nologin
10 operator x 11 0 operator /root /sbin/nologin
11 games x 12 100 games /usr/games /sbin/nologin
12 ftp x 14 50 FTP User /var/ftp /sbin/nologin
13 nobody x 65534 65534 Kernel Overflow User / /sbin/nologin
14 dbus x 81 81 System message bus / /sbin/nologin
15 systemd-coredump x 999 997 systemd Core Dumper / /sbin/nologin
16 systemd-resolve x 193 193 systemd Resolver / /sbin/nologin
17 tss x 59 59 Account used by the trousers package to sandbox the tcsd daemon /dev/null /sbin/nologin
18 polkitd x 998 996 User for polkitd / /sbin/nologin
19 unbound x 997 995 Unbound DNS resolver /etc/unbound /sbin/nologin
20 sssd x 996 993 User for sssd / /sbin/nologin
21 sshd x 74 74 Privilege-separated SSH /var/empty/sshd /sbin/nologin
22 rngd x 995 992 Random Number Generator Daemon /var/lib/rngd /sbin/nologin
23 alice x 1000 1000 /home/alice /bin/bash
24 apache x 48 48 Apache /usr/share/httpd /sbin/nologin
25 mysql x 1001 1001 /home/mysql /bin/bash
mysql>
自定义要导出的数据内容，列的分隔符 以$分割
mysql> select name,uid from user into outfile '/var/lib/mysql-files/userid.txt' fields terminated by '$';
Query OK, 25 rows affected (0.01 sec)
mysql> system cat /var/lib/mysql-files/userid.txt
root$0
bin$1
daemon$2
adm$3
lp$4
sync$5
shutdown$6
halt$7
mail$8
operator$11
games$12
ftp$14
nobody$65534
dbus$81
systemd-coredump$999
systemd-resolve$193
tss$59
polkitd$998
unbound$997
sssd$996
sshd$74
rngd$995
alice$1000
apache$48
mysql$1001
mysql>
自定义导出行的符号为！！，并换行
mysql> system cat /var/lib/mysql-files/userid01.txt root$0!!elect name,uid from user where id<=5 into outfile '/var/lib/mysql-files/userid02.txt' fields terminated by '$' lines terminated by '!!\n';
Query OK, 5 rows affected (0.00 sec)
mysql> system cat /var/lib/mysql-files/userid02.txt
root$0!!
bin$1!!
daemon$2!!
adm$3!!
lp$4!!
mysql>
2670
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
