基本管理

查看数据库中行数大于 0 的表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 查找表行大于0的
SELECT CONCAT(table_schema, '.', table_name) AS TABLE_NAME
,engine AS TABLE_ENGINE
,table_type AS TABLE_TYPE
,table_rows AS TABLE_ROWS
,CONCAT(ROUND(data_length / ( 1024 * 1024), 2), 'M') AS TB_DATA_SIZE
,CONCAT(ROUND(index_length / ( 1024 * 1024), 2), 'M') AS TB_IDX_SIZE
,CONCAT(ROUND((data_length + index_length )
/ ( 1024 * 1024 ), 2), 'M') AS TOTAL_SIZE
,CASE WHEN data_length =0 THEN 0
ELSE ROUND(index_length / data_length, 2) END AS TB_INDX_RATE
,CONCAT(ROUND( data_free / 1024 / 1024,2), 'MB') AS TB_DATA_FREE
,CASE WHEN (data_length + index_length) = 0 THEN 0
ELSE ROUND(data_free/(data_length + index_length),2)
END AS TB_FRAG_RATE
FROM information_schema.TABLES
WHERE TABLE_ROWS > 0
AND table_name in ('entitylog','iterfacelog','stockshiftflow','stockoperlog')
ORDER BY data_length
limit 0, 20

命令执行

  • -e: 执行 SQL 语句
  • -D: 连接的数据库
  • -s, --silent: 使用 tab 作为分隔符,row ⇒ line,用于制作报表
  • -C, --compress: 使用压缩在 client/server 之间
  • 输出: 配合 Excel 制表
    • -B: 使用 Tab 替换分隔符
    • -N: 不输出列信息
    • -E: 垂直输出,展示格式
    • -H: 以 HTML 输出
    • -X: 以 XML 输出
1
2
3
4
# 连接 DB 执行 SQL,让结果按照特定格式显示,方便 awk, sed 处理
mysql -uroot -p$MYSQL_ROOT_PASSWORD -h<ip> -D school -e "SELECT * FROM student;"
mysql -udbuser -p123456 -h<ip> -D school -N -B -e "SELECT * FROM student;"
mysql -udbuser -p123456 -h<ip> -D school -N -H -B -e "SELECT * FROM student;" > result.html

服务信息查看

查看最近的 InnoDB 信息

1
SHOW ENGINE INNODB STATUS \\G

查看存储过程

1
SHOW PROCEDURE STATUE LIKE '.*' \\G

查看函数

1
SHOW FUNCTION STATUE LIKE '.*' \\G

查找出所有 function,routines

1
SELECT SPECIFIC_NAME FROM information_schema.Routines \\G

查看服务器状态

1
show status like '%lock%'\\G

查询是否锁表

1
2
# 记录当前锁表状态 
show OPEN TABLES where In_use > 0

查询 MySQL 进程

1
2
3
# Top 100
show processlist
show full processlist;

查看正在锁的事务

1
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

查看等待锁的事务

1
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

慢查询

1
2
3
4
5
6
-- 查看慢查询时间
show variables like "long_query_time";
-- 查看慢查询配置情况
show status like "%slow_queries%";
-- 查看慢查询日志路径
show variables like "%slow%";

查看当前有那些表是打开的

1
2
show open tables;
show open tables from database;

查看服务器超时参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
show variables like ‘%timeout%’;
-- 隔离级别SHOW VARIBALES LIKE 'ios%'
-- innodb
SHOW VARIABLES LIKE 'innodb_file_per_table'
-- 日志缓冲区
SHOW VARIABLES lIKE 'innodb_log_buffer_size'
-- 表的索引信息查看
SHOW INDEX FROM <mytab>
-- 表信息查看
SHOW CREATE TABLE <mytab> \\G
-- warning 出现后的查看
show warnings;
-- 时区设置
set time_zone='+10:00';
-- 隔离级别设置
SET SESSSION TRANSACTION LEVEL

用户与权限

