---
title: "Mysql 笔记"
date: 2019-10-30T11:51:23+08:00
lastmod: 2019-10-30T11:51:23+08:00
tags: ["mysql"]
categories: ["database"]
---

## MySQL 5.7 配置文件 my.cnf
```
[mysqld]
datadir                        = /db/mysql
socket                         = /var/lib/mysql/mysql.sock
symbolic-links                 = 0
log-timestamps                 = SYSTEM
slow-query-log                 = 1
slow-query-log-file            = /var/log/mysqld/slow.log
long-query-time                = 8
#log-queries-not-using-indexes  = 1
log-error                      = /var/log/mysqld/error.log
pid-file                       = /var/run/mysqld/mysqld.pid
max-connections                = 1000
max-connect-errors             = 1000
max-user-connections           = 600
interactive-timeout            = 3600
wait-timeout                   = 3600
skip-name-resolve              = 1
lower-case-table-names         = 1
default-time-zone              = '+08:00'
character-set-server           = utf8mb4
sql-mode                       = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
bind-address                   = 0.0.0.0
table-open-cache               = 2048
default-storage-engine         = innodb
innodb-autoinc-lock-mode       = 2
innodb-flush-log-at-trx-commit = 0
# 建议物理内存一半
innodb-buffer-pool-size        = 8G
innodb-buffer-pool-instances   = 8
max-allowed-packet             = 512M
query-cache-size               = 0
query-cache-type               = 0
# 建议点分 ip 的最后一个数字
server-id                      = 123
# bin log
#binlog-format                  = ROW
#log-bin                        = /var/lib/mysql/mysql-bin
#expire-logs-days               = 3
# relay log
#read-only                      = 1
#replicate-wild-do-table        = db1.%
#relay-log                      = /var/lib/mysql/mysql-relay-bin
#slave-parallel-type            = logical-clock
```

## 复制表结构
```sql
create table db1.t1 like db2.t2;
create table db1.t1 select db2.t2 where 1=2;
```

## 复制表结构及其数据
```sql
create table db1.t1 select db2.t2 [where ...]
```

## 复制表数据
```
insert into db2.t2(column1, column2 ...)
    select column1, column2 ... from db1.t1 [where ...]
```

## 通过复制表文件来复制表数据
- 在db2中创建同结构表
    ```sql
    create table db2.t1 like db1.t1;
    ```

- 丢弃表空间
    ```sql
    alter table db2.t1 discard tablespace;
    ```

- 复制 t1 的表数据文件
    ```bash
    #关闭数据库
    systemctl stop mysqld
    cd /var/lib/mysql
    scp db1/t1.idb db2/t1.idb
    chown mysql.mysql db2/t1.idb
    #启动数据库
    systemctl start mysqld
    ```

- 导入表空间
    ```sql
    alter table db2.t1 import tablespace;
    ```

## 设置一个表的空列自增
```sql
-- 删除可能存在的主键
alter table 表名 drop primary key;
alter table 表名 modify 列名 auto_increment primary key;
```

## 查看数据库中每个表的全部列名
```sql
select table_name, column_name from
    information_schema.columns
    where table_schema = '数据库名';
```

## 查看数据库中每个表的行数
```sql
select table_name, table_rows
    from information_schema.tables
    where table_schema = '数据库名';
```

## 查看数据库中每个表的索引
```sql
select table_name, column_name, index_name
    from INFORMATION_SCHEMA.STATISTICS
    where table_schema = '数据库名';
```

## 表的部分列数据到另一个表
```sql
update db2.t2(column1, column2 ...) = (
    select column1, column2 from db1.t1
    where db1.t1.id = db2.t2.id);
```

## 把语句执行结果写到文件
```bash
mysql -uroot -p -hsever_ip -Ddb_name
    -Ne "select ... from table_name;" > file_name
```

## 表分区
- 查看表的分区情况
    ```sql
    select table_schema, table_name, partition_name, table_rows
        from information_schema.partitions
        where table_name = 'table_name';
    ```

- 建表时指定
    ```sql
    create table table_name(...)
        partition by range columns(column_name)
        (partition part_name values less than(some_value));
    ```

- 修改成分区表
    ```sql
    alter table table_name
        partition by range(column_name)
        (partition part_name values less than(som_value));
    ```

- 增加分区
    ```sql
    alter table table_name add partition
        (partition part_name values less than(som_value));
    ```

- 删除分区
    ```sql
    alter table table_name drop partition part_name;
    ```

- 合并/拆分分区
    ```sql
    alter table table_name
        reorganize part_old_1, part_old_2, part_old_3 into
        (partition part_new_1 values less than(value_1),
        partition part_new_2 values less than(value_2));
    ```

- 重建分区，整理分区碎片
    ```sql
    alter table table_name
        rebuild partition part_name_1, part_name_2;
    ```

- 优化分区，回收空间，整理碎片
    ```sql
    alter table table_name
        optimize partition part_name_1, part_name_2;
    ```

- 分析分区，读取并保存分区的健分布
    ```sql
    alter table table_name
         analyze partition part_name_1, part_name_2;
    ```

- 修复分区
    ```sql
    alter table table_name
        repair partition part_name_1, part_name_2;
    ```

- 检查分区
    ```sql
    alter table table_name
        check partition part_name_1, part_name_2;
    ```

## MySQL 5.7 从库多线程同步
```sql
stop slave;
set global slave_parallel_type='logical_clock';
set global slave_parallel_workers=4;
start slave;
show processlist;
```

## MySQL 5.7 提示密码复杂度不够
```sql
set global validate_password_policy=0;
```

