我叫张贺，贪财好色。一名合格的LINUX运维工程师，专注于LINUX的学习和研究，曾负责某中型企业的网站运维工作，爱好佛学和跑步。
个人博客：传送阵
MYSQL运行时，应该遵循以下准则：
- 不要给开发配置超级用户权限
- 不要在数据库当中存储明文密码
- 不要用不满足复杂性要求的密码
- 不允许非信任主机的扫描（需安全设备配合）
1.登录和退出 MySQL , 使⽤ mysql -u root -p 可以连接数据库, 但这只是本地连接数据库的⽅式,
在⽣产很多情况下都是连接⽹络中某⼀个主机上的数据库
-P //指定连接远程数据库端⼝[默认3306]
-h //指定连接远程数据库地址[默认localhost]
-u //指定连接远程数据库账户[默认root]
-p //指定连接远程数据库密码[默认密码为空]
-e //执⾏mysql数据库sql指令,非交互操作
上面几个选项最后一个-e我们会经常在脚本里面使用，举个例子:
mysql -u root -p'abc-123' -e "show databases;" | sed 1d | egrep -v 'information_schema|mysql|test|performance_schema' bgx bgx_edu
2、创建用户
//先用create创建用户，然后再用grant进行授权 create user zhanghe@'localhost' identified by 'cba-123'; grant all on *.* to zhanghe@'localhost'; //创建用户和授权可以放到一起 grant all on *.* to zhangjia@'localhost' identified by 'cba-123';
3、删除用户
//用`drop user`删除 drop user zhanghe@'localhost'; //delete语句删除 delete from mysql.user where user='zhangjia';
4、修改root用户密码
//用shell方式修改
mysqladmin -uroot -p'abc-123' password 'cba-123';
//用updata直接修改数据表，别忘记刷新
update mysql.user set password=password("cba-123") where user='root' and host='localhost';
//在当前用户下直接修改密码
set password=password("cba-123");
5、修改其它用户密码
//用`set password`指令修改
create user zhangsan@'localhost' identified by 'cba-123';
set password for zhangsan@'localhost'=password('NEW-passwd');
//用`updata`直接修改`mysql.user`表，别忘记刷新
update mysql.user set password=password("cba-123") where user='zhangsan' and host='localhost';
2、访问权限管理
1、权限表
mysql.user全局授权
mysql.db数据库级
mysql.tables_priv表级别
mysql.columns_priv列级
权限应用顺序：user>db>tables>columns
3、例子
//例一、下面这个是全局级别的授权 grant ALL ON *.* to zhanghe@'%' identified by 'cba-123'; select * from mysql.user\G #通过此条命令可以看到权限 //例二、全局级别，只不过比例一多一个`GRANT`权限而已。 grant ALL ON *.* to zhanghe@'%' identified by 'cba-123' with grant option; //例三：库级别 rant ALL on bgx.* to admin02@'%' identified by 'cba-123'; select * from mysql.user\G #通过这个表发现admin02没有任何权限，因为根本没有存放在此 select * from mysql.db; #在这 //例四：表级别 grant ALL on bgx.t5 to admin01@'%' identified by 'cba-123'; select * from mysql.tables_priv\G //例五，列级别 grant select(id),insert(name) on bgx.t5 to lisi@'%' identified by 'cba-123'; select * from columns_priv\G3、访问权限回收
1、查看用户的权限
//查看root用户的权限 MariaDB [mysql]> show grants; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*D1BE934B99C7CFFE2843BDE71DB34BBB0894B2DD' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ //查看其它用户的权限 MariaDB [mysql]> show grants for lisi@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for lisi@% | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'lisi'@'%' IDENTIFIED BY PASSWORD '*D1BE934B99C7CFFE2843BDE71DB34BBB0894B2DD' | | GRANT SELECT (id), INSERT (name) ON `bgx`.`t5` TO 'lisi'@'%' | +-----------------------------------------------------------------------------------------------------+
2、权限回收
//回收用户的DELETE权限 revoke DELETE on *.* from zhanghe@'%'; //回收用户所有的权限 revoke ALL on *.* from zhanghe@'%'; //回收grant权限 revoke GRANT OPTION on *.* from zhanghe@'%';
