How to export my.cnf from dev to use as base on local test site?

Question

MySQL

Hello. The bottom line is, there is a server on the Internet on which the site is spinning.
I want to copy the exact mysql server settings from it and run a copy locally so that the settings match the original on the production server.

What I do: I
start
mysqld --help --verbose
And I look where the configuration files are, I see

Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnf

I look into these files and see a very meager amount of settings:

cat my.cnf
[mysqld]
performance_schema=1
collation-server = utf8_general_ci
character-set-server = utf8
table_open_cache=777777700
local-infile=0
pid-file = /var/lib/mysql/mysqld.pid
datadir=/var/lib /mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#bind-address = 127.0.0.1

slow_query_log=1
slow_query_log_file=/var/log/mariadb /mysql-slow.log
long_query_time=1

join_buffer_size=22M

key_buffer=512M ## 128MB for every 1GB of RAM
sort_buffer_size=12M ## 1MB for every 1GB of RAM
read_buffer_size=12M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=12M ## 1MB for every 1GB of RAM
thread_concurrency=8 ## Number of CPUs x 2
max_user_connections=100
max_connections=300
max_allowed_packet=512M

query_cache_limit=1M
query_cache_size=0 ## 32MB for every 1GB of RAM
query_cache_type=0

interactive_timeout=600
wait_timeout=60
net_read_timeout=60

innodb_strict_mode=0
innodb_file_per_table=1
#innodb_buffer_pool_size=4096M
innodb_buffer_pool_size=6144M
innodb_flush_log_at_trx_commit=0
innodb_log_instance_buffer =
0
innodb_thread_concurrency=8 ## Number of CPUs x 2

max_heap_table_size=512M
tmp_table_size=512M

local-infile=1

# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb .log
pid-file=/var/run/mariadb/mariadb.pid

the second file is essentially empty.

But if you execute for example

mysql -urootikovskiy -A -e"SHOW GLOBAL VARIABLES;" > MySQLCurrentSettings.txt

then hundreds of settings get into the MySQLCurrentSettings.txt file, the mention of which is not even close in the configuration file.

The question is how to export server settings? so that they are divided into sections

[mysqld]
...

[mysql]
...

[client]
...

and so on.

And yet it was a complete list of settings, with those variable values ​​that the SHOW GLOBAL VARIABLES query produces; ?

It is not clear how to pull out a separate list that, for example, only applies to [mysqld] and a separate list for [mysql]

because some variables available for the [mysqld] section (group) simply do not work and give errors when the server starts if they are set for the section (groups) [mysql]

that is, in fact, it is not clear how to get a configuration file with a complete list of all VARIABLES AND at the same time that they would be correctly divided into their groups.

Who knows how to do this, please help.

I totally missed something...

Answer 1

In the file you see those settings that are changed manually, the rest of the settings are left by default. While doing

mysql -urootikovskiy -A -e"SHOW GLOBAL VARIABLES;" > MySQLCurrentSettings.txt

the changed settings and default settings are also unloaded, which is why there are so many of them. Use the settings from the file and exactly the same version of mysql, and get a similar system