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
使用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在线修改表结构
# 选择版本，对应系统
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
