Day15-数据库服务全面优化与PT工具应用

Day-15-数据库服务优化与PT工具应用

01 数据库服务优化讲解
02 数据库管理工具应用
03 数据库缓存服务应用 （Redis-NoSQL）

1、数据库服务优化讲解

1.2 数据库服务系统层面的优化

更改文件句柄和进程数

[root@xiaoQ ~]# vim /etc/sysctl.conf
vm.swappiness = 5
-- 也可以设置为0（/proc/sys/vm/swappiness）物理内存剩余的百分比之后，使用swap
-- 参数值越大，越积极使用swap空间，参数值越小，越积极使用物理内存
-- 默认值为可以通过cat /proc/sys/vm/swappiness命令查看
vm.dirty_ratio = 20
-- 表示可以用脏数据填充的绝对最大系统内存量，当系统到达此点时，必须将所有脏数据提交到磁盘，
-- 同时所有新的I/O块都会被阻塞，直到脏数据被写入磁盘。
-- 这通常是长I/O卡顿的原因，但这也是保证内存中不会存在过量脏数据的保护机制。
vm.dirty_background_ratio = 10
-- 定义内存中脏页的刷新比例，在指定比例之上就要刷写脏页
net.ipv4.tcp_max_syn_backlog = 819200
net.core.netdev_max_backlog = 400000
net.core.somaxconn = 4096
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 0[root@xiaoQ~l# vim /etc/security/limits.conf
* hard nofile 63000
-- 可打开的文件描述符的最大数(超过会报错)；
* soft nofile 63000
-- 可打开的文件描述符的最大数(超过会警告)；

防火墙与selinux安全设置：

[root@xiaoQ-01 ~]# systemctl is-active firewalld
unknown
[root@xiaoQ-01~]# systemctl is-enabled firewalld
disabled
-- 查看防火墙服务是否关闭，如果有需要开启时，别忘把数据库服务相关的端口开启即可[root@xia0Q-01 ~]# getenforce
Disabled
-- 查看selinux安全策略是否关闭

文件系统优化设置

#推荐使用XFS文件系统，并设置数据库的数据为独立分区，不建议使用LVM
挂载点为: /data
挂载参数: defaults，noatime，nodiratime，nobarrier
[root@xiaoQ-01 ~]# vim /etc/fstab
/dev/sdbl   /data    xfs    defaults, noatime, nodiratime, nobarrier    1 2

IO调度设置
在系统中的IO调度器的总体目标是：希望让磁头能够总是往一个方向移动，移动到底了再往反方向走，这恰恰就是现实生活中的电梯模型；所以IO调度器也被叫做电梯（elevator）而相应的算法也就被叫做电梯算法。
而Linux中I0调度的电梯算法有好几种：

• as（Anticipatory）
  已经废弃…
• cfq（Complete Fairness Queueing-完全公平排队I/O调度程序）
  为每个进程/线程，单独创建一个队列来管理该进程所产生的请求，也就是说每个进程一个队列，各队列之间的调度使用时间片来调度，以此来保证每个进程都能被很好的分配到I/O带宽，I/O调度器每次执行一个进程的4次请求。
• deadline
  确保了在截止时间内完成服务请求，这个截止时间是可调整的，默认读期限短于写期限；
  Deadline对数据库环境(ORACLE RAC，MYSQL等)是最好的选择。
• noop（No Operation-电梯式调度程序）
  该算法实现了最简单的FIFO队列，所有 I/O请求大致按照先来后到的顺序进行操作。
  实现了一个简单的FIFO队列，就像电梯的工作方法一样对I/O请求进行组织。

具体使用哪种算法我们可以在启动的时候通过内核参数elevator来指定

#SAS deadline/SSD&PCl-E: noop
[root@xiaoQ-01 ~]# echo deadline >/sys/block/sda/queue/scheduler
或者
[root@xiaoQ ~]# vim /etc/default/grub
GRUB_CMDLINE_LINUX="spectre_v2=retpoline net.ifnames=0 elevator=deadline rhgb quiet"

1.3 数据库服务软件版本选择

• 建议选择开源社区版本，并且选择稳定的GA版本；
• 选择数据库服务GA版本时，最好是发布了6个月_{12个月的GA双数版本，大约在15}20个小版本左右；
• 选择数据库服务版本时，要主要选择前后几个月没有大的BUG修复的版本，而不是大量修复BUG的集中版本；
• 选数据库服务版本还要考虑开发人员所开发程序使用的版本是否与实际数据库应用兼容；
• 选择好数据库服务版本后，建议内部开发人员测试下数据库环境，跑大概3~6个月的时间；
• 企业非核心业务可以优先采用新版本的数据库进行应用；
• 可以多咨询DBA大佬，或者在技术氛围好的群里进行交流咨询，使用真正的高手们用过且好用的GA版本产品；

说明：最终建议可以选择8.0.20，以及8.0.20之后的双数版本；

1.4 数据库服务结构参数优化

1.4.1 数据库连接层优化

# 连接层相关优化参数
max_connections=1000         # select @@max_connections;
-- 单节点建议不高于3000
max_connect_errors=999999     # select @@max_connect_errors;
-- 定义最大连接失败的次数，当超过定义的数值，就会影响正常的连接建立
wait_timeout=600             # show processlist;
-- 定义连接会话的超时时间(释放更多的连接数)，具体指定sleep连接会话的超时时间
interactive_wait_timeout=3600    (老版) # select @@interactive_wait_timeout;
interactive_timeout=3600        （新版）# select @@interactive_timeout;
-- 定义连接会话的超时时间(释放更多的连接数)，定义交互式的超时时间
net_read_timeout=120
net_write_timeout=120
-- 定义网络传输读或写数据包的超时时间
max_allowed_packet=32M
-- 定义允许的最大数据包大小

1.4.2 数据库服务层优化

# 服务层相关优化参数
sql_safe_updates = 1
-- 设置当使用update或delete命令时，必须加上where才能执行
slow_query_log = ON
slow_query_log_file = /xxx
long_query_time = 1
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10  -- 不走索引的相同索引语句只记录指定的次数
-- 进行数据库慢日志信息相关配置
sort_buffer_size = 262144
join_buffer_size = 262144
read_buffer_size = 131072
read_rnd_buffer_size = 262144
-- 定义session级别的缓冲区大小，不建议设置大小超过8M，因为是根据每个会话进行的缓冲区分配；
tmp_table_size = 16777216
max_heap_table_size = 16777216
-- 生成的临时表空间建议不超过128M
max_execution_time = 28800
-- 当跑大的事务操作时，可以设置事务最大的执行时间，建议再跑批量操作，可以设置大些
lock_wait_timeout = 60
-- 表示设置锁等待的时间，当锁定时间到达指定时间后，会实现自动解锁(主要针对元数据锁，默认是1年)
lower_case_table_names = 1
-- 表示创建表时，自动将表名的大写信息转化为小写;(必须初始化时进行设置)
thread_cache_size = 64
-- 表示设置线程缓存的个数信息，可以使线程缓存资源进行复用，从而减少CPU工作压力；(比如连接线程就可以应用）
character_set_server = utf8mb4
-- 设置数据库服务端字符集，建议设置为utf8或utf8mb4
log_timestamps = SYSTEM
-- 表示设置日志信息的时间尽量和系统时间信息保持一致
init_connect = '普通用户登录信息'
init_connect = 'insert into auditdb.access(thread_id,login_time,localname,matchname) values (connection_id(),now(),user(),current_user());'
-- 一般在进行审计时进行使用，表示普通用户登录时，自动进行相应语句的操作
-- 参考链接说明：https://blog.csdn.net/mingli_a/article/details/115351986
event_scheduler = OFF
-- 事件调度信息一般不用使用，关闭即可
secure-file-priv = /tmp
-- 当需要在数据库子系统中把信息导出到当前系统指定目录文件中时，进行使用
expire_logs_days = 10
sync_binlog = 1
log_bin = ON
log_bin_basename = /data/3306/binlog/mysql-bin
log_bin_index = /data/3306/binlog/mysql-bin.index
max_binlog_size = 500M
binlog_format = ROW
max_binlog_cache_size = 2G
max_binlog_stmt_cache_size = 2G
-- 表示和binlog有关的配置信息

1.4.3 数据库引擎层优化

# 引擎层相关优化参数
transaction_isolation = "READ-COMMITTED"
-- 设置事务默认隔离级别，基本RC级别即可
innodb_data_home_dir = /xxx
-- 表示定义共享表空间文件ibdate存储路径(了解即可)
innodb_log_group_home_dir = /xxx
-- 表示定义redo日志文件存储路径(了解即可)
innodb_log_file_size = 2048M
-- 表示定义redo日志单个文件大小(建议1~4G)
innodb_log_files_in_group = 3
-- 表示定义redo日志文件的组数(一般可以定义为3~4组)
innodb_flush_log_at_trx_commit = 2
-- 表示定义事务redo日志刷新到磁盘的策略(双一配置中的其中一个)，有binlog日志时，可以不用设置为1
innodb_flush_method = O_DIRECT
-- 表示log buffer中的信息是直接写入到磁盘中的，而不经过系统的buffer（建议硬盘配合SSD使用）
innodb_io_capacity = 1000
innodb_io_capacity_max = 4000
-- 表示每次IO可以刷新数据页的数量（SSD盘按照以上配置 SAS盘按照默认即可）
innodb_buffer_pool_size = 64G
-- 表示定义buffer pool的空间大小（基于128G内存配置，建议不要超过75~80%）
innodb_buffer_pool_instances = 4
-- 表示将定义好的buffer pool空间可以拆分为4份，给不同的实例进行使用，避免相同内存空间的争用;
innodb_log_buffer_size = 64M
-- 定义log buffer空间大小，建议不要超过128M;
innodb_max_dirty_pages_pct = 85
-- 控制在buffer pool中脏页数量的比例，当达到指定的比例就进行checkpoint操作，将脏页信息进行落盘
innodb_lock_wait_timeout = 10
-- 主要是控制行锁的等待超时时间，一般控制在10s内
innodb_open_files = 63000
-- 表示定义最多打开文件句柄的个数，数据库每次访问一个表(即打开一个文件)，都会占用一定的文件句柄数量
innodb_page_cleaners = 4
-- 表示和线程有关的优化（可以忽略）
innodb_sort_buffer_size = 64M
-- 表示做排序时利用的缓冲区大小
innodb_print_all_deadlocks = 1
-- 表示将死锁的日志全部记录下来
innodb_rollback_on_timeout = ON
-- 表示当到达超时时间，会自动解决死锁事务
innodb_deadlock_detect = ON
-- 表示开启死锁检测功能(默认开启)
-- 表示和死锁检测和分析有关的参数
-- 对于死锁概念的资料参考:https://blog.csdn.net/java1527/article/details/127105144

1.4.4 数据库复制相关优化

# 主从复制相关优化参数
relay_log = db-01-relay-bin
relay_log_basename = /data/3306/data/db-01-relay-bin
relay_log_index = /data/3306/data/db-01-relay-bin.index
max_relay_log_size = 500M
relay_log_purge = ON
relay_log_recovery = ON
-- 表示和relay log日志相关的配置参数信息
rpl_semi_sync_master_enabled=on
rpl_semi_sync_master_timeout=1000
rpl_semi_sync_master_trace_level=32
rpl_semi_sync_master_wait_for_slave_count=1
rpl_semi_sync_master_wait_no_slave=on
rpl_semi_sync_master_wait_point=after_sync
rpl_semi_sync_slave_enabled=on
rpl_semi_sync_slave_trace_level=32
binlog_group_commit_sync_delay=1
binlog_group_commit_sync_no_delay_count=1000
-- 表示和半同步复制相关的配置参数
gtid_mode = ON
enforce_gtid_consistency = ON
-- 表示和GTID相关的配置参数信息
master_verify_checksum = ON
-- 表示激活主从复制事件校验机制
sync_master_info=1
-- 表示每个EVENT都要执行刷盘操作，主要影响masterinfo信息(注意不是每个事务!)
-- 参数参考博文资料:https://blog.csdn.net/weixin_39940344/article/details/113275456
skip_slave_start = 1
-- 表示随着数据库服务的启动，自动启动从库线程
-- 参数参考博文资料:https://blog.csdn.net/csdnhsh/article/details/116355191
# read_only = ON
# super_read_only = ON
-- 表示是否设置从库为只读状态
log_slave_updates = ON
-- 表示指定从库的事务更新操作，是否也记录到从库的binlog日志中
server_id = xx
-- 定义主从的实例标识信息
report_host = xxxx
report_port = xxxx
-- 表示是否允许主库探测从库的网络配置信息，主要影响show slave hosts命令的输出
-- 参数参考博文资料：http://04007.cn/article/527.html
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
-- 表示设置从库的多线程复制，可以对单个事务中的语句进行多线程回放
master_info_repository = TABLE
relay_log_info_repository = TABLE
-- 表示定义master info和relay log info以什么方式记录信息（了解）

1.4.5 数据库其他相关优化

# 数据库客户端配置
[mysql]
no-auto-rehash （等价于mysql-A参数作用）
-- 默认每次连接数据库服务会扫描数据库中所有元数据信息，可以利用此参数关闭扫描功能，有效节省扫描过程占用的内存资源；
pager less
-- 开启数据库输出信息过滤功能

1.5 数据库服务开发规范要求

1.5.1 数据库开发字段规范

• 每个表建议在30个字段以内；
• 需要存储emoii字符时，则选择utf8mb4字符集；
• 机密数据信息，需要进行加密后再存储；
• 整型数据类型，默认加上UNSIGNED；
• 存储IPv4地址建议用INT UNSIGNE只存储数字信息，查询时在利用INET_ATON()，INET_NTOA()函数转换；
• 如果遇到BLOB、TEXT大字段单独存储表或者附件形式存储；
• 选择尽可能小的数据类型，用于介绍磁盘和内存空间；
• 存储浮点数，可以放大倍数存储；
• 每个表必须有主键，INT/BIGINT类型、以及自增作为主键，分布式架构使用sequence序列生成器保存；
• 表中每个列使用not null或者增加默认值；

1.5.3 数据库开发语句规范（SQL）

# 01 去掉不必要的括号
before:
((a AND b) AND c OR (((a AND B)) AND ((C AND d))))
after
(a AND b AND C) OR (a AND b AND c AND d)# 02 去掉重叠条件
before:
(a<b AND b=c) AND a=5
after
b>5 AND b=C AND a=5before
(b>=5 AND B=5) OR (b=6 AND 5=5) OR (B=7 AND 5=6)
after
b=5 OR b=6# 03 避免使用not in、not exists、<>、like %%
# 04 多表连接，小表驱动大表
# 05 减少临时表应用，优化order by、group by、uninon、distinct、join等
# 06 减少语句查询范围，精确查询条件
# 07 多条件，符合联合索引最左原则
# 08 查询条件减少使用函数、拼接字符等条件、条件隐式转换
# 09 union all 替代 union
# 10 减少having子句使用
# 11 如非必要不使用for update语句（会加表级别意向锁）
# 12 update和delete，开启安全更新参数
# 13 减少insert ... select 语句应用
mysql > create table city_bak like city;
mysql > desc city_bak;
mysql > insert into city_bak select * from city;    (会对原有city表进行加锁，建议加条件扫描) pt 归档功能 --CSV# 14 使用load替代insert录入大数据(会产生大量的意向锁)
# 15 导入大量数据时，可以禁用索引、增大缓冲区、增大redo文件和buffer，关闭autocommit、RC级别可以提高效率
# 16 优化limit，最好业务逻辑中先获取主键ID，再基于ID进行查询
limit 5000000,10
before
select * from t1 where num > 10 limit 5000000,10;
after
select id from t1 where num > 10 limit 5000000,10;
select id from t1 where id;
# 17 DDL执行前要审核
# 18 多表连接语句执行前要看执行计划

1.6 数据库服务索引相关优化

• 非唯一索引按照 ‘i_字段名称_字段名称[ _字段名 ]’ 进行命名

• 是唯一索引按照 ‘u_字段名称_字段名称[ _字段名 ]’ 进行命名

• 索引名称使用小写

• 联合索引中的字段数不超过5个

• 唯一键由3个以下字段组成，并且字段都是整型时，使用唯一键作为组合主键

• 没有唯一键或者唯一键不符合上面的条件时，使用自增id作为主键

• 唯一键不能和主键重复

• 索引选择度高的列作为联合索引最左条件

• ORDER BY、GROUP BY、DISTINCY的字段需要添加在索引的后面，构建联合索引

• 单张表的索引数量控制在5个以内，若单张表多个字段在查询需求上都要单独用到索引，需要经过DBA评估；查询性能问题无法解决的，应从产品设计上进行重构

• 使用EXPLAN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temorary；

• UPDATE DELETE 语句需要根据where条件添加索引；

• 对长度大于50的VARCHAR字段建立索引时，按需求恰当的使用前缀索引，或使用其他方法；

• 下面的表增加一列url_crc32，然后对url_crc32建立索引，减少索引字段的长度，提高效率；

create table all_url(
ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
url varchar(255) not null default 0,
url_crc32 int unsigned not null
index idx_url(url_crc32));

• 合理创建联合索引(避免冗余)，(a,b,c)相当于(a)，(a,b)，(a,b,c)
• 合理利用覆盖索引，减少回表次数；
• 减少冗余索引和使用率较低的索引；

1.7 数据库服务事务及锁优化

锁是计算机协调多个进程或纯线程并发访问某一资源的机制
在数据库中，除传统的计算资源(CPU、RAM、I/0)的争用以外，数据也是一种供许多用户共享的资源。
如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

1.7.1 数据库闩锁介绍-latch

latch主要用于管理对共享内存资源的并发访问，例如：操作缓冲池汇总的LRU列表，删除、添加、移动LRU列表中的元素；
为了保证一致性，必须有锁的接入，这就是latch锁；
latch锁和一般的lock锁之间的区别为：

区别	lock	latch
锁定对象	事务(SQL操作流程)	线程
保护对象	数据库对象(库 表 行 索引 表空间 数据页等)	所有共享内存数据结构
生命周期	整个操作周期	临界资源-mutex(互斥量)
锁定模式	MDL(元数据锁)、Table、Record、Gap(间隙锁)、NextLock、意向	rw-latch

名词解释：临界资源
有的资源一次只允许一个进程使用，在它未用完之前，不允许其他进程使用，这类资源被称为临界资源，也称为互斥资源。

输入机、打印机以及变量、数据、表格、队列等都属于临界资源。

其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

读写锁：

• 读锁（共享锁）
  当MySQL的一个进程(sessionA)为某一表开启读锁后，其他的进程包含自身都没有权利去修改这张表的内容。
  但是所有的进程还是可以读出表里面的内容的
  sessionA可以继续对该数据表加写锁，其他session也可以对该数据表继续加读锁但不能加写锁，直到sessionA释放共享锁权限
  当其他的session对这张表进行更新操作时，该线程进入阻塞，直至sessionA释放锁
  sessionA没有释放锁之前不能对其他表进行任何操作
• 写锁（排他锁）
  当MySQL的某一个进程（sessionA）在对某一张表开启写锁后，sessionA只能对该表进行读取或修改，
  在没有释放锁之前不能对其他表进行任何操作
  其他session既不能读取也不能修改该表，更不能对该表加任何类型的锁，直到sessionA释放写锁

查看latch争用的类型信息：

# 模拟存储数据信息操作
mysql> source ~/t100w_oldboy.sql# 查看mutex的互斥争用信息:
mysql> show engine innodb mutex;
+-------+----------------------------------+--------+
|Type   |Name                             |Status  |
+-------+----------------------------------+--------+
|InnoDB|rwlock:dict0dict.cc:2678           |waits=1 |
|InnoDB|rwlock: dictOdict.cc:1184          |waits=13|
|InnoDB|rwlock:log0log.cc:844              |waits=35|
|InnoDB|sum rwlock:bufObuf.cc:788waits=16  |waits=16|
+-------+----------------------------------+--------+
1 rowin set(0.0l sec)
-- 表示进行sum统计读写锁的总占用时间，为等待状态16毫秒，并输出造成锁等待的源码文件和位置信息;
-- 通过源码文件的信息，可以推断分析出，可能在写内存信息(buffer pool)，可能是某个内存链位置点或内存页信息被占用# 可以通过第三方工具分析堆栈信息:
pstack -p 'pidof mysqld' > /tmp/aa.txt
pt-pmp /tmp/aa.txt|more

分析latch争用的发生时间：

• 当A线程访问x内存链表时，B线程排队等待x内存链表解锁，此时CPU发现B线程处于等待状态，所以会将B线程从CPU中踢出；
• A线程进行访问锁链的时间，就是A线程找数据的时间；
• 由于B线程知道A线程很快就会结束，所以B不去排队，而是做spin操作(空转CPU)，然后再去看内存数据结构，A线程是否已解锁；
• 当B线程转了一圈后，在B线程spin操作的时间段中，C线程进来了，连续多次的spin操作后，即产生了os waits；
• 操作系统会将B线程从CPU中踢出；

说明：latch锁争用的表面现象为，CPU在争用期间繁忙、10很闲、没有做实际的事情；

监控latch争用的具体情况：（主要关注较为严重的争用）

mysql> show engine innodb status\G
------------
SEMAPHORES
------------
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: signal count 0
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx-- rounds：意思是每次询问旋转的次数；
-- os waits：表示sleep，当突然增长的比较快的时候，说明latch争用的比较严重；

注意：只要监控信息中的 OS waits/rounds > 5%，就表示latch锁的争用非常严重了；

导致latch争用的发生原因：

• 存访问太过于频繁（不停的找），因为早期数据库版本中，是不存在AHI技术概念；
• list链太长（链上挂10000个块，被持有的几率太大）

可以利用以下方式，降低latch争用：

• 优化SQL语句，从而降低对内存读的数量；
• 增加数据库instances的数量，即拆分多个内存区域；

mysql> show variables like '%buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_instances        | 1              |
+-------------------------------------+----------------+
mysql> 
-- 默认是1，实际可以修改为4、6等，即拆分多个内存区域；

1.7.2 数据库全局锁介绍-GRL

GRL（Global ReadLock）全局锁也可以理解为是全局读锁；

# 主要的加锁方式为：FTWRL
> flush tables with read lock;# 主要的解锁方式为：
> unlock tables;

经常在进行数据备份时，mysqldump --master-data或者xtrabackup（8.0之前早期版本）等备份出现全局锁；
mysqldump --master-data备份时实际上是做了四件事；

序号	行为	锁机制影响
01	记录binlog位置点	备份期间不让所有事务提交
02	实现FTWRL锁定	备份期间不让新的修改进入
03	unlock tables	备份期间非Innodb表的表结构备份完毕后，会进行解锁表操作
04	snapshot innodb	备份期间需要对数据信息实现快照方式备份

xtrabackup备份数据时，备份非InnoDB表数据时，会FTWRL，备份完毕后，会unlock tables；
xtrabackup备份数据时，备份InnoDB表数据时，会备份checkpoint后的数据页，并记录redo变化，可以允许DML，不允许DDL；
GRL全局锁实质是属于MDL（matedatalock）层面的元数据锁；
在全局锁GRL出现加锁期间，会阻塞所有事务写入，阻塞所有已有事务commit；
全局锁的控制机制，是由以下时间参数信息进行的控制：

mysql> select @@lock_wait_timeout;
+---------------------+
| @@lock_wait_timeout |
+---------------------+
|            31536000 |
+---------------------+
1 row in set (0.00 sec)

检测是否存在全局GRL方法：

# 记录加锁过程信息
mysql> update performance_schema.setup_instruments set ENABLED='Yes', TIMED='Yes' where NAME='wait/lock/metadata/sql/mdl';
-- 激活GRL锁检测功能(8.0版本之后默认激活了)mysql> select * from performance_schema.metadata_locks;
mysql> select object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id,owner_event_id from performance_schema.metadata_locks;
-- 查看GRL锁定的相关信息，其中granted表示请求到了GRL锁，而若变为pending状态，表示处于阻塞状态# 记录加锁过程信息(5.7)
mysql> show processlist:
mysql> select * from sys.schema_table_lock_waits;

GRL锁机制案例模拟：
案例01：在数据库5.7环境中，利用xtrabackup/mysqldump备份时数据库出现hang状态，所有查询都不能进行；

# 会话01:模拟一个大的查询或事务
mysql> select *,sleep(100) from city where id<l0 for update;# 会话02:模拟备份时的FTWRL
mysql> flush tables with read lock;
-- 此时发现命令被阻塞#会话03:发起正常查询，发现被阻塞
mysql> select * from world.city where id=1 for update;
mysql> show processlist;

结论：备份数据时，一定要选择业务不繁忙期间，否则有可能会阻塞正常的业务操作；

案例02：5.7数据库进行innobackupex备份全库，造成进程死了(FTWRL后)，mysql里就是全库读锁，后边insert全被阻塞了；

# 会话01:模拟一个大的查询或事务
mysql> select *,sleep(100) from city where id<10;
mysql> show processlist;# 会话02:模备份时的FTWRL
mysql> flush tables with read lock;
-- 保证命令执行成功
mysql> unlock tables;
-- 解除命令锁定操作后，后续修改和插入操作也可以成功# 会话03:发起正常查询，发现被阻塞
mysql> select * from world.city where id=1 for update;
mysql> select * from world.city where id=1
-- 要进行的修改操作是不行的，但是所有简单查询操作是可以进行的

1.7.3 数据库数据信息死锁-Deadlock

死锁主要发生在多个并发事务之间出现交叉资源依赖时；类似A会话正在等待B会话事务的解锁，B会话正在等待A会话事务的解锁；一旦触发了死锁机制，作为InnoDB引擎会自动发现持查死锁的争用情况，识别代价比较低的事务信息，并进行回操作；
死锁信息监控以及分析：

mysql> show engine innodb status\G
-- 记录最后一次死锁情况信息
mysql> set global innodb_print_all_deadlocks=1;
-- 将所有死锁情况信息记录到错误日志文件中;(可以在线直接打开)

死锁经典案例复盘解析：
在数据库会话一窗口进行的操作信息：

# 进行死锁环境准备
mysql> create database test;
mysql> use test;
mysql> create table t1 (id int not null primary key,k1 varchar(20));
mysql> insert into t1 values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
mysql> commit;# 查看数据信息
mysql> select * from t1;
+----+------+
| id | k1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)# 模拟触发死锁问题（A事务从前向后删除 B事务从后向前删除）
# A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> delete from t1 where id=2;
Query OK, 1 row affected (0.00 sec)mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.01 sec)mysql> delete from t1 where id=4;
Query OK, 1 row affected (0.00 sec)mysql> delete from t1 where id=5;
Query OK, 1 row affected (0.00 sec)mysql> select * from t1;
Empty set (0.00 sec)# B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> delete from t1 where id=5;
Query OK, 1 row affected (0.00 sec)mysql> delete from t1 where id=4;
Query OK, 1 row affected (0.00 sec)mysql> delete from t1 where id=3;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
-- 死锁出现，自动对代价小的事务进行回滚；
mysql> select * from t1;
+----+------+
| id | k1   |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)# 线上业务死锁情况排查
mysql> pager less
mysql> show engine innodb status\G
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-10-10 16:29:16 139876118058752
*** (1) TRANSACTION:
TRANSACTION 3413, ACTIVE 34 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 2
MySQL thread id 11, OS thread handle 139876554188544, query id 62 localhost root updating
delete from t1 where id=3
-- 死锁构建的第一个事务信息*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 13 page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id 3413 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 4; hex 80000004; asc     ;;1: len 6; hex 000000000d55; asc      U;;2: len 7; hex 0100000132029a; asc     2  ;;3: len 1; hex 64; asc d;;Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 4; hex 80000005; asc     ;;1: len 6; hex 000000000d55; asc      U;;2: len 7; hex 01000001320276; asc     2 v;;3: len 1; hex 65; asc e;;*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13 page no 4 n bits 72 index PRIMARY of table `test`.`t1` trx id 3413 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 4; hex 80000003; asc     ;;1: len 6; hex 000000000d54; asc      T;;2: len 7; hex 02000001200ca0; asc        ;;3: len 1; hex 63; asc c;;*** (2) TRANSACTION:
TRANSACTION 3412, ACTIVE 52 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 3
MySQL thread id 10, OS thread handle 139876555532032, query id 63 localhost root updating
delete from t1 where id=4
-- 死锁构建的第二个事务信息（从而造成事务交叉冲突或依赖）

