Database Reference
In-Depth Information
Solution
To change settings, specify them at server startup or at runtime. To verify the changes,
examine the relevant system variables at runtime.
Discussion
The MySQL server places many configuration parameters under your control. For ex‐
ample, resources that require memory can be adjusted up or down to tailor resource
usage. A heavily used server requires more memory; a lightly used one, less. You can
set command options and system variables at server startup, and many system variables
are settable at runtime as well. You can also examine your settings at runtime to verify
that the configuration is as you intend.
Configuration control at server startup
To configure the server at startup time, specify options on the command line or in an
option file. The latter is usually preferable because you can specify settings once and
they'll apply at each startup. (For background on using command-line options and
option files, see
Recipe 1.4
.)
Command option names typically use dashes, whereas system variable names use un‐
derscores. However, the server is more permissive at startup and recognizes command
options and system variables written using dashes or underscores interchangeably. For
example,
sql_mode
and
sql-mode
are equivalent on the command line or in an option
file. This differs from runtime, when references to system variables
must
be written
using underscores.
To specify server parameters in an option file, list them in the
[mysqld]
group of a file
the server reads. To illustrate, here are some parameters you might set:
• The default character set is
latin1
. To use a more general character set, change this
to
utf8
, or even
utf8mb4
to include the 4-byte supplemental characters not part of
utf8
.
• The default SQL mode is
NO_ENGINE_SUBSTITUTION
(empty before MySQL 5.6.6).
To be more restrictive by default, enable strict SQL mode. Or be even more restric‐
tive, like “traditional” database servers.
• The event scheduler is disabled by default. If you plan to use scheduled events (see
Recipe 9.8
), you must enable it.
• If your users run a lot of queries on InnoDB tables, it might be a good idea to increase
the InnoDB buffer pool size from its default of 128MB.
To implement these configuration ideas, write the
[mysqld]
group in your option file
like this:
