DBA数据库笔记之(五)提升MySQL维护效率-PT工具

2024-01-19 本文已影响0人 Mr培

DBA必备的常用开发语言连接MySQL

使用Shell如何对MySQL进行操作

创建测试用户和测试表

create user 'shell_rw'@'%' identifiled by '123456';
grant create,alter,insert,delete,select,update,show view on *.* to 'shell_rw'@'%';
# 创建测试表
... ...

通过linux命令连接MySQL

# 通过命令行访问数据库 -e "sql语句"
mysql -ushell_rw -p'123456' -e "show databases"
mysql -ushell_rw -p'123456' -e "show tables"

通过linux命令行查询MySQL数据

# martin数据库名
mysql -ushell_rw -p'123456' martin -e "select * from t1 limit 1"

在Shell脚本里执行MySQL增删查改

vim mysql_crud.sh

#!/bin/bash
# 设置MySQL连接信息
#设置MySQL连接信息
mysql_host='localhost'
mysql_user='shell_rw!'
mysql_pass='Idyq8_al'
mysql_db='martin'
# 执行写入操作
mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_pass" $mysql_db -e "insert into productes(name,price) values ('apple'10.50)"
# 查询数据
mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_pass" $mysql_db -e "select * from products where name='apple'"
# 修改数据
mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_pass" $mysql_db -e "update products set price=12.5 where name='apple'"
# 删除数据
mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_pass" $mysql_db -e "delete from products where name='apple'"

用ChatGPT帮忙写Shell连接MySQL的脚本

编写一个Shell脚本，查询MySQL8.0当前活跃连接，并导入到当前路径下的文件下，文件名是processlist，当前时间.log,比如processlist_2030101080808.log

用ChatGPT帮忙写多个MySQL参数查询的脚本

ip.txt中，每一行存放MySQL的IP地址、用户名和密码，形式如下，请编写一个shell脚本，提示我们
输入变量名，就可以把所有MySQL的IP，这个变量和变量在MySQL中对应的值展示出192.168.152.70,read_only,xxUagc8_6
192.168152.71,read_only,dagdacag6

用ChatGPT帮忙写统计MySQL信息的shell脚本

编辑一个shell脚本
脚本运行10秒
统计10秒前后MySQL8.0的查询次数 (Queries的变化)
以及这10秒产生的慢查询数量 (Slow_queries的变化)
需要注意这些值，都是在执行 SHOW GLOBAL STATUS like xxx;之后的第二行，需要过滤出来
用户名和密码在脚本前面定义，后面通过用户名密码的变量来连接MySQL

用ChatGPT帮忙写业务表统计信息

编辑一个shell脚本
查information_schema.tables
统计某个库所有表的预估数据量
自增值
存储引擎
每一行展示一张表的信息

使用Python如何对MySQL进行操作

安装python
安装VScode

python操作MySQL数据库

使用Go如何对MySQL进行操作

go教程
Go操作MySQL

详解SQL注入及分析存在哪些风险

select * from users where username='user111' and password='pass111';

使用or的SQL注入

# pass111 变成 ' or 'a'='a
select * from users where username='user111' and password=' ' or 'a'='a ';

使用union的SQL注入

# 输入 ' union select * from users;-- 
select * from users where username=' ' union select * from users;--' and password='pass111';

数据泄露
篡改数据
服务中断

SQL注入演示实验

... ...

如何预防SQL注入

代码中使用预编译形式
对输入进行校验和过滤
权限最小化
关注自己使用的框架或者包是否存在漏洞
审计和监控

DBA必备常用PT工具的用法

PT工具	作用
pt-online-schema-change	不锁表更改表结构
pt-query-digest	分析MySQL查询
pt-kill	kill掉符合特定条件的MySQL查询
pt-table-checksum	验证MySQL复制完整性
pt-table-sync	同步MySQL表数据

pt-osc在线修改表结构

安装找到Percona Toolkit 选择版本，对应系统

# 选择版本，对应系统
wget https://downloads.percona.com/downloads/percona-toolkit/3.5.5/binary/redhat/7/x86_64/percona-toolkit-3.5.5-1.el7.x86_64.rpm?_gl=1*1ec2jux*_gcl_au*ODkzMjE1NzE2LjE2OTcxNjY2MzE.
yum install percona-toolkit-3.5.5-1.el7.x86_64.rpm

