MySQL :: MySQL 5.7 Reference Manual :: 5.1.6 Server Command Options (original) (raw)

5.1.6 Server Command Options

When you start the mysqld server, you can specify program options using any of the methods described inSection 4.2.2, “Specifying Program Options”. The most common methods are to provide options in an option file or on the command line. However, in most cases it is desirable to make sure that the server uses the same options each time it runs. The best way to ensure this is to list them in an option file. SeeSection 4.2.2.2, “Using Option Files”. That section also describes option file format and syntax.

mysqld reads options from the[mysqld] and [server] groups. mysqld_safe reads options from the[mysqld], [server],[mysqld_safe], and[safe_mysqld] groups.mysql.server reads options from the[mysqld] and [mysql.server] groups.

An embedded MySQL server usually reads options from the[server], [embedded], and[_`xxxxx`__SERVER] groups, where xxxxx is the name of the application into which the server is embedded.

mysqld accepts many command options. For a brief summary, execute this command:

mysqld --help

To see the full list, use this command:

mysqld --verbose --help

Some of the items in the list are actually system variables that can be set at server startup. These can be displayed at runtime using the SHOW VARIABLES statement. Some items displayed by the preceding mysqld command do not appear in SHOW VARIABLES output; this is because they are options only and not system variables.

The following list shows some of the most common server options. Additional options are described in other sections:

Some options control the size of buffers or caches. For a given buffer, the server might need to allocate internal data structures. These structures typically are allocated from the total memory allocated to the buffer, and the amount of space required might be platform dependent. This means that when you assign a value to an option that controls a buffer size, the amount of space actually available might differ from the value assigned. In some cases, the amount might be less than the value assigned. It is also possible that the server adjusts a value upward. For example, if you assign a value of 0 to an option for which the minimal value is 1024, the server sets the value to 1024.

Values for buffer sizes, lengths, and stack sizes are given in bytes unless otherwise specified.

Some options take file name values. Unless otherwise specified, the default file location is the data directory if the value is a relative path name. To specify the location explicitly, use an absolute path name. Suppose that the data directory is/var/mysql/data. If a file-valued option is given as a relative path name, it is located under/var/mysql/data. If the value is an absolute path name, its location is as given by the path name.

You can also set the values of server system variables at server startup by using variable names as options. To assign a value to a server system variable, use an option of the form--_`varname`_=_`value`_. For example,--sort_buffer_size=384M sets thesort_buffer_size variable to a value of 384MB.

When you assign a value to a variable, MySQL might automatically correct the value to stay within a given range, or adjust the value to the closest permissible value if only certain values are permitted.

To restrict the maximum value to which a system variable can be set at runtime with theSET statement, specify this maximum by using an option of the form--maximum-_`varname`_=_`value`_ at server startup.

You can change the values of most system variables at runtime with the SET statement. See Section 13.7.4.1, “SET Syntax for Variable Assignment”.

Section 5.1.7, “Server System Variables”, provides a full description for all variables, and additional information for setting them at server startup and runtime. For information on changing system variables, seeSection 5.1.1, “Configuring the Server”.

ALTER TABLE table_name ENGINE = INNODB;  
mysqld --early-plugin-load="myplug1=myplug1.so;myplug2=myplug2.so"

