MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home Configuring MySQL and Specifying Options

Configuring MySQL and Specifying Options

In this lesson of the MySQL tutorial, you will learn...
  1. To understand configuration options
  2. To understand options behavior and precedence
  3. To set up configuration files
  4. To set options as environment variables
  5. Where the configuration files are located
  6. To use pre-existing Templates
  7. Various options grouping and meanings

This lesson gives an overview of how MySQL configuration works and what options are available. Some of these configuration options affect performance.



Configuration Options

Print PDF

This lesson explains the management of the many configuration parameters that affect the general operation of the MySQL server and are useful to for tweaking, managing and optimizing the server.

Unless otherwise specified, MySQL assumes a default set of configuration settings upon each start of the MySQL server daemon. Using defaults, it is very possible to run MySQL without many changes to its configuration.

But some changes not only will better adapt your deployment to your specific hosting environment, but could also greatly enhance the performance of your application based on its behavioral characteristics.

For example, some applications might be update-intensive, prompting you to adjust the resources that MySQL requires for handling write/modification queries. Other applications might need to handle a large number of user connections, prompting a change to the number of threads allocated to new connections. MySQL is extremely configurable allowing administrators to manage its various facilities and operations.

Viewing MySQL's Configuration Parameters

To see all possible options available, run:

>INSTALL-DIR/bin/mysqld --verbose --help

To see the present option settings, you instead need to execute the mysqladmin client:

Code Sample: Configuration/Demos/Show-Vars.bat

mysqladmin -u root -p variables
Code Explanation

Doing so produces a lengthy list of variable settings similar to this:

+---------------------------------+----------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | C:\...\MySQL Server 5.0\ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| . . . | |
| version | 5.0.27-community-nt |
| . . . | |
| wait_timeout | 28800 |
+---------------------------------+----------------------------+
226 rows in set (0.00 sec)

Alternatively, you can log in to the mysql client and execute the following command:

mysql>SHOW VARIABLES;

You can view the setting of select variables by using the LIKE clause. For example, to determine the base directory, use:

Code Sample: Configuration/Demos/Show-Vars-Like.sql

SHOW VARIABLES LIKE "basedir";
Code Explanation

Output from this command looks like:

+---------------+------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------+
| basedir | C:\Program Files\MySQL\MySQL Server 5.0\ |
+---------------+------------------------------------------+
1 row in set (0.02 sec)

You can explore useful statistical information such as uptime, queries processed, and total bytes received and sent by using:

Code Sample: Configuration/Demos/Show-Status.sql

SHOW STATUS;
Code Explanation

Output will look like:

+-----------------------------------+-----------+
| Variable_name | Value |
+-----------------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 1 |
| . . . | |
| Bytes_received | 134 |
| Bytes_sent | 6149 |
| . . . | |
| Threads_created | 1 |
| Threads_running | 1 |
| Uptime | 848 |
+-----------------------------------+-----------+
249 rows in set (0.00 sec)

Some Examples of Option Usage

In a MySQL installation, many hundreds of configuration options are available to fine-tune almost all facets of the server's operation, such as memory usage, logging sensitivity, and limits such as maximum number of simultaneous connections, user resources and connection errors. Here are a few examples:

Managing Connection Loads

MySQL server can serve many connections simultaneously where each connection is delegated to a new thread by the main MySQL thread, a task that may have some delay. The back_log parameter determines the number of connections that are allowed to queue up while this main thread deals with the connection load (default 50).

mysql> SHOW VARIABLES LIKE 'back_log';

Setting the Data Directory Location

If MySQL data directory in a nonstandard location such as another disk partition, one can use the datadir option to redefine this path:

mysql> SHOW VARIABLES LIKE 'datadir';
root> ./bin/mysqld_safe --datadir=/data/mysql --user=mysql &

Note: You will need to copy the MySQL permission tables from DATADIR/mysql to this new data location. Because MySQL's databases are stored in files, you can do so by using operating system commands.

Setting the Default Storage Engine

If you commonly use of a particular MySQL engine you can set it as default by using the -- default-storage- engine parameter, (the default is MyISAM on Linux/Unix, and InnoDB on Windows). The following displays the current engine and then sets it to memory:

