1、创建账号
1）只读账号
GRANT USAGE ON *.* TO 'testgao_r'@'192.168.1.1' IDENTIFIED BY 'password' WITH MAX_QUERIES_PER_HOUR 14400000 MAX_UPDATES_PER_HOUR 7200000 MAX_CONNECTIONS_PER_HOUR 14400000 MAX_USER_CONNECTIONS 2000;
GRANT SELECT, EXECUTE, SHOW VIEW ON `testdb`.* TO 'testgao_rw'@'192.168.1.1';
2）读写账号
GRANT USAGE ON *.* TO 'testgao_rw'@'192.168.1.1' IDENTIFIED BY 'password' WITH MAX_QUERIES_PER_HOUR 14400000 MAX_UPDATES_PER_HOUR 7200000 MAX_CONNECTIONS_PER_HOUR 14400000 MAX_USER_CONNECTIONS 2000;
GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW ON `testdb`.* TO 'testgao_rw'@'192.168.1.1';
3）管理账号
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'admin'@'10.%.%.%' IDENTIFIED BY 'pass123' WITH GRANT OPTION;
2、删除账号
DROP USER testgao_rw@'192.168.1.1';
3、查询账号权限
select * from mysql.user where user='letv_ugc_r' and host='10.127.92.157';
4、移除权限
REVOKE DELETE ON testdb FROM 'testgao_rw'@'192.168.1.1';
5、修改密码
SET PASSWORD FOR 'testgao'@'192.168.1.1' = PASSWORD('newpassword');
flush privileges;