# 查看是否安装成功
pt-online-schema-change --version

创建测试用户和测试表

# 使用 mysql_native_password
create user 'pt_osc'@'localhost' identifiled with mysql_native_password by '123456';
grant create,drop,alter,insert,delete,select,update,process,replication slave,trigger on *.* to 'pt_osc'@'localhost';
# 创建测试表，测试数据

哪些版本建议使用pt-osc

MySQL5.7及之前的版本；MySQL8.0.12以及之后的版本InnoDB原生支持快速加列的功能

pt-osc原理分析
用pt-osc增加字段

set global general_log=on;
# 实时打印操作的日志
tail -f /data/mysql/log/mysql-general.log

# 另一窗口
# D=martin 数据库名，t=users_info 表名
pt-online-schema-change -u pt_osc -p '123456' -S /tmp/mysql.sock --alter "add column age int" --execute D=martin,t=users_info

查看general_log，原理总结

创建一张与原始表结构相同的临时表
然后对临时表进行表结构变更
通过触发器实现增量数据处理
将原始表中的数据复制到新表中
把原始表重命名为_x_old，将临时表重命名为x
删除_xxx_old表，删除3个触发器
表结构变更完成

pt-osc更多用法

删除列

pt-online-schema-change -u pt_osc -p '123456' -S /tmp/mysql.sock --alter "drop column age" --execute D=martin,t=users_info

修改列类型

pt-online-schema-change -u pt_osc -p '123456' -S /tmp/mysql.sock --alter "modify column email varchar(100)" --execute D=martin,t=users_info

修改列名

pt-online-schema-change -u pt_osc -p '123456' -S /tmp/mysql.sock --alter "change column name user_name varchar(50)" --execute --no-check-alter D=martin,t=users_info

添加索引

pt-online-schema-change -u pt_osc -p '123456' -S /tmp/mysql.sock --alter "add index index_user_name (user_name)" --execute D=martin,t=users_info

删除索引

pt-online-schema-change -u pt_osc -p '123456' -S /tmp/mysql.sock --alter "drop index index_user_name" --execute D=martin,t=users_info

添加唯一索引的问题

# 如果数据存在相同的数据，然后添加唯一索引，则可能导致数据丢失
pt-online-schema-change -u pt_osc -p '123456' -S /tmp/mysql.sock --alter "add unique key unique_user_name (user_name)" --execute --no-check-unique-key-change  D=martin,t=users_info

使用pt-osc的注意事项

表必须有主键或者唯一索引
保证足够空间
原表上不能有触发器存在

pt-query-digest分析慢查询

SQL指纹

select * from user where user_name='aaa';
select * from user where user_name='bbb';
# 则这两条sql语句拥有相同的指纹
select * from user where user_name= ？

用pt-query-digest分析慢查询

# 查看慢查询是否开启
show global variables like "slow_query%";
# /data/mysql/log/mysql-slow.log 慢查询日志文件
pt-query-digest /data/mysql/log/mysql-slow.log
# 将结果导入文件
pt-query-digest /data/mysql/log/mysql-slow.log > slowquery_digest.log
# 查看分析报告
cat slowquery_digest.log

分析24小时以内的慢查询

pt-query-digest --since=24h /data/mysql/log/mysql-slow.log > 24h_slowquery_digest.log

指定时间范围

pt-query-digest /data/mysql/log/mysql-slow.log  --since '2023-07-11 23:00:00' --until '2023-07-11 23:59:59'  > time_range_slowquery_digest.log

某个用户执行的慢查询

create user 'martin'@'localhost' identifiled with mysql_native_password by '123456';
grant select on *.* to 'martin'@'localhost';
# 执行慢查询测试
mysql -umartin -p'123456' -e "select sleep(2)";
# 分析
pt-query-digest --filter '($event->{user} || "" ) =~ m/^martin/i' /data/mysql/log/mysql-slow.log > martin_slowquery_digest.log

把慢查询结果存放在MySQL中

