Mysql之pt工具集
1. percona-toolkit介绍
percona-toolkit是一组高级命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的mysql和系统任务，这些任务包括：
1）检查master和slave数据的一致性
2）有效地对记录进行归档
3）查找重复的索引
4）对服务器信息进行汇总
5）分析来自日志和tcpdump的查询
6）当系统出问题的时候收集重要的系统信息
percona-toolkit工具中最主要的三个组件分别是：
1）pt-table-checksum 负责监测mysql主从数据一致性
2）pt-table-sync 负责当主从数据不一致时修复数据，让它们保存数据的一致性
3）pt-heartbeat 负责监控mysql主从同步延迟
2. 安装percona-toolkit工具
建议主从上都安装此工具
1. 使用yum方式安装
sudo yum install -y percona-toolkit --nogpgcheck
或者下载rpm包，然后执行
yum -y install perl-devel perl-Digest-MD5 perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL.noarch perl-Time-HiRe
wget https://www.percona.com/downloads/percona-toolkit/3.0.6/binary/redhat/7/x86_64/percona-toolkit-3.0.6-1.el7.x86_64.rpm
yum -y localinstall percona-toolkit-3.0.6-1.el7.x86_64.rpm
2. 使用编译安装
yum -y install perl-devel perl-Digest-MD5 perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL.noarch perl-Time-HiRes
tar zxvf percona-toolkit-<version>.tar.gz
cd percona-toolkit-<version>
perl Makefile.PL
make
make test
make install
3. 常用工具功能介绍
1. pt-table-checksum
pt-table-checksum 是 Percona-Toolkit的组件之一，用于检测MySQL主、从库的数据是否一致。其原理是在主库执行基于statement的sql语句来生成主库数据块的checksum，把相同的sql语句传递到从库执行，并在从库上计算相同数据块的checksum，最后，比较主从库上相同数据块的checksum值，由此判断主从数据是否一致。检测过程根据唯一索引将表按row切分为块（chunk），以块为单位计算，可以避免锁表。检测时会自动判断复制延迟、 master的负载， 超过阀值后会自动将检测暂停，减小对线上服务的影响。
为了保证主数据库服务的安全，该工具实现了许多保护措施：
1）自动设置 innodb_lock_wait_timeout 为1s，避免引起
2）默认当数据库有25个以上的并发查询时，pt-table-checksum会暂停。可以设置 --max-load 选项来设置这个阀值
3）当用 Ctrl+C 停止任务后，工具会正常的完成当前 chunk 检测，下次使用 --resume 选项启动可以恢复继续下一个 chunk
参数意义： –-nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用–databases来指定需要检查的数据库。 –-no-check-binlog-format : 不检查复制的binlog模式，要是binlog模式是ROW，则会报错。 –-replicate-check-only :只显示不同步的信息。 –-replicate= ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中 –-databases= ：指定需要被检查的数据库，多个则用逗号隔开。 --tables= ：指定需要被检查的表，多个用逗号隔开 --recursion-method=： 主机信息 h=172.16.10.53 ：Master的地址 u=checksums ：用户名 p=checksums ：密码 P=3306 ：端口
注意：
1.需要一个既能登录主库，也能登录从库的账号，权限一般SELECT, PROCESS, SUPER, REPLICATION SLAVE等权限就已经足够了
2.只能指定一个host,必须为主库的IP
3.在检查时会向表加S锁
4.运行之前需要检查从库的同步IO和SQL进程是YES状态
实例：
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=huanqiu.checksums --create-replicate-table --databases=huanqiu --tables=haha h=192.168.1.101,u=root,p=123456,P=3306
第一次运行添加--create-replicate-table参数，后续再运行时就不需要加了
1 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 2 01-08T04:11:03 0 0 4 1 0 1.422 huanqiu.haha 解释： TS ：完成检查的时间。 ERRORS ：检查时候发生错误和警告的数量。 DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。 ROWS ：表的行数。 CHUNKS ：被划分到表中的块的数目。 SKIPPED ：由于错误或警告或过大，则跳过块的数目。 TIME ：执行的时间。 TABLE ：被检查的表名。
在从库上执行：select * from huanqiu.checksums where master_cnt<>this_cnt OR master_crc<>this_crc OR ISNULL(master_crc)<>ISNULL(this_crc);
checksums表一旦产生，不仅这张表默认删除不了，连同它所在的库，要是想删除它们，只能如上操作先撤销权限
2. pt-table-sync
1. 功能介绍：高效的同步MySQL表之间的数据，他可以做单向和双向同步的表数据。他可以同步单个表，也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。
2. 参数意义
–replicate= ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。 –databases= : 指定执行同步的数据库，多个用逗号隔开。 –tables= ：指定执行同步的表，多个用逗号隔开。 –sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。 h=127.0.0.1 ：服务器地址，命令里有2个ip，第一次出现的是M的地址，第2次是Slave的地址。 u=root ：帐号。 p=123456 ：密码。 –print ：打印，但不执行命令。 –execute ：执行命令。
3. 实例
pt-table-sync --replicate=huanqiu.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456 --print 打印出修复数据的sql，可以手动在slave从库上执行
pt-table-sync --replicate=huanqiu.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456 --execute 执行修复操作
3. pt-archive ----MySQL的在线归档，无影响生产
1. 数据库连接参数
A 字符编码 D 库 F 从文件读取选项 L 加载数据本地文件 P 端口 S socket文件 a 执行查询的数据库 b 如果是true, 禁用SQL_LOG_BIN h 数据库地址 i 查询使用的索引 m 插件模块名称 p 数据库密码 t 表 u 用户名
2. 常用参数
--limit 10000 每次取1000行数据用pt-archive处理，Number of rows to fetch and archive per statement. --txn-size 1000 设置1000行为一个事务提交一次，Number of rows pertransaction. --where 'id<3000' 设置操作条件 --progress 5000 每处理5000行输出一次处理信息 --statistics 输出执行过程及最后的操作统计 --charset=UTF8 指定字符集为UTF8 --bulk-delete 批量删除source上的旧数据(例如每次1000行的批量删除操作) --bulk-insert 批量插入数据到dest主机 (看dest的general log发现它是通过在dest主机上LOAD DATA LOCAL INFILE插入数据的) --replace 将insert into 语句改成replace写入到dest库 --sleep 120 每次归档了limit个行记录后的休眠120秒（单位为秒） --file '/root/test.txt' 导出的文件路径 --purge 删除source数据库的相关匹配记录 --header 输入列名称到首行（和--file一起使用） -no-check-charset 不指定字符集 --check-columns 检验dest和source的表结构是否一致，不一致自动拒绝执行（不加这个参数也行。默认就是执行检查的） --no-check-columns 不检验dest和source的表结构是否一致，不一致也执行（会导致dest上的无法与source匹配的列值被置为null或者0） --chekc-interval 默认1s检查一次 --local 不把optimize或analyze操作写入到binlog里面（防止造成主从延迟巨大） --retries 超时或者出现死锁的话，pt-archiver进行重试的间隔（默认1s） --no-version-check 目前为止，发现部分pt工具对阿里云RDS操作必须加这个参数 --analyze=ds 操作结束后，优化表空间（d表示dest，s表示source）
3. 例子
1. 删除旧数据
pt-archiver \ --source h=localhost,u=root,p=1234,P=3306,D=test,t=t \ --no-check-charset --where ‘a<=376‘ --limit 10000 --txn-size 1000 --purge
2. 复制数据到其他mysql实例，且不删除source的数据(指定字符集)
/usr/bin/pt-archiver \ --source h=localhost,u=root,p=1234,P=3306,D=test,t=t1\ --dest h=192.168.2.12,P=3306,u=archiver,p=archiver,D=test,t=t1_bak \ --progress 5000 --where 'mc_id<=125' \ --statistics --charset=UTF8 --limit=10000 --txn-size 1000 --no-delete
3. 复制数据到其他mysql实例，并删source上的旧数据(指定字符集)
/usr/bin/pt-archiver \ --source h=localhost,u=root,p=1234,P=3306,D=test,t=t1 \ --dest h=192.168.2.12,P=3306,u=archiver,p=archiver,D=test,t=t1_his \ --progress 5000 --where "CreateDate <‘2017-05-01 00:00:00‘ " \ --statistics --charset=UTF8 --limit=10000 --txn-size 1000 --bulk-delete
4. 复制数据到其他mysql实例，不删除source数据，但是使用批量插入dest上新的数据(指定字符集)
/usr/bin/pt-archiver \ --source h=localhost,u=archiver,p=archiver,P=3306,D=test,t=t1 \ --dest h=192.168.2.12,P=3306,u=archiver,p=archiver,D=test,t=t1_his \ --progress 5000 --where "c <‘2017-05-01 00:00:00‘ " \ --statistics --charset=UTF8 --limit=10000 --txn-size 1000 --no-delete --bulk-insert
5. 导出数据到文件
/usr/bin/pt-archiver \ --source h=10.0.20.26,u=root,p=1234,P=3306,D=test,t=t \ --file ‘/root/test.txt‘ \ --progress 5000 --where ‘a<12000‘ \ --no-delete --statistics --charset=UTF8 --limit=10000 --txn-size 1000
6. 导出数据到文件并删除数据库的相关行
/usr/bin/pt-archiver \ --source h=10.0.20.26,u=root,p=1234,P=3306,D=test,t=t \ --file ‘/root/test.txt‘ \ --progress 5000 --where ‘a<12000‘ \ --statistics --charset=UTF8 --limit=10000 --txn-size 1000 --purge
4. pt-summary 显示和系统相关的信息
# Percona Toolkit System Summary Report ######################
Date | 2020-11-06 05:31:26 UTC (local TZ: CST +0800)
Hostname | nginx-b-h5test-zpdc-zptest-118
Uptime | 1234 days, 20:49, 4 users, load average: 1.40, 0.89, 0.63
Platform | Linux
Release | CentOS release 6.5 (Final)
Kernel | 2.6.32-431.el6.x86_64
Architecture | CPU = 64-bit, OS = 64-bit
Threading | NPTL 2.12
SELinux | Disabled
Virtualized | VMWare
# Processor ##################################################
Processors | physical = 2, cores = 8, virtual = 8, hyperthreading = no
Speeds | 8x2599.999
Models | 8xIntel(R) Xeon(R) CPU E5-2670 0 @ 2.60GHz
Caches | 8x20480 KB
# Memory #####################################################
Total | 15.6G
Free | 266.5M
Used | physical = 15.3G, swap allocated = 7.8G, swap used = 3.4G, virtual = 18.8G
Buffers | 142.6M
Caches | 1.6G
Dirty | 140 kB
UsedRSS | 13.7G
Swappiness | 60
DirtyPolicy | 20, 10
DirtyStatus | 0, 0
# Mounted Filesystems ########################################
Filesystem Size Used Type Opts Mountpoint
/dev/sda1 194M 15% ext4 rw /boot
/dev/sda3 485G 46% ext4 rw /
tmpfs 7.8G 1% tmpfs rw /dev/shm
# Disk Schedulers And Queue Size #############################
sda | [cfq] 128
sr0 | [cfq] 128
# Disk Partioning ############################################
Device Type Start End Size
============ ==== ========== ========== ==================
/dev/sda Disk 537944653824
/dev/sda1 Part 1 26 205632000
/dev/sda2 Part 26 1046 8389785600
/dev/sda3 Part 1046 65271 528268608000
# Kernel Inode State #########################################
dentry-state | 42865 34149 45 0 0 0
file-nr | 4672 0 65535
inode-nr | 59101 27105
# LVM Volumes ################################################
Unable to collect information
# LVM Volume Groups ##########################################
Unable to collect information
# RAID Controller ############################################
Controller | No RAID controller detected
# Network Config #############################################
Controller | VMware VMXNET3 Ethernet Controller (rev 01)
Controller | VMware VMXNET3 Ethernet Controller (rev 01)
FIN Timeout | 1
Port Range | 65535
# Interface Statistics #######################################
interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors
========= ========= ========== ========== ========== ========== ==========
lo 3000000000 500000000 0 3000000000 500000000 0
eth0 350000000 3500000000 0 3500000000 3000000000 0
eth1 700000000 1250000000 0 900000000 150000000 0
# Network Devices ############################################
Device Speed Duplex
========= ========= =========
eth0 10000Mb/s Full
eth1 10000Mb/s Full
# Network Connections ########################################
Connections from remote IP addresses
192.168.1.130 5
192.168.1.201 2
Connections to local IP addresses
192.168.1.118 7
Connections to top 10 local ports
11984 3
15851 1
24980 1
51138 1
56804 1
States of connections
ESTABLISHED 175
LISTEN 20
TIME_WAIT 9
# Top Processes ##############################################
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4030 root 20 0 2379m 1.8g 5056 S 1.9 11.6 50919:47 prometheus
9507 root 20 0 15156 1340 880 R 1.9 0.0 0:00.01 top
29150 mysql 20 0 15.2g 9.8g 8456 S 1.9 63.2 14574:34 mysqld
1 root 20 0 19232 372 204 S 0.0 0.0 0:19.99 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 1:59.64 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 8:14.50 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
6 root RT 0 0 0 0 S 0.0 0.0 3:02.88 watchdog/0
# Notable Processes ##########################################
PID OOM COMMAND
21625 -17 sshd
21476 -17 udevd
21477 -17 udevd
# Simplified and fuzzy rounded vmstat (wait please) ##########
procs ---swap-- -----io---- ---system---- --------cpu--------
r b si so bi bo ir cs us sy il wa st
1 0 0 0 80 90 0 0 1 0 98 0 0
0 0 0 0 0 0 3500 1750 3 4 93 0 0
0 0 0 0 0 150 1500 1000 2 0 98 0 0
6 0 0 0 0 0 2250 700 16 0 84 0 0
3 0 0 0 0 0 1250 350 5 0 94 0 0
# The End ####################################################
5. pt-mysql-summary 查看mysql的统计信息
pt-mysql-summary -S /data/mysql/mysql.sock --user=root --host=localhost --port=3306 --password=123456
mysql: [Warning] Using a password on the command line interface can be insecure.
# Percona Toolkit MySQL Summary Report #######################
System time | 2020-11-06 06:14:44 UTC (local TZ: CST +0800)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
3308 /data/mysql/mysql3308/data 0 0 /data/mysql/mysql3308/data/mysql.sock
3307 /data/mysql/mysql3307/data 0 0 /data/mysql/mysql3307/data/mysql.sock
# MySQL Executable ###########################################
Path to executable | /usr/local/mysql/bin/mysqld
Has symbols | Yes
# Report On Port 3307 ########################################
User | root@localhost
Time | 2020-11-06 14:14:44 (CST)
Hostname | nginx-b-h5test-zpdc-zptest-118
Version | 5.7.25-log MySQL Community Server (GPL)
Built On | linux-glibc2.12 x86_64
Started | 2020-05-13 18:14 (up 176+20:00:03)
Databases | 33
Datadir | /data/mysql/mysql3307/data/
Processes | 150 connected, 1 running
Replication | Is a slave, has 0 slaves connected
Pidfile | /data/mysql/mysql3307/data/mysql.pid (exists)
# Processlist ################################################
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Query 1 1 0 0
Sleep 150 0 500000 15000
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
ro_all_db 4 0 0 0
root 1 1 0 0
rw_all_db 150 0 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
192.168.1.130 8 0 0 0
192.168.1.131 20 0 0 0
192.168.1.161 1 0 0 0
192.168.1.162 2 0 0 0
192.168.1.163 4 0 0 0
192.168.1.164 45 0 0 0
192.168.1.165 70 0 0 0
192.168.1.172 2 0 0 0
192.168.1.231 4 0 0 0
192.168.1.232 4 0 0 0
192.168.1.233 2 0 0 0
192.168.1.24 1 0 0 0
192.168.1.47 1 0 0 0
192.168.1.63 1 0 0 0
localhost 1 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
activity 1 0 0 0
NULL 5 1 0 0
shop_zp 80 0 0 0
user_behavior 25 0 0 0
zhen_base 1 0 0 0
zhen_cms 3 0 0 0
zhen_finance 6 0 0 0
zhen_jobs 15 0 0 0
zhen_stock 10 0 0 0
zpprovider 20 0 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
150 0 0 0
starting 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 10 secs
Aborted_clients 500
Aborted_connects 250000 3
Binlog_cache_use 7000
Binlog_stmt_cache_use 1500
Bytes_received 900000000 10000 600
Bytes_sent 2000000000 22500 5000
Com_admin_commands 2250
Com_alter_table 1
Com_change_db 1500
Com_commit 200000 2
Com_create_db 700
Com_create_table 700
Com_delete 1000
Com_insert 4000
Com_rollback 40000
Com_rollback_to_savepoint 4
Com_select 2250000 25 3
Com_set_option 450000 5 1
Com_show_collations 1
Com_show_create_func 25
Com_show_create_proc 35
Com_show_create_table 3000
Com_show_databases 800
Com_show_engine_status 1500
Com_show_fields 225
Com_show_function_status 4
Com_show_keys 30
Com_show_master_status 125
Com_show_procedure_status 4
Com_show_processlist 35000
Com_show_profiles 25
Com_show_slave_status 1500
Com_show_status 1750
Com_show_storage_engines 4
Com_show_table_status 30
Com_show_tables 800
Com_show_triggers 45
Com_show_variables 2250
Com_show_warnings 15000
Com_update 12500
Connections 300000 3 1
Created_tmp_disk_tables 600 1
Created_tmp_files 10
Created_tmp_tables 350000 4 6
Handler_commit 1500000 20 1
Handler_delete 4000
Handler_external_lock 8000000 90 1
Handler_prepare 40000
Handler_read_first 175000 2 1
Handler_read_key 17500000 225 1
Handler_read_last 150
Handler_read_next 90000000 1000
Handler_read_prev 3500000 45
Handler_read_rnd 225000 2
Handler_read_rnd_next 4000000000 45000 100
Handler_rollback 2250
Handler_savepoint_rollback 4
Handler_update 1500000 15
Handler_write 2000000 25 40
Innodb_buffer_pool_bytes_data 45000000 500
Innodb_buffer_pool_pages_flushed 90000
Innodb_buffer_pool_read_ahead 2500
Innodb_buffer_pool_read_ahead_evicted 60
Innodb_buffer_pool_read_requests 800000000 9000 35
Innodb_buffer_pool_reads 2250
Innodb_buffer_pool_write_requests 3500000 40 25
Innodb_data_fsyncs 35000
Innodb_data_read 80000000 900
Innodb_data_reads 4500
Innodb_data_writes 100000 1
Innodb_data_written 3000000000 35000 7000
Innodb_dblwr_pages_written 80000
Innodb_dblwr_writes 8000
Innodb_log_write_requests 700000 7
Innodb_log_writes 12500
Innodb_os_log_fsyncs 15000
Innodb_os_log_written 350000000 4000
Innodb_pages_created 600
Innodb_pages_read 4500
Innodb_pages_written 90000
Innodb_row_lock_time 30000
Innodb_row_lock_waits 700
Innodb_rows_deleted 4000
Innodb_rows_inserted 125000 1 4
Innodb_rows_read 4000000000 45000 25
Innodb_rows_updated 1250000 15
Innodb_num_open_files 4
Key_read_requests 1250000 15
Key_reads 125
Key_write_requests 50
Key_writes 50
Open_table_definitions 7
Opened_files 12500 1
Opened_table_definitions 600
Opened_tables 4500
Performance_schema_digest_lost 100000 1 1
Performance_schema_file_instances_lost 1
Queries 3000000 35 9
Questions 3000000 35 9
Select_full_join 22500
Select_full_range_join 20
Select_range 12500
Select_scan 150000 1 1
Slow_queries 80
Sort_merge_passes 15
Sort_range 17500
Sort_rows 1000000 10
Sort_scan 125000 1
Table_locks_immediate 80000
Table_open_cache_hits 4000000 45
Table_open_cache_misses 4500
Table_open_cache_overflows 4500
Threads_created 1
Uptime 90000 1 1
# Table cache ################################################
Size | 2000
Usage | 100%
# Key Percona Server features ################################
Table & Index Stats | Not Supported
Multiple I/O Threads | Enabled
Corruption Resilient | Not Supported
Durable Replication | Not Supported
Import InnoDB Tables | Not Supported
Fast Server Restarts | Not Supported
Enhanced Logging | Not Supported
Replica Perf Logging | Disabled
Response Time Hist. | Not Supported
Smooth Flushing | Not Supported
HandlerSocket NoSQL | Not Supported
Fast Hash UDFs | Unknown
# Percona XtraDB Cluster #####################################
# Plugins ####################################################
InnoDB compression | ACTIVE
# Query cache ################################################
query_cache_type | OFF
Size | 0.0
Usage | 0%
HitToInsertRatio | 0%
# Schema #####################################################
Specify --databases or --all-databases to dump and summarize schemas
# Noteworthy Technologies ####################################
SSL | No
Explicit LOCK TABLES | No
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | No
Prepared statement count | 0
# InnoDB #####################################################
# MyISAM #####################################################
Key Cache | 500.0M
Pct Used | 20%
Unflushed | 0%
# Security ###################################################
Users |
Old Passwords | 0
# Binary Logging #############################################
Binlogs | 9
Zero-Sized | 0
Total Size | 8.2G
binlog_format | MIXED
expire_logs_days | 60
sync_binlog | 1
server_id | 1183307
binlog_do_db |
binlog_ignore_db |
# Noteworthy Variables #######################################
Auto-Inc Incr/Offset | 1/1
default_storage_engine | InnoDB
flush_time | 0
init_connect |
init_file |
sql_mode | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
join_buffer_size | 32M
sort_buffer_size | 32M
read_buffer_size | 32M
read_rnd_buffer_size | 32M
bulk_insert_buffer | 0.00
max_heap_table_size | 128M
tmp_table_size | 128M
max_allowed_packet | 64M
thread_stack | 256k
log |
log_error | /data/mysql/mysql3307/data/error.log
log_warnings | 1
log_slow_queries |
log_queries_not_using_indexes | OFF
log_slave_updates | ON
# Configuration File #########################################
Config File | /etc/my.cnf
[mysqld]
user = mysql
port = 3307
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3307/data/
tmpdir = /data/mysql/mysql3307/tmp/
slave-load-tmpdir = /data/mysql/mysql3307/tmp/
socket = /data/mysql/mysql3307/data/mysql.sock
log-error = /data/mysql/mysql3307/data/error.log
pid-file = /data/mysql/mysql3307/data/mysql.pid
character-set-server = utf8
collation-server = utf8_bin
log_timestamps = SYSTEM
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
key_buffer_size = 500M
max_heap_table_size = 128M
tmp_table_size = 128M
join_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
default-storage-engine = InnoDB
innodb_data_home_dir = /data/mysql/mysql3307/data/
innodb_log_group_home_dir = /data/mysql/mysql3307/data/
innodb_buffer_pool_size = 8G
innodb_data_file_path = ibdata1:20M:autoextend
innodb_status_file = 1
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_io_capacity = 2000
innodb_lock_wait_timeout = 60
innodb_log_buffer_size = 32M
innodb_log_files_in_group = 2
innodb_log_file_size = 100M
innodb_rollback_on_timeout
innodb_support_xa = 1
log-bin = /data/mysql/mysql3307/logs/mysql-bin
log-bin-index = /data/mysql/mysql3307/logs/mysql-bin.index
relay-log = /data/mysql/mysql3307/logs/relay-bin
relay-log-index = /data/mysql/mysql3307/logs/relay-bin.index
server-id = 1183307
binlog_format = MIXED
binlog_cache_size = 64M
max_allowed_packet = 64M
max_binlog_cache_size = 10G
expire_logs_days = 60
log_warnings = 1
slow_query_log
slow_query_log_file = /data/mysql/mysql3307/logs/slow.log
long_query_time = 1
log_slave_updates = 1
relay_log_recovery = 1
group_concat_max_len = 1024
transaction_isolation = REPEATABLE-READ
query_cache_size = 0
query_cache_type = 0
skip-symbolic-links
skip-external-locking
skip-name-resolve
back_log = 200
max_connections = 1000
max_user_connections = 800
max_connect_errors = 99999
open_files_limit = 65535
lock_wait_timeout = 120
net_read_timeout = 120
net_write_timeout = 120
thread_cache_size = 300
innodb_max_dirty_pages_pct = 75
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
log_slave_updates = ON
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no-auto-rehash
default-character-set = utf8
[client]
port = 3307
socket = /data/mysql/mysql3307/data/mysql.sock
# The End ####################################################
6. pt-salve-find 查看指定主服务器的从服务器信息
pt-slave-find --host=10.10.76.96 --port=3306 --user=root --password=123456
Localhost:3306 Version 5.7.25-log Server ID 1183307 Uptime 176+21:14:58 (started 2020-05-13T18:14:41) Replication Is a slave, has 0 slaves connected, is not read_only Filters Binary logging MIXED Slave status seconds behind, not running, no errors Slave mode STRICT Auto-increment increment 1, offset 1 InnoDB version 5.7.25 You have new mail in /var/spool/mail/root
5. pt-query-digest 慢日志查询
pt-query-digest slow.log-20200430 > /tmp/1.txt
6. pt-online-schema-change 在线执行DDL操作
正常在线修改表结构，流程如下： 在线上直接操作的话会导致大量的写入阻塞，临时表可能会导致磁盘空间占满等问题
①按照原始表（original_table）的表结构和DDL语句，新建一个不可见的临时表（tmp_table） ②在原表上加write lock，阻塞所有更新操作（insert、delete、update等） ③执行insert into tmp_table select * from original_table ④rename original_table和tmp_table，最后drop original_table ⑤释放 write lock
使用pt-online-schema-change，流程如下：
1. 创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)
2. 在新表执行alter table 语句（速度应该很快）
3. 在原表中创建触发器3个触发器分别对应insert,update,delete操作
4. 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表
5. Rename 原表到old表中，在把临时表Rename为原表
6. 如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理
7. 默认最后将旧原表删除如果执行失败了，或手动停止了，需要手动删除下划线开头的表(_表名)及三个触发器 参数：
--check-replication-filters 检查从库
--nocheck-replication-filters 不检查从库
例子：
添加字段 pt-online-schema-change --user=root --password=123456 --host=192.168.200.25 --alter "ADD COLUMN content text" D=aaa,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute 删除字段 pt-online-schema-change --user=root --password=123456 --host=192.168.200.25 --alter "DROP COLUMN content " D=aaa,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute 修改字段 pt-online-schema-change --user=root --password=123456 --host=192.168.200.25 --alter "MODIFY COLUMN age TINYINT NOT NULL DEFAULT 0" D=aaa,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute 字段改名 pt-online-schema-change --user=root --password=123456 --host=192.168.200.25 --alter "CHANGE COLUMN age address varchar(30)" D=aaa,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute 增加索引 pt-online-schema-change --user=root --password=123456 --host=192.168.200.25 --alter "ADD INDEX idx_address(address)" D=aaa,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute 删除索引 pt-online-schema-change --user=root --password=123456 --host=192.168.200.25 --alter "DROP INDEX idx_address" D=aaa,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute
参考文章： https://www.cnblogs.com/wsl222000/p/9072750.html
pt-online-schema-change --host=10.10.191.89 --user=root --password=123456 --port=3306 --no-drop-old-table --execute --alter "add column app_status char(1) default 0" D=test111,t=tbl_app_table
实战例子：
995 pt-online-schema-change -uroot -p'' -h192.168.1.200 -P3306 --charset=utf8 --alter="ADD COLUMN bn varchar(100) NULL COMMENT '产品数字货号'" D=shop_zp,t=gt_task_edit_original --no-drop-old-table --recursion-method=none --print --execute 997 pt-online-schema-change -uroot -p'' -h192.168.1.200 -P3306 --charset=utf8 --alter="ADD COLUMN bn varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品数字货号'" D=shop_zp,t=gt_cooperators_product --no-drop-old-table --recursion-method=none --print --execute 998 pt-online-schema-change -uroot -p'' -h192.168.1.200 -P3306 --charset=utf8 --alter="MODIFY COLUMN bn varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品数字货号'" D=shop_zp,t=gt_products --no-drop-old-table --recursion-method=none --print --execute
7. pt-kill工具
1. 常用参数
① --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-commandd在前 match-command在后， ① --match-db cdelzone 匹配哪个库 ① command有： Query、Sleep、Binlog Dump、Connect、Delayed insert、Execute、Fetch、Init DB、Kill、Prepare、Processlist、Quit、Reset stmt、Table Dump
2. 实战例子
-- 1、每10秒检查一次，发现有 Query 的进程就给干掉 # 只打印-每10秒检查一次，发现有 Query 的进程就给干掉 pt-kill --host=192.168.65.128 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-command="Query" --busy-time 30 --victims all --interval 10 --daemonize --print --log=/tmp/1.log # 执行杀操作 pt-kill --host=192.168.65.128 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-command="Query" --busy-time 30 --victims all --interval 10 --daemonize --kill --log=/tmp/kill.log -- 2、查杀select大于30s的会话 # 只打印-查杀select大于30s的会话 pt-kill --host=192.168.65.128 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-info "select|SELECT" --busy-time 30 --victims all --interval 10 --daemonize --print --log=/tmp/pt_select.log # 执行杀操作-查杀select大于30s的会话 pt-kill --host=192.168.65.128 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-info "select|SELECT" --busy-time 30 --victims all --interval 10 --daemonize --kill --log=/tmp/pt_select_kill.log -- 3、查杀某IP来源的会话 # 只打印-查杀某IP来源的会话 pt-kill --host=192.168.65.128 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-host "192.168.65.129" --busy-time 30 --victims all --interval 10 --daemonize --print --log=/tmp/pt_select.log # 执行杀操作-查杀某IP来源的会话 pt-kill --host=192.168.65.128 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-host "192.168.65.129" --busy-time 30 --victims all --interval 10 --daemonize --kill --log=/tmp/pt_select_kill.log -- 4、查杀访问某用户的会话 # 只打印-查杀访问某用户的会话 pt-kill --host=192.168.65.128 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-user "u2" --busy-time 30 --victims all --interval 10 --daemonize --print --log=/tmp/pt_select.log # 执行杀操作-查杀访问某用户的会话 pt-kill --host=192.168.65.128 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-user "u2" --busy-time 30 --victims all --interval 10 --daemonize --kill --log=/tmp/pt_select_kill.log -- 5、杀掉正在进行filesort的sql # 只打印-杀掉正在进行filesort的sql pt-kill --host=192.168.65.128 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-command Query --match-state "Sorting result" --run-time 1 --busy-time 30 --victims all --interval 10 --daemonize --print --log=/tmp/pt_select.log # 执行杀操作-杀掉正在进行filesort的sql pt-kill --host=192.168.65.128 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-command Query --match-state "Sorting result" --run-time 1 --busy-time 30 --victims all --interval 10 --daemonize --kill --log=/tmp/pt_select_kill.log # 只打印 pt-kill --host=192.168.65.128 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-command Query --match-state "Creating sort index" --run-time 1 --busy-time 30 --victims all --interval 10 --daemonize --print --log=/tmp/pt_select.log # 执行杀操作 pt-kill --host=192.168.65.128 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-command Query --match-state "Creating sort index" --run-time 1 --busy-time 30 --victims all --interval 10 --daemonize --kill --log=/tmp/pt_select_kill.log
一往无前虎山行，拨开云雾见光明
浙公网安备 33010602011771号