【MySQL 第十四天 数据库用户管理|日志管理|备份和恢复|数据表的导出和导入】

---

【1】mysql数据库的用户管理

---

---

create user 'username@'hostname' indentified by [password] 'password'
indentified :识别
1
2

---

明文密码方式创建

mysql> create user 'qq'@'localhostt' identified by 'root123';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+---------------+------------+
| user          | host       |
+---------------+------------+
| kali          | %          |
| rose          | locaalhost |
| mysql.session | localhost  |
| mysql.sys     | localhost  |
| root          | localhost  |
| qq            | localhostt |
+---------------+------------+
6 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

---

哈希密码方式创建

mysql> select password('root321');
+-------------------------------------------+
| password('root321')                       |
+-------------------------------------------+
| *A5219B9C114066F880112DA42E78FA40EC4A5781 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> create user 'qq11'@'localhostt' identified by password '*A5219B9C114066F880112DA42E78FA40EC4A5781'
    -> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> select user,host from mysql.user;
+---------------+------------+
| user          | host       |
+---------------+------------+
| kali          | %          |
| rose          | locaalhost |
| mysql.session | localhost  |
| mysql.sys     | localhost  |
| root          | localhost  |
| qq            | localhostt |
| qq11          | localhostt |
+---------------+------------+
7 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

---

grant -> 准予；授予；同意；承认
1

grand privileges ON dbname.tablename TO 'user'@'host' [identified by 'password']
1

---

mysql> grant select,update ON *.* TO 'rnm'@'localhost' identified by 'kali';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql>
mysql>
mysql> show tables;
+------------------+
| Tables_in_rb_sql |
+------------------+
| book_bak         |
| bookinfo         |
| bookinfo_bak     |
| bookparent       |
| borrowinfo       |
| myengine         |
| mytest           |
| readerfee        |
| readerinfo       |
+------------------+
9 rows in set (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+------------+
| user          | host       |
+---------------+------------+
| kali          | %          |
| rose          | locaalhost |
| mysql.session | localhost  |
| mysql.sys     | localhost  |
| rnm           | localhost  |
| root          | localhost  |
| qq            | localhostt |
| qq11          | localhostt |
+---------------+------------+
8 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

---

---

mysql> select user  host from  mysql.user;
+---------------+
| host          |
+---------------+
| kali          |
| rose          |
| mysql.session |
| mysql.sys     |
| rnm           |
| root          |
| qq            |
| qq11          |
+---------------+
8 rows in set (0.00 sec)

mysql> delete from mysql.user where user = 'qq11' and host = 'localhost';
Query OK, 0 rows affected (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

---

【2】mysql数据库用户的权限管理

刷新权限命令

flush privileges;
1

grand priv_type ON dbname.tablename to 'username'@'host' [identified by [password] 'password'];
1

---

mysql>
mysql> grant insert,select on bookinfo.* to 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

1
2
3
4
5
6
7

对某个用户授予全部权限

mysql> grant all privileges on *.* to 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)
1
2

show grants for 'username'@'host';
1

mysql>
mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT SELECT, INSERT ON `bookinfo`.* TO 'root'@'localhost'          |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>
mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13

---

revoke privileges ON db.table FROM 'user'@'host';
revoke :v. 撤回；撤销；废除；取消；
1
2

收回插入权限

---

【3】mysql数据库的日志管理

---

---

show variables like 'log_error';
variables : n. 变量；可变因素；易变的东西；（variable的复数）
1
2

mysql> show variables like 'log_error';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| log_error     | .\DESKTOP-L8ILHS8.err |
+---------------+-----------------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

---

---

通过两种方式创建错误日志文件
【1】将存在的文件放在另一个文件夹下，重新创建

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql>
1
2
3
4

---

【4】mysql数据库的备份和恢复

---

备份数据库

C:\Windows\system32>
C:\Windows\system32>mysqldump -uroot -p rb_sql >C:\Users\ll\Desktop\mysql\rb_sql_20221112.sql
Enter password: ****

1
2
3
4

备份表

C:\Windows\system32>mysqldump -uroot -p rb_sql bookinfo >C:\Users\ll\Desktop\mysql\bookinfo_20221112.sql
Enter password: ****

C:\Windows\system32>
1
2
3
4

同时备份多个数据库

mysqldump -uroot -p databases dbname1 dbname2>path/filename.sql
1

备份所有数据库

mysqldump -uroot -p --all-databases>path/filename.sql
1

---

mysql -uroot -p dbname<path/filename.sql

1
2

---

source path/filename.sql
1

---

【5】mysql数据表的导出

---

select columnlist_name from table_name where 条件 into outfile 'filename' [options]
【1】fields terminated by 'value' :以“值”结束的字段
【2】lines terminated by 'value'  :以“值”结束的行
1
2
3

---

必须导入在此文件路径下

mysql>
mysql> select *from rb_sql.readerinfo into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/readerinfo.txt';
Query OK, 9 rows affected (0.01 sec)

mysql>
1
2
3
4
5

---

指定分隔符导入

mysql>
mysql> select *from rb_sql.readerinfo into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/readerinfo.txt'
    -> fields terminated by '-'
    -> lines terminated by '\r\n';
Query OK, 9 rows affected, 1 warning (0.00 sec)

1
2
3
4
5
6

---

== 限制mysql数据库导入导出==
在my.ini文件这样处理

secure-file-priv=NULL
1

修改为可以指定的路径保存文件

secure-file-priv=''
1

---

mysqldump -T path -uroot -p dbname [tables][options]
1

---

同时生成两个文件

C:\Windows\system32>
C:\Windows\system32>mysqldump -T "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads" -uroot -p rb_sql bookinfo
Enter password: ****

C:\Windows\system32>
1
2
3
4
5

---

查询某些所有或部分列数据导入

mysql -uroot -p --execute = "select 语句" dbname >path/filename.txt
1

C:\Windows\system32>mysql -uroot -p --execute="select *from bookinfo;"  rb_sql> "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/1.txt"
Enter password: ****

C:\Windows\system32>
1
2
3
4

---

【6】mysql数据库的导入

load data infile 'filename.txt' into table tablename [options][ignore number lines]
1

---

load data infile "path/name.txt" into table dbname.tablename;
1

---

mysqlimport -uroot -p dbname path/filename.txt [options]
1

---

mysqlimport -uroot -p dbname "path/filename"
1

---

时隔半个月半，练习时常半个半的我终于结束了，下一期SQLITE3大刑伺候