【Percona-Toolkit】系列之pt-config-diff配置文件对比差异神器

背景

相信做DBA的朋友都有遇到过问题，随着管理运维的集群数量变多，再加上有的时候会动态去优化部分参数，可能会导致MySQL配置文件内容不一致，针对这个问题，我们今天的主角
pt-config-diff，可以很方便的帮大家去解决这个问题。

工具简介

pt-config-diff
usgae:
1.对比两个数据库配置文件中[mysqld]下面不一样的内容
#注意只会对比两边都有的参数的不同
2.对比数据库中系统变量的差异
3.对比数据库中配置文件和变量的差异
官方文档：
https://www.percona.com/doc/percona-toolkit/LATEST/pt-config-diff.html

常用参数

--[no]ignore-case 			忽略配置的大小写差异
--ignore-variables=a		忽略哪些变量之间的对比
--report-width=i			结果显示的长度
--[no]version-check			忽略两个之间版本的不一致

使用示例

1.对比两个配置文件的差异

#可以看到会输出配置文件下面不一样的地方，但是有些会截断
$ pt-config-diff /etc/my.cnf ./my.sandbox.cnf
8 config differences
Variable                  /etc/my.cnf               ./my.sandbox.cnf
========================= ========================= =========================
datadir                   /data/mysql/              /root/sandboxes/mysql_...
log_error                 /data/logs/mysql-error... /root/sandboxes/mysql_...
pid_file                  /data/mysql/mysql.pid     /root/sandboxes/mysql_...
port                      6606                      8026
report_port               8027                      8026
socket                    /data/mysql/mysql.sock    /tmp/mysql_sandbox8026...
tmpdir                    /data/mysql_tmp           /root/sandboxes/mysql_...
user                      mysql                     root

#增加显示长度report-width，可以看到全部显示出来了
pt-config-diff /etc/my.cnf ./my.sandbox.cnf --report-width=120
8 config differences
Variable                  /etc/my.cnf                ./my.sandbox.cnf
========================= ========================== ================================================================
datadir                   /data/mysql/               /root/sandboxes/mysql_home/msb_8_0_26/data
log_error                 /data/logs/mysql-error.log /root/sandboxes/mysql_home/msb_8_0_26/data/msandbox.err
pid_file                  /data/mysql/mysql.pid      /root/sandboxes/mysql_home/msb_8_0_26/data/mysql_sandbox8026.pid
port                      6606                       8026
report_port               8027                       8026
socket                    /data/mysql/mysql.sock     /tmp/mysql_sandbox8026.sock
tmpdir                    /data/mysql_tmp            /root/sandboxes/mysql_home/msb_8_0_26/tmp
user                      mysql                      root

2.对比数据库中系统变量的差异