1.8 数据库服务架构设计优化

选择合理架构环境，避免单点故障对数据库服务的影响：

• 可以选择高可用架构：MHA+ProxySQL+GTID+半同步，MGR+InnoDB Cluster，PXC；
• 可以选择读写分离架构：ProxySQL、MySQL-Router
• 可以选择分布式架构：MyCAT
• 可以选择缓存服务架构：Redis+sentinel，Redis Cluster，MongoDB RS/MongoDB SHARDING Cluster，ES

1.9 数据库服务安全应用优化

• 使用普通nologin用户管理MySQL服务进程；
• 合理授权用户、设置密码复杂度及最小权限，系统表保证只有管理员用户可以访问；
• 删除数据库服务中的默认匿名用户信息；
• 锁定数据库服务中的非活动用户信息；
• 数据库服务尽量不要暴露到互联网中，需要在互联网中暴露数据库服务地址信息时，要明确设置好白名单信息；替换数据库默认端口，使用SSL远程连接数据库；
• 对业务程序代码做好扫描检测优化，防止出现SQL注入漏洞情况；

2、数据库管理工具应用

pt(percona-toolkit)工具箱应用安装部署：

[root@master ~]# cd /usr/local/
[root@master local]# yum install -y percona-toolkit-3.1.0-2.el7.x86_64.rpm