mysql> SHOW VARIABLES LIKE 'table%';
root> ./bin/mysqld_safe --default-table-type=memory

All subsequent table creation will automatically use the MEMORY engine as default unless otherwise specified.

Executing SQL Commands Automatically

You can execute a series of SQL commands at daemon startup by placing them in a script file and setting that file name to init_file. For example, we wish to see status of MySQL server repeatedly. Place the following query in a file named mysql_repeat_cmds.sql:

SHOW STATUS;

Then, assign init_file like so when executing mysqld_safe:

>./bin/mysqld_safe --init_file=/scripts/mysql_repeat_cmds.sql &

Logging Potentially Nonoptimal Queries

The log-queries-not-using-indexes parameter defines a file to which all queries are logged that aren't using indexes for performance improvement.

Logging Slow Queries

The log_slow_queries parameter defines a file to which all queries are logged that take longer than long_query_time seconds to execute. More on this in another lesson.

Code Sample: Configuration/Demos/Show-Query-Log.sql

SHOW VARIABLES LIKE 'log%';
SHOW VARIABLES LIKE 'long_query%';

Setting the Maximum Allowable Simultaneous Connections

The max_connections parameter determines the maximum permitted number of simultaneous database connections and by default is set to 100. You can check the maximum number of simultaneous opened connections via the max_used_connections parameter in SHOW STATUS. If number of connections are frequently close to the limit, you may want to increase this count.

Warning: As the number of connections increases, so will memory consumption as MySQL allocates additional memory to every connection it opens.

Code Sample: Configuration/Demos/Show-Connection.sql

SHOW VARIABLES LIKE '%connection%';
SHOW STATUS LIKE '%connection%';
root> ./bin/mysqld_safe --max_connections=30

Setting MySQL's Communication Port

By default, MySQL's communication port is 3306; You can reconfigure any other port by using the port parameter.

mysql> SHOW VARIABLES LIKE 'port%';

Rules for Specifying Options

This section covers some rules around specifying configuration options.

Session Specific Options

Besides start up options, there are several MySQL server variables that are permitted to be changed by the user at run time specifically for the currently active connection. To do this, the user simply executes an SQL command like SET sort_buffer_size=2M. For resource-related variables, an upper bound can be set while starting the server by prefixing --maximum:

root> ./bin/mysqld_safe --maximum-sort_buffer_size=8M

Turning Options On or Off

Some options work as flags to control behavior that can be turned on or off. For example, the mysql client supports a --column-names option that determines whether or not to display a row of column names at the beginning of query results. By default, this option is enabled. If disabled, you see only table data without column headers.

To disable column names, you can specify the option using any of three forms. The prefixes -- disable / --skip and the assignment of zero =0 will all turn the option off.

--disable-column-names
--skip-column-names
--column-names=0

Code Sample: Configuration/Demos/Disable-Column-Names.bat

mysql -usakilauser -psakila --disable-column-names sakila -e "SELECT * FROM category"

mysql -usakilauser -psakila --column-names=0 sakila -e "SELECT * FROM category"
Code Explanation

Do not show column names.

To turn on the flag or enable an option, any of the following will do:

--column-names
--enable-column-names
--column-names=1

Note: Some options like --skip-grant-tables have skip is an integral part of the operator name. In this case, --enable-grant- tables and --grant-tables=0 do not function.

--loose Option

Every new version of MySQL offers new options. If you are writing a script and do not wish to prescribe a particular version of MySQL, you may use the new option --loose as a prefix (e.g.,--loose-optionname=3).

If the named option (after loose) exists, it will be set accordingly. If the MySQL program does not recognize the option, --loose will cause the option to be ignored without an error message. In other words, if an option is prefixed by --loose, a program does not exit with an error if it does not recognize the option, but instead issues only a warning:

mysql -xbrl -usakilauser -psakila --column-names=0 sakila

mysql --loose-xbrl -usakilauser -psakila --column-names=0 sakila
Code Explanation

The first mysql invocation fails, as there no xbrl option.