#可以看到我们通过两个连接串来对比两个数据库的参数差异
$ pt-config-diff h=localhost,u=root,p=pass,S=/data/mysql/mysql.sock h=localhost,u=msandbox_rjw,p=pass,S=/tmp/mysql_sandbox8026.sock,P=8026
95 config differences
Variable                  CHNCJG-VS248106           CHNCJG-VS248106
========================= ========================= =========================
auto_generate_certs       OFF                       ON
back_log                  1238                      151
basedir                   /usr/                     /root/sandboxes/mysql_...
bind_address              *                         127.0.0.1
binlog_cache_size         16777216                  32768
binlog_expire_logs_sec... 259200                    2592000
character_set_system      utf8                      utf8mb3
character_sets_dir        /usr/share/percona-ser... /root/sandboxes/mysql_...
datadir                   /data/mysql/              /root/sandboxes/mysql_...
enforce_gtid_consistency  ON                        OFF
event_scheduler           OFF                       DISABLED
general_log_file          /data/mysql/CHNCJG-VS2... /root/sandboxes/mysql_...
gtid_executed             f269e743-e94f-11eb-be9...
gtid_executed_compress... 1000                      0
gtid_mode                 ON                        OFF
gtid_purged               f269e743-e94f-11eb-be9...
have_openssl              DISABLED                  YES
have_ssl                  DISABLED                  YES
host_cache_size           664                       279
innodb_buffer_pool_ins... 8                         1
innodb_buffer_pool_size   2147483648                134217728
innodb_doublewrite_files  16                        2
innodb_flush_log_at_tr... 0                         1
innodb_flush_method       O_DIRECT                  fsync
innodb_flush_neighbors    1                         0
innodb_log_file_size      2147483648                50331648
innodb_lru_scan_depth     4096                      1024
innodb_max_undo_log_size  2147483648                1073741824
innodb_monitor_enable     all
innodb_online_alter_lo... 1073741824                134217728
innodb_open_files         400                       4000
innodb_page_cleaners      8                         1
innodb_print_all_deadl... ON                        OFF
innodb_sort_buffer_size   67108864                  1048576
innodb_stats_persisten... 64                        20
innodb_version            8.0.22-13                 8.0.26-16
join_buffer_size          524288                    262144
key_buffer_size           33554432                  8388608
lc_messages_dir           /usr/share/percona-ser... /root/sandboxes/mysql_...
log_bin_basename          /data/logs/mysql-bin      /root/sandboxes/mysql_...
log_bin_index             /data/logs/mysql-bin.i... /root/sandboxes/mysql_...
log_error                 /data/logs/mysql-error... /root/sandboxes/mysql_...
log_queries_not_using_... ON                        OFF
log_slow_admin_statements ON                        OFF
log_timestamps            SYSTEM                    UTC
long_query_time           0.000000                  10.000000
max_allowed_packet        16777216                  67108864
max_binlog_cache_size     4294967296                18446744073709547520
max_connect_errors        1000000                   100
max_connections           1238                      151
max_heap_table_size       33554432                  16777216
myisam_recover_options    BACKUP,FORCE              OFF
open_files_limit          2048                      655350
performance_schema_err... 4888                      5029
pid_file                  /data/mysql/mysql.pid     /root/sandboxes/mysql_...
plugin_dir                /usr/lib64/mysql/plugin/  /root/sandboxes/mysql_...
port                      6606                      0
relay_log                 /data/logs/relay-bin      CHNCJG-VS248106-relay-bin
relay_log_basename        /data/logs/relay-bin      /root/sandboxes/mysql_...
relay_log_index           /data/logs/relay-bin.i... /root/sandboxes/mysql_...
relay_log_recovery        ON                        OFF
report_host                                         single-8026
report_port               6606                      8026
secure_file_priv          /var/lib/mysql-files/     NULL
server_id                 178119                    1
server_uuid               f269e743-e94f-11eb-be9... 00008026-0000-0000-000...
sha256_password_auto_g... OFF                       ON
skip_name_resolve         ON                        OFF
skip_networking           OFF                       ON
slave_load_tmpdir         /data/mysql_tmp           /root/sandboxes/mysql_...
slave_parallel_type       LOGICAL_CLOCK             DATABASE
slave_parallel_workers    16                        0
slave_preserve_commit_... ON                        OFF
slave_transaction_retries 128                       10
slow_query_log            ON                        OFF
slow_query_log_file       /data/logs/mysql-slow.log /root/sandboxes/mysql_...
slow_query_log_use_glo... log_slow_filter,log_sl...
socket                    /data/mysql/mysql.sock    /tmp/mysql_sandbox8026...
sort_buffer_size          2097152                   262144
sql_mode                  STRICT_TRANS_TABLES,NO... ONLY_FULL_GROUP_BY,STR...
ssl_ca                                              ca.pem
ssl_cert                                            server-cert.pem
ssl_key                                             server-key.pem
sync_binlog               0                         1
table_definition_cache    600                       2000
table_open_cache          400                       4000
thread_cache_size         100                       9
thread_handling           pool-of-threads           one-thread-per-connection
thread_pool_oversubscribe 16                        3
thread_pool_stall_limit   30                        500
tmp_table_size            33554432                  16777216
tmpdir                    /data/mysql_tmp           /root/sandboxes/mysql_...
version                   8.0.22-13                 8.0.26-16
version_comment           Percona Server (GPL), ... Percona Server (GPL), ...
wait_timeout              3600                      28800

3.对比我当前的数据库和配置文件中参数有哪些不一致

#可以看到当前数据库里面的参数和配置文件有些是有差异的
$ pt-config-diff h=localhost,u=root,p=pass,S=/data/mysql/mysql.sock /etc/my.cnf
9 config differences
Variable                  CHNCJG-VS248106 /etc/my.cnf
========================= =============== ============
enforce_gtid_consistency  ON              on
gtid_mode                 ON              on
innodb_numa_interleave    OFF             off
innodb_open_files         400             4096
max_connections           1238            2000
myisam_recover_options    BACKUP,FORCE    FORCE,BACKUP
open_files_limit          2048            65535
report_port               6606            8027
table_open_cache          400             1000

总结

可以看到使用这个工具可以很快速方便帮我们进行日常的一些对比。
对于DBA来说可以讲这个加入到每日巡检，检查参数的一致性。