compare mysql configuration

wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-release-latest.noarch.rpm
yum install percona-toolkit

Create same MySQL user on different databases and run:

pt-config-diff h=192.168.0.99 h=192.168.0.100 -p Password -u User

# A software update is available:
74 config differences
Variable                  m1.srv.net               old100.srv.net
========================= ========================= =========================
back_log                  80                        50
binlog_format             MIXED                     STATEMENT
general_log_file          m1.log                    /var/lib/mysql/old100.log
group_concat_max_len      1048576                   1024
have_openssl              YES                       DISABLED
have_symlink              YES                       DISABLED
hostname                  m1.srv.net                old100.srv.net
innodb_autoextend_incr... 64                        8
innodb_buffer_pool_ins... 8                         1
innodb_buffer_pool_size   5368709120                1073741824
innodb_concurrency_tic... 5000                      500
innodb_data_file_path     ibdata1:12M:autoextend    ibdata1:10M:autoextend
innodb_file_format                                  Antelope
innodb_flush_method       fsync                     
innodb_large_prefix                                 OFF
innodb_log_buffer_size    16777216                  8388608
innodb_log_file_size      50331648                  5242880
innodb_max_dirty_pages... 75.000000                 75
innodb_old_blocks_time    1000                      0
innodb_open_files         2000                      300
innodb_purge_batch_size   300                       20
innodb_purge_threads      4                         0
innodb_spin_wait_delay    4                         6
innodb_stats_on_metadata  OFF                       ON
innodb_strict_mode        ON                        OFF
innodb_version            10.4.13                   5.5.54
join_buffer_size          262144                    131072
key_buffer_size           134217728                 8388608
lc_messages_dir                                     /usr/share/mysql/
lock_wait_timeout         86400                     31536000
log_error                                           /var/log/mysqld.log
log_warnings              2                         1
max_allowed_packet        16777216                  1048576
max_connect_errors        100                       10
max_long_data_size        16777216                  1048576
max_relay_log_size        1073741824                0
max_seeks_for_key         4294967295                18446744073709551615
max_write_lock_count      4294967295                18446744073709551615
myisam_recover_options    BACKUP,QUICK              OFF
myisam_sort_buffer_size   134216704                 8388608
old_alter_table           DEFAULT                   OFF
open_files_limit          16364                     5000
optimizer_switch          index_merge=on,index_m... index_merge=on,index_m...
performance_schema_eve... -1                        10000
performance_schema_eve... -1                        10
performance_schema_max... 90                        80
performance_schema_max... -1                        1000
performance_schema_max... -1                        10000
performance_schema_max... -1                        1000000
performance_schema_max... 40                        30
performance_schema_max... -1                        1000000
performance_schema_max... -1                        100000
performance_schema_max... -1                        50000
performance_schema_max... -1                        1000
pid_file                  /var/lib/mysql/m1.pid   /var/run/mysqld/mysqld...
query_alloc_block_size    16384                     8192
query_cache_size          1048576                   0
query_cache_type          OFF                       ON
query_prealloc_size       24576                     8192
secure_auth               ON                        OFF
secure_file_priv                                    /var/lib/mysql-files/
server_id                 1                         0
slave_net_timeout         60                        3600
slow_query_log_file       m1-slow.log               /var/lib/mysql/old100-slo...
sql_mode                  STRICT_TRANS_TABLES,ER... 
sync_master_info          10000                     0
sync_relay_log            10000                     0
sync_relay_log_info       10000                     0
system_time_zone          UTC                       EEST
table_open_cache          2000                      400
thread_cache_size         151                       0
thread_stack              299008                    262144
version                   10.4.13-MariaDB           5.5.54
version_comment           MariaDB Server            MySQL Community Server...

Leave a Reply

Your email address will not be published. Required fields are marked *