mysql权限管理

43212gg

Facebook使用了GTID
一个实例多个库
show privileges;
给开发一个账号
CRUD
grant select,insert,update,delete on dnname.* to 'username'@'192.168.0.%';
help create user;
用户名长度        8-16字符
密码        8-20字符
create user 'app_dev'@'%' identified by 'a1';
create user 'app_dev'@'192.168.0.%' identified by 'a2';
create user 'app_dev'@'192.168.0.101' identified by 'a3';
精确匹配

create user 'test'@'192.168.0.%' identified by '123456';

查看有多少账号
mysql> select user,host from mysql.user;
+------------+--------------+
| user       | host         |
+------------+--------------+
| ‘test’     | 192.168..0.% |
| test       | 192.168.0.%  |
| ‘test’     | 192.168.0.%  |
| mysql.sys  | localhost    |
| root       | localhost    |
+------------+--------------+
5 rows in set (0.00 sec)

select user(),current_user();

desc information_schema.processlist;
show processlist;

select host from information_schema.processlist;
查看那台机器及连接数
select host, current_connections from sys.host_summary;

select substring_index(host,":",1),count(*) as conn from information_schema.processlist group by substring_index(host,":",1);

假设：        DB里有20个前端连接
拒接某个连接
app_dev@'192.168.0.%';
192.168.0.101 app_dev
精确匹配
create user 'app_dev'@'192.168.0.101' identified by 'aa';

select user,host,authentication_string from mysql.user where user='test';

删除用户
drop user 'app_dev'@'192.168.0.101' and host='192.168.0.%';
flush privileges;

delete from mysql.user where user='test' and host='192.168.0.%';
flush privileges;

flush full privileges;

mysql用户名root、密码忘记解决方法
修改配置文件
my.cnf
[mysqld]
skip-grant-tables

mysql --defaults-file=/path/my.cnf --skip-grant-tables  &

service mysqld restart

set password


或者
update mysql.user set authentication_string=password('wubxwubx') where user='root' and host='localhost';

#小心执行
flush tables;
flush logs

#kill -HUP 8462 测试环境

cat /etc/init.d/mysql | grep kill


mysql schema怎么回事？
在db里面代表一个库