对于连接程序的账户,不给予 ALERT 权限,但是给定 DML 语句的 EXEC,让用户通过 EXEC 执行函数或是存储过程来变更表结构,防止权限造成的表结构更改混乱问题

权限

创建检查账号:

1
2
3
grant select,process,super,replication slave on . 
to 'mysql_check'@'x.x.x.x' identified by 'mysql_check';
flush privileges;

如何在给定场景下为某用户授权?

对于对接的数据库授权,主要为 DML 权限

如何为用户授权

  • 遵循最小权限原则
  • 使用 Grant 命令对用户授权(非改 db)

用户管理流程规范 数据库用户管理流程规范 (1) 最小权限原则 (2) 密码强度策略 (3) 密码过期原则。5.7 中引入 (4) 限制历史密码重用原则

定义账号 如何定义MySQL数据库账号? (1) 用户名@可访问控制列表 1.%:代表可以从所有外部主机访问 2.192.168.1.%:表示可以从192.168.1网段访问 3.localhost: DB服务器本地访问 (2) 使用CREATE USER命令建立用户

8.0 信息更多

如何从一个实例迁移数据库账号到另一个实例? 根据 mysql 的数据库版本是否一致判断

  • 一致,备份 mysql 数据库,目的实例进行恢复 - 不一致,到处授权 sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
pt-show-grants u=root,p=123456,h=localhost
show priviledges;
CREATE USER userinfo IDENTIFIED BY '3UuQuskw2k%k';
RENAME USER userinfo TO janhen;
DROP USER janhen;
SHOW GRANTS FOR janhen;

-- 授权给新增的用户
GRANT SELECT,UPDATE,INSERT,DELETE ON openapi.* TO openapi;

-- 取消授权
REVOKE DELETE ON openapi.* FROM openapi;、

-- 更改密码
SET PASSWORD FOR openapi = PASSWORD('!BrJ%4MROWvN');

SET PASSWORD FOR 'dbadmin'@'localhost' = bigshark;

-- 设置登陆用户的密码
SET PASSWORD = PASSWORD('xxxxx');

权限查看

1
show grants for 'test'@'%'

权限收回

1
revoke insert on test.* from 'test'@'%'

常见权限

(1) 管理权限

不提供 MySQL 的 root 权限,给管理员权限,防止误操作

1
2
3
4
5
GRANT ALL PRIVILEGES
ON *.*
TO 'janhenadmin'@'sjfksjfaksdjfad'
WITH GRANT OPTION
grant super,process,file on *.* to 'janhen'@'%'

(2) 数据库的读写权限

主要用于系统对接使用, SELECT, INSERT, UPDATE, DELETE, EXECUTE

1
2
3
4
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE 
ON openapi.*
TO 'openapi'@'%'
IDENTIFIED BY '123'

(3) 监控权限

用于命令行监控,或是 Prometheus 进行监控信息的获取 基本的主从命令 对于统计数据库的权限

基本的要求: SHOW MASTER LOGS SHOW ENGINE INNODB STATUS SHOW VIEW, PROCESS, REPLICATION CLIENT, SELECT and SHOW DATABASES

对 mysql 表的查看

(4) 备份权限

通过 mysqldump 或是 xtracback 执行备份,需要对 VIEW、Function、event 等进行备份 SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, PROCESS

1
2
3
GRANT select,reload,lock tables,replication client,show view,event,process 
ON *.*
TO 'backup'@'%';

(5) 登陆权限

1
grant usage on *.* to 'test'@'%'

MySQL 监控

概述

监控什么

1、可用性: 端口检测,执行 select 1 或 show status 作为测试 SQL

2、性能监控: 并发线程监控

3、对主从复制进行监控

4、服务器资源的监控

可用性监控

本地 | 网络, 自带命令 ping 进行检测

1
mysqladmin -umonitor -p -h pingtelnet ip db_port

2、Telent 连接

3、程序通过网络建立数据库

@Q: 确定 DB 是否读写 read_only 参数,主从中注意,定期检查

