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

【1】mysql数据库的用户管理【2】mysql数据库用户的权限管理【3】mysql数据库的日志管理【4】mysql数据库的备份和恢复【5】mysql数据表的导出【6】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_1112.sqlEnter password: ****

备份表

C:\Windows\system32>mysqldump -uroot -p rb_sql bookinfo >C:\Users\ll\Desktop\mysql\bookinfo_1112.sqlEnter 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 bookinfoEnter 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大刑伺候