2.1 数据库服务工具实践-pt-archiver

pt-archiver工具比较适合于大量数据信息的归档操作；
比如：亿级的数据大表，当delete批量删除100w左右数据信息时，就可以使用到此工具；
比如：定期按照时间范围进行归档数据表；
官方资料参考: https://docs.percona.com/percona-toolkit/pt-archiver.html
下载地址: https://www.percona.com/percona-toolkit

工具使用过程重要参数：

序号	参数信息	解释说明
01	–limit 100	每次取100行数据用pt-archive处理
02	–txn-size 100	设置每100行进行一次事务提交操作
03	–where ‘id<3000’	设置操作条件
04	–progress 5000	每处理5000行数据信息，输出一次处理信息的情况
05	–statistics	输出执行过程及最后的操作统计 只要不加上–quiet，默认情况下会输出命令操作的执行过程
06	–charset=UTF8	指定字符集为UTF8(这个参数最后要加上，否则可能会出现乱码)
07	–bulk-delete	批量删除source上的旧数据(例如每次1000行的批量删除操作)
08	–commit-each	提交每组提取和归档的行事务

说明：需要归档表中至少有一个索引，最好是where条件列信息具有索引；

作用：可以实现将数据表中的数据进行归档，迁移恢复到其他数据表中或文件中
命令操作练习：