简单查询 SELECT @@version

连接数量监控:

1
2
3
4
5
6
-- 运行最大
show variables like 'max_connections';
-- 当前连接
show global status like 'Threads_connected';
-- 比例监控
Threads_connected/max_connections>0.8;

性能监控

@Q: QPS 和 TPS TPS 每秒的事务数量

1
2
3
CRU QPS=(Queries2-Queries1)/(Uptime_since_flush_status2- Uptime_since_flush_status1)

TPS=((Com_insert2+Com_update2+Com_delete2)- (Com_insert1+Com_update1+Com_delete1))/ (Uptime_ since_flush_status2-Uptime_since_flush_ status1)

主从复制监控

@Q: 监控主从复制链路的状态

1
2
3
4
5
6
Master_Log_File:mysql-bin.001083
Read_Master_Log_Pos:228613650
Relay_Log_File:mysqld-relay-bin.003606
Relay_Log_Pos:228613813
Relay_Master_Log_File:mysql-bin.001083
Slave_Io_Running:Yes Slave_SQL_Running:Yes Replicate_Do__DB: Replicate_Iqnore_DB:

@Q: 主从延迟 #{。。。}

@Q: 验证主从复制的数据一致性

MySQL 信息查看

不使用其他的工具,借助 MySQL 自带的命令或表进行查看

MySQL 状态信息查看

收集这些信息可以用于检测数据库的运行情况 分为全局状态、会话状态

1
2
3
show processlist;  
show status;
show status like ‘%下面变量%’;
  • Aborted_clients 由于客户没有正确关闭连接已经死掉,已经放弃的连接数量.
  • Flush_commands 执行FLUSH命令的次数.
  • Aborted_connects 尝试已经失败的MySQL服务器的连接的次数.
  • Max_used_connections 同时使用的连接的最大数目.
  • Slow_queries:要花超过long_query_time时间的查询数量.
  • Open_tables 打开表的数量.
  • Open_files 打开文件的数量.
  • Open_streams 打开流的数量(主要用于日志记载)
  • Opened_tables 已经打开的表的数量.
  • Threads_connected 当前打开的连接的数量.

INFORMATION_SCHEMA

辅助表信息

transaction

1
SELECT * FROM information_schema.INNODB_TRX;  

PERFORMANCE_SCHEMA

1
SHOW STATUS LIKE 'perf%';

可不与 mysql 在同一个机器上,作为额外的监控组件进行操作 支持服务器分组,支持多个 mysql 连接,较少的配置易于执行

Innotop

Innotop 为 MySQL和 InnoDB 事务/状态的监视器,类似 MySQL 的 top 命令,显示查询、InnoDB 事务、锁等待、死锁、打开的表、复制的状态、缓冲信息等。

运行的参数

  • d:多久时间更新一次
  • h:连接的主机名
  • p:连接的端口
  • S:socket的位置
  • u:连接的用户
  • c: 指定配置文件运行

# 进行服务器分组@ 进行选择连接

1
2
innotop -h 127.0.0.1 \\
-u root -p$MYSQL_ROOT_PASSWORD

服务器分组

保存配置

1
2
3
4
5
6
7
8
9
10
11
[server_groups]

inner=master233 slave158

[/server_groups]

[connections]
master233=user=slave have_user=1 pass=slave have_pass=1 dsn=DBI:mysql:;host=172.17.10.233;port=3306 savepass=1
slave158=user=slave have_user=1 pass=slave have_pass=1 dsn=DBI:mysql:;host=172.17.10.158;port=3306 savepass=1

[/connections]

面板

M: 主从情况 T: 事务情况

O: 打开的表 是否在使用,是否被锁住

数据存储碎片化

概述

碎片分类

三种类型的数据碎片化

  • 行碎片(Row fragmentation)
  • 行间碎片(Intra-row fragmentaion)
  • 剩余空间碎片(Free space fragmentation)

碎片的影响

