MYSQL操作命令

本文作者：丁辉

MYSQL字符集

Unicode 标准

常用基础 sql 命令

以 “utf8mb4” 字符集作为示例

• 查看当前 MYSQL 支持的字符集

  show collation;

  过滤 “utf8mb4” 字符集

  SHOW COLLATION LIKE 'utf8mb4%';

• 查看 “数据库” 或 “表” 字符集

  show create database <库名>;

  show create table <表名>;

• 创建时指定字符集

  • 创建 “数据库” 指定字符集

    CREATE DATABASE <库名> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

  • 创建 “表” 指定字符集

    CREATE TABLE demo_table (
        name VARCHAR(3)
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• 修改字符集

  • 修改 “数据库” 字符集

    ALTER DATABASE <库名> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

  • 修改 “表” 字符集

    ALTER TABLE <表名> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

常见的 MySQL 字符集和排序规则

字符集	排序规则
utf8mb4_general_ci	一般排序规则，对各种语言都能工作，但不支持所有语言的精确排序。
utf8mb4_bin	二进制排序规则，区分大小写，直接按照字符编码的二进制值排序。
utf8mb4_unicode_ci	Unicode排序规则，更准确地排序各种语言的字符，但可能会比较耗费资源。

MYSQL 授权

以 “offends” 用户作为示例

• 查看 MYSQL 用户

  select user,host from mysql.user;

• 创建用户

  CREATE USER offends IDENTIFIED BY 'offends';

  创建用户时配置允许哪些主机登录

  >CREATE USER 'offends'@'host' IDENTIFIED BY 'offends';

• 用户权限

  • 查看权限

    SHOW GRANTS FOR 'offends'@'%';

  • 删除权限

    REVOKE ALL PRIVILEGES ON *.* FROM 'offends'@'%';

• 授权用户

  • 完整权限授权

    GRANT ALL PRIVILEGES ON *.* TO 'offends'@'%' IDENTIFIED BY 'offends';

    • GRANT: 这个关键字表示你要给予权限。
    • ALL PRIVILEGES: 表示赋予用户所有权限，包括SELECT、INSERT、UPDATE、DELETE等等。
    • *.*: 这部分表示所有数据库和所有表。第一个星号表示所有数据库，第二个星号表示所有表。
    • TO 'offends'@'%': 表示授予权限给一个名为 ‘offends’ 的用户，@’%’ 表示这个用户可以从任何主机连接到数据库。如果你希望用户只能从特定主机连接，可以在这里指定该主机的 IP 地址或主机名。
    • IDENTIFIED BY 'offends': 表示用户的密码是 ‘offends’。这是用于验证用户身份的密码。

  • 普通授权

    • 指定数据库授权,配置主机登录

      GRANT ALL PRIVILEGES ON <需要授权库名>.* TO 'offends'@'localhost' IDENTIFIED BY 'offends';

    • 配置普通权限

      GRANT SELECT ON offends.* TO 'offends'@'%' IDENTIFIED BY 'offends';

      通常情况下公司内部授予查询权限

  • 信任用户委派权限管理(用户可以将权限分配给其他用户)

    GRANT ALL PRIVILEGES ON *.* TO 'offends'@'%' WITH GRANT OPTION;

• 刷新（reload）权限，确保当前对用户权限的更改立即生效。

  FLUSH PRIVILEGES;

MYSQL 常用命令

• 使用 mysqladmin 修改密码

  mysqladmin -u用户名 -p旧密码 password 新密码

• 修改密码

  MYSQL 5.7

  update mysql.user set authentication_string=password('newpassword') where user='offends';

  刷新

  FLUSH PRIVILEGES;

  忘记密码则可以在 MYSQL 配置文件 my.cnf 内添加 skip-grant-tables 开启免密登录修改密码

• 修改(无法加载身份验证插件”caching_sha2_password”)问题

  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<密码>';

• 修改表中数据

  UPDATE <表名> SET <需要修改的字段>='值' WHERE ID='字段ID号';

• 查看数据库表结构

  SHOW CREATE TABLE <表名>;

• 修改数据库表结构

  alter table <表名> modify column <字段> <修改后的字段> NULL;

• 重置副本数

  RESET REPLICA;

• 禁止导出gtid信息

  mysqldump  -uroot -p密码 库 --set-gtid-purged=off > demo.sql

• 清空所有GTID信息

  reset slave all;
  reset master;

MYSQL 备份

• MYSQL 备份,禁止导出gtid信息

  mysqldump  -uroot -p<密码> <库> --set-gtid-purged=off > backup.sql

• MYSQL 全量备份

  mysqldump -uroot -p<密码> --all-databases > backup.sql

  参数	描述
  --quick	使用快速插入方式，适用于大型数据库，减少锁定时间
  --events	备份事件（Event）信息，这包括触发器等
  --all-databases	备份所有数据库
  --flush-logs	备份时刷新 MySQL 的日志，确保备份包含完整的事务日志
  --delete-master-logs	备份后删除主服务器上的二进制日志，释放磁盘空间
  --single-transaction	在整个备份过程中使用单一事务，保证数据的一致性，适用于 InnoDB 存储引擎

• MYSQL 增量备份

  • 检查是 log_bin 是否开启(增量备份需开启)

    show variables like '%log_bin%';

    配置 my.cnf 添加 log-bin=/var/lib/mysql/backup 开启 log_bin

  • 增量备份

    mysqladmin -uroot -p<密码> flush-logs

    在 /var/lib/mysql/backup 下查看增量备份日志

  • 恢复增量备份

    mysqlbinlog /var/lib/mysql/backup/binlog.000008 | mysql -uroot -p<密码>;