1
2
3
4
5

-- 标准语法
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 示例
CREATE USER 'app_user'@'%' IDENTIFIED BY '{{REDACTED}}';

1
2

-- 语法：grant 权限 on 库.表 to 'user'@'host'
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%' WITH GRANT OPTION;

1
2
3
4
5

ALL  SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD,
SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES,
SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE,
REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE,
CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE

1
2
3
4
5
6
7
8

APPLICATION_PASSWORD_ADMIN, AUDIT_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN,
BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN,
FLUSH_OPTIMIZER_COSTS, FLUSH_STATUS, FLUSH_TABLES, FLUSH_USER_RESOURCES,
GROUP_REPLICATION_ADMIN, INNODB_REDO_LOG_ARCHIVE, INNODB_REDO_LOG_ENABLE,
PERSIST_RO_VARIABLES_ADMIN, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN,
RESOURCE_GROUP_ADMIN, RESOURCE_GROUP_USER, ROLE_ADMIN, SERVICE_CONNECTION_ADMIN,
SESSION_VARIABLES_ADMIN, SET_USER_ID, SHOW_ROUTINE, SYSTEM_USER,
SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN, XA_RECOVER_ADMIN

1
2
3
4

-- 业务账号：只允许 CRUD 业务库
GRANT SELECT, INSERT, UPDATE, DELETE
ON industry_iot_dev.*
TO 'app_user'@'10.0.0.%';

1
2
3
4
5

-- 撤销所有权限 + 授权选项
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_user'@'%';

-- 撤销特定权限
REVOKE INSERT, UPDATE ON industry_iot_dev.* FROM 'app_user'@'10.0.0.%';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 1. 创建用户
CREATE USER 'app_user'@'%' IDENTIFIED BY '{{REDACTED}}';

-- 2. 授权
GRANT SELECT, INSERT, UPDATE, DELETE
ON industry_iot_dev.*
TO 'app_user'@'%';

-- 3. 刷新（必须有）
FLUSH PRIVILEGES;

-- 4. 验证：用新账号登录
-- mysql -h db_host -u app_user -p'{{REDACTED}}' industry_iot_dev

1

GRANT ALL PRIVILEGES ON industry_iot_dev.* TO 'dev'@'%' IDENTIFIED BY '{{REDACTED}}' WITH GRANT OPTION;

1

ERROR 1130 (HY000): Host 'xxx' is not allowed to connect to this MySQL server

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 1. 用 mysql_native_password 创建远程专用账号
CREATE USER IF NOT EXISTS 'app_user'@'10.0.0.%'
IDENTIFIED WITH mysql_native_password BY '{{REDACTED}}';

-- 2. 授权（限定库，不给全局）
GRANT SELECT, INSERT, UPDATE, DELETE
ON industry_iot_dev.*
TO 'app_user'@'10.0.0.%';

-- 3. 刷新
FLUSH PRIVILEGES;

1
2
3
4
5

-- 把 root 改成允许任意 IP
CREATE USER IF NOT EXISTS 'root'@'%'
IDENTIFIED WITH mysql_native_password BY '{{REDACTED}}';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

1
2

[mysqld]
skip-name-resolve

1
2
3

[mysqld]
# 关闭反向 DNS 解析
skip-name-resolve

1
2
3
4
5
6
7
8

-- 新版本（推荐）
ALTER USER 'root'@'localhost' IDENTIFIED BY '{{REDACTED}}';

-- 远程访问账号
ALTER USER 'root'@'%' IDENTIFIED BY '{{REDACTED}}';

-- 刷新
FLUSH PRIVILEGES;

1
2
3
4
5
6
7
8
9

-- 5.6 / 5.7 老语法
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('{{REDACTED}}');

-- mysql_native_password 兼容
UPDATE user SET authentication_string = SHA1('{{REDACTED}}') WHERE user = 'root';

-- 允许任意 IP
UPDATE user SET host = '%' WHERE user = 'root';
FLUSH PRIVILEGES;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

# 1. 停 MySQL
systemctl stop mysqld

# 2. 临时加 skip-grant-tables
vim /etc/my.cnf
# [mysqld]
# skip-grant-tables

# 3. 启动
systemctl start mysqld

# 4. 空密码登录
mysql -uroot

# 5. 清空 root 密码
USE mysql;
FLUSH PRIVILEGES;
UPDATE user SET authentication_string = '' WHERE user = 'root';

# 6. 退出，注释掉 skip-grant-tables
# 7. 重启
systemctl restart mysqld

# 8. 重新登录（空密码）
mysql -uroot

-- 9. 设置新密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '{{REDACTED}}';
ALTER USER 'root'@'%' IDENTIFIED BY '{{REDACTED}}';
FLUSH PRIVILEGES;

1
2

# 知道旧密码时最快
mysqladmin -uroot -p'{{OLD_PASSWORD}}' password '{{NEW_PASSWORD}}'

1

Authentication plugin 'caching_sha2_password' cannot be loaded

1
2
3
4
5
6

-- 单个账号降级
ALTER USER 'app_user'@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY '{{REDACTED}}';
FLUSH PRIVILEGES;

-- 全局默认改成 mysql_native_password
SET GLOBAL default_authentication_plugin = 'mysql_native_password';

1
2
3

# /etc/my.cnf
[mysqld]
default_authentication_plugin = caching_sha2_password

1
2
3
4

[InnoDB] Unable to lock ./undo_001 error: 11
2025-01-03T02:52:18 [ERROR] [MY-012562] [InnoDB] We scanned the log up to ...
InnoDB: Assertion failure: log0log.cc:657:first_rec_group <= start_lsn - block_lsn
InnoDB: We intentionally generate a memory trap.

1
2
3

# 1. 把有问题的 undo 文件备份
mv undo_001 undo_001.bak
cp -a undo_001.bak undo_001

1
2
3

# /etc/my.cnf
[mysqld]
innodb_force_recovery = 6

1
2
3

CREATE DATABASE industry_iot_dev
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE risk_task (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(64)
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_general_ci
        NOT NULL DEFAULT '',
    -- ...
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;