453 lines
11 KiB
Markdown
453 lines
11 KiB
Markdown
---
|
||
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
|
||
|