您好,登录后才能下订单哦!
下文主要给大家带来操作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']]...............
//错误
(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' ]....
对于以上关于操作MySQL数据库的高级方法,大家是不是觉得非常有帮助。如果需要了解更多内容,请继续关注我们的行业资讯,相信你会喜欢上这些内容的。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。