PDF (US Ltr)
- 0.7Mb
PDF (A4)
- 0.7Mb
The following are the MySQL Administration Standard compliance rules:
Description The binary log captures DML,
DDL, and security changes that occur and stores these changes in
a binary format. The binary log enables point-in-time recovery,
preventing data loss during a disaster recovery situation. It
also enables you to review all alterations made to your
database. The
binlog_rows_query_log_events
system variable affects row-based logging only. When enabled, it
causes a MySQL 5.6.2 or later server to write informational log
events such as row query log events to its binary log. This
information can be used for debugging and related purposes; such
as obtaining the original query issued on the master when it
cannot be reconstructed from the row updates. These events are
normally ignored by MySQL 5.6.2, and later programs reading the
binary log, and so cause no issues when replicating or restoring
from backup. This is not true for a mysqld or
mysqlbinlog from MySQL 5.6.1 or earlier. If
the older version of the program reading the log encounters an
informational log event, it fails, and stops reading at that
point. To make the binary log readable by slave replication
MySQL servers and others, such as mysqlbinlog
from MySQL 5.6.1 or earlier distribution,
binlog_rows_query_log_events
must be disabled during logging.
Severity Minor Warning
Advice Investigate whether writing
informational log events such as row query log events into your
binary log is appropriate for your environment. That is, if all
servers and other log readers such as mysqlbinlog that read your
logs are MySQL 5.6.2 and later. If so, turn this feature on to
capture extra information in your logs. You can dynamically set
the value of the
binlog_rows_query_log_events
system variable to ON, put the new value in
the [mysqld] section of your my.cnf/my.ini
file so it remains in effect when you restart the server.
Description The binary log captures DML,
DDL, and security changes that occur and stores these changes in
a binary format. The binary log enables point-in-time recovery,
preventing data loss during a disaster recovery situation. It
also enables you to review all alterations made to your
database. Binary logging can be limited to specific databases
with the --binlog-do-db and the
--binlog-ignore-db options.
However, if these options are used, your point-in-time recovery
options are limited accordingly, along with your ability to
review alterations made to your system.
Severity Minor Warning
Advice Review the
--binlog-do-db and the
--binlog-ignore-db settings in
your my.cnf/my.ini file to be sure you are capturing updates to
all important databases. They are currently set as follows on
server: --binlog-do-db : %binlog_do_db% and
--binlog-ignore-db : %binlog_ignore_db%
Description The binary log captures DML, DDL, and security changes that occur and stores these changes in a binary format. The binary log enables point-in-time recovery, preventing data loss during a disaster recovery situation. It also enables you to review all alterations made to your database.
Severity Minor Warning
Advice Enable binary logging for
point-in-time recovery by setting the
log-bin configuration variable
in the [mysqld] section of your my.cnf/my.ini
file.
Description By default, the binary log contents are not synchronized to disk. If the server host machine or operating system crash, there is a chance that the latest events in the binary log are not persisted on disk. You can alter this behavior using the sync_binlog server variable. If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after sync_binlog commit groups are written to the binary log. The default value of sync_binlog is 0, which does no synchronizing to disk - in this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).
Severity Minor Warning
Advice Set sync_binlog = 1 within the [mysqld] section of your my.cnf/my.ini file to ensure the greatest safety for recovering from hardware, OS, and MySQL server crashes.
Description The binary log captures DML,
DDL, and security changes that occur and stores these changes in
a binary format. The binary log enables point-in-time recovery,
preventing data loss during a disaster recovery situation. It is
used on master replication servers as a record of the statements
to be sent to slave servers. It also enables you to review all
alterations made to your database. However, the number of log
files and the space they use can grow rapidly, especially on a
busy server, so it is important to remove these files on a
regular basis when they are no longer needed, as long as
appropriate backups have been made. The
binlog_expire_logs_seconds
parameter enables automatic binary log removal.
Severity Minor Warning
Advice Investigate why binary logs are automatically removed every %expire_logs_days% days. This may be an appropriate setting for your environment, but is unusually low, so be sure that your backup plan and execution is sufficient to support your disaster recovery scenarios. If necessary, increase the setting of expire_logs_days to a value that ensures safe and secure operations in your environment while also minimizing disk usage, and be sure that the binary logs go at least as far back as your last full backup. Be sure to also update the value of expire_logs_days in your my.cnf/my.ini file so it is set properly when the server is restarted.
Description The case sensitivity of the underlying operating system determines the case sensitivity of database and table names. If you are using MySQL on only one platform, you don't normally have to worry about this. However, depending on how you have configured your server you may encounter difficulties if you want to transfer tables between platforms that differ in filesystem case sensitivity.
Severity Minor Warning
Advice Set lower_case_table_names=1 in your my.cnf/my.ini file and restart your MySQL server. Note that if you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert your old database and table names to lowercase before restarting mysqld with the new variable setting.
Description The Event Scheduler is a very useful feature when enabled. It is a framework for executing SQL commands at specific times or at regular intervals. Conceptually, it is similar to the idea of the Unix crontab (also known as a "cron job") or the Windows Task Scheduler. The basics of its architecture are simple. An event is a stored routine with a starting date and time, and a recurring tag. Once defined and activated, it will run when requested. Unlike triggers, events are not linked to specific table operations, but to dates and times. Using the event scheduler, the database administrator can perform recurring events with minimal hassle. Common uses are the cleanup of obsolete data, the creation of summary tables for statistics, and monitoring of server performance and usage.
Severity Minor Warning
Advice Enable the Event Scheduler and use it to automate recurring events. Add the line event_scheduler=1 to the [mysqld] section of your my.cnf/my.ini file so the variable is set properly when the server is restarted.
Description The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld. However, the general query log should not be enabled in production environments because: It adds overhead to the server; It logs statements in the order they were received, not the order they were executed, so it is not reliable for backup/recovery; It grows fast and can use a lot of disk space. You should use the binary log instead.
Severity Minor Warning
Advice Disable the general query log: Remove the log option from your my.cnf/my.ini file, or remove the --log option from the script that starts your MySQL server.
Description If the space required to build a temporary table exceeds either tmp_table_size or max_heap_table_size , MySQL creates a disk-based table in the server's tmpdir directory. For performance reasons it is ideal to have most temporary tables created in memory, leaving exceedingly large temporary tables to be created on disk. Many DBAs configure tmp_table_size appropriately, but forget that max_heap_table_size also plays a role.
Severity Minor Warning
Advice Consider setting max_heap_table_size to be equal to or larger than tmp_table_size . The variable tmp_table_size is currently set to %tmp_table_size% and max_heap_table_size is set to %max_heap_table_size% .
Description To guard against ignored typos and syntax errors in SQL, or other unintended consequences of various combinations of operational modes and SQL commands, InnoDB provides a "strict mode" of operations. In this mode, InnoDB will raise error conditions in certain cases, rather than issue a warning and process the specified command (perhaps with some unintended defaults). This is analogous to MySQL's sql_mode, which controls what SQL syntax MySQL will accept, and determines whether it will silently ignore errors, or validate input syntax and data values. Using the new clauses and settings for ROW_FORMAT and KEY_BLOCK_SIZE on CREATE TABLE and ALTER TABLE commands and the CREATE INDEX command can be confusing when not running in strict mode. Unless you run in strict mode, InnoDB will ignore certain syntax errors and will create the table or index, with only a warning in the message log. However if InnoDB strict mode is on, such errors will generate an immediate error and the table or index will not be created, thus saving time by catching the error at the time the command is issued.
Severity Minor Warning
Advice Investigate why the innodb_strict_mode variable is set to OFF. Add innodb_strict_mode=1 to your my.cnf/my.ini file so it is set properly when the server is restarted.
Description If the InnoDB system tablespace is not allowed to automatically grow to meet incoming data demands and your application generates more data than there is room for, out-of-space errors will occur and your application may experience problems.
Severity Minor Warning
Advice Configure the InnoDB system tablespace to automatically extend by including the autoextend keyword in your innodb_data_file_path variable in your my.cnf/my.ini file. To help ensure low levels of fragmentation, set the autoextend increment (the amount of space that the InnoDB tablespace will grow) to an amount that is large in size.
Description To avoid frequent checkpoint activity and reduce overall physical I/O, which can slow down write-heavy systems, the InnoDB transaction logs should be approximately 50-100% of the size of the InnoDB buffer pool, depending on the size of the buffer pool.
Severity Minor Warning
Advice Increase the size of your InnoDB transaction logs. Note, however, that larger transaction logs can mean increased crash recovery times, and more intense checkpointing periods, as more data must be flushed from the buffer pool and logs to the tablespace datafiles. With this in mind, the maximum recommended size is 1 GB per log file. To change the size of your log files, make a clean shutdown of MySQL, alter the value of innodb_log_file_size accordingly within your my.cnf/my.ini file, move the current ib_logfile* files from the data directory to another location, and restart MySQL so the new log files can be created automatically.
Description Error conditions encountered by a MySQL server are always logged in the error log, but warning conditions are only logged if log_warnings is set to a value greater than 0. If warnings are not logged you will not get valuable information about aborted connections and various other communication errors. This is especially important if you use replication so you get more information about what is happening, such as messages about network failures and re-connections. Note that as of MySQL 5.7.2, the log_error_verbosity system variable is preferred over, and should be used instead of, log_warnings .
Severity Minor Warning
Advice Investigate why log_warnings is set to 0. Unless there are clear and compelling reasons not to log warnings, set log_warnings to a value greater than 0. However, when choosing a value for log_warnings, please be aware of Bug #42851 and Bug #46265 . When using binary logging with certain statements, it is possible that setting log_warnings = 2 can flood the error log with warnings about those statements. In those cases, check whether you can use Row Based Logging, as that format is always safe. Important Note: As of MySQL 5.7.2, the log_error_verbosity system variable is preferred over, and should be used instead of, log_warnings . See the description of http://dev.mysql.com/doc/mysql/en/server-system-variables.html#sysvar_log_warnings for information about how that variable relates to log_error_verbosity . In particular, assigning a value to log_warnings assigns a value to log_error_verbosity and vice versa. Also, be aware of the log_statements_unsafe_for_binlog option added in MySQL Server 5.7.11. If error 1592 is encountered, it controls whether the generated warnings are added to the error log or not.