Mysql 笔记

MySQL 5.7 配置文件 my.cnf

 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
31
32
33
34
35
36
37
38
39
40
41
42
43

[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

复制表结构

1
2

create table db1.t1 like db2.t2;
create table db1.t1 select db2.t2 where 1=2;

复制表结构及其数据

1

create table db1.t1 select db2.t2 [where ...]

复制表数据

1
2

insert into db2.t2(column1, column2 ...)
    select column1, column2 ... from db1.t1 [where ...]

通过复制表文件来复制表数据

• 在db2中创建同结构表

  1	create table db2.t1 like db1.t1;

• 丢弃表空间

  1	alter table db2.t1 discard tablespace;

• 复制 t1 的表数据文件

  1 2 3 4 5 6 7

  #关闭数据库 systemctl stop mysqld cd /var/lib/mysql scp db1/t1.idb db2/t1.idb chown mysql.mysql db2/t1.idb #启动数据库 systemctl start mysqld

• 导入表空间

  1	alter table db2.t1 import tablespace;

设置一个表的空列自增

1
2
3

-- 删除可能存在的主键
alter table 表名 drop primary key;
alter table 表名 modify 列名 auto_increment primary key;

查看数据库中每个表的全部列名

1
2
3

select table_name, column_name from
    information_schema.columns
    where table_schema = '数据库名';

查看数据库中每个表的行数

1
2
3

select table_name, table_rows
    from information_schema.tables
    where table_schema = '数据库名';

查看数据库中每个表的索引

1
2
3

select table_name, column_name, index_name
    from INFORMATION_SCHEMA.STATISTICS
    where table_schema = '数据库名';

表的部分列数据到另一个表

1
2
3

update db2.t2(column1, column2 ...) = (
    select column1, column2 from db1.t1
    where db1.t1.id = db2.t2.id);

把语句执行结果写到文件

1
2

mysql -uroot -p -hsever_ip -Ddb_name
    -Ne "select ... from table_name;" > file_name

表分区

• 查看表的分区情况

  1 2 3

  select table_schema, table_name, partition_name, table_rows from information_schema.partitions where table_name = 'table_name';

• 建表时指定

  1 2 3

  create table table_name(...) partition by range columns(column_name) (partition part_name values less than(some_value));

• 修改成分区表

  1 2 3

  alter table table_name partition by range(column_name) (partition part_name values less than(som_value));

• 增加分区

  1 2	alter table table_name add partition (partition part_name values less than(som_value));

• 删除分区

  1	alter table table_name drop partition part_name;

• 合并/拆分分区

  1 2 3 4

  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));

• 重建分区，整理分区碎片

  1 2	alter table table_name rebuild partition part_name_1, part_name_2;

• 优化分区，回收空间，整理碎片

  1 2	alter table table_name optimize partition part_name_1, part_name_2;

• 分析分区，读取并保存分区的健分布

  1 2	alter table table_name analyze partition part_name_1, part_name_2;

• 修复分区

  1 2	alter table table_name repair partition part_name_1, part_name_2;

• 检查分区

  1 2	alter table table_name check partition part_name_1, part_name_2;

MySQL 5.7 从库多线程同步

1
2
3
4
5

stop slave;
set global slave_parallel_type='logical_clock';
set global slave_parallel_workers=4;
start slave;
show processlist;

MySQL 5.7 提示密码复杂度不够

1

set global validate_password_policy=0;

MySQL 5.7 从库复制失败跳过指定数量的事务

1
2
3
4
5
6
7

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 查看全部任务

1
2
3
4

-- 分号换成 \G 显示完整 sql
show processlist;
show full processlist;
SELECT command FROM information_schema.processlist;

MySQL 5.7 ssl 连接

1

--ssl-mode=REQUIRED

MariaDB 10.1 修改密码

1

UPDATE user SET password=password('newpassword') WHERE user='root';

MySQL 5.7 编码

• 查看

  1	SHOW VARIABLES LIKE 'character_set%';

• 数据库连接参数中，characterEncoding=utf8 会被自动识别为 utf8mb4，但是 autoReconnect=true 必须指定

• 更改数据库编码

  1 2	ALTER DATABASE db_name CHARSET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI;

• 更改表编码

  1 2	ALTER TABLE table_name CONVERT TO CHARSET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI;

MySQL 5.7 升级数据库管理表结构

1

mysql_upgrade -u root -p

MySQL 5.7 误删 root 后恢复 root 账户

• 停止 mysql 服务

  1	systemctl stop mysqld

• 修改 my.cnf

  1 2	# 添加如下一行 skip-grant-tables

• 启动 mysql 服务

  1	systemctl start mysqld

• 重建 root 账户，并授权

  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 31 32 33 34 35 36 37 38 39

  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 服务

  1	systemctl stop mysqld

• 修改 my.cnf

  1 2	# 删除或注释刚添加的如下一行 skip-grant-tables

• 启动 mysql 服务，root 账户正常可用

  1	systemctl start mysqld

通过EXPLAIN分析SQL的执行计划

• 使用

  1	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 次数

1
2

show global status where Variable_name in
    ('com_insert', 'com_delete', 'com_update', 'com_select');

csv 文件

• 导出

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

  # 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';

• 导入

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

  # 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 分钟

• 安装插件

  1 2 3 4

  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'; "

• 查看插件列表

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

• 修改 my.cnf，增加两行

  1 2	connection-control-failed-connections-threshold=5 connection-control-min-connection-delay=300000

• 重启 mysql