mysql 创建新数据库并添加用户授权

创建数据库

CREATE DATABASE IF NOT EXISTS `username` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

创建用户，并：

  • 创建与用户同名的数据库并授予所有权限。
  • 给以 用户名_ 开头的数据库 (username_%) 授予所有权限。
CREATE USER `username`@`%` IDENTIFIED WITH mysql_native_password BY 'password' PASSWORD EXPIRE NEVER;
GRANT ALL PRIVILEGES ON `username`.* TO 'username'@'%';
GRANT ALL PRIVILEGES ON `username\_%`.* TO 'username'@'%';
FLUSH PRIVILEGES;

创建只读用户

CREATE USER `username`@`%` IDENTIFIED WITH mysql_native_password BY 'password' PASSWORD EXPIRE NEVER;
GRANT Select ON `username`.* TO `username`@`%`;
FLUSH PRIVILEGES;

删除用户

DROP USER `username`@`%`;
FLUSH PRIVILEGES;