create user 'slowlog_rw'@'localhost' identifiled with mysql_native_password by '123456';
grant all on slow_log.* to 'slowlog_rw'@'localhost';
# 创建库
create database slow_log;
pt-query-digest --user=slowlog_rw --password=123456 -S /tmp/mysql.sock --review D=slow_log,t=global_query_review --history D=slow_log,t=global_query_review_history /data/mysql/log/mysql-slow.log

分析binlog

mysqlbinlog /data/mysql/binlog/mysql-bin.000028 -vv > binlog.sql
pt-query-digest --type=binlog binlog.sql > binlog_digest.log
cat binlog_digest.log

分析General Log

# 查看general log是否开启
show global variables like "general%";
pt-query-digest --type=genlog /data/mysql/log/mysql-general.log > general_digest.log
cat general_digest.log

pt-kill来kill掉一些垃圾SQL

创建测试用户

create user 'dba'@'localhost' identifiled with mysql_native_password by '123456';
grant all on *.* to 'dba'@'localhost';

打印但不kill问题SQL

# --busy-time=10 查过这个时间就打印；--interval=10 检测的时间间隔10s
pt-kill --user=dba --password='123456' --socket=/tmp/mysql.sock --busy-time=10 --interval=10 --print

kill掉问题SQL

pt-kill --user=dba --password='123456' --socket=/tmp/mysql.sock --busy-time=10 --interval=10 --print --kill

后台运行

# --daemonize 后台运行
pt-kill --user=dba --password='123456' --socket=/tmp/mysql.sock --busy-time=10 --interval=10 --print --kill --daemonize
ps -ef | grep pt-kill

打印日志

pt-kill --user=dba --password='123456' --socket=/tmp/mysql.sock --busy-time=10 --interval=10 --print --kill --daemonize --log=/data/ptkill.log
tail -f /data/ptkill.log

根据用户去杀会话

pt-kill --user=dba --password='123456' --socket=/tmp/mysql.sock --busy-time=10 --interval=10 --print --kill --match-user 'martin'

pt-table-checksum进行主从一致性排查

pt-table-checksum特点

可以用来校验大表
pt-table-checksum的保护策略
监控从库

创建测试用户

# 这个用户要连接上主库和从库
create user 'dba'@'192.168.%' identifiled with mysql_native_password by '123456';
grant all on *.* to 'dba'@'192.168.%';

执行一致性校验

pt-table-checksum --no-check-binlog-format --host=192.168.12.161 --user=dba --password='123456' --socket=/tmp/mysql.sock

只检查指定库

pt-table-checksum --no-check-binlog-format --host=192.168.12.161 --user=dba --password='123456' --socket=/tmp/mysql.sock --databases=martin

只检查指定表

pt-table-checksum --no-check-binlog-format --host=192.168.12.161 --user=dba --password='123456' --socket=/tmp/mysql.sock --databases=martin --tables=pt_checksum

只检查某些字段

pt-table-checksum --no-check-binlog-format --host=192.168.12.161 --user=dba --password='123456' --socket=/tmp/mysql.sock --databases=martin --tables=pt_checksum --columns=nickname

把结果写到表中

create database checksum;
pt-table-checksum --no-check-binlog-format --host=192.168.12.161 --user=dba --password='123456' --socket=/tmp/mysql.sock --databases=martin --replicate=checksum.result
# 查询结果
select * from checksum.result;

pt-table-sync进行主从一致性的修复

输出修复语句

show slave hosts;
# 打印出修复语句；--replicate=把校验结果记录到percona.checksums表里面；--sync-to-master 修复指定的从库
pt-table-sync --print --replicate=percona.checksums --sync-to-master  h=192.168.12.162,u=dba,p=123456
# 输出需要在从库执行的修复语句，解决主从不一致的情况

输出指定库的修复语句

pt-table-sync --print --replicate=percona.checksums --sync-to-master  h=192.168.12.162,u=dba,p=123456 --databases=martin

执行修复操作

pt-table-sync --execute --replicate=percona.checksums --sync-to-master  h=192.168.12.162,u=dba,p=123456 --databases=martin

pt-archiver来进行数据归档

pt-archiver的作用

清理过期的数据
归档数据

创建测试用户

create user 'dba'@'192.168.%' identifiled with mysql_native_password by '123456';
grant all on *.* to 'dba'@'192.168.%';

