07 mysql之数据导入导出与用户管理#

1
[root@mysql50 ~]# mysql -uroot -pNSD123456...a
2
mysql> show【瘦】 variables【wèi 利 bóu 斯】 like【赖特】 "%file【伐尔】%";    -- 查看与文件相关的配置项
3
-- mysql> show variables like "%file%";
4
+---------------------------------------+---------------------------------+
5
| Variable_name                         | Value                           |
6
+---------------------------------------+---------------------------------+
7
| character_set_filesystem              | binary                          |
8
| core_file                             | OFF                             |
9
| ft_stopword_file                      | (built-in)                      |
10
| general_log_file                      | /var/lib/mysql/mysql50.log      |
11
| init_file                             |                                 |
12
| innodb_buffer_pool_filename           | ib_buffer_pool                  |
13
| innodb_buffer_pool_in_core_file       | ON                              |
14
| innodb_data_file_path                 | ibdata1:12M:autoextend          |
15
| innodb_disable_sort_file_cache        | OFF                             |
16
| innodb_doublewrite_files              | 2                               |
17
| innodb_file_per_table                 | ON                              |
18
| innodb_log_file_size                  | 50331648                        |
19
| innodb_log_files_in_group             | 2                               |
20
| innodb_open_files                     | 4000                            |
21
| innodb_temp_data_file_path            | ibtmp1:12M:autoextend           |
22
| keep_files_on_create                  | OFF                             |
23
| large_files_support                   | ON                              |
24
| local_infile                          | OFF                             |
25
| lower_case_file_system                | OFF                             |
26
| myisam_max_sort_file_size             | 9223372036853727232             |
27
| open_files_limit                      | 10000                           |
28
| performance_schema_max_file_classes   | 80                              |
29
| performance_schema_max_file_handles   | 32768                           |
30
| performance_schema_max_file_instances | -1                              |
31
| pid_file                              | /run/mysqld/mysqld.pid          |
32
| relay_log_info_file                   | relay-log.info                  |
33
| secure_file_priv                      | /var/lib/mysql-files/           |
34
| slow_query_log_file                   | /var/lib/mysql/mysql50-slow.log |
35
+---------------------------------------+---------------------------------+
36
28 rows in set (0.00 sec)
37

38
-- 查看默认检索目录
39
mysql> show【瘦】 variables【wèi 利 bóu 斯】 like【赖特】 "secure【瑟Q儿】_file【伐尔】_priv【破略夫】";
40
-- mysql> show variables like "secure_file_priv";
41
+------------------+-----------------------+
42
| Variable_name    | Value                 |
43
+------------------+-----------------------+
44
| secure_file_priv | /var/lib/mysql-files/ |
45
+------------------+-----------------------+
46
1 row in set (0.00 sec)
47
mysql> exit【诶西特】
48

49
-- 安装MySQL服务软件时自动创建
50
[root@mysql50 ~]# ls -ld /var/lib/mysql-files【伐尔】/
51
-- [root@mha ~]# ls -ld /var/lib/mysql-files/
52
drwxr-x--- 2 mysql mysql 6 Sep 22  2021 /var/lib/mysql-files【伐尔】/
53

54

55
-- 修改主配置文件
56
[root@mysql50 ~]# vim /etc/my.cnf.d/mysql-server.cnf
57
[mysqld]
58
secure【瑟Q儿】_file【伐尔】_priv【破略夫】=/myload【my漏德】  -- 添加此行
59
:wq
60

61
-- 创建目录并修改所有者为mysql用户 ,并保证mysql用户对父目录有rx
62
[root@mysql50 ~]# mkdir /myload【my漏德】
63
-- [root@mha ~]# mkdir /myload
64
[root@mysql50 ~]# chown【吃-昂】 mysql /myload【my漏德】
65
-- [root@mha ~]# chown mysql /myload
66

67
-- 关闭selinux
68
[root@mysql50 ~]# setenforce【赛特-因佛尔斯】 0
69
-- [root@mha ~]# setenforce 0
70
setenforce: SELinux is disabled
71

72
-- 重启服务
73
[root@mysql50 ~]# systemctl【西斯屯-ctl】  restart【 率 s 达r】 mysqld
74
-- [root@mha ~]# systemctl restart mysqld
75