磁盘上索引页的物理排序不接近页面上记录的索引排序,或者64页块中有许多未使用的页面被分配给索引

  • 占用的空间比“应该”占用的空间多,所有 InnoDB 数据和索引都存储在 B-trees 中,它们的 fill factor 可能在50%到100%之间变化。
  • 表扫描需要比“应该”花费更多的时间。

碎片查看

(1)碎片大小=数据总大小-实际表空间文件大小

(2)数据总大小=data_length+index_length=15220736

(3)实际表空间文件大小=rowsavg_rog_length=29933550=14966750

(4)碎片大小=(15220736-14966750)/1024/1024=0.2M

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
show table status from  mall like 'stockshiftflow' \\G;
show table status from mall like 'stockoperlog' \\G;
-- 查询空闲空间超过 50M 大小的表
SELECT CONCAT(table_schema, '.', table_name) AS TABLE_NAME
,engine AS TABLE_ENGINE
,table_type AS TABLE_TYPE
,table_rows AS TABLE_ROWS
,CONCAT(ROUND(data_length / ( 1024 * 1024), 2), 'M') AS TB_DATA_SIZE
,CONCAT(ROUND(index_length / ( 1024 * 1024), 2), 'M') AS TB_IDX_SIZE
,CONCAT(ROUND((data_length + index_length )
/ ( 1024 * 1024 ), 2), 'M') AS TOTAL_SIZE
,CASE WHEN data_length =0 THEN 0
ELSE ROUND(index_length / data_length, 2) END AS TB_INDX_RATE
,CONCAT(ROUND( data_free / 1024 / 1024,2), 'MB') AS TB_DATA_FREE
,CASE WHEN (data_length + index_length) = 0 THEN 0
ELSE ROUND(data_free/(data_length + index_length),2)
END AS TB_FRAG_RATE
FROM information_schema.TABLES
WHERE ROUND(DATA_FREE/1024/1024,2) >=50
ORDER BY data_free DESC
limit 0, 20;

碎片整理

optimize table table_name

OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长。

OPTIMIZE TABLE 后,表的变化跟存储引擎有关。

对于 INNODB 表,OPTIMIZE TABLE 映射到 ALTER TABLE … FORCE(或者这样翻译:在 InnoDB 表中等价 ALTER TABLE … FORCE),它重建表以更新索引统计信息并释放聚簇索引中未使用的空间。当您在InnoDB表上运行时,它会显示在 OPTIMIZE TABLE 的输出中

对于 innodb_file_per_table=1 的 InnoDB 表,OPTIMIZE TABLE 会重组表和索引的物理存储,将空闲空间释放给操作系统。也就是说 OPTIMIZE TABLE [tablename] 这种方式只适用于独立表空间

ALTER TABLE table_name ENGINE = Innodb

实际上重新整理碎片了.当执行优化操作时,实际执行的是一个空的 ALTER 命令,但是这个命令也会起到优化的作用,它会重建整个表,删掉未使用的空白空间。

ALTER TABLE ENGINE= INNODB,会重新整理在聚簇索引上的数据和索引。

在有些情况下,ALTER TABLE xxxx ENGINE= INNODB 更好。例如old_alter_table 系统变量没有启用等等。另外对于 MyISAM 类型表,使用 ALTER TABLE xxxx ENGINE= INNODB 是明显要优于 OPTIMIZE TABLE 这种方法的。

1
ALTER TABLE stockshiftflow ENGINE = Innodb;

pt-online-schema-change

1
2
3
4
5
6
7
8
9
10
pt-online-schema-change  \\
--user=${user} \\
--password=${passwd} \\
--host=${host} \\
P=3306,D=${database},t=$table \\
--charset=utf8 \\
--alter="ENGINE=InnoDB" \\
--nocheck-replication-filters \\
--alter-foreign-keys-method=auto \\
--execute

执行脚本

