Databases Reference
In-Depth Information
expire_logs_days
If you enable binary logging, you should enable this option, which causes the server
to purge old binary logs after the specified number of days. If you don't enable it,
you will eventually run the server out of disk space, and it will freeze or crash. We
suggest setting this option large enough that you can recover from at least two
backups ago (in case the most recent backup fails). Even if you take backups every
day, still leave yourself at least 7 to 14 days' worth of binary logs. Our experience
shows that you'll be grateful for a week or two of binary logs when you have some
unusual problem, such as rebuilding a replica and then trying to get it caught up
again with the master. You want to keep enough binary logs around to give yourself
some breathing room for operations such as these.
max_allowed_packet
This setting prevents the server from sending too large a packet, and also controls
how large a packet it will accept. The default is probably too small, but it can also
be set dangerously large. If it's set too small, sometimes problems can occur in
replication, typically when the replica can't retrieve data from the master that it
needs for replication. You might increase the setting from its default to 16 MB or so.
It's not documented, but this option also controls the maximum size of a user-
defined variable, so if you need very large variables, be careful—they can be trun-
cated or set to
NULL
if they exceed the size of this variable.
max_connect_errors
If something goes wrong with your networking for a moment, there is an applica-
tion or configuration error, or there is another problem such as privileges that
prevent connections from completing successfully for a brief period of time, clients
can get blacklisted and will be unable to connect again until you flush the host
cache. The default setting for this option is so small that this problem can happen
too easily. You might want to increase it, and in fact, if you know that the server
is adequately secured against brute-force attacks, you can just make it very large
to effectively disable host blacklisting.
skip_name_resolve
This setting disables another networking- and authentication-related trap: DNS
lookups. DNS is one of the weak points in MySQL's connection process. When
you connect to the server, by default it tries to determine the hostname from which
you're connecting and uses that as part of the authentication credentials. (That
is, your credentials are your username, hostname, and password—not just your
username and password.) But to verify your hostname, the server needs to perform
both a reverse and a forward DNS lookup. This is all fine until DNS starts to have
problems, which is pretty much a certainty at some point in time. When that hap-
pens, everything piles up and eventually the connection times out. To prevent this,
we strongly recommend that you set this option, which disables DNS lookups
during authentication. However, if you do this you will need to convert all of your
