0

Mysqld process showed all config file options on command line in CentOs 5 and CentOs 6. But its 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.

0

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 !!!

  • So CentOS 7 flavor wont display them due to security purposes. right? – kasi Feb 5 '18 at 13:21
0

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, privacy policy and cookie policy

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