文件名 clean_pieces.sh

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
44
45
46
47
48
49
50
51
#!/bin/bash
#
#********************************************************************
#$1 : 清理碎片的数据库名称
#$2 : 清理碎片的表名称
#Description: 清理 MySQL 表的碎片
#
args=($*)
database=${args[0]}
tables=( ${args[@]:1} )

echo "database: $database"
for table in "${tables[@]}";
do
echo "table: $table"
done

host='127.0.0.1'
user='root'
passwd=$MYSQL_ROOT_PASSWORD

for table in "${tables[@]}"
do
sql="
select
CONCAT(table_schema, '.', table_name) AS TABLE_NAME,
concat(round(sum((DATA_LENGTH+Index_length)/1024/1024),2),'MB') AS TABLE_SIZE,
concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') AS DATA_SIZE,
concat(round(sum(Index_length/1024/1024),2),'MB') AS INDEX_SIZE,
table_rows AS TABLE_ROWS,
CASE WHEN data_length =0 THEN 0
ELSE ROUND(index_length / data_length, 2) END AS TB_INDX_RATE
from TABLES where table_schema='$database' and table_name='$table'
"
echo "--------------------"
echo "--------------- $(date +%s) Database: $database, Table: $table, Begin clean ... --------------- "
mysql -u$user -p$passwd -P3306 -h$host information_schema -e "$sql"

time pt-online-schema-change \\
--user=${user} \\
--password=${passwd} \\
--host=${host} \\
P=3306,D=${database},t=$table \\
--charset=utf8 \\
--alter="ENGINE=InnoDB" \\
--nocheck-replication-filters \\
--alter-foreign-keys-method=auto \\
--execute

mysql -u$user -p$passwd -P3306 -h$host information_schema -e "$sql"
done

执行 CASE

1
2
# 清理 mall 数据库中表 interfacelog stockoperatelog stockshiftflow 的碎片
bash clean_pieces.sh mall interfacelog stockoperatelog stockshiftflow

MySQL 配置

服务器参数

基本参数

1
2
3
auto-increment-increment = 2      
auto-increment-offset = 2
slave-skip-errors = all

InnoDb基本参数

状态变量

基本配置

innodb_buffer_pool_size

缓冲池并不仅仅缓存素引:它还会缓存行的数据、自适应哈希索引、插入缓冲(Insert Buffer)、锁,以及其他内部数据结构。 来帮助延迟写入,可合并多个写入操作

innodb_log_file_size

max_connections

最大连接数,阿里云的 MySQL5.8, 8C16G 默认为 4000

Innodb 参数

innodb_file_per_table

每张表都是一个独立表空间,对应每张表都是一个 idb 文件。

配置成独立表空间后可以对表进行数据、索引碎片整理。

Innodb_flush_log_at_trx_commit

事务刷写磁盘日志的策略

  • 0: 数据不安全,适合配置 slave 机器
  • 1: 默认值,最安全的设置,每次事务后日志都会刷新到磁盘。
  • 2: 每秒刷新一次事务,有丢失的风险,对于 master 有时可以接受; MySQL 进挂掉,不会丢任何事务,整个服务器挂了或断点,可能会丢失一些事务

缓冲区大小

Innodb read io threads innodb write io threads

以上两个参数決定了 Innodb 读写的 I0 进程数,默认为 4

Innodb stats on metadata

决定了 MYSQL 在什么情況下会刷新 innodb 表的统计信息。

配置的内容:

  • 个数 - 字节 - 开启与关闭 - 百分比

table_cache: 表可被缓存的数量

key_buffer_size: 以字节为单位

max_heap_table_size: 指定隐式内存临时表最大允许的大小

table_cache_size: 结果值比缓存中的表数小,MYSQL 将从缓存中删除不常使用的表

thread_cache_size:

query_cache_size:

sort_buffer_size:

innodb_buffer_pool_pages_dirty: 状态变量,缓冲池中的脏页数量

innodb_buffer_pool_instances: 5.5+ 新增

open_files_limit: 设置的较少,可能出现 too many open files