创建测试表和数据
... ...
全表归档不删除原表数据

# --source 要归档的表;--dest 存放的实例;--where '1=1' 归档全表数据；--progress 1000每一万行打印进度信息；--limit=10000 一次查询一万行；--txn-size=10000设置一万行为一次事务提交一次；--no-safe-auto-increment 不加这个参数，最大的记录归档不了，加了则可能MySQL重启之后再次归档出现主键冲突问题；--statistics 搜集并打印搜集的数据；--no-delete 不删除原表数据

pt-archiver --source h=192.168.12.161,u=dba,p='123456',D=martin,t=archiver_test --dest h=192.168.12.162,u=dba,p='123456',D=archiver_db,t=archiver_test --where '1=1' --progress 1000 --limit=10000 --txn-size=10000 --no-safe-auto-increment --statistics --no-delete

全部归档删除原表数据

pt-archiver --source h=192.168.12.161,u=dba,p='123456',D=martin,t=archiver_test --dest h=192.168.12.162,u=dba,p='123456',D=archiver_db,t=archiver_test --where '1=1' --progress 1000 --limit=10000 --txn-size=10000 --no-safe-auto-increment --statistics --purge

不归档直接删除原表数据

pt-archiver --source h=192.168.12.161,u=dba,p='123456',D=martin,t=archiver_test --where '1=1' --progress 1000 --limit=10000 --txn-size=10000 --no-safe-auto-increment --statistics --purge

按条件归档部分数据

pt-archiver --source h=192.168.12.161,u=dba,p='123456',D=martin,t=archiver_test --dest h=192.168.12.162,u=dba,p='123456',D=archiver_db,t=archiver_test --where 'age<30' --progress 1000 --limit=10000 --txn-size=10000 --no-safe-auto-increment --statistics --purge

归档到文件

pt-archiver --source h=192.168.12.161,u=dba,p='123456',D=martin,t=archiver_test --where 'age<30' --progress 1000 --limit=10000 --txn-size=10000 --no-safe-auto-increment --statistics --purge --file=archiver.sql
# 指定文件格式 --output-format=csv
pt-archiver --source h=192.168.12.161,u=dba,p='123456',D=martin,t=archiver_test --where 'age<30' --progress 1000 --limit=10000 --txn-size=10000 --no-safe-auto-increment --statistics --purge --file=archiver.csv --output-format=csv

通过ChatGPT编写自动化归档脚本

设计一张MySQL日志表，包括日志信息，记录时间和主键
通过pt-archiver，帮忙写一个martin库的logs表归档的shell脚本，这个实例的IP是192.168.12.161，超过30天的数据归档到实例192.168.12.163中的archiver_db中的logs表中。两个数据库的连接用户都是dba，密码是Id81Gdac_a

其它一些PT工具

信息汇总工具pt-mysql-summary

用法

pt-mysql-summary --user=dba --password='123456' --host=192.168.12.161

参数分析工具pt-variable-advisor

用法

pt-variable-advisor h=192.168.12.161,u=dba,p=123456

复制拓扑发现工具pt-slave-find

pt-slave-find h=192.168.12.161,u=dba,p=123456

只显示复制拓扑

pt-slave-find h=192.168.12.161,u=dba,p=123456 --report-format=hostname

参数差异对比工具pt-config-diff

对比两个实例的配置文件

pt-config-diff h=192.168.12.161,u=dba,p=123456 h=192.168.12.162,u=dba,p=123456

对比mysql服务中的配置和配置文件

pt-config-diff /data/mysql/conf/my.cnf h=192.168.12.161,u=dba,p=123456

对比两个配置文件

pt-config-diff /data/mysql/conf/my.cnf /data/my.cnf

权限查看工具pt-show-grants

pt-show-grants h=192.168.12.161,u=dba,p=123456

增加drop user命令

pt-show-grants h=192.168.12.161,u=dba,p=123456 --drop

查找重复索引工具pt-duplicate-key-checker

pt-duplicate-key-checker h=192.168.12.161,u=dba,p=123456

查看磁盘IO状态工具pt-diskstats

pt-diskstats

监控某块磁盘的IO

pt-diskstats --device sda