Quotes surround the argument value because otherwise some command interpreters interpret semicolon (;) as a special character. (For example, Unix shells treat it as a command terminator.)
Each named plugin is loaded early for a single invocation ofmysqld only. After a restart, the plugin is not loaded early unless--early-plugin-load is used again.
If the server is started using--initialize or--initialize-insecure, plugins specified by--early-plugin-load are not loaded.
If the server is run with--help, plugins specified by--early-plugin-load are loaded but not initialized. This behavior ensures that plugin options are displayed in the help message.
InnoDB tablespace encryption relies on the MySQL Keyring for encryption key management, and the keyring plugin to be used must be loaded prior to storage engine initialization to facilitate InnoDB recovery for encrypted tables. For example, administrators who want the keyring_file plugin loaded at startup should use--early-plugin-load with the appropriate option value (such askeyring_file.so on Unix and Unix-like systems or keyring_file.dll on Windows).
Important
In MySQL 5.7.11, the default--early-plugin-load value is the name of the keyring_file plugin library file, causing that plugin to be loaded by default. In MySQL 5.7.12 and higher, the default--early-plugin-load value is empty; to load the keyring_file plugin, you must explicitly specify the option with a value naming the keyring_file plugin library file.
This change of default--early-plugin-load value introduces an incompatibility for InnoDB tablespace encryption for upgrades from 5.7.11 to 5.7.12 or higher. Administrators who have encryptedInnoDB tablespaces must take explicit action to ensure continued loading of the keyring plugin: Start the server with an--early-plugin-load option that names the plugin library file. For additional information, seeSection 6.4.4.1, “Keyring Plugin Installation”.
For information about InnoDB tablespace encryption, see Section 14.14, “InnoDB Data-at-Rest Encryption”. For general information about plugin loading, seeSection 5.5.1, “Installing and Uninstalling Plugins”.

$> grep mlockall /usr/include/sys/mman.h

If mlockall() is supported, you should see in the output of the previous command something like the following: 

extern int mlockall (int __flags) __THROW;

Important
Use of this option may require you to run the server asroot, which, for reasons of security, is normally not a good idea. SeeSection 6.1.5, “How to Run MySQL as a Normal User”.
On Linux and perhaps other systems, you can avoid the need to run the server as root by changing thelimits.conf file. See the notes regarding the memlock limit inSection 8.12.4.3, “Enabling Large Page Support”.
You must not use this option on a system that does not support the mlockall() system call; if you do so, mysqld is very likely to exit as soon as you try to start it.

mysqld --plugin-load="myplug1=myplug1.so;myplug2=myplug2.so"

Quotes surround the argument value because otherwise some command interpreters interpret semicolon (;) as a special character. (For example, Unix shells treat it as a command terminator.)
Each named plugin is loaded for a single invocation ofmysqld only. After a restart, the plugin is not loaded unless --plugin-load is used again. This is in contrast toINSTALL PLUGIN, which adds an entry to the mysql.plugins table to cause the plugin to be loaded for every normal server startup.
During the normal startup sequence, the server determines which plugins to load by reading themysql.plugins system table. If the server is started with the--skip-grant-tables option, plugins registered in the mysql.plugins table are not loaded and are unavailable.--plugin-load enables plugins to be loaded even when--skip-grant-tables is given.--plugin-load also enables plugins to be loaded at startup that cannot be loaded at runtime.
This option does not set a corresponding system variable. The output of SHOW PLUGINS provides information about loaded plugins. More detailed information can be found in the Information SchemaPLUGINS table. SeeSection 5.5.2, “Obtaining Server Plugin Information”.
For additional information about plugin loading, seeSection 5.5.1, “Installing and Uninstalling Plugins”.

--plugin-load=x --plugin-load-add=y

are equivalent to this option: 

--plugin-load="x;y"

But these options: 

--plugin-load-add=y --plugin-load=x

are equivalent to this option: 

--plugin-load=x

This option does not set a corresponding system variable. The output of SHOW PLUGINS provides information about loaded plugins. More detailed information can be found in the Information SchemaPLUGINS table. SeeSection 5.5.2, “Obtaining Server Plugin Information”.
For additional information about plugin loading, seeSection 5.5.1, “Installing and Uninstalling Plugins”.

GRANT INSERT(user) ON mysql.user TO 'user_name'@'host_name';

This ensures that the user cannot change any privilege columns directly, but has to use theGRANT statement to give privileges to other users.

[ERROR] --tc-heuristic-recover rollback  
strategy is not safe on systems with more than one 2-phase-commit-capable  
storage engine. Aborting crash recovery.