参数动态配置

1
2
3
4
5
set sort_buffer_size = <value>
set GLOBAL sort_buffer_size = <value>
SET @@sort_buffer_size := <value>
SET @@session.sort_buffer_size := <value>
@@global.sort_buffer_size := <value>

控制 Innodb 并发

innodb_thread_concurrency: 并发设置,默认为 0,表示不限制 建议并发值 = CPU 数量 * 磁盘数量 * 2

innodb_thread_sleep_delay: 微秒为单位, 在进入内核线程超过运行的数量后,第一次休眠的时间,之后重试,若不能进入,则放入到等待队列,由 OS 处理

innodb_concurrency_tickets: 较少小改

innodb commit concurrency: 変量控制有多少个线程可以在同时间提交

排序

max_length_for_sort_data: 影响使用哪种排序算法

max_connect_errors: 网络、配置、权限等问题导致大连个链接重试,记录黑名单设置的大,有效地禁用主机黑名单

read_only: 建议备库设置成只读模式

slave_net_timeout: 默 1h,可缩短到 1min

I/O 配置

innodb_log_file_in_group

主从配置

跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断 1062: 一些主键重复 1032: 主从数据库数据不一致

1
slave_skip_errors=1062  

忽略指定的数据库同步

1
binlog-ignore-db=mysql

sync_binlog

控制何时 binlog 刷新到磁盘上

0: 由 OS 进行控制 1: 每次事务提交的时候写入,最为安全,但影响性能

连接参数

Connection

  • createDatabaseIfNotExist: 默认为 false
  • rollbackOnPooledClose: 默认为 true, 在 Driver 因为问题回滚..
  • useAffectedRows: 默认为 false

Session

  • characterEncoding=utf8: 设置 session 对应的编码格式,告知 server 返回的结果编码格式,默认为 “autodetect”。
  • useUnicode=true: 使用 Unicode
  • serverTimezone=Asia/Shanghai: 设置时区

Networking

  • connectTimeout: 默认为 0,连接超时, 单位 ms
  • socketTimeout: 默认为 0,单位 ms. 网络 socket 操作的超时时间
  • maxAllowedPacket: 默认为 65535,最大允许发送的网络包 Packet 大小
  • useCompression: 默认为 false,使用 zlib 压缩,当与服务器通信的时候

Statements

  • cacheDefaultTimezone: 默认为 true,缓存客户端默认时区,MySQL8.0.20 后添加的
  • queryTimeoutKillsConnection: 默认 false

Performance Extensions

If ‘cacheCallableStmts’ is enabled, how many callable statements should be cached?

Default: 100

  • metadataCacheSize: 结果元数据缓存的大小,在 cacheResultSetMetaData 设置为 true 时有效,默认 50
  • prepStmtCacheSize: 多少 prepared statements 被缓存,默认 25
  • rewriteBatchedStatements=true:默认 false,重写批量的语句,提高批量的操作效率
  • allowMultiQueries=true: 允许多查询

归档数据库配置

可定期对 MySQL 进行数据归档,可考虑使用 Archive 存储引擎,对于归档的数据库可适当减少配置,关闭一些耗时的配置。

配置如下:

  • 使用 4G 的 innodb 缓冲区
  • 更改事务隔离级别为 READ-UNCOMMITTED,一般只是导入导出无事务操作
  • 适当调整 mysqldump 的 max_allowed_packet,方便进行必要数据的恢复
  • 关闭慢查询、binlog
  • 减小相应的 buffer
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
[mysqld]
port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
datadir = /var/lib/mysql
log-error = /var/log/mysql/mysql-error.log
tmpdir = /tmp

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# binlog setting
#log-bin = mysql-bin
#binlog_format = MIXED # can be mixed, decrease ..
#binlog_format = ROW # can be mixed, decrease ..
#binlog_row_image = minimal
sync_binlog = 1 # default 0 affect performance,
# safe to guarantee replication
slave_net_timeout = 60 # defacult 3600