76
-- 管理员员登陆查看目录
77
[root@mysql50 ~]# mysql -uroot -pNSD123456...a
78
mysql> show【瘦】 variables【wèi 利 bóu 斯】 like【赖特】 "secure【瑟Q儿】_file【伐尔】_priv【破略夫】";
79
-- mysql> show variables like "secure_file_priv";
80
+------------------+----------+
81
| Variable_name    | Value    |
82
+------------------+----------+
83
| secure_file_priv | /myload/ |
84
+------------------+----------+
85
1 row in set (0.01 sec)

1
[root@mysql50 ~]# mysql -uroot -pNSD123456...a
2
mysql> create【克瑞特】 database【得塔-贝斯】 db1;
3
-- mysql> create database db1;
4
-- Query OK, 1 row affected (0.01 sec)
5

6
-- 建表（ 根据导入的文件内容 创建表头）
7
mysql> create【克瑞特】 table【忒-部】 db1.user3(
8
   name varchar【瓦儿-查儿】(30),
9
   password【帕斯沃德】 char【查尔】(1),
10
   uid int【因特】 ,
11
   gid int【因特】 ,
12
   comment【坑门特】 varchar【瓦儿-查儿】(200),
13
   homedir【home-跌儿】 varchar【瓦儿-查儿】(50),
14
   shell varchar【瓦儿-查儿】(30));
15
Query OK, 0 rows affected (0.41 sec)
16
-- mysql> create table db1.user3(
17
--    name varchar(30),
18
--    password char(1),
19
--    uid int,
20
--    gid int,
21
--    comment varchar(200),
22
--    homedir varchar(50),
23
--    shell varchar(30));
24
-- Query OK, 0 rows affected (0.06 sec)
25

26
-- 查看表头
27
mysql> desc db1.user3;
28
-- mysql> desc db1.user3;
29
+----------+--------------+------+-----+---------+-------+
30
| Field    | Type         | Null | Key | Default | Extra |
31
+----------+--------------+------+-----+---------+-------+
32
| name     | varchar(30)  | YES  |     | NULL    |       |
33
| password | char(1)      | YES  |     | NULL    |       |
34
| uid      | int          | YES  |     | NULL    |       |
35
| gid      | int          | YES  |     | NULL    |       |
36
| comment  | varchar(200) | YES  |     | NULL    |       |
37
| homedir  | varchar(50)  | YES  |     | NULL    |       |
38
| shell    | varchar(30)  | YES  |     | NULL    |       |
39
+----------+--------------+------+-----+---------+-------+
40
7 rows in set (0.01 sec)
41

42
-- 没有数据
43
mysql> select【涩莱克特】  * from【弗乱】 db1.user3;
44
-- mysql> select * from db1.user3;
45
Empty set (0.01 sec)
46

47
-- 拷贝文件到检索目录 system【西斯屯】 在MySQL 里执行系统命令
48
mysql> system【西斯屯】 cp /etc/passwd  /myload【my漏德】/
49
-- mysql> system cp /etc/passwd /myload
50
-- mysql>
51

