• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

【Percona-toolkit系列】Percona Toolkit工具之pt-config-diff

Percona-toolkit SEian.G 5年前 (2019-04-14) 1620次浏览 已收录 0个评论
文章目录[隐藏]

Percona-toolkit工具包系列文章

在日常运维过程中,我们时常遇到这样的烦恼,两套库配置差不多,性能就是不一样,或者将自建的数据库迁移到云上之后,性能差别非常的大;无奈之举,去比对数据库配置是否相同,人肉对比,不仅效率低下而且容易出错哦。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.161.135,u=root,p=seiang@2019,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.161.135,u=root,p=seiang@2019,P=3306 h=localhost,u=root,p=qcloud@2018,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

【Percona-toolkit系列】Percona Toolkit工具之pt-config-diff


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:【Percona-toolkit系列】Percona Toolkit工具之pt-config-diff
喜欢 (4)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址