create database oldboy;
use oldboy;
source ~/t100w_oldboy.sql;
commit;
create table test1 like t100w;
show tables;alter table t100w modify id int not null primary key;
alter table test1 modify id int not null primary key;# 实现数据表信息归档
pt-archiver --source h=10.0.0.51,P=3307,D=oldboy,t=t100w,u=root,p=123 --dest h=10.0.0.51,P=3307,D=oldboy,t=test1,u=root,p=123 --where 'id<10000' --no-check-charset --no-delete --limit=1000 --commit-each --progress 1000 --statistics# 实现数据表信息清理
pt-archiver --source h=10.0.0.51,P=3307,D=oldboy,t=t100w,u=root,p=123 --where 'id<10000' --purge --limit=1 --no-check-charset# 实现数据表信息导出到外部文件
pt-archiver --source h=10.0.0.51,P=3307,D=oldboy,t=t100w,u=root,p=123 --where '1=1' --no-check-charset --no-delete --file="/tmp/archiver.csv"

2.2 数据库服务工具实践-pt-osc

pt-osc工具对于修改表结构、索引创建删除比较擅长，pt工具应用不能加快改写速度，但能减少业务影响，主要是锁对业务的影响；

pt-osc工具应用流程：（面试题）

• 检查更改表是否有主键或唯一键索引信息，并检查是否存在触发器设置；
• 检查要修改的表结构情况，创建一个临时表，在新表上执行alter table语句；