Allow for missing options.

Tip : The --loose prefix can be useful when you run programs from multiple installations of MySQL on the same machine and list options in an option file. Versions of the program that recognize the option process it normally, and versions that do not recognize it issue a warning and ignore it.

Memory Specifications

For options and variables that require a memory specification, the letters K, M, and G may be used to denote kilobytes (1024 bytes), megabytes, and gigabytes. Thus the settings key_buffer_size=16M, key_buffer_size=16384K, and key_buffer_size=16777216 are equivalent.

Exercise: Using Options

Duration: 5 to 10 minutes.

In this exercise, we will work with SQL_MAX_JOIN_SIZE option to manipulate server settings.

  1. Set SQL_MAX_JOIN_SIZE option
  2. Restart the MySQL server.
  3. Running the first sql, you will see the following error:
     ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check
    your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
Tip: The -C or --compress option minimizes the data flow between client and server by making use of data compression.

Precedence of Options

Option settings are read in the following order:

  • Environment variables - set in shell / batch scripts etc
  • Configuration files as part of the Server installation
  • Options specified at program startup on command line

In the case of contradictory settings, the last setting read takes precedence. Hence, options on command line become more immediate and overwrite settings in environment variables.

Setting Options in Configuration Files

If some options are being repeatedly set for your sessions, you can save these in options files for many of the commands covered in this section. Some options are used by more than one MySQL programs.

For the MySQL server, MySQL provides a number of pre-configured option files.

Some configuration changes are made on the command line when starting the MySQL daemon via wrapper script - mysqld_safe. You can also set many other parameters in MySQL's configuration files.

Putting the options in a file is better than unwieldy number of options on the command line. In addition, option in files ensure consistency of using same options every time and without the risk of an option being left out.

The syntax of the file is based on the following pattern:

# Comment
[program name]
option1 # equivalent to: --option1
option2=value2 # equivalent to: --option2=value2

These options are divided into groups for each program. program name specifies the name of the program:

