1 创建超级账号:
GRANT ALL ON *.* TO 'dba'@'%' IDENTIFIED BY 'dba' WITH GRANT OPTION;
FLUSH PRIVILEGES;
2 创建主从同步账号(前两个语句创建的账号即可实现读binlog,如果不行的话换后两个试试):
GRANT REPLICATION CLIENT ,REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';
FLUSH PRIVILEGES;
GRANT SELECT, RELOAD,SHOW DATABASES, REPLICATION CLIENT ,REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';
FLUSH PRIVILEGES;
3 创建备份账号:
GRANT BACKUP_ADMIN,PROCESS,SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backupuser'@'localhost' IDENTIFIED BY 'backupuser';
FLUSH PRIVILEGES;
4 创建只读账号
-- 创建用户 用户名:readonly 密码:123456 可自行更改 --
CREATE USER 'readonly'@'%' IDENTIFIED BY '123456';
-- 赋予用户只读权限 库名:mydb 用户:readonly --
GRANT SELECT ON mydb.* TO 'readonly'@'%';
-- 刷新权限 --
FLUSH PRIVILEGES;