mysql> create table backup like t1;
mysql> alter table backup add telnum char(11) not null;

• 在源表上创建三个触发器分别对于insert update delete操作；

mysql> create trigger;a xx;b xx;c xx;

• 从源表拷贝数据到临时表，在拷贝过程中，对原表的更新操作也会写入到新建的临时表中；

mysql> insert into backup select * from t1;

• 将临时表和源表进行重命名操作rename；（需要利用元数据修改锁，会出现短时间锁表）

• 删除源表和触发器设置，最终完成表结构信息的修改；

pt-osc工具使用限制：

• 源表必须有主键或唯一键索引，如果没有工具将停止工作；
• 如果线上的复制环境过滤器操作过于复杂，工具将无法工作；
• 如果开启复制延迟检查，但主从延迟时，工具将暂停数据拷贝工作；
• 如果开启主服务器负载检查，但主服务器负载较高时，工具将暂停操作；
• 当表使用外键时，如果未使用–alter-foreign-keys-method参数，工具将无法执行；
• 只支持Innodb存储引擎表，且要求服务器上有该表1倍以上的空闲空间；

pt-osc工具应用alter语句限制：

• 不需要包含alter table关键字，可以包含多个修改操作，使用逗号分隔：drop column cl;add column c2 int;
• 不支持rename语句来对表进行重命名操作；
• 不支持对索引进行重命名操作；
• 如果删除外键，需要对外键名加下划线，例如删除外键fk_uid，修改语句为”DROP FOREIGN KEY _fk_uid”

