271 lines
6.3 KiB
Markdown
271 lines
6.3 KiB
Markdown
> 本文作者：丁辉
|
||
|
||
# MYSQL字符集
|
||
|
||
[Unicode 标准](https://home.unicode.org/)
|
||
|
||
## 常用基础 sql 命令
|
||
|
||
> 以 "utf8mb4" 字符集作为示例
|
||
|
||
- 查看当前 MYSQL 支持的字符集
|
||
|
||
```sql
|
||
show collation;
|
||
```
|
||
|
||
> 过滤 "utf8mb4" 字符集
|
||
>
|
||
> ```sql
|
||
> SHOW COLLATION LIKE 'utf8mb4%';
|
||
> ```
|
||
|
||
- 查看 "数据库" 或 "表" 字符集
|
||
|
||
```sql
|
||
show create database <库名>;
|
||
```
|
||
|
||
```sql
|
||
show create table <表名>;
|
||
```
|
||
|
||
- 创建时指定字符集
|
||
|
||
- 创建 "数据库" 指定字符集
|
||
|
||
```sql
|
||
CREATE DATABASE <库名> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
```
|
||
|
||
- 创建 "表" 指定字符集
|
||
|
||
```sql
|
||
CREATE TABLE demo_table (
|
||
name VARCHAR(3)
|
||
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
```
|
||
|
||
- 修改字符集
|
||
|
||
- 修改 "数据库" 字符集
|
||
|
||
```sql
|
||
ALTER DATABASE <库名> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
```
|
||
|
||
- 修改 "表" 字符集
|
||
|
||
```sql
|
||
ALTER TABLE <表名> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
```
|
||
|
||
|
||
|
||
## 常见的 MySQL 字符集和排序规则
|
||
|
||
| 字符集 | 排序规则 |
|
||
| :----------------: | :----------------------------------------------------------: |
|
||
| utf8mb4_general_ci | 一般排序规则，对各种语言都能工作，但不支持所有语言的精确排序。 |
|
||
| utf8mb4_bin | 二进制排序规则，区分大小写，直接按照字符编码的二进制值排序。 |
|
||
| utf8mb4_unicode_ci | Unicode排序规则，更准确地排序各种语言的字符，但可能会比较耗费资源。 |
|
||
|
||
|
||
|
||
# MYSQL 授权
|
||
|
||
> 以 "offends" 用户作为示例
|
||
|
||
- 查看 MYSQL 用户
|
||
|
||
```sql
|
||
select user,host from mysql.user;
|
||
```
|
||
|
||
- 创建用户
|
||
|
||
```sql
|
||
CREATE USER offends IDENTIFIED BY 'offends';
|
||
```
|
||
>创建用户时配置允许哪些主机登录
|
||
>
|
||
>```sql
|
||
>CREATE USER 'offends'@'host' IDENTIFIED BY 'offends';
|
||
>```
|
||
|
||
- 用户权限
|
||
|
||
- 查看权限
|
||
|
||
```sql
|
||
SHOW GRANTS FOR 'offends'@'%';
|
||
```
|
||
|
||
- 删除权限
|
||
|
||
```sql
|
||
REVOKE ALL PRIVILEGES ON *.* FROM 'offends'@'%';
|
||
```
|
||
|
||
- 授权用户
|
||
|
||
- 完整权限授权
|
||
|
||
```sql
|
||
GRANT ALL PRIVILEGES ON *.* TO 'offends'@'%' IDENTIFIED BY 'offends';
|
||
```
|
||
|
||
> - `GRANT`: 这个关键字表示你要给予权限。
|
||
> - `ALL PRIVILEGES`: 表示赋予用户所有权限，包括SELECT、INSERT、UPDATE、DELETE等等。
|
||
> - `*.*`: 这部分表示所有数据库和所有表。第一个星号表示所有数据库，第二个星号表示所有表。
|
||
> - `TO 'offends'@'%'`: 表示授予权限给一个名为 'offends' 的用户，@'%' 表示这个用户可以从任何主机连接到数据库。如果你希望用户只能从特定主机连接，可以在这里指定该主机的 IP 地址或主机名。
|
||
> - `IDENTIFIED BY 'offends'`: 表示用户的密码是 'offends'。这是用于验证用户身份的密码。
|
||
|
||
- 普通授权
|
||
|
||
- 指定数据库授权,配置主机登录
|
||
|
||
```sql
|
||
GRANT ALL PRIVILEGES ON <需要授权库名>.* TO 'offends'@'localhost' IDENTIFIED BY 'offends';
|
||
```
|
||
|
||
- 配置普通权限
|
||
|
||
```sql
|
||
GRANT SELECT ON offends.* TO 'offends'@'%' IDENTIFIED BY 'offends';
|
||
```
|
||
|
||
> 通常情况下公司内部授予查询权限
|
||
|
||
- 信任用户委派权限管理(用户可以将权限分配给其他用户)
|
||
|
||
```sql
|
||
GRANT ALL PRIVILEGES ON *.* TO 'offends'@'%' WITH GRANT OPTION;
|
||
```
|
||
|
||
- 刷新（reload）权限，确保当前对用户权限的更改立即生效。
|
||
|
||
```sql
|
||
FLUSH PRIVILEGES;
|
||
```
|
||
|
||
|
||
|
||
# MYSQL 常用命令
|
||
|
||
- 使用 mysqladmin 修改密码
|
||
|
||
```bash
|
||
mysqladmin -u用户名 -p旧密码 password 新密码
|
||
```
|
||
|
||
- 修改密码
|
||
|
||
> MYSQL 5.7
|
||
|
||
```sql
|
||
update mysql.user set authentication_string=password('newpassword') where user='offends';
|
||
```
|
||
|
||
> 刷新
|
||
>
|
||
> ```sql
|
||
> FLUSH PRIVILEGES;
|
||
> ```
|
||
>
|
||
>忘记密码则可以在 MYSQL 配置文件 my.cnf 内添加 `skip-grant-tables` 开启免密登录修改密码
|
||
|
||
- 修改(无法加载身份验证插件"caching_sha2_password")问题
|
||
|
||
```sql
|
||
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<密码>';
|
||
```
|
||
|
||
- 修改表中数据
|
||
|
||
```sql
|
||
UPDATE <表名> SET <需要修改的字段>='值' WHERE ID='字段ID号';
|
||
```
|
||
|
||
- 查看数据库表结构
|
||
|
||
```sql
|
||
SHOW CREATE TABLE <表名>;
|
||
```
|
||
|
||
- 修改数据库表结构
|
||
|
||
```sql
|
||
alter table <表名> modify column <字段> <修改后的字段> NULL;
|
||
```
|
||
|
||
- 重置副本数
|
||
|
||
```bash
|
||
RESET REPLICA;
|
||
```
|
||
|
||
- 禁止导出gtid信息
|
||
|
||
```bash
|
||
mysqldump -uroot -p密码 库 --set-gtid-purged=off > demo.sql
|
||
```
|
||
|
||
- 清空所有GTID信息
|
||
|
||
```bash
|
||
reset slave all;
|
||
reset master;
|
||
```
|
||
|
||
|
||
|
||
# MYSQL 备份
|
||
|
||
- MYSQL 备份,禁止导出gtid信息
|
||
|
||
```sql
|
||
mysqldump -uroot -p<密码> <库> --set-gtid-purged=off > backup.sql
|
||
```
|
||
|
||
- MYSQL 全量备份
|
||
|
||
```sql
|
||
mysqldump -uroot -p<密码> --all-databases > backup.sql
|
||
```
|
||
|
||
> | 参数 | 描述 |
|
||
> | ---------------------- | ------------------------------------------------------------ |
|
||
> | `--quick` | 使用快速插入方式，适用于大型数据库，减少锁定时间 |
|
||
> | `--events` | 备份事件（Event）信息，这包括触发器等 |
|
||
> | `--all-databases` | 备份所有数据库 |
|
||
> | `--flush-logs` | 备份时刷新 MySQL 的日志，确保备份包含完整的事务日志 |
|
||
> | `--delete-master-logs` | 备份后删除主服务器上的二进制日志，释放磁盘空间 |
|
||
> | `--single-transaction` | 在整个备份过程中使用单一事务，保证数据的一致性，适用于 InnoDB 存储引擎 |
|
||
|
||
- MYSQL 增量备份
|
||
|
||
- 检查是 log_bin 是否开启(增量备份需开启)
|
||
|
||
```sql
|
||
show variables like '%log_bin%';
|
||
```
|
||
|
||
> 配置 my.cnf 添加 `log-bin=/var/lib/mysql/backup` 开启 log_bin
|
||
|
||
- 增量备份
|
||
|
||
```bash
|
||
mysqladmin -uroot -p<密码> flush-logs
|
||
```
|
||
|
||
> 在 /var/lib/mysql/backup 下查看增量备份日志
|
||
|
||
- 恢复增量备份
|
||
|
||
```bash
|
||
mysqlbinlog /var/lib/mysql/backup/binlog.000008 | mysql -uroot -p<密码>;
|
||
```
|
||
|
||
|