#expire_logs_days = 7
#relay_log = mysql-relay
#server_id = {{server_id | default('555')}}
#log_bin_trust_function_creators = 1
wait_timeout = 57600
interactive_timeout = 57600

max_allowed_packet = 512M # or 100M
event_scheduler = 1
max_connections = 2000

# log setting {slow query, not using indexs}
#slow_query_log = {{slow_query_log | default('0')}}
#slow_query_log_file = mysql-slow.log
#long_query_time = {{long_query_time | default('0.20')}}
#log_queries_not_using_indexes = {{log_queries_not_using_indexes | default('0')}}

# character
character-set-server = utf8mb4

# tx
transaction-isolation = READ-UNCOMMITTED #REPEATABLE-READ req for ACID, SERIALIZABLE req XA

# innodb
#innodb_buffer_pool_size = 768M
innodb_buffer_pool_size = 4G
# old 128M, 70%-80% mache mem
innodb_log_file_size = 32M # old 500M, total 1G, can bigger to 1024M
# Note: modify need to move old file to other position,may start fail
# 64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128
#innodb_log_files_in_group = 2 # defacult 2
innodb_log_buffer_size = 64M # defalut 8M
innodb_buffer_pool_instances = 8 # default
innodb_flush_log_at_trx_commit = 2 # defalut 1, per second trx flush 2/0 = perf, 1 = ACID
innodb_file_per_table = 1
innodb_lock_wait_timeout = 60 # timeout 500
innodb_status_output = ON
innodb_status_output_locks = ON
innodb_print_all_deadlocks = ON
innodb_read_io_threads = 6 # default 4
innodb_write_io_threads = 6 # default 4
#innodb_thread_concurrency = 16 # default 0 recommend 2x core quantity
#innodb_additional_mem_pool_size = 8M #default 8M
#innodb_open_files = 2000 # default 2000, can open *.idb file count
# buffer setting
sort_buffer_size = 256K # default 0.25M
join_buffer_size = 256K # default 0.25M, can bigger to 128M
read_buffer_size = 512K # default 0.125M
read_rnd_buffer_size = 512K # default 0.25M
#max_length_for_sort_data = 1024 # default 1024
#max_connect_errors = 100 # defacult 100
#innodb_doublewrite = 1 # default on
#thread_concurrency = 12 # default 10 recommend 2x CPU cores
#thread_cache_size = 28 # defacult 28 recommend 5% of max_connections
#open_files_limit = 1048576 # default 1048576

# MyISAM
#key_buffer_size = 32M # default 8M
#query_cache_size = 1M # default 1M

# table size
tmp_table_size = 128M # default 16M
max_heap_table_size = 128M # default 16M recommend same size as tmp_table_size

# concat
group_concat_max_len = 100000000

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

[mysqldump]
quick
quote-names
max_allowed_packet = 256M

配置案例

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
[mysqld]
port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
datadir = /var/lib/mysql
log-error = /var/log/mysql/mysql-error.log
tmpdir = /tmp

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# binlog setting
log-bin = mysql-bin
#binlog_format = MIXED # can be mixed, decrease ..
binlog_format = ROW # can be mixed, decrease ..
binlog_row_image = minimal
#sync_binlog = 1 # default 0 affect performance,
# safe to guarantee replication
slave_net_timeout = 60 # defacult 3600

expire_logs_days = 7
relay_log = mysql-relay
server_id = 199192
log_bin_trust_function_creators = 1
wait_timeout = 57600
interactive_timeout = 57600

max_allowed_packet = 256M # or 100M
event_scheduler = 1
max_connections = 2000

# log setting {slow query, not using indexs}
slow_query_log = 1
slow_query_log_file = mysql-slow.log
long_query_time = 0.20
log_queries_not_using_indexes = 1

# character
character-set-server = utf8mb4

# tx
transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA

# innodb
innodb_buffer_pool_size = 18G
# old 128M, 70%-80% mache mem
innodb_log_file_size = 500M # old 500M, total 1G, can bigger to 1024M
# Note: modify need to move old file to other position,may start fail
# 64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128
#innodb_log_files_in_group = 2 # defacult 2
innodb_log_buffer_size = 8M # defalut 8M
innodb_buffer_pool_instances = 8 # default
innodb_flush_log_at_trx_commit = 2 # defalut 1, per second trx flush 2/0 = perf, 1 = ACID
innodb_file_per_table = 1
innodb_lock_wait_timeout = 60 # timeout 500
innodb_status_output = ON
innodb_status_output_locks = ON
innodb_print_all_deadlocks = ON
innodb_read_io_threads = 6 # default 4
innodb_write_io_threads = 6 # default 4
#innodb_thread_concurrency = 16 # default 0 recommend 2x core quantity
#innodb_additional_mem_pool_size = 8M #default 8M
#innodb_open_files = 2000 # default 2000, can open *.idb file count
# buffer setting
sort_buffer_size = 1M # default 0.25M
join_buffer_size = 1M # default 0.25M, can bigger to 128M
read_buffer_size = 1M # default 0.125M
read_rnd_buffer_size = 1M # default 0.25M
#max_length_for_sort_data = 1024 # default 1024
#max_connect_errors = 100 # defacult 100
#innodb_doublewrite = 1 # default on
#thread_concurrency = 12 # default 10 recommend 2x CPU cores
#thread_cache_size = 28 # defacult 28 recommend 5% of max_connections
#open_files_limit = 1048576 # default 1048576

# MyISAM
#key_buffer_size = 32M # default 8M
#query_cache_size = 1M # default 1M

# table size
tmp_table_size = 128M # default 16M
max_heap_table_size = 128M # default 16M recommend same size as tmp_table_size

# concat
group_concat_max_len = 100000000

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

[mysqldump]
quick
quote-names
max_allowed_packet = 128Mr

其他

MySql 大表数据删除

Mysql 官网对于大表数据的删除说明

If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful: Select the rows not to be deleted into an empty table that has the same structure as the original table: INSERT INTO t_copy SELECT * FROM t WHERE … ; Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name: RENAME TABLE t TO t_old, t_copy TO t; Drop the original table:

1
2
3
4
5
6
INSERT INTO copy_interfacelog 
SELECT *
FROM mall.servicelog
WHERE logtime between '2020-04-07 02:00:00' and '2020-04-14 10:23:24';
-- 使用 mysqldump, file...备份恢复
RENAME TABLE mall.servicelog TO mall.old_servicelog copy_interfacelog TO interfacelog;

通过 INSERT INTO <table-name> SELECT .. 筛选出不需要删除的数据

1
2
3
4
5
6
7
DELETE FROM `table` WHERE (whatever criteria) ORDER BY `id` LIMIT 1000
MYSQL="mysql -uroot -p$MYSQL_ROOT_PASSWORD "
sql="select uuid from mail.interfacelog where (condition) order by uuid desc limit 1000 "
for i in `seq 1 1000`;
do
$MYSQL -e "$sql" | sed 's;/|;;g' | awk '{if(NR>1)print "delete from table_name where uuid = ",$1,";" }' | $MYSQL;
done

Ref

Deleting millions of rows in MySQL

MySQL :: MySQL 8.0 Reference Manual :: 13.7.3.4 OPTIMIZE TABLE Statement

官网对于 OPTIMIZE TABLE 说明

潇湘隐者

MySQL 数据存储碎片化

Optimized my.cnf configuration for MySQL/MariaSQL (on Ubuntu, CentOS etc. servers)

优化的 mysql 配置

Ten MySQL performance tuning settings after installation

percona 网站对于 MySQL 性能调整配置说明

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html

官网对于属性的配置

What’s the difference between cachePrepStmts and useServerPrepStmts in MySQL JDBC Driver

https://stackoverflow.com/questions/2993251/jdbc-batch-insert-performance

批量操作参数的更改