说明：一般使用pt-osc主要用于对索引或表结构，进行添加或删除操作；默认在数据库8.0之后，也可以不使用工具直接修改；

pt-osc工具应用模板：

pt-online-schema-change \
--host="127.0.0.1" \
--port=3358 \
--user="root" \
--password="root@root" \
--charset="utf8" \
--max-lag=10 \
--check-slave-lag='xx.xx.xx.xX' \
--recursion-method="hosts" \
--check-interval=2 
--datebase="testdbl" \
t="tb001" \
--alter="add column c4 int"
--execute# 重点参数解释说明:
--execute: 表示执行参数
--dry-run: 表示只进行模拟测试
其中表名只能使用参数t来设置，没有长参数

pt-osc应用：

pt-online-schema-change --user=root --password=123 --host=10.0.0.51 --port=3307 --alter "add column state int not null default 1" D=oldboy,t=test1 --print --dry-run
pt-online-schema-change --user=root --password=123 --host=10.0.0.51 --port=3307 --alter "add column state int not null default 1" D=oldboy,t=test1 --print --execute

2.3 数据库服务工具实践-pt-table-checksum

pt-table-checksum工具主要用于校验主从数据一致性情况，主要针对数据库或者数据表进行一致性检查；
此工具可以在主从复制时，当SQL线程出现异常报错时，可以利用此工具进行校验检查；

