mysqld Startup options

mysqld namely MySQL Server, Is in MySQL The program executed during the initialization of database service installation . perform mysqld When initializing the database , You can specify server startup options （server options）.

#  View all startup options 
$ mysqld --verbose --help
abort-slave-event-count           0
allow-suspicious-udfs             FALSE
archive                           ON
auto-increment-increment          1
auto-increment-offset             1
autocommit                        TRUE
automatic-sp-privileges           TRUE
avoid-temporal-upgrade            FALSE
back-log                          80
basedir                           /home/jon/bin/mysql-5.7/
...
tmpdir                            /tmp
transaction-alloc-block-size      8192
transaction-isolation             REPEATABLE-READ
transaction-prealloc-size         4096
transaction-read-only             FALSE
transaction-write-set-extraction  OFF
updatable-views-with-limit        YES
validate-user-plugins             TRUE
verbose                           TRUE
wait-timeout                      28800

The configuration file ：my.cnf

because MySQL Server There are many startup options , Usually not written directly on the command line , Instead, it is uniformly written into the configuration file , Then specify the file read startup option during initialization .

$ mysqld --defaults-file=/etc/my.cnf --initialize

Priority of configuration file

In the class Unix Operating system ,MySQL The configuration file required for startup will be found from top to bottom in the order shown in the table below .

among ,SYSCONFDIR It means in use CMake build MySQL The path specified by this parameter , The default is... Under the compiled installation directory etc Catalog .

Be careful ： If a startup item is repeatedly defined in the above configuration file for multiple times , The value that will take effect after the service is started will be MySQL Last The value found . The only exception is user This startup item , For safety reasons ,MySQL The first one I found user The value of will take effect .

for example , hypothesis /etc/my.cnf and ~/.my.cnf The startup items of the storage engine are defined in , Then the value defined in the next one shall prevail .

# vim /etc/my.cnf
default-storage-engine=InnoDB

# vim ~/.my.cnf
default-storage-engine=MyISAM

namely MySQL After the service is started, the default storage engine is MyISAM.

The format of the configuration file

Only one startup item can be written per line in the configuration file . Some startup items need to specify a value , Some don't . Its format is compared with that in the command line , Generally, you only need to remove the two short horizontal lines in front --. Use... In the configuration file # notes .

Use [group] You can specify startup items for different groups or programs separately .

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=16M
max_allowed_packet=8M

[mysqldump]
quick

The following table shows different MySQL The startup item of the group that the program can read .

We can also add the version to the configuration file group, and then limit the database version of the startup item application .

[mysqld-5.7]
sql_mode=TRADITIONAL

References
【1】https://dev.mysql.com/doc/refman/5.7/en/mysqld.html
【2】https://dev.mysql.com/doc/refman/5.7/en/mysqld-server.html
【3】https://dev.mysql.com/doc/refman/5.7/en/server-configuration.html
【4】https://dev.mysql.com/doc/refman/5.7/en/option-files.html
【5】https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html
【6】https://dev.mysql.com/doc/refman/5.7/en/binary-log.html