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)

[[email protected] ~]# 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!





发表评论

必填

选填

选填

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。