## MySQL 5.7 从库复制失败跳过指定数量的事务
```sql
stop slave;
-- 跳过一个事务
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;
-- 修改 my.cnf
slave-skip-errors=1062,1053,1146,1032 #跳过指定error no类型的错误
slave-skip-errors=all #跳过所有错误
```

## MySQL 5.7 查看全部任务
```sql
-- 分号换成 \G 显示完整 sql
show processlist;
show full processlist;
SELECT command FROM information_schema.processlist;
```

## MySQL 5.7 ssl 连接
```
--ssl-mode=REQUIRED
```

## MariaDB 10.1 修改密码
```sql
UPDATE user SET password=password('newpassword') WHERE user='root';
```

## MySQL 5.7 编码
- 查看
    ```sql
    SHOW VARIABLES LIKE 'character_set%';
    ```

- 数据库连接参数中，characterEncoding=utf8 会被自动识别为 utf8mb4，但是 autoReconnect=true 必须指定
- 更改数据库编码
    ```sql
    ALTER DATABASE db_name
        CHARSET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI;
    ```

- 更改表编码
    ```sql
    ALTER TABLE table_name
        CONVERT TO CHARSET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI;
    ```

## MySQL 5.7 升级数据库管理表结构
```bash
mysql_upgrade -u root -p
```

## MySQL 5.7 误删 root 后恢复 root 账户
- 停止 mysql 服务
    ```bash
    systemctl stop mysqld
    ```

- 修改 my.cnf
    ```
    # 添加如下一行
    skip-grant-tables
    ```

- 启动 mysql 服务
    ```bash
    systemctl start mysqld
    ```

- 重建 root 账户，并授权
    ```sql
    insert into user
        set user='root',
        ssl_cipher='',
        x509_issuer='',
        x509_subject='';
    update user
        set Host='localhost',
        select_priv='y',
        insert_priv='y',
        update_priv='y',
        Alter_priv='y',
        delete_priv='y',
        create_priv='y',
        drop_priv='y',
        reload_priv='y',
        shutdown_priv='y',
        Process_priv='y',
        file_priv='y',
        grant_priv='y',
        References_priv='y',
        index_priv='y',
        create_user_priv='y',
        show_db_priv='y',
        super_priv='y',
        create_tmp_table_priv='y',
        Lock_tables_priv='y',
        execute_priv='y',
        repl_slave_priv='y',
        repl_client_priv='y',
        create_view_priv='y',
        show_view_priv='y',
        create_routine_priv='y',
        alter_routine_priv='y',
        create_user_priv='y',
        event_priv='y',
        trigger_priv='y',
        create_tablespace_priv='y'
        where user='root';
    flush privileges;
    ```

- 停止 mysql 服务
    ```bash
    systemctl stop mysqld
    ```

- 修改 my.cnf
    ```
    # 删除或注释刚添加的如下一行
    skip-grant-tables
    ```

- 启动 mysql 服务，root 账户正常可用
    ```bash
    systemctl start mysqld
    ```

## 通过EXPLAIN分析SQL的执行计划
- 使用
    ```sql
    explain sql
    ```

- select_type 查询类型
    - SIMPLE 简单表，没有表连接或子查询
    - PRIMARY 最外层的查询
    - UNION union语句的后置查询
    - SUBQUERY 第一个子查询
- table 表/别名
- type 访问类型
    - ALL 全表扫描
    - index 全索引扫描
    - range 索引范围扫描
    - ref 非唯一索引扫描
    - eq_ref 唯一索引扫描
    - const,system 单表最多一个匹配行
    - NULL 不需要扫描表或索引
- possible_keys 查询可能使用的索引
- key 实际使用的索引
- key_len 使用的索引字段的长度
- ref 其他匹配字段
- rows 扫描行的数量
- filtered 满足查询条件的记录占存储引擎返回记录的比例
- Extra 执行情况说明
    - Using Index 全部使用索引，没有回表查询
    - Using Where 有回表查询
    - Using Index Condition ICP优化，直接在存储引擎完成条件过滤
    - Using Flesort 依靠索引顺序达不到排序效果，需额外排序

## 统计 insert、delete、update 和 select 次数
```sql
show global status where Variable_name in
    ('com_insert', 'com_delete', 'com_update', 'com_select');
```

## csv 文件
- 导出
    ```
    # Shell 终端
    # mysql -e "select * from t1
        into outfile '/var/lib/mysql-files/t1.csv'
        fields terminated by ','
        enclosed by '\"'
        escaped by '\\\'
        lines terminated by '\n'"
    # MySQL 终端
    # MySQL> select * from t1
                 into outfile '/var/lib/mysql-files/t1.csv'
                 fields terminated by ','
                 enclosed by '\"'
                 escaped by '\\'
                 lines terminated by '\n';
    ```

- 导入
    ```
    # Shell 终端
    # mysql -e "load data infile '/var/lib/mysql-files/t1.csv'
        into table t1
        fields terminated by ','
        enclosed by '\"'
        escaped by '\\\'
        lines terminated by '\n'"
    # MySQL 终端
    # MySQL> load data infile '/var/lib/mysql-files/t1.csv'
                 into table t1
                 fields terminated by ','
                 enclosed by '\"'
                 escaped by '\\'
                 lines terminated by '\n';
    ```

## mysql8 配置登录失败 5 次锁定 5 分钟
- 安装插件
    ```
    docker exec -ti mysql mysql -e "
        install plugin CONNECTION_CONTROL soname 'connection_control.so';
        install plugin CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS soname 'connection_control.so';
    "
    ```

- 查看插件列表
    ```
    docker exec -ti mysql mysql -e "show plugins"
    # 此时会看到最后两行时新激活的插件
    ```

- 修改 my.cnf，增加两行
    ```
    connection-control-failed-connections-threshold=5
    connection-control-min-connection-delay=300000
    ```

- 重启 mysql