52
-- mysql> system cp /etc/passwd /myload
53
-- cp: cannot stat '/etc/passwd': No such file or directory -- 提示该报错是因为一、没有权限，需要修改权限，二、没有文件，需要拷贝文件，三、当前登录的mysql账号没有权限，需要修改权限
54
-- 一、修改权限
55
-- mysql> system【西斯屯】 chmod【吃-默德】 777 /myload【my漏德】/
56
-- mysql> system chmod 777 /myload
57
-- 三、修改mysql用户权限
58
-- mysql> grant【格拉特】 all【阿尔】 on /myload【my漏德】/* to【托 敏 内提】 mysql@'%'【托 敏 内提】;
59
-- mysql> grant all on /myload/* to mysql@'%';
60
-- 或者切换有权限用户
61
-- mysql> system whoami --查看当前登录用户
62
-- sf\ta206682
63

64
mysql> system【西斯屯】 ls /myload【my漏德】/  -- 查看文件
65
-- mysql> system ls /myload
66
passwd
67
mysql>
68

69
-- 导入数据
70
mysql> load【漏德】 data  infile【因-伐尔】 "/myload【my漏德】/passwd" into【因兔】 table【忒-部】 db1.user3 fields【菲尔兹】 terminated【托 敏 内提】 by【拜】 ":" lines【赖嗯 s】 terminated【托 敏 内提】 by【拜】 "\n" ;
71
-- mysql> load data infile "/myload/passwd" into table db1.user3 fields terminated by ":" lines terminated by "\n";
72
Query OK, 23 rows affected (0.06 sec)
73
Records: 23  Deleted: 0  Skipped: 0  Warnings: 0
74

75
-- 查看表记录
76
mysql> select【涩莱克特】 count【kàn特】(*) from【弗乱】  db1.user3;
77
-- mysql> select count(*) from db1.user3;
78
+----------+
79
| count(*) |
80
+----------+
81
|       23 |
82
+----------+
83
1 row in set (0.00 sec)
84

85
mysql> select【涩莱克特】  * from【弗乱】 db1.user3;
86
-- mysql> select * from db1.user3;
87
+------------------+----------+-------+-------+-----------------------------+-----------------+----------------+
88
| name             | password | uid   | gid   | comment                     | homedir         | shell          |
89
+------------------+----------+-------+-------+-----------------------------+-----------------+----------------+
90
| root             | x        |     0 |     0 | root                        | /root           | /bin/bash      |
91
| bin              | x        |     1 |     1 | bin                         | /bin            | /sbin/nologin  |
92
| daemon           | x        |     2 |     2 | daemon                      | /sbin           | /sbin/nologin  |
93
| adm              | x        |     3 |     4 | adm                         | /var/adm        | /sbin/nologin  |
94
| lp               | x        |     4 |     7 | lp                          | /var/spool/lpd  | /sbin/nologin  |
95
| sync             | x        |     5 |     0 | sync                        | /sbin           | /bin/sync      |
96
| shutdown         | x        |     6 |     0 | shutdown                    | /sbin           | /sbin/shutdown |
97
| halt             | x        |     7 |     0 | halt                        | /sbin           | /sbin/halt     |
98
| mail             | x        |     8 |    12 | mail                        | /var/spool/mail | /sbin/nologin  |
99
| operator         | x        |    11 |     0 | operator                    | /root           | /sbin/nologin  |
100
| games            | x        |    12 |   100 | games                       | /usr/games      | /sbin/nologin  |
101
| ftp              | x        |    14 |    50 | FTP User                    | /var/ftp        | /sbin/nologin  |
102
| nobody           | x        | 65534 | 65534 | Kernel Overflow User        | /               | /sbin/nologin  |
103
| dbus             | x        |    81 |    81 | System message bus          | /               | /sbin/nologin  |
104
| systemd-coredump | x        |   999 |   997 | systemd Core Dumper         | /               | /sbin/nologin  |
105
| systemd-resolve  | x        |   193 |   193 | systemd Resolver            | /               | /sbin/nologin  |
106
| polkitd          | x        |   998 |   995 | User for polkitd            | /               | /sbin/nologin  |
107
| unbound          | x        |   997 |   994 | Unbound DNS resolver        | /etc/unbound    | /sbin/nologin  |
108
| tss              | x        |    59 |    59 | Account used for TPM access | /dev/null       | /sbin/nologin  |
109
| chrony           | x        |   996 |   993 |                             | /var/lib/chrony | /sbin/nologin  |
110
| sshd             | x        |    74 |    74 | Privilege-separated SSH     | /var/empty/sshd | /sbin/nologin  |
111
| tcpdump          | x        |    72 |    72 |                             | /               | /sbin/nologin  |
112
| mysql            | x        |    27 |    27 | MySQL Server                | /var/lib/mysql  | /sbin/nologin  |
113
+------------------+----------+-------+-------+-----------------------------+-----------------+----------------+
114
23 rows in set (0.00 sec)
115
mysql>

1
mysql> select【涩莱克特】  * from【弗乱】 db1.user3 into【因兔】 outfile【奥特-伐尔】 "/myload【my漏德】/user.txt" ;
2
-- mysql> select * from db1.user3 into outfile "/myload/user.txt";
3
Query OK, 23 rows affected (0.00 sec)
4

5
mysql> system【西斯屯】 ls /myload【my漏德】/
6
-- mysql> system ls /myload/
7
passwd  user.txt
8

9
mysql> system【西斯屯】 wc -l  /myload【my漏德】/user.txt
10
-- mysql> system wc -l /myload/user.txt
11
23 /myload/user.txt
12

13
mysql>
14
mysql> system【西斯屯】 vim /myload【my漏德】/user.txt
15
-- mysql> system vim /myload/user.txt
16
root    x       0       0       root    /root   /bin/bash
17
bin     x       1       1       bin     /bin    /sbin/nologin
18
daemon  x       2       2       daemon  /sbin   /sbin/nologin
19
adm     x       3       4       adm     /var/adm        /sbin/nologin
20
lp      x       4       7       lp      /var/spool/lpd  /sbin/nologin
21
sync    x       5       0       sync    /sbin   /bin/sync
22
shutdown        x       6       0       shutdown        /sbin   /sbin/shutdown
23
halt    x       7       0       halt    /sbin   /sbin/halt
24
mail    x       8       12      mail    /var/spool/mail /sbin/nologin
25
operator        x       11      0       operator        /root   /sbin/nologin
26
games   x       12      100     games   /usr/games      /sbin/nologin
27
ftp     x       14      50      FTP User        /var/ftp        /sbin/nologin
28
nobody  x       65534   65534   Kernel Overflow User    /       /sbin/nologin
29
dbus    x       81      81      System message bus      /       /sbin/nologin
30
systemd-coredump        x       999     997     systemd Core Dumper     /       /sbin/nologin
31
systemd-resolve x       193     193     systemd Resolver        /       /sbin/nologin
32
polkitd x       998     995     User for polkitd        /       /sbin/nologin
33
unbound x       997     994     Unbound DNS resolver    /etc/unbound    /sbin/nologin
34
tss     x       59      59      Account used for TPM access     /dev/null       /sbin/nologin
35
chrony  x       996     993             /var/lib/chrony /sbin/nologin
36
sshd    x       74      74      Privilege-separated SSH /var/empty/sshd /sbin/nologin
37
tcpdump x       72      72              /       /sbin/nologin
38
mysql   x       27      27      MySQL Server    /var/lib/mysql  /sbin/nologin

1
LOAD DATA INFILE最适合处理格式规整的文本文件
2
MySQL 导出的数据文件（如使用 SELECT ... INTO OUTFILE导出的）格式规整，最适合用此命令重新导入

1
-- 数据库创建
2
mysql> create【克瑞特】 database【得塔-贝斯】 new_db【牛-滴比】;
3
-- mysql> create database new_db;
4
Query OK, 1 row affected (6.379 sec)
5

6
-- admin_user 用户创建
7
mysql> create【克瑞特】 user【右瑟】 admin_user【admin-右瑟】@"%" identified【爱-丹-提伐】 by【拜】 "admin_user";
8
-- mysql> create user admin_user@"%" identified by "admin_user";
9
Query OK, 0 rows affected (7.111 sec)
10

11
-- admin_user 用户赋予权限
12
mysql> grant【古亮普】 all【ò】 on【昂】 new_db【牛-滴比】.* to admin_user【admin-右瑟】@"%";
13
-- mysql> grant all on new_db.* to admin_user@"%";
14
Query OK, 0 rows affected (0.680 sec)
15

16
-- guest_user 用户创建
17
mysql> create【克瑞特】 user【右瑟】 guest_user【盖斯特-右瑟】@"%" identified【爱-丹-提伐】 by【拜】 "guest_user【盖斯特-右瑟】";
18
-- mysql> create user guest_user@"%" identified by "guest_user";
19
Query OK, 0 rows affected (0.224 sec)
20

21
-- 创建new_db.table1
22
mysql> create【克瑞特】 table【忒-部】 new_db【牛-滴比】.table1【忒-部-万】(id int【因特】);
23
-- mysql> create table new_db.table1(id int);
24
Query OK, 0 rows affected (2.602 sec)
25

26
-- guest_user 用户赋予权限
27
mysql> grant【古亮普】 select【涩莱克特】 on【昂】 new_db【牛-滴比】.table1【忒-部-万】 to guest_user【盖斯特-右瑟】@"%";
28
-- mysql> grant select on new_db.table1 to guest_user@"%";
29
Query OK, 0 rows affected (0.295 sec)

1
-- 查看用户列表
2
mysql> select【涩莱克特】 user【右瑟】, host【厚斯特】 from【弗乱】 mysql.user【右瑟】 where【威尔】 user【右瑟】 like【赖特】 '%admin%' or user【右瑟】 like【赖特】 '%guest%';
3
-- mysql> select user, host from mysql.user where user like '%admin%' or user like '%guest%';
4
+-------------+------+
5
| user        | host |
6
+-------------+------+
7
| admin_user  | %    |
8
| guest_user  | %    |
9
+-------------+------+
10
2 rows in set (0.00 sec)
11

12
-- 查看 admin_user 权限
13
mysql> show【瘦】 grants【古亮普 s】 for【fó】 admin_user【admin-右瑟】@"%";
14
-- mysql> show grants for admin_user@"%";
15
+-------------------------------------------------------------+
16
| Grants for admin_user@%                                     |
17
+-------------------------------------------------------------+
18
| GRANT USAGE ON *.* TO 'admin_user'@'%'                      |
19
| GRANT ALL PRIVILEGES ON `new_db`.* TO 'admin_user'@'%'      |
20
+-------------------------------------------------------------+
21

22
-- 查看 guest_user 权限
23
mysql> show【瘦】 grants【古亮普 s】 for【fó】 guest_user【盖斯特-右瑟】@"%";
24
-- mysql> show grants for guest_user@"%";
25
+-------------------------------------------------------------+
26
| Grants for guest_user@%                                     |
27
+-------------------------------------------------------------+
28
| GRANT USAGE ON *.* TO 'guest_user'@'%'                      |
29
| GRANT SELECT ON `new_db`.`table1` TO 'guest_user'@'%'      |
30
+-------------------------------------------------------------+

1
2. 从 'employees' 表中导出员工的每一列信息到一个 TXT 文件，要求使用逗号作为字段分隔符，换行符作为行分隔符。之后将这个 TXT 文件中的数据导入到新创建的 'employees_backup' 表。
2
```sql
3
mysql>-- 导出moershi.employees
4
mysql> select * from moershi.employees into outfile "/myload/employees.txt" fields terminated by "," lines terminated by "\n";
5
Query OK, 136 rows affected (0.200 sec)
6

7
mysql>-- 创建new_db.employees_backup
8
mysql> create table new_db.employees_backup like moershi.employees;
9
Query OK, 0 rows affected (3.269 sec)
10

11
mysql> -- 导入
12
mysql> load data infile "/myload/employees.txt" into table new_db.employees_backup fields terminated by "," lines terminated by "\n";
13
Query OK, 136 rows affected (1.059 sec)
14
Records: 136  Deleted: 0  Skipped: 0  Warnings: 0
15

16
mysql> select count(*) from new_db.employees_backup;
17
+----------+
18
| count(*) |
19
+----------+
20
|      136 |
21
+----------+
22
1 row in set (0.351 sec)

1
mysql> --创建数据库
2
mysql> create database news;
3
Query OK, 1 row affected (0.664 sec)
4

5
mysql>-- 创建表
6
mysql> create table news.articles(id int,name varchar(9));
7
Query OK, 0 rows affected (2.513 sec)
8

9
mysql>-- 创建用户
10
mysql> create user editor_user@"%" identified by "editor_user";
11
Query OK, 0 rows affected (1.115 sec)
12

13
mysql>-- 赋予权限
14
mysql> grant all on news.articles to editor_user@"%";
15
Query OK, 0 rows affected (0.571 sec)
16

17
mysql>-- 撤销权限
18
mysql> revoke delete on news.articles from editor_user@"%";
19
Query OK, 0 rows affected (0.438 sec)
20

21
mysql>-- 增加权限
22
mysql> grant update on news.articles to editor_user@"%";
23
Query OK, 0 rows affected (0.469 sec)

1
mysql> --创建数据库
2
mysql> create database test_db;
3
Query OK, 1 row affected (0.648 sec)
4

5
mysql> -- 创建表
6
mysql> create table test_db.logs(
7
    -> id int,
8
    -> logs varchar(200));
9
Query OK, 0 rows affected (1.589 sec)
10

11
mysql> 创建用户
12
mysql> create user temp_user@"%" identified by "temp_user";
13
Query OK, 0 rows affected (0.760 sec)
14

15
mysql> --赋予权限
16
mysql> grant insert on  test_db.logs to temp_user@"%";
17
Query OK, 0 rows affected (0.676 sec)
18

19
mysql> --插入数据
20
mysql> insert into test_db.logs values(1,"程序卡死");
21
Query OK, 1 row affected (0.291 sec)
22

23
mysql> --删除用户
24
mysql> drop user temp_user@"%";
25
Query OK, 0 rows affected (0.693 sec)