pt-table-checksum工具应用实践：
确认现有主从复制情况：

# 查看主库节点情况：
[root@db01 ~ 09:58]# mysql -S /data/3307/mysql.sock
mysql> select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.00 sec)# 查看从库节点情况：
[root@db01 ~ 10:00]# mysql -S /data/3309/mysql.sock
mysql> select @@port;
+--------+
| @@port |
+--------+
|   3309 |
+--------+
1 row in set (0.00 sec)mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 核实主从关系处于正常状态

主库节点创建校验库和校验用户信息：

# 创建校验使用数据库信息
mysql> create database pt character set utf8;
-- 用于存储pt工具验证主从一致性信息，数据库中的表会自动创建;# 创建校验使用连接用户信息
mysql> create user checksum@'10.0.0.%' identified with mysql_native_password by 'checksum';
mysql> grant all on *.* to checksum@'10.0.0.%';
mysql> flush privileges;

从库设置报告信息

vim /data/3309/my.cnf
[mysqld]
report_host='10.0.0.51'
report_port='3309'mysql -S /data/3309/mysql.sock
select @@report_host;
select @@report_port;mysql -S /data/3307/mysql.sock
show slave hosts;#模拟数据库信息不一致
create database test;
use test
create table t1 (id int,k1 varchar(1));
insert into t1 values (1,'a'),(2,'b'),(3,''),(4,'d'),(5,'e');
-- 主库操作信息-- 从库操作信息
delete from t1 where id=5;# 验证主从数据一致性
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test --tables=t1 h=10.0.0.51,P=3307,u=checksum,p=checksum
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test h=10.0.0.51,P=3307,u=checksum,p=checksum# 执行参数信息说明
--[no]check-replication-filters：表示是否检查复制的过滤器，默认是yes，建议启用不检查模式；
--database|-d：指定需要被检查的数据库，多个库之间可以用逗号分隔;
--[no]-check-binlog-format：是否检查binlog文件的格式，默认是yes，建议开启不检查，因为在默认row格式下会出错；
--replicate：把checksum的信息写入到指定表中；
--replicate-check-only：只显示不同步信息[root@db01 ~ 10:32]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test --tables=t1 h=10.0.0.51,P=3307,u=checksum,p=checksum
Checking if all tables can be checksummed ...
Starting checksum ...
Cannot connect to P=3307,h=,p=...,u=checksum
*******************************************************************Using the default of SSL_verify_mode of SSL_VERIFY_NONE for clientis deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEERpossibly with SSL_ca_file|SSL_ca_path for verification.If you really don't want to verify the certificate and keep theconnection open to Man-In-The-Middle attacks please setSSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************at /usr/bin/pt-table-checksum line 332.
*******************************************************************Using the default of SSL_verify_mode of SSL_VERIFY_NONE for clientis deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEERpossibly with SSL_ca_file|SSL_ca_path for verification.If you really don't want to verify the certificate and keep theconnection open to Man-In-The-Middle attacks please setSSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************at /usr/bin/pt-table-checksum line 332.# A software update is available:TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
10-11T11:42:14      0      1        5          0       1       0   0.089 test.t1mysql> use pt
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select * from checksums;
+------+-----+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db   | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts                  |
+------+-----+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| test | t1  |     1 |   0.004589 | NULL        | NULL           | NULL           | f2890e1c |        4 | 39818021   |          5 | 2024-10-11 11:42:14 |
+------+-----+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
1 row in set (0.00 sec)

2.4 数据库服务工具实践-pt-table-sync

pt-table-sync工具可以对主从不一致的数据信息，进行同步复制修复，实现恢复主从数据的一致性：

pt-table-sync工具应用实践：
工具应用实践：

mysql -S /data/3307/mysql.sock
alter table t1 add primary key (id);
-- 要有主键或者唯一键索引pt-table-sync --replicate=pt.checksums --databases test --tables t1 h=10.0.0.51,u=checksum,p=checksum,P=3307 h=10.0.0.51,u=checksum,p=checksum,P=3309 --print
pt-table-sync --replicate=pt.checksums --databases test --tables t1 h=10.0.0.51,u=checksum,p=checksum,P=3307 h=10.0.0.51,u=checksum,p=checksum,P=3309 --execute

2.5 数据库服务工具实践-pt-duplicate-key-checker

pt-duplicate-key-checker工具主要用于检查数据库重复索引信息；

pt-duplicate-key-checker工具实践应用：

mysql> alter table t1 add index idx_a(k1);
mysql> alter table t1 add index idx_b(k1);
-- 创建重复索引pt-duplicate-key-checker --database=test --host=10.0.0.51 --user=root --password=123 --port=3307
-- 检查是否含有重复索引# 结论
[root@db01 ~ 11:56]# pt-duplicate-key-checker --database=test --host=10.0.0.51 --user=root --password=123 --port=3307
# ########################################################################
# test.t1                                                                 
# ######################################################################### idx_b is a duplicate of idx_a
# Key definitions:
#   KEY `idx_b` (`k1`)
#   KEY `idx_a` (`k1`),
# Column types:
#	  `k1` varchar(20) default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`t1` DROP INDEX `idx_b`;
-- 推荐删除索引，可直接复制删除索引# ########################################################################
# Summary of indexes                                                      
# ######################################################################### Size Duplicate Indexes   83
# Total Duplicate Indexes  1
# Total Indexes            3

pt-kill应用：

