-- 创建用户，并允许其在任何IP登陆
create user 'username'@'%' identified by 'password';

-- 创建用户，并允许其在任何主机登陆，不设置密码
create user 'username'@'%';

-- 创建用户，并仅允许使用domian.com域名的主机登陆
create user 'username'@'domian.com' identified by 'password';

-- 创建用户，并仅允许使用192.168.1.1的主机登陆
create user 'username'@'192.168.1.1' identified by 'password';

-- 创建用户，并仅允许使用192.168.1开头的主机登陆
create user 'username'@'192.168.1.%' identified by 'password';

rename user 'old_username'@'old_ip_addr' to 'new_username'@'new_ip_addr';

set password for 'username'@'ip_addr'=Password('new_password');

//修改完密码，需将配置刷到内存
flush privileges;

rename user 'username'@'ip_addr' to 'zhiqiang'@'%'; 

drop user 'username'@'ip_addr';

-- 90天过期
ALTER USER 'username'@'ip_addr' PASSWORD EXPIRE INTERVAL 90 DAY; 
-- 密码不过期
ALTER USER 'username'@'ip_addr' PASSWORD EXPIRE NEVER; 
-- 默认过期策略
ALTER USER 'username'@'ip_addr' PASSWORD EXPIRE DEFAULT; 
-- 强制用户密码过期
ALTER USER 'username'@'ip_addr' PASSWORD EXPIRE;

show grants for 'username'@'ip_addr';

-- 授予用户所有库所有表的所有权限
grant all privileges on *.* to 'username'@'ip_addr';

-- 授予用户database1库所有表的所有权限
grant all privileges on `database1`.* to 'username'@'ip_addr';

-- 授予用户database1库table1表的所有权限
grant all privileges on `database1`.`table1` to 'username'@'ip_addr';

-- 授予用户database1库所有表的只读权限
grant select on `database1`.* to 'username'@'ip_addr';

-- 授予用户database1库table1表的插入、更新权限
grant insert,update on `database1`.`table1` to 'username'@'ip_addr';

-- 回收用户所有权限
revoke all,grant option from 'username'@'ip_addr';

-- 回收用户database1库所有表的只读权限
revoke select ON `database1`.* FROM 'username'@'%';

// 先撤销用户对数据库的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'zhiqiang'@'%';
//授予zhiqiang用户对a、b数据库的全部权限,包括DDL和DML权限
GRANT ALL PRIVILEGES ON a.* TO 'zhiqiang'@'%';
GRANT ALL PRIVILEGES ON b.* TO 'zhiqiang'@'%';
//最后授权查询mysql.user表的权限,用于确保用户可以验证自己的权限设置
GRANT SELECT ON mysql.user TO 'zhiqiang'@'%';
//显示的权限设置情况确认是否符合要求
SHOW GRANTS FOR 'zhiqiang'@'%';
//刷新权限
flush privileges;

//撤销zhiqiang原有所有数据库权限
REVOKE ALL PRIVILEGES ON *.* FROM 'zhiqiang'@'%';
//针对a、b数据库授权常见的DDL操作权限如ALTER、CREATE、DROP等
GRANT CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON a.* TO 'zhiqiang'@'%';
GRANT CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON b.* TO 'zhiqiang'@'%';  
//最后再授权查询mysql.user表权限以方便权限验证
GRANT SELECT ON mysql.user TO 'zhiqiang'@'%';
//显示的权限设置情况确认是否符合要求
SHOW GRANTS FOR 'zhiqiang'@'%';
//刷新权限
flush privileges;

//撤销zhiqiang用户所有数据库的权限
REVOKE ALL PRIVILEGES ON *.* FROM 'zhiqiang'@'%';
//授予zhiqiang对a、b数据库的SELECT/INSERT/UPDATE/DELETE权限
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON a.* TO 'zhiqiang'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON b.* TO 'zhiqiang'@'%';
//授权查询mysql.user表权限用于权限验证
GRANT SELECT ON mysql.user TO 'zhiqiang'@'%';
//显示的权限设置情况确认是否符合要求
SHOW GRANTS FOR 'zhiqiang'@'%';
//刷新权限
flush privileges;

//撤销zhiqiang所有数据库权限
REVOKE ALL PRIVILEGES ON *.* FROM 'zhiqiang'@'%';
//授予zhiqiang用户对a、b数据库的SELECT, SHOW VIEW, EVENT, TRIGGER查询权限
GRANT SELECT, LOCK TABLES, SHOW VIEW ON a.* TO 'zhiqiang'@'%';  
GRANT SELECT, LOCK TABLES, SHOW VIEW ON b.* TO 'zhiqiang'@'%';
//授权查询mysql.user表用于权限验证
GRANT SELECT ON mysql.user TO 'zhiqiang'@'%';
//显示的权限设置情况确认是否符合要求
SHOW GRANTS FOR 'zhiqiang'@'%';
//刷新权限
flush privileges;

//生成只读的sql语句
SELECT CONCAT('GRANT SELECT, LOCK TABLES, SHOW VIEW ON ', schema_name, '.* TO ''zhiqiang''@''%'';') as sqls FROM information_schema.schemata;
//按需执行返回的sqls
.....
//刷新权限
flush privileges;

-- 备份database1库所有表结构+数据
mysqdump -u username database1 > database1.sql -p

-- 备份database1库所有表结构
mysqdump -u username -d database1 > database1.sql -p

-- 进入数据库
use database1;
-- 将备份文件恢复到数据库中
mysqdump -u username -d database1 < database1.sql -p

-- 备份192.168.3.10中的database数据
mysqldump -h 192.168.3.10 -u root -p123456 database > database.sql
-- 还原数据库
mysql -h 192.168.3.11 -P 3306 -u root -p123456 database < database.sql