MySQL数据设置搜索路径，以及导入导出的操作。
一、设置搜索路径，默认路径为：/var/lib/mysql-files/
修改配置文件：
vim /etc/my.cnf
[mysqld] secure_file_priv="/mydir" //加入此行。引号内写新的路径。
chown mysql /mydir //授予权限
systemctl restart mysqld //重启服务
登陆数据库查看结果：
mysql> show variables like 'secure_file_priv' //查询数据库设置 show variables; -> ; +------------------+---------+ | Variable_name | Value | +------------------+---------+ | secure_file_priv | /mydir/ | +------------------+---------+ 1 row in set (0.00 sec)
二、导入数据库。
举例：将/etc/passwd导入到数据库中，并添加行号。
mysql> create database userdb; //新建数据库 Query OK, 1 row affected (0.00 sec) mysql> use userdb Database changed mysql> create table user ( //新建数据表 -> name char(30), -> passwd char(1), -> uid int, -> gid int, -> comment char(150), -> homedir char(150), -> shell char(50), -> index(name)); Query OK, 0 rows affected (0.18 sec) [root@sql50 ~]# cp /etc/passwd /mydir/passwd //拷贝到目录下 mysql> load data infile '/mydir/passwd' //登陆数据库导入 -> into table user //指定数据表 -> fields terminated by ':' //定义分隔符 -> lines terminated by '\n'; //记录间隔符（回车） mysql> alter table user add //添加字段 -> id int primary key auto_increment first; //添加id字段，并设置为自增长。 Query OK, 0 rows affected (0.57 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from user; //查看表。 +----+---------------------+--------+-------+-------+-----------------------------------------------------------------+---------------------------+----------------+ | id | name | passwd | uid | gid | comment | homedir | shell | +----+---------------------+--------+-------+-------+-----------------------------------------------------------------+---------------------------+----------------+ | 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 | 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 | polkitd | x | 999 | 998 | User for polkitd | / | /sbin/nologin | | 17 | libstoragemgmt | x | 998 | 996 | daemon account for libstoragemgmt | /var/run/lsm | /sbin/nologin | | 18 | rpc | x | 32 | 32 | Rpcbind Daemon | /var/lib/rpcbind | /sbin/nologin | | 19 | colord | x | 997 | 995 | User for colord | /var/lib/colord | /sbin/nologin | | 20 | saslauth | x | 996 | 76 | Saslauthd user | /run/saslauthd | /sbin/nologin | | 21 | abrt | x | 173 | 173 | | /etc/abrt | /sbin/nologin | | 22 | rtkit | x | 172 | 172 | RealtimeKit | /proc | /sbin/nologin | | 23 | radvd | x | 75 | 75 | radvd user | / | /sbin/nologin | | 24 | chrony | x | 995 | 993 | | /var/lib/chrony | /sbin/nologin | | 25 | tss | x | 59 | 59 | Account used by the trousers package to sandbox the tcsd daemon | /dev/null | /sbin/nologin | | 26 | usbmuxd | x | 113 | 113 | usbmuxd user | / | /sbin/nologin | | 27 | geoclue | x | 994 | 991 | User for geoclue | /var/lib/geoclue | /sbin/nologin | | 28 | qemu | x | 107 | 107 | qemu user | / | /sbin/nologin | | 29 | rpcuser | x | 29 | 29 | RPC Service User | /var/lib/nfs | /sbin/nologin | | 30 | nfsnobody | x | 65534 | 65534 | Anonymous NFS User | /var/lib/nfs | /sbin/nologin | | 31 | setroubleshoot | x | 993 | 990 | | /var/lib/setroubleshoot | /sbin/nologin | | 32 | pulse | x | 171 | 171 | PulseAudio System Daemon | /var/run/pulse | /sbin/nologin | | 33 | gdm | x | 42 | 42 | | /var/lib/gdm | /sbin/nologin | | 34 | gnome-initial-setup | x | 992 | 987 | | /run/gnome-initial-setup/ | /sbin/nologin | | 35 | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin | | 36 | avahi | x | 70 | 70 | Avahi mDNS/DNS-SD Stack | /var/run/avahi-daemon | /sbin/nologin | | 37 | postfix | x | 89 | 89 | | /var/spool/postfix | /sbin/nologin | | 38 | ntp | x | 38 | 38 | | /etc/ntp | /sbin/nologin | | 39 | tcpdump | x | 72 | 72 | | / | /sbin/nologin | | 40 | student | x | 1000 | 1000 | Student | /home/student | /bin/bash | | 41 | apache | x | 48 | 48 | Apache | /usr/share/httpd | /sbin/nologin | | 42 | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/false | +----+---------------------+--------+-------+-------+-----------------------------------------------------------------+---------------------------+----------------+ 42 rows in set (0.00 sec)
导入语法：
load data infile '目录/文件'
into table user //指定数据表
fields terminated by ':' //定义分隔符
lines terminated by '\n'; //记录间隔符（回车）
三、数据导出。
举例：将userdb.user表中uid小于100的前十条记录导出并另存为/mydir/user.txt文件。
mysql> select * from user where uid < 100 limit 10 into outfile '/mydir/user.txt' ; //定义条件 Query OK, 10 rows affected (0.00 sec) mysql> system cat /mydir/user.txt //查看，system可用调用系统命令。 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 mysql> mysql> system cat /mydir/user.txt 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
导出语法：
查询条件 into outfile '目录/文件'
fields terminated by ':' //也可以定义分隔符，默认tab。
lines terminated by '\n'; //定义跳行符，默认回车换行。
注意：
导出的内容由查询语句决定。
导出的是表中的记录，不包括字段名。
导出失败检查SElinux设置，需要禁用SElinux！
发表评论