# 实践情况-01：杀掉空闲链接sleep 5秒的SQL，并把日志放在/home/pt-kill.log文件中
usr/bin/pt-kill --user=root --password=123 --match-command sleep --idle-time 5 --victim all --interval 5 --kill --daemonize -S /data/3307/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &# 实践情况-02：查询select语句超过1分钟的会话
usr/bin/pt-kill --user=用户名 --password=密码 --busy-time 60 --match-info "select|SELECT" --victim all --interval 5 --kill --daemonize -S /data/3307/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &# 实践情况-03：kill掉查询语句select ..ifnull.* 语句开头的SQL
usr/bin/pt-kill --user=用户名 --password=密码 --victim all --busy-time=0 --match-info "select IFNULL.*" --interval 1 -S /data/3307/mysql.sock --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &# 实践情况-04：kill掉state locked
usr/bin/pt-kill --user=用户名 --password=密码 --victim all --match-state="Locked" --interval 5 --kill --daemonize -S /data/3307/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &# 实践情况-05：kill掉 A库 web为10.0.0.11的连接
usr/bin/pt-kill --user=用户名 --password=密码 --victim all --match-db="a" --match-host='10.0.0.11' --kill --daemonize --interval 10 -S /data/3307/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &# 实践情况-06:指定哪个用户kill
usr/bin/pt-kill --user=用户名 --password=密码 --victim all --match-user="root" --kill --daemonize --interval 10 -S /data/3307/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &# 实际情况-07:kill掉 command query|Execute
usr/bin/pt-kill --user=用户名 --password=密码 --victim all --match-command="query|Execute" --interval 10 --kill --daemonize -S /data/3307/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &# 常用参数解释说明
-- daemonize：放在后台以守护进程的形式运行；
-- interval：多久运行一次，单位时间可以是s，m，h，d；默认是s，参数不设置默认是5秒；
-- victims：默认是oldest，只杀最古老的查询。这是防止被查杀是不是真的长时间运行的查询，只是长期等待，这种匹配按时间查询，杀死一个时间最高值
-- all：杀掉所有满足的线程
-- kill-query：只杀掉连接执行的语句，但是线程不会被终止
-- print：打印满足条件的语句
-- busy-time：批次查询已运行的时间超过这个时间的线程
-- idle-time：杀掉sleep空闲了多少时间的连接线程，必须在--match-command sleep时才有效-也就是匹配使用
-- match-command：匹配相关的语句
-- ignore-command：忽路相关的匹配，这两个搭配使用一定是ignore-command在前，match-command在后
-- match-db cdelzone：匹配哪个库
command: 有Query、sleep、Binlog Dump、Connect、Delayed insert、 Execute、Fetch、Init DB、kill、Prepare、Processlist、Quit、Reset stmt、Table Dumpmysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| checksum         | 10.0.0.%  | mysql_native_password |
| repl             | 10.0.0.%  | mysql_native_password |
| root             | 10.0.0.%  | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
7 rows in set (0.00 sec)mysql> alter user root@'localhost' identified with mysql_native_password by '123';
Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

2.6 数据库服务工具实践-pt-slave-find

pt-slave-find工具主要用于输出主从关系的拓扑结构信息；

pt-slave-find工具应用实践：

[root@db01 ~ 16:34]# pt-slave-find -h10.0.0.51 -P3307 -uchecksum -pchecksum
Cannot connect to P=3307,h=,p=...,u=checksum
10.0.0.51:3307
Version         8.0.26
Server ID       7
Uptime          07:29:34 (started 2024-10-11T09:25:09)
Replication     Is a slave, has 2 slaves connected, is not read_only
Filters         
Binary logging  ROW
Slave status    0 seconds behind, running, no errors
Slave mode      STRICT
Auto-increment  increment 1, offset 1
InnoDB version  8.0.26
+- 10.0.0.51:3309Version         8.0.26Server ID       9Uptime          06:22:27 (started 2024-10-11T10:32:16)Replication     Is a slave, has 0 slaves connected, is not read_onlyFilters         Binary logging  ROWSlave status    0 seconds behind, running, no errorsSlave mode      STRICTAuto-increment  increment 1, offset 1InnoDB version  8.0.26
[root@db01 ~ 16:54]# 

2.7 数据库服务工具实践-pt-heartbeat

pt-heartbeat工具主要用于监控主从延时的情况；

pt-heartbeat工具应用实践：

# 主库进行操作
pt-heartbeat --user=root --ask-pass --host=10.0.0.51 --port=3307 --create-table -D test --interval=1 --update --replace --daemonize
--产生以下进程信息
perl /usr/bin/pt-heartbeat --user=root --ask-pass --host=10.0.0.51 --port=3307 --create-table -D test --interval=1 --update --replace --daemonize#从库进行操作
pt-heartbeat --user=root --ask-pass --host=10.0.0.51 --port=3309 -D test --table=heartbeat --monitor
pt-heartbeat --user=root --password=123 --host=10.0.0.51 --port=3309 -D test --table=heartbeat --monitor --file=/tmp/aa.log &stop slave sql_thread;
-- 可以模拟停止从库SQL线程（stop slave sql_thread；），查看延时情况

2.8 数据库服务工具实践-pt-show-grants

pt-show-grants应用：

pt-show-grants -h10.0.0.51 -P3307 -uchecksum -pchecksum[root@db01 ~ 17:12]# pt-show-grants -h10.0.0.51 -P3307 -uchecksum -pchecksum
-- Grants dumped by pt-show-grants
-- Dumped from server 10.0.0.51 via TCP/IP, MySQL 8.0.26 at 2024-10-11 17:15:13
-- Grants for 'checksum'@'10.0.0.%'
CREATE USER IF NOT EXISTS 'checksum'@'10.0.0.%';
ALTER USER 'checksum'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' AS '*E5E390AF1BDF241B51D9C0DBBEA262CC9407A2DF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROLE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO `checksum`@`10.0.0.%`;
....