Key things to note:

  • Comments are prefaced with a hash mark (#).

  • Variables are assigned exactly like they would be when passed as parameters to mysqld_safewithout the double hyphen prefix.

  • The group (context) of these variables is set by prefacing the section with the intended beneficiary, enclosed in square brackets. For example, if you want to tweak the default behavior of mysqldump with the relevant variable settings as shown:

    [mysqldump]
    quick
    max_allowed_packet = 4M

    This context is assumed until the next square-bracket setting is encountered.

  • You can use either underscore (_) or dash (-) when specifying configuration options.
Guidelines for Grouping for various Options
Options Group to be Specified in
Used by all programs other than mysqld [client]
Relate only to the servermysqld [mysqld], [server]
Specific to a program prog-name [prog-name]

By using multiple levels of configuration files that cascade to create a complete set of options, MySQL offers a powerful mechanism to customize the environment for the server and various tools.

In config files, option is the option name in long form, but without hyphens. (So, for example, the option--host in the configuration file becomes simply host.) If options expect parameters, then these are specified with =.

Let us see, finally, a concrete example of a configuration file:

# configuration file /etc/my.cnf (Unix/Linux) or my.ini (Windows)
# options for all MySQL tools
[client]
user=sakilauser
password=sakila
host=webucator.org
# options for mysqldump
[mysqldump]
force
# options for mysql (command interpreter)
[mysql]
safe-updates
select_limit=100

Changes to configuration files are effective only after a restart of the program in question, especially for the MySQL server in the group [mysqld].

Warning: Please confirm that the options specified in the [client] section are truly supported by all MySQL tools. If a MySQL tool encounters an unknown option in the [client] section, then the command is terminated with an error message.

Exercise: Using Options in Files

Duration: 15 to 20 minutes.

In this exercise, we will set and use options from files.

  1. Pick different options from the documentation.
  2. Set these options in files, either one-by-one or as a group for server and client programs.
  3. Test the effect of the options.

Configuration Templates

Generally, you should base your configuration upon one of five preconfigured my.cnf files. These templates are located in INSTALL-DIR/support-files. On Windows, these files are found in the installation directory. The purpose of each is defined in Table below:

MySQL Configuration Templates
Name Targetted For/Description
my-huge.cnf

High-end production servers, containing 1 to 2GB RAM, tasked with primarily running MySQL

my-innodb-heavy-4G.ini

InnoDB-only installations for up to 4GB RAM involving large queries and low traffic

my-large.cnf

Medium-sized production servers, containing around 512MB RAM, tasked with primarily running MySQL

my-medium.cnf

Low-end production servers containing little memory (less than 128MB)

my-small.cnf

Minimally equipped servers, possessing nominal RAM (less than 64MB)

Here's a partial listing of the my-large.cnf configuration template:

# Example mysql config file for large systems.
#
# This is for large system with memory = 512M where the system runs mainly
# MySQL.

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port=3306
socket=/tmp/mysql.sock
skip-locking
key_buffer=256M
max_allowed_packet=1M
table_cache=256
sort_buffer=1M
record_buffer=1M
myisam_sort_buffer_size=64M
[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
...

Location of Configuration Files

The following table collects the locations where the options files must be stored. At startup, all existing files are read in the order in which they are listed below. In the case of contradictory settings, the most recently read options file takes precedence.

The options --no-defaults, --defaults-file, and --defaults-extra-file affect if and which configuration files will be read.

Validity Windows Unix/Linux
Global options Current MySQL versions: C:\Programs\MySQL\ MySQL Server n.n\my.ini Old MySQL versions: C:\my.cnf or Windows\ my.ini /etc/my.cnf
User-specific options (only for client programs) ~/.my.cnf

Server-specific options (only for mysqld)

C:\my.cnf,C:\my.ini DATADIR/my.cnf

The directory DATADIR is the default data directory. Under Windows, this is normally C:\Programs\MySQL\ MySQL Server n.n\data, while under Unix/Linux, it is /var/lib/mysql.

It is possible that DATADIR is not the directory in which MySQL actually stores the database files. This directory is usually set when the MySQL server is launched with the option --datadir. However, access to the configuration file takes place before this option is evaluated.

Unix Configuration Files

At startup, MySQL looks in several directories for the my.cnf file, with each directory determining the scope of the parameters declared within. The location and relative scope of each directory is highlighted here:

  • /etc/my.cnf: Global configuration file that loads global options for all servers and users. All MySQL server daemons located on the server refer first to this file.
  • MYSQL_HOME/my.cnf: MYSQL_HOME env variable points to a directory.
  • If MYSQL_HOME isn't set:

    DATADIR/my.cnf: Server-specific configuration. This file is placed in the directory of the directory referenced by the server installation.

    We use the data directory specified at configuration time, even if a new data directory is specified at run time. MySQL first looks in the data directory and then in the base directory for a configuration file

    .
  • --defaults-extra-file=name : A admin-file specified with absolute path.
  • ~/.my.cnf: User-specific configuration. A user-specific file located in the user's home directory.

    If you wish under Unix/Linux to execute user-specific options settings in ~/.my.cnf and possibly specify passwords there, then ensure that no other users are able to read this file.

    user$ chmod 600 ~/.my.cnf

For a binary installation, the data directory is typically /usr/local/mysql/data. For a source installation, the data directory is /usr/local/var. Some packages, like RPM, are configured to store the data in /var/lib/mysql.

You should understand that MySQL attempts to read from each of these locations at startup. If multiple configuration files exist, parameters read in later take precedence over earlier parameters.

Note: As a safety precaution on Unix platforms, MySQL ignores configuration files that have global-writable permissions (-ow).

Windows Configuration Files

On Windows, MySQL programs read startup options from the following files:

  • MySQL-INSTALL-DIR\my.ini>: Global configuration file. (MySQL-INSTALL-DIR commonly C:\Programs\MySQL\MySQL Server n.n\my.ini)

    All MySQL server daemons located on the server refer first to this file. Note the extension of .ini and it is placed when the Windows MySQL Configuration Wizard is used.

  • WINDIR\my.ini: Alternate Global configuration file.
  • C:\my.ini OR c:\my.cnf
  • The file specified with --defaults-extra-file=path

You can use the program my_print_defaults <group> to determine the options set for given [group] in the configuration file. More on this program is covered in another lesson.

Some rules to look out for:

  • MySQL's Windows distribution does not look in DATA directory or in user-home directory for configuration files.
  • If you specify Windows paths or directories in a configuration file, then you must use / or \\ instead of the back-slash \. (In the Windows version of MySQL, the backslash is used as an escape character.)
  • Paths cannot be placed in quotation marks within a configuration file (even if the path contains space characters): basedir=C:/Programs/MySQL/MySQL Server 5.0/.
  • These rules are different from those that obtain a direct setting of options with --option =....

Options in Environment/System Variables

An additional possibility for specifying options are environment variables at the level of the operating system. (Under Windows, these variables are usually known as system variables.)

The following table names the most important of these variables. To set such variables under Windows, you use the dialog for system control.

Under Linux you can define such variables in script files (e.g., in /etc/profile or ~/.profile) with export. Depending on which shell you use, you may use the command declare - x or setenv instead of export.

Import and Environment Variables for mysql, mysqladmin, mysqld, mysqldump
MYSQL_TCP_PORT Specifies the port number for the TCP/IP connection to MySQL (generally 3306).
MYSQL_UNIX_PORT Specifies the socket file for local communication under Linux/Unix (e.g., /var/mysql.lock).
TMPDIR Specifies the directory to be used for temporary files; this directory is also used for temporary tables.
MYSQL_PS1 The command prompt to use in the mysql command-line client.
MYSQL_PWD The default password when connecting to mysqld. Warning: Using this is insecure.
USER Specifies the user name.

Code Sample: Configuration/Demos/env-vars.bat

SET USER=sakilaadmin
SET MYSQL_PS1=MySQL::
SET MYSQL_PWD=sakila

mysql sakila
Code Explanation

The USER is set as an environment variable to sakilaadmin.

The MYSQL_PS1 environment variable changes the mysql prompt.

The MYSQL_PWD environment variable specifies the password in clear-text.

So mysql does not require auth parameters and simply uses sakila as default database.

Main Configuration Groups

As seen before, the options in configuration files can be further separated in groups for specific tools and even specific versions of the mysqld server. This means you can have one buffer size for mysqldump and another one for your mysql client, or one username/ password for the program mysqladmin and another for mysqlshow.

The configuration file starts with options common to all commands, before any group is mentioned. After common options, a group name using brackets [group] initiates its specific options. All options after the group tag will apply to servers or tools in that group. The start of a new group or end of file marks the end of a group.

Here are the core Configuration Groups that can be specified in options files:

Common Configuration Groups

Group

Applies to

mysqld

The mysqld server program, regardless of startup mechanism

mysqld-MAJOR.MINOR

mysqld specific to version MAJOR.MINOR.

mysqld_safe

Database process started with the mysqld_safe script

safe_mysqld

Database process started using the mysqld_safe script

server

Startup scripts for mysqld, mysqld_safe, mysql.server

client

Any client program, not mysqld

mysql

MySQL client only

myisamchk

myisamchk tool, which checks and repairs MyISAM tables

myisampack

myisampack tool, which packs MyISAM tables into smaller, read-only format

mysqladmin

mysqladmin tool, which is used for database administration commands

mysqlbinlog

mysqlbinlog tool, which converts binary logs to text

mysqlcheck

mysqlcheck tool, which checks the health of tables

mysqldump

mysqldump tool, which creates SQL statements for rebuilding databases or tables

mysqlhotcopy

mysqlhotcopy tool, which copies data files

mysqlimport

mysqlimport, a command-line interface to LOAD DATA INFILE

mysqlshow

mysqlshow tool, which is used for showing information about databases, tables, columns, and so on

mysql_multi

mysql_multi tool, which is used for managing multiple running databases on one machine

mysqld1

mysql_multi with server 1 group number specified; use mysqld<group number> to create mysql_multi groups with options for each server

It is possible that with this wide array of configuration groups and options, a configuration file can become quite large. Here is an example that provides a simple configuration file with common groups and options.

[client]
username=sakilauser
password=sakila
port=3306

[mysql]
prompt=mysql-prod>

[mysqld]
key_buffer_size=16M
max_allowed_packet=2M
port=3306

Key Configuration Options

The heart of configuring MySQL is in the options—several hundred of them. The values are typically Boolean where no value needed but if the option is present, it will be used; string values, or integer values. Options can be specified on the command line when starting the server or running a tool.

Here, we will briefly outline commonly used options, grouped in the following functional areas:

  • Client configuration

  • Server configuration

  • Logging configuration

  • Engine-specific configuration

  • Replication configuration

  • Buffer and cache allocation configuration

The use of some particular configuration options are in other lessons. Also, refer to the MySQL documentation at http://dev.mysql.com/doc/mysql/en/Option_files.html.

Client Configuration Options

The Table below lists the common options used by client programs, including the mysql commandline interface and the administrative tools.

Common Client Configuration Options

Option

Description

password=<password>

Password used to connect to server

port=<number>

Port number for clients to connect to server

socket=<file>

Socket file for client connections

ssl

Use SSL for your database connection

user=<name>

Username used for connecting to server

Server Configuration Options

The options outlined in Table below are directed at running the mysql server and the core functionality provided by the server.

Server Configuration Options

Option

Description

ansi

Use stricter ANSI SQL syntax versus MySQL syntax

basedir =<dir>

Path to installation directory; most paths are relative to this

chroot =<name>

During startup, chroot mysqld daemon

datadir =<dir>

Path to the data files

default-storage-engine =<name>

Use this as the default storage engine for tables

default-time-zone =<name>

Set the default time zone

init-file =<name>

At startup, read SQL commands from this file

join_buffer_size =<number>

Size of buffers used for full joins

key_buffer_size =<number>

Size of buffer allocated for index blocks for MyISAM tables

long_query_time =<number>

Query execute time which, when exceeded, triggers entry Xin slow query log

low-priority-updates

Make SELECT statements take priority over INSERT, UPDATE, or DELETE

lower_case_table_names [=<number>]

Set to 1 when tables are created in lowercase on disk and table names are case-insensitive; set to 2 for a case-insensitive file system

max_allowed_packet =<number>

Maximum packet length to send/receive from/to server

max_binlog_size =<number>

Binary log will be rotated automatically when the size exceeds this value (minimum value is 4096)

max_connections =<number>

Number of simultaneous clients allowed

max_connect_errors =<number>

Connections from a host will be blocked if number of interrupted connections from the host exceeds this number

max_join_size =<number>

Error returned if join statement will return more than this number of records

max_length_for_sort_data =<number>

Maximum number of bytes in a set of sorted records

max_sort_length =<number>

Maximum number of bytes for sorting BLOB or TEXT values

max_tmp_tables =<number>

Maximum number of temporary tables a client can keep open at a time

max_user_connections =<number>

Maximum number of active connections for a single user; set to 0 for no limit

port=<number>

Use this port number for connections

safe-user-create

Don't allow new user creation by the user who has no write privileges to the mysql.user table

read-only

Except for replication and users with SUPER privilege, make tables read-only

skip-grant-tables

Don't load grant tables on startup; open all tables to all users

skip-networking

Don't allow TCP/IP connections

skip-show-database

Prevent SHOW DATABASE commands

skip-symbolic-links

Prevent symbolic linking of tables

skip-thread-priority

Give all threads identical priorities

sort_buffer_size =<number>

When a thread needs to sort, this size is used for allocating the buffer for that sort

symbolic-links

Support symbolic links

sync-binlog =<number>

Every nth event, the binary log is synchronized to disk; set to 0 for no synchronization

tmp_table_size =<number>

If an in-memory temporary table exceeds this size, MySQL will automatically convert it to an on-disk MyISAM table

tmpdir=<dir>

Location for temporary files; specify multiple paths separated by a colon, which will be used in round-robin order

user=<name>

Run mysqld server daemon as user

Logging Configuration Options

The options listed in Table below are geared toward the logging functions of MySQL, allowing you to specify types of information to log, location of logs, detail level, and log sizes. These options can be studied further in MySQL documentation.

Common Logging Configuration Options

Option

Description

binlog-do-db =<name>

Log updates for the specified database, and exclude all others not explicitly mentioned

binlog-ignore-db =<name>

Do not log updates in the binary log for this database

log [=<file>]

File to log connections and queries

log-bin [=<file>]

Log updates using binary format

log-bin-index =<file>

File that keeps track of binary log filenames

log-error [=<file>]

Log error file

log-isam [=<file>]

File to log all MyISAM changes

log-queries-not -using-indexes

Log queries that are executed without using an index

log-short-format

Log minimal information for updates and slow queries

log-slave-updates

Slave will log updates made to the slave database; turn on for daisy-chain slaves

log-slow-queries [=<file>]

Log slow queries to this log file; default name is hostname-slow.log

log-warnings [=<number>]

Send noncritical warnings to the log file

Engine-Specific Configuration Options

Engine-specific configuration options are outlined in Table below. Storage engines are discussed in more detail in another lesson.

Common Engine-Specific Configuration Options

Option

Description

innodb

If MySQL binary allows, enable InnoDB

innodb_buffer_pool_size =<number>

Size of memory used by InnoDB to cache data and indexes

innodb_data_file_path =<path>

Path to individual files and their sizes (combines with innodb_data_home_dir)

innodb_data_home_dir =<path>

The common part for path to InnoDB tablespaces

innodb_fast_shutdown

Faster server shutdown

innodb_file_per_table

Breaks InnoDB tables into separate .ibd file in data directory

innodb_flush_log_at_trx_commit [=<number>]

Value of 0 will write and flush every second; value of 1 (recommended and the default) will write and flush at each commit; value of 2 writes at commit, flushes every second

innodb_log_arch_dir =<path>

Location for log archives

innodb_log_archive [=<number>]

If you want archived logs, set to 1

innodb_log_buffer_size =<number>

Buffer size for InnoDB when writing logs to disk

innodb_log_file_size =<number>

Size of each InnoDB log file in group, specified in megabytes; once size is reached, MySQL creates new log file

innodb_log_group_home_dir =<path>

Path to InnoDB log files

innodb_max_dirty_pages_pct =<number>

Percentage of dirty pages allowed in buffer pool

innodb_open_files =<number>

Maximum number of files InnoDB keeps open simultaneously

innodb_safe_binlog

InnoDB truncates the binary log after the last notrolled-back transaction after a recovery from a crash

max_heap_table_size =<number>

Don't allow creation of heap tables bigger than this

myisam-recover [=<name>]

Syntax is myisam-recover[=option[,option ... ]], where option can be DEFAULT, BACKUP, FORCE, or QUICK

myisam_sort_buffer_size =<number>

Size of buffer for sorting when recovering tables or creating indexes

ndbcluster

Enable NDB Cluster (if this version of MySQL supports it).

skip-bdb

Disable BDB table type

Buffer and Cache Allocation Configuration Options

The Table below lists the buffer and cache allocation options. These are tightly aligned with server performance.

Common Buffer and Cache Allocation Configuration Options

Option

Description

binlog_cache_size =<number>

Cache size for holding SQL statements headed to the binary log

flush_time =<number>

Flush all tables at the given interval (in seconds); handled by at dedicated thread

query_cache_limit =<number>

Queries with results larger than this are not cached

query_cache_size =<number>

Memory allocated for storing results from queries

Exercise: Review Options in Pre-configured Files

Duration: 15 to 20 minutes.

In this exercise, you will review options from pre-configured files.

  1. Analyze provided configuration templates from MySQL.
  2. Understand option grouping from documentation.

Configuring MySQL and Specifying Options Conclusion

This lesson covered the configuration and options in MySQL.

To continue to learn MySQL go to the top of this page and click on the next lesson in this MySQL Tutorial's Table of Contents.
 


mysql tools

How create data source in Visual Studio with data from My SQL tables with FK. more...

'mysql.proc' doesn't exist fixed problem

_

Table 'mysql.proc' doesn't exist. Fix error 1146 Table doesn't exist here...