2

Mysqld process showed all config file options on command line in CentOs 5 and CentOs 6. But it's not showing the same in CentOs 7. I am using mysql 5.7.21 on CentOs 7 example stated below and 5.7.19 on CentOs 6 example stated below.

centos 6:

mysql@xxxxxxxx ==> ps -ef | grep 3306
mysql    23084     1  0 Jan16 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --socket=/datadata/mysql.sock --port=3306 --max_allowed_packet=100M --datadir=/datadata --pid-file=/datadata/mysqld-qa.pid --max_connections=500 --open-files-limit=8192 --thread_cache_size=64 --delayed_insert_limit=4096 --query_cache_limit=1M --query_cache_size=32M --query_cache_type=1 --log_queries_not_using_indexes=off --log-error=/datalogs/mysql-error.log --expire_logs_days=3 --sort_buffer_size=2M --join_buffer_size=4M --read_buffer_size=2M --read_rnd_buffer_size=2M --tmp_table_size=64M --max_heap_table_size=64M --lower_case_table_names=0 --myisam_sort_buffer_size=16M --innodb_file_per_table --innodb_buffer_pool_size=1G --innodb_open_files=600 --innodb_max_dirty_pages_pct=10 --innodb_flush_log_at_trx_commit=0 --innodb_log_file_size=64M --innodb_log_buffer_size=4M --innodb_log_files_in_group=3 --innodb_flush_method=O_DIRECT --binlog_cache_size=64K --transaction_isolation=REPEATABLE-READ --server-id=3664 --log-bin=mysqldb01.bin --log-slave-updates --binlog_format=statement --sysdate-is-now=1 --sync-binlog=1 --sync-master-info=1 --log-queries-not-using-indexes=1 --innodb_buffer_pool_instances=5 --innodb_concurrency_tickets=1000000 --auto_increment_increment=10 --auto_increment_offset=2 --binlog_checksum=none --sql_mode=STRICT_ALL_TABLES --explicit_defaults_for_timestamp --open_files_limit=10000 --max_connections=4000 --table_open_cache=400 --binlog_format=statement --log_timestamps=SYSTEM --explicit_defaults_for_timestamp --master_info_repository=table --relay_log_info_repository=table --relay-log=dbtemp02-relay-bin --mysqld_safe_log_timestamps=SYSTEM --slave_net_timeout=3600 --default_password_lifetime=0 --relay_log_recovery=ON
mysql    27230 23084  0 Jan16 ?        00:32:37 /usr/sbin/mysqld --basedir=/usr --datadir=/datadata --plugin-dir=/usr/lib64/mysql/plugin --max-allowed-packet=100M --max-connections=500 --thread-cache-size=64 --delayed-insert-limit=4096 --query-cache-limit=1M --query-cache-size=32M --query-cache-type=1 --log-queries-not-using-indexes=off --expire-logs-days=3 --sort-buffer-size=2M --join-buffer-size=4M --read-buffer-size=2M --read-rnd-buffer-size=2M --tmp-table-size=64M --max-heap-table-size=64M --lower-case-table-names=0 --myisam-sort-buffer-size=16M --innodb-file-per-table --innodb-buffer-pool-size=1G --innodb-open-files=600 --innodb-max-dirty-pages-pct=10 --innodb-flush-log-at-trx-commit=0 --innodb-log-file-size=64M --innodb-log-buffer-size=4M --innodb-log-files-in-group=3 --innodb-flush-method=O_DIRECT --binlog-cache-size=64K --transaction-isolation=REPEATABLE-READ --server-id=3664 --log-bin=mysqldb01.bin --log-slave-updates --binlog-format=statement --sysdate-is-now=1 --sync-binlog=1 --sync-master-info=1 --log-queries-not-using-indexes=1 --innodb-buffer-pool-instances=5 --innodb-concurrency-tickets=1000000 --auto-increment-increment=10 --auto-increment-offset=2 --binlog-checksum=none --sql-mode=STRICT_ALL_TABLES --explicit-defaults-for-timestamp --max-connections=4000 --table-open-cache=400 --binlog-format=statement --log-timestamps=SYSTEM --explicit-defaults-for-timestamp --master-info-repository=table --relay-log-info-repository=table --relay-log=dbtemp02-relay-bin --slave-net-timeout=3600 --default-password-lifetime=0 --relay-log-recovery=ON --log-error=/datalogs/mysql-error.log --open-files-limit=10000 --pid-file=/datadata/mysqld-qa.pid --socket=/datadata/mysql.sock --port=3306

centos 7:

[mysql@xxxxxx binlogs]$ ps -ef | grep mysql
mysql    16343     1 44 00:42 ?        00:53:00 /usr/sbin/mysqld --defaults-group-suffix=@0 --daemonize --pid-file=/var/run/mysqld/mysqld-0.pid

Any idea how to display all variables in CentOs 7.

2 Answers 2

1

You shouldn't be relying on the OS processlist to display mysqld options in the events other versions of the OS do not display them (as your questions points out)

What other methods can you use that are OS-independent ???

METHOD #1

If you enabled show_compatibility_56, then login to MySQL and run

SELECT * FROM information_schema.global_variables;

METHOD #2

If you did not enable show_compatibility_56, then login to MySQL and run

SELECT * FROM performance_schema.global_variables;

METHOD #3

From the OS command line, use mysqladmin utility with variables command

mysqladmin -h127.0.0.1 -P3306 --protocol=tcp -uroot -p variables

GIVE IT A TRY !!!

2
  • So CentOS 7 flavor wont display them due to security purposes. right?
    – kasi
    Commented Feb 5, 2018 at 13:21
  • Centos 7 doesn't display them because it stopped using a hugely hacky script like mysqld_safe to start services. my_print_defaults --defaults-group-suffix=@0 --mysqld will probably also display the values changed in configuration files from the shell.
    – danblack
    Commented Jul 20, 2023 at 5:37
1

SHOW GLOBAL VARIABLES; works on all versions of MySQL on all operating systems. When run from the commandline 'mysql' tool, the output is a few hundred lines something like:

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
| autocommit               | ON    |
| automatic_sp_privileges  | ON    |
...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.