【MySQL 第十四天 数据库用户管理|日志管理|备份和恢复|数据表的导出和导入
【1】mysql数据库的用户管理
create user 'username@'hostname' indentified by [password] 'password'
indentified :识别
明文密码方式创建
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)
哈希密码方式创建
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)
grant -> 准予；授予；同意；承认
grand privileges ON dbname.tablename TO 'user'@'host' [identified by 'password']
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)
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)
【2】mysql数据库用户的权限管理
刷新权限命令
flush privileges;
grand priv_type ON dbname.tablename to 'username'@'host' [identified by [password] 'password'];
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)
对某个用户授予全部权限
mysql> grant all privileges on *.* to 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)
show grants for 'username'@'host';
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>
revoke privileges ON db.table FROM 'user'@'host';
revoke :v. 撤回；撤销；废除；取消；
收回插入权限
【3】mysql数据库的日志管理
show variables like 'log_error';
variables : n. 变量；可变因素；易变的东西；（variable的复数）
mysql> show variables like 'log_error';
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| log_error | .\DESKTOP-L8ILHS8.err |
+---------------+-----------------------+
1 row in set, 1 warning (0.00 sec)
通过两种方式创建错误日志文件
【1】将存在的文件放在另一个文件夹下，重新创建
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql>
【4】mysql数据库的备份和恢复
备份数据库
C:\Windows\system32>
C:\Windows\system32>mysqldump -uroot -p rb_sql >C:\Users\ll\Desktop\mysql\rb_sql_20221112.sql
Enter password: ****
备份表
C:\Windows\system32>mysqldump -uroot -p rb_sql bookinfo >C:\Users\ll\Desktop\mysql\bookinfo_20221112.sql
Enter password: ****
C:\Windows\system32>
同时备份多个数据库
mysqldump -uroot -p databases dbname1 dbname2>path/filename.sql
备份所有数据库
mysqldump -uroot -p --all-databases>path/filename.sql
mysql -uroot -p dbname<path/filename.sql
source path/filename.sql
【5】mysql数据表的导出
select columnlist_name from table_name where 条件 into outfile 'filename' [options]
【1】fields terminated by 'value' :以“值”结束的字段
【2】lines terminated by 'value' :以“值”结束的行
必须导入在此文件路径下
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>
指定分隔符导入
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)
== 限制mysql数据库导入导出==
在my.ini文件这样处理
secure-file-priv=NULL
修改为可以指定的路径保存文件
secure-file-priv=''
mysqldump -T path -uroot -p dbname [tables][options]
同时生成两个文件
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>
查询某些所有或部分列数据导入
mysql -uroot -p --execute = "select 语句" dbname >path/filename.txt
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>
【6】mysql数据库的导入
load data infile 'filename.txt' into table tablename [options][ignore number lines]
load data infile "path/name.txt" into table dbname.tablename;
mysqlimport -uroot -p dbname path/filename.txt [options]
mysqlimport -uroot -p dbname "path/filename"
时隔半个月半，练习时常半个半的我终于结束了，下一期SQLITE3大刑伺候
459
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
