mysql高级操作_MySQL数据库的高级操作

1.数据备份与还原

(1)备份

mysqldump:

mysqldump -u username -p password dbname [tbname1 [tbname2....] ]> filename.sql

mysqldump -u root -p mydb2 > C:\mysql\dump\mydb2_dump.sql

(2)恢复：只能恢复数据库的表和记录，不能恢复数据库本身

mysql:方法一

mysql -u username -p password [dbname] < filename.sql

mysql：方法二，source命令

在MySQL命令提示符下：创建数据库 进入数据库 source xxx.sql 文件 将备份的sql文件在当前位置执行

source filename.sql //路径

2.user表

3.创建普通用户

(1)使用GRANT语句创建用户

GRANT privileges ON dtabase.table

TO 'username' @ 'hostname' [IDENTIFIED BY [PASSWORD] 'password'] [,'username' @ 'hostname' [IDENTIFIED BY [PASSWORD] 'password']]...............

grant select on mydb2.* to 'haha' @'localhost' identified by '123456';

//错误

(2)使用CREATE语句

CREATE USER 'username'@'hostname' [IDENTIFIED BY [PASSWORD] 'password'] [,'username' @ 'hostname' [IDENTIFIED BY [PASSWORD] 'password']]...............

create user 'haha'@'localhost' identified by '123456';

(3) 使用INSERT语句

4.删除普通用户

~ROP USER 'username'@'hostname' [,'username'@'hostname'];

drop user 'ha'@'localhost';

~ELETE FROM mysql.user WHERE Host= 'hostname' AND User = 'username';

delete from mysql.user where host = 'localhost' and user = 'ha';

flush privileges; #由于直接对user表执行操作，要重新加载用户权限

5.修改用户密码

(1)修改root用户密码

+++++++++++++++++++++++++++++++++++++++++++++++

UPDATE mysql.user set Password = PASSWORD('new_password') WHERE User='username' and Host='hostname';

FLUSH PRIVILEGES;

update mysql.user set password=PASSWORD('QWE123!@#') where user='root' and host='localhost';

flush privileges;

+++++++++++++++++++++++++++++++++++++++++++++

//不成功

(2)root用户修改普通用户密码

SET PASSWORD FOR 'username'@'hostname'=PASSWORD('new_password');

set password for 'haha'@'localhost'=PASSWORD('123');

+++++++++++++++++++++++++++++++++++++++

//不成功

(3)普通用户修改密码

SET PASSWORD=PASSWORD('new_password');

6.授予权限：使不同用户有不同权限

(1)GRANT privileges [ (columns) ] [,privileges[(columns)]] ON database.table TO 'username'@'hostname' [IDENTIFIED BY [PASSWORD] 'password' ] [ 'username'@'hostname' [IDENTIFIED BY [PASSWORD] 'password' ]]...........

[WITH with_option [with_option]...]

with_option参数如下：

(1)GRANT OPTION：将自己的权限授予其他用户

(2)MAX_QUERIES_PER_HOUR count：设置每小时最大查询次数count。

(3)MAX_UPDATES_PER_HOUR count：设置每小时最多可执行多少次更新

(4)MAX_CONNECTIONS_PER_HOUR count：设置每小时最大连接数量

(5)MAX_USER_CONNECTIONS：设置每个用户最多可以同时建立连接数量

GRANT INSERT,SELECT ON mydb2.star TO 'haha'@'localhost' IDENTIFIED BY '123456'

WITH GRANT OPTION;

++++++++++++++++++++++++++++++++++++++++++++

//不成功

7.查看权限

(1) SHOW GRANTS FOR 'username'@'hostname';

show grants for 'haha'@'localhost';

8.收回权限

REVOKE privileges [ (columns) ] [,privileges[(columns)]] ON database.table FROM 'username'@'hostname' [,'username'@'hostname' ]....