MySQL常用脚本_用户相关

生成随机密码

select substring(md5(rand()), 1, 15);


select left(replace(uuid(),  '-',  '.'),15);

创建用户及赋权

MySQL5.6及以前

grant select on 库名.* to  `用户名`@`主机名` identified by '密码';

MySQL5.7+

create user `用户名`@`主机名` identified by '密码';
grant select on 库名.* to  `用户名`@`主机名`;

MySQL8.0

create user `用户名`@`主机名`identified  with  mysql_native_password by '密码';
GRANT select on 库名.* TO `用户名`@`主机名`;

常用语句

-- 创建一个管理员帐号
create user 'dba'@'%' IDENTIFIED BY  '********';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%'   WITH GRANT OPTION;

-- 创建一个复制帐号
create user repl@'%' identified  with  mysql_native_password by '********';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `repl`@`%`;

修改/删除 用户

alter user '用户名'@'主机名' identified with mysql_native_password by '密码';

drop user '用户名'@'主机名';

查用户

select * from user;

权限管理

show grants for '用户名'@'主机号';

grant 权限 on 数据库名.表名 to '用户名'@'主机号';

revoke 权限 on 数据库名.表名 from '用户名'@'主机号';

常用:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON dbtest_owner.* TO `dbtest`@`%`;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `repl`@`%`;

生成用户及权限

MySQL5.6及以前

mysql  -h127.0.0.1 -P3306 -udba -p{password} --skip-column-names --execute "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user" | mysqlw  -h127.0.0.1 -P3306 -udba -p{password} --skip-column-names |egrep -v "localhost|127.0.0.1|::1"

MySQL5.7+

mysqlpump   -h127.0.0.1 -P3306 -udba -p{password} --set-gtid-purged=off --exclude-databases=% --users


mysql   -h127.0.0.1 -P3306 -udba -p{password}  --silent --skip-column-names --execute "SELECT CONCAT('CREATE USER ', QUOTE(user), '@', QUOTE(host),  IF(LENGTH(plugin) > 0, CONCAT(' IDENTIFIED WITH ', plugin, IF(LENGTH(password) > 0, CONCAT(' AS ', QUOTE(password)), '')), IF(LENGTH(password) > 0, CONCAT(' IDENTIFIED BY PASSWORD ', QUOTE(password)), '')), ';') FROM mysql.user"

mysql  -h127.0.0.1 -P3306 -udba -p{password}  --skip-column-names --execute "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user" | mysql  -h127.0.0.1 -P3306 -udba -p{password} --skip-column-names #| sed -e "s/ IDENTIFIED BY PASSWORD '.*'//"