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
