常用命令

设定 root 用户密码

-- 查看当前所有用户
SELECT Host, User, plugin, authentication_string, password_expired, account_locked, Password_require_current FROM mysql.user \G;
-- 将 `root` 用户设定为可从任意客户端登录
UPDATE mysql.user SET Host = '%' WHERE User = 'root';
-- 更改 `root` 用户密码
ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY 'password';

认证授权

-- 创建用户
CREATE USER 'user_name'@'host' IDENTIFIED WITH caching_sha2_password BY 'password';
CREATE USER 'user_name'@'host' IDENTIFIED WITH mysql_native_password BY 'password';
-- 授权
GRANT ALL ON *.* TO 'user_name'@'host' WITH GRANT OPTION;
GRANT ALL ON schema_name.table_name TO 'user_name'@'host';
GRANT ALL PRIVILEGES ON schema_name.table_name TO 'user_name'@'host';
-- 更改密码
ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY 'new_password';
-- 刷新权限
FLUSH PRIVILEGES;

database

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
  | ENCRYPTION [=] {'Y' | 'N'}
}
-- Example
CREATE DATABASE `foo_bar` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
CREATE DATABASE `foo_bar` CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;