【Percona-toolkit系列】Percona Toolkit工具之pt-config-diff
【Percona-toolkit系列】Percona Toolkit工具之pt-config-diff
SEian.G
发布于 2021-03-03 10:11:06
发布于 2021-03-03 10:11:06
在日常运维过程中，我们时常遇到这样的烦恼，两套库配置差不多，性能就是不一样，或者将自建的数据库迁移到云上之后，性能差别非常的大；无奈之举，去比对数据库配置是否相同，人肉对比，不仅效率低下而且容易出错哦。pt拿出了这么一个神器，专门对比mysql配置，有这么几种对比方式:
pt-config-diff用户区别 MySQL配置文件和服务器变量。CONFIG可以是文件名或DSN。至少必须给出两个CONFIG源。
两个CONFIG源的类型： 1、两个mysql的参数配置文件（见下一） 2、一个是运行的mysql，一个是参数文件（见下二） 3、两个运行的mysql（见下三）
语法：
pt-config-diff [OPTIONS] CONFIG CONFIG [CONFIG…]
一、比较两个实例的参数配置文件的差异
如果人肉来对比，看着头晕眼花，说不定还错误一大片。
[root@VM_54_118_centos ~]# pt-config-diff /tmp/my3306.cnf /tmp/my3307.cnf
11 config differences
Variable /tmp/my3306.cnf /tmp/my3307.cnf
========================= ========================= =========================
basedir /usr/local/mysql /data/my5628
character_set_server utf8mb4 utf8
datadir /data/mysql/mysql_3306... /data/my5628/data
log_bin /data/mysql/mysql_3306... /data1/my5628/log/mysq...
log_error /data/mysql/mysql_3306... /data/my5628/log/alert...
max_allowed_packet 16777216 134217728
port 3306 5628
query_cache_type 1 0
server_id 3306 5628
slow_query_log_file /data/mysql/mysql_3306... /data/my5628/log/slow.log
socket /tmp/mysql3306.sock /data/my5628/run/mysql...二、比较运行中的实例和参数配置文件的差异
[root@VM_54_118_centos ~]# pt-config-diff h=10.66.xx.xx,u=root,p=xxxxxx,P=3306 /tmp/my3306.cnf
12 config differences
Variable TENCENT64.site /tmp/my3306.cnf
========================= ========================= =========================
basedir /data1/mysql_root/base... /usr/local/mysql
character_set_server utf8 utf8mb4
datadir /data1/mysql_root/data... /data/mysql/mysql_3306...
log_error /data1/mysql_root/data... /data/mysql/mysql_3306...
max_allowed_packet 1073741824 16777216
port 20133 3306
query_cache_type OFF 1
secure_file_priv /data1/mysql_root/data... /tmp
server_id 102865 3306
slow_query_log_file /data1/mysql_root/data... /data/mysql/mysql_3306...
socket /tmp/mysql_20133.sock /tmp/mysql3306.sock
sql_mode ONLY_FULL_GROUP_BY,STR... NO_ENGINE_SUBSTITUTION...三、比较两个运行中的实例
[root@VM_54_118_centos ~]# pt-config-diff h=10.66.xx.xx,u=root,p=xxxxxxx,P=3306 h=localhost,u=root,p=qxxxxxx,S=/tmp/mysql3306.sock
127 config differences
Variable TENCENT64.site VM_54_118_centos
========================= ========================= =========================
back_log 210 80
basedir /data1/mysql_root/base... /usr/local/mysql/
binlog_row_image MINIMAL FULL
character_set_client utf8 utf8mb4
character_set_connection utf8 utf8mb4
character_set_database utf8 utf8mb4
character_set_results utf8 utf8mb4
character_set_server utf8 utf8mb4
character_sets_dir /data1/mysql_root/base... /usr/local/mysql/share...
collation_connection utf8_general_ci utf8mb4_general_ci
collation_database utf8_general_ci utf8mb4_general_ci
collation_server utf8_general_ci utf8mb4_general_ci
datadir /data1/mysql_root/data... /data/mysql/mysql_3306...
event_scheduler ON OFF
general_log_file /data1/mysql_root/data... /data/mysql/mysql_3306...
gtid_executed 361afb90-1c67-11e9-b6f... c9eeb85d-de5b-11e8-80d...
gtid_purged 361afb90-1c67-11e9-b6f...
host_cache_size 643 279
hostname TENCENT64.site VM_54_118_centos
innodb_buffer_pool_ins... 8 1
innodb_buffer_pool_size 2147483648 134217728
innodb_flush_log_at_tr... 2 1
innodb_flush_method O_DIRECT
innodb_flush_neighbors 0 1
innodb_io_capacity 20000 200
innodb_io_capacity_max 40000 2000
innodb_lock_wait_timeout 7200 50
innodb_log_buffer_size 67108864 16777216
innodb_log_file_size 536870912 50331648
innodb_log_group_home_dir /data/mysql_root/log/2... ./
innodb_open_files 1024 2000
innodb_page_cleaners 4 1
innodb_print_all_deadl... ON OFF
innodb_read_io_threads 12 4
innodb_strict_mode OFF ON
innodb_version 5.7.18 5.7.19
innodb_write_io_threads 12 4
interactive_timeout 1500 28800
key_buffer_size 16777216 8388608
lc_messages_dir /data1/mysql_root/base... /usr/local/mysql/share/
log_bin_basename /data/mysql_root/log/2... /data/mysql/mysql_3306...
log_bin_index /data/mysql_root/log/2... /data/mysql/mysql_3306...
log_bin_trust_function... ON OFF
log_error /data1/mysql_root/data... /data/mysql/mysql_3306...
log_timestamps SYSTEM UTC
max_allowed_packet 1073741824 16777216
max_connect_errors 999999999 100
max_connections 800 151
net_buffer_length 8192 16384
open_files_limit 102400 100001
performance_schema OFF ON
performance_schema_acc... 0 -1
performance_schema_dig... 0 10000
performance_schema_eve... 0 10000
performance_schema_eve... 0 10
performance_schema_eve... 0 10000
performance_schema_eve... 0 10
performance_schema_eve... 0 10000
performance_schema_eve... 0 10
performance_schema_eve... 0 10000
performance_schema_eve... 0 10
performance_schema_hos... 0 -1
performance_schema_max... 0 80
performance_schema_max... 0 -1
performance_schema_max... 0 1024
performance_schema_max... 0 80
performance_schema_max... 0 32768
performance_schema_max... 0 -1
performance_schema_max... 0 -1
performance_schema_max... 0 320
performance_schema_max... 0 -1
performance_schema_max... 0 210
performance_schema_max... 0 -1
performance_schema_max... 0 -1
performance_schema_max... 0 -1
performance_schema_max... 0 40
performance_schema_max... 0 -1
performance_schema_max... 0 10
performance_schema_max... 0 -1
performance_schema_max... 0 1024
performance_schema_max... 0 150
performance_schema_max... 0 193
performance_schema_max... 0 10
performance_schema_max... 0 -1
performance_schema_max... 0 -1
performance_schema_max... 0 -1
performance_schema_max... 0 50
performance_schema_max... 0 -1
performance_schema_ses... 0 512
performance_schema_set... 0 -1
performance_schema_set... 0 -1
performance_schema_use... 0 -1
pid_file /data1/mysql_root/data... /data/mysql/mysql_3306...
plugin_dir /data1/mysql_root/base... /usr/local/mysql/lib/p...
port 20133 3306
query_cache_type OFF ON
read_buffer_size 262144 131072
read_rnd_buffer_size 524288 262144
relay_log /data/mysql_root/log/2...
relay_log_basename /data/mysql_root/log/2... /data/mysql/mysql_3306...
relay_log_index /data/mysql_root/log/2... /data/mysql/mysql_3306...
report_port 20133 3306
rpl_semi_sync_master_w... AFTER_COMMIT AFTER_SYNC
rpl_stop_slave_timeout 120 31536000
secure_file_priv /data1/mysql_root/data... /tmp/
server_id 102865 3306
server_uuid 361afb90-1c67-11e9-b6f... c9eeb85d-de5b-11e8-80d...
skip_name_resolve ON OFF
slave_load_tmpdir /data1/mysql_root/tmp/... /tmp
slave_net_timeout 120 60
slow_query_log_file /data1/mysql_root/data... /data/mysql/mysql_3306...
socket /tmp/mysql_20133.sock /tmp/mysql3306.sock
sort_buffer_size 524288 262144
sql_mode ONLY_FULL_GROUP_BY,STR... STRICT_TRANS_TABLES,NO...
sync_binlog 0 1
table_definition_cache 768 1400
table_open_cache 512 2000
table_open_cache_insta... 8 16
thread_cache_size 512 9
thread_handling pool-of-threads one-thread-per-connection
thread_stack 524288 262144
tmp_table_size 419430400 16777216
tmpdir /data1/mysql_root/tmp/... /tmp
version 5.7.18-txsql-log 5.7.19-log
version_comment 20180530 MySQL Community Server...
version_compile_os Linux linux-glibc2.12
wait_timeout 1500 28800关于pt-config-diff的详细使用可参考官方文档：
https://www.percona.com/doc/percona-toolkit/3.0/pt-config-diff.html
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2019-05-16，如有侵权请联系 cloudcommunity@tencent.com 删除
目录
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号