The MySQL configuration file my.ini parameters notes

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

The mysqld program - directory and file
Basedir = path using a given directory as the root directory (the installation directory).
Character-sets-dir = path is kept the character set directory.
DataDir = path read the database file in a given directory.
Pid-file = filename to specify a storage process ID file for the mysqld program (only applicable to UNIX/Linux system); the Init-V script that needs to use the files in the process ID to end the mysqld process.
Socket = filename for MySQL between the client and the server's local communication specifies a socket file (only applicable to UNIX/Linux system; the default is generally /var/lib/mysql/mysql.sock file).
In the environment of Windows, if the MySQL client and server communicate via a named pipe, given by the --sock option will be the named pipe name (the default is MySQL).
lower_case_table_name = 1/Whether the 0 new directory and data table names are only allowed to use lowercase letters; this option in the Windows environment, the default setting is 1 (only allows the use of lower case letters).

The mysqld program -- the language settings
Character-sets-server = new name database or data table's default character set. In order to maintain compatibility with earlier versions of MySQL, the character set can also use the --default-character-set option is given; but this option is already somewhat outdated.
The default sort order collation-server = new name database or data sheet.
Lanuage = name in the specified language display an error message.

Mysqld program, communication, network, information security
Enable-named-pipes allows Windows 2000/XP environment of the client and server use the named pipes (named pipe) communication. The named pipe default name is MySQL, but you can use the --socket option to change.
local-infile [=0] Enable / disable the use of the LOAD DATA LOCAL statement to handle the local file.
myisam-recover [=opt1, opt2, ...] Automatic repair all the damage MyISAM data table at startup. This option can be value has 4 kinds: DEFAULT, BACKUP, QUICK and FORCE; the same options their role and myisamchk program of the same.
The old algorithm used by old-passwords MySQL 3.23 and 4 version of the MySQL database password to encrypt (new default encryption algorithm using MySQL version 4.1 introduced).
Port = n to specify a TCP/IP communication port for the MySQL program (usually 3306 port).
Safe-user-create only has INSERT permission on the mysql.user table in the database users can use the GRANT command; this is a kind of double insurance mechanism (the user must have GRANT permission to execute the GRANT command).
Shared-memory allows the use of memory (shared memory) communication (Windows only).
Shared-memory-base-name = name for shared memory block has a name (the default name is MySQL).
Skip-grant-tables does not use the MySQL database information to access control (warning: this will allow the user to any user to modify any database).
Relationship between the skip-host-cache does not use a cache to store the host name and IP address.
Skip-name-resovle does not address resolution for IP host name; and access control (table mysql.user) related to check all through the IP address.
Skip-networking allows only through a socket file (Unix/Linux) or by the named pipe (Windows) is locally connected, don't allow ICP/IP connections; this improves security, but blocked from an external network connectivity and all Java client program (Java customers also use TCP even in the local area connection/IP).
User = name mysqld procedures in the start will be performed on a given UNIX/Linux account; mysqld must start to start after the switch to another account executive account from root; mysqld_safe script uses the --user=mysql option to start the mysqld program.

Mysqld program, memory management, optimization, the query cache
Length of buffer bulk_insert_buffer_size = n command for the INSERT distribution into the many new record at a time (the default is 8M).
Key_buffer_size = n is used to store the index blocks RMA value (the default is 8M).
Join_buffer_size = n in the JOIN column of data buffer length is JOIN distribution operations without index (the default is 128K).
The maximum length of max_heap_table_size = n HEAP data sheet (the default is 16M); more than the length of the HEAP data will be stored in a temporary file instead of residing in memory.
Maximum number of simultaneous processing of max_connections = n MySQL server database connection (default is 100).
The maximum length of query_cache_limit = n allows temporary storage in the query cache query results (the default is 1M).
The maximum length of query_cache_size = n in the query cache (the default is 0, do not open the query cache).
query_cache_type = 0/1/2 the query cache working mode: 0, disable the query cache; 1, the query cache enabled (default setting); 2, "distribution according to need" mode, only in response to a SELECT SQL_CACHE command.
Read_buffer_size = n for cache read operation data from the data table in order to retain the length (the default is 128KB); this option settings can be changed if necessary with SQL SET SESSION read_buffer_size = n commands.
Read_rnd_buffer_size = n is similar to the read_buffer_size option, but for certain specific sequence (such as the use of the ORDER BY clause of the query) output results (the default is 256K).
Sore_buffer = n for cache sorting operation assignment length (the default is 2M); if the buffer is too small, you must create a temporary file to sort.
Number of tables table_cache = N open at the same time (default is 64).
Tmp_table_size = n HEAP the maximum length of the temporary data tables (the default is 32M); more than the length of the temporary data tables will be converted to MyISAM data tables and stored in a temporary file.

The mysqld program -- log
log [= file] All the connection and command all SQL log (general query log); if no file parameter is given, MySQL will create a hostname.log file as the log file in the database directory (hostname is the host name server).
log-slow-queries [= file] To execute more than the value of the long_query_time variable query command log (slow query log); if no file parameter is given, MySQL will create a hostname-slow.log file as the log file in the database directory (hostname is a server host name).
Long_query_time = n slow query execution time limit (the default is 10s).
Long_queries_not_using_indexs the slow query and does not use the index of execution of the query command all logged (the rest with the --log-slow-queries option).
log-bin [= filename] To all of the SQL commands modifications to data (i.e., INSERT, UPDATE and DELETE command) in binary format (binary change log, log binary update log). This log file name is filename.n or the default hostname.n, wherein n is an integer of a 6 digit (log file sequence number).
Index file log-bin-index = filename binary log function name. By default, the index file and binary log file the same name, but the extension is.Index rather than.Nnnnnn.
The maximum length of max_binlog_size = N binary log file (default is 1GB). Before the information on top of a binary log file exceeds the maximum length of the server, MySQL will automatically provide a new binary log file connection.
Binlog-do-db = dbname only changes in a given database to binary log file, changes in the database is not recorded in other. If you need to record the changes of multiple databases, it must be in the configuration file using a plurality of the option to set, each database in a row.
Binlog-ignore-db = dbname does not change the given in the database to binary log file.
Sync_binlog = n / after n log writes the log files are written to disk at a time (a synchronization log information). N=1 is the most safe, but the efficiency is the lowest. The default setting is n=0, mean work synchronously by the operating system is responsible for the binary log file.
log-update [= file] Record the error log file name (the error log). The log function cannot be disabled. If no file parameter is given, MySQL will use the hostname.err as a log file name.

The mysqld program - mirror (main control the mirror server)
Server-id = n to the server assigns a the one and only ID number; the range of n is 32 times of 1~2 enabled binary log function.
Log-bin = name enabled binary log function. This log file name is filename.n or the default hostname.n, where n is the number of a 6 digit (log file sequence number).
binlog-do/Ignore-db = dbname only changes in a given database to binary log file / not to change given in the database to binary log file.

The mysqld program - mirror (secondary mirror server)
Server-id = n to the server is assigned a unique ID number
Log-slave-updates enable log function dependent on the server, so that the computer can be used to form an image chain(A->B->C).
Host name or IP address of the master-host = hostname master server. If the file exists on the slave (mirror image relationship definition file), it will ignore this option.
Master-user = replicusername slave server to connect to the master server name. If the file exists on the slave, it will ignore this option.
Master-password = passwd slave server to connect to the master server password. If the file exists on the slave, it will ignore this option.
Master-port = n slave server to connect to the master server TCP/IP port (the default is 3306 port).
Master-connect-retry = n if the server and the main control connection is not successful, then wait for N seconds (s) after management mode (the default is 60s). If the slave server in an file,
It will ignore this option.
Master-ssl-xxx = XXX to the main, from the SSL communication between the server configuration.
read-only = 0/1 0: allows the slave server independently execute the SQL command (the default); 1: slave server can only be executed from the master server command SQL.
read-log-purge = 0/1 1: the processing of the SQL command immediately removed from the relay log file (default setting); 0: is not the processing of the SQL command immediately removed from the relay log file.
Replicate-do-table = dbname.tablename and --replicate-do-table options the meaning and usage of the same, but the database and the database table name wildcard characters are allowed to appear"%"
(for example: test%.%-- on the name to "all database test" at the beginning, so the database tables for image processing).
Replicate-do-db = name for image processing to the database.
Replicate-ignore-table = dbname.tablename. This data sheet for image processing.
Replicate-wild-ignore-table = dbn.tablen is wrong with these data sheet for image processing.
Replicate-ignore-db = dbname is wrong with the database mirroring.
replicate-rewrite-db = db1name > Db2name database db1name to the image processing a master database on server db2name database for the slave.
Host name, report-host = hostname slave server; this information only with SHOW SLAVE HOSTS command - the master server can generate a slave server list with this command.
Slave-compressed-protocol = 1, from the server using a compressed format of communication -- if they are supported to do so.
slave-skip-errors = n1, n2, All... Or even error code N1, N2 error, image processing work to continue (that is, no matter what the error, image processing work to continue).
If configured properly, the slave server should not error occurred during the execution of the SQL command (error executing in the main control server the SQL command will not be sent to the slave server to do image processing); if you do not use
The slave-skip-errors option, the slave image work may for error interrupt, interrupt is required after artificial participation to continue.

Mysqld--InnoDB-- basic settings, table space file
Skip-innodb does not load the InnoDB data table driven program -- if not InnoDB tables, you can use this option to save some memory.
Innodb-file-per-table for each new data table to create a table space file rather than the data tables are stored in the table space (which is the default). This option was found in MySQL 4.1.
Innodb-open-file = n InnoDB data table driven program can open up to the number of files (default is 300). If you use the innodb-file-per-table option and the need to open a lot
Data table, this number is likely to increase.
Innodb_data_home_dir = P InnoDB home directory, all of the InnoDB data sheet directory or file paths are relative to this path. By default, the MySQL home directory is a directory of data.
Innodb_data_file_path = ts for receiving InnoDB data table space: may involve more than one file; the maximum length of each table space file must be in the byte (B), megabytes (MB) or
Gigabyte (GB) is given; the table space file names must be separated with semicolon; finally a table space file can also take a AUTOEXTEND attribute and a maximum length(max:n).
For example, ibdata1:1G; ibdata2:1G:autoextend:max:2G means: the maximum length of the file ibdata1 table space is 1GB, the maximum length of ibdata2 is 1G, but allow it to expand to 2GB.
In addition to the file name, also can use the hard disk partition set name to define table space, must give the maximum initial length table space value plus the newraw keyword as a suffix, for maximum extended length table space value plus
The raw keyword as a suffix (e.g. /dev/hdb1:20Gnewraw or /dev/hdb1:20Graw); the MySQL version 4 and higher default setting is ibdata1:10M:autoextend.
Innodb_autoextend_increment = n with a AUTOEXTEND attribute table space file at a time of increasing the number of megabytes (default is 8MB). This property does not involve data table file specific, those documents.
Increase the speed is relatively small.
Innodb_lock_wait_timeout = n if a transaction waiting for N seconds (s) is not required to obtain resources, use the ROLLBACK command to abandon the transaction. This setting for discovery and treatment could not be
Plays an important role in the InnoDB data sheet deadlock conditions drivers identified. The default setting is 50s.
innodb_fast_shutdown 0/1 is the fastest rate close to InnoDB, the default is 1, meaning is not cache the data in a INSERT buffer to write data table, the data will be in the next MySQL server
Then write a startup (do not what risk, because the INSERT cache is a part of data loss, not the table space). This option is set to 0 opposite danger, because when the computer is turned off,
The InnoDB driver probably didn't have enough time to finish it the data synchronization, the operating system may be done before the data synchronization in it forced the end of InnoDB, which will lead to incomplete data.

The mysqld program --InnoDB-- log
The directory path innodb_log_group_home_dir = P is used to store the InnoDB log file (such as ib_logfile0, ib_logfile1 etc.). By default, the InnoDB driver will use the MySQL data.
Recorded as their save log file location.
Innodb_log_files_in_group = n to use the number of log files (the default is 2). Table InnoDB driver in a round robin order fill out these papers; when all the log files are full,
The log information to the maximum length of the first log file (default is 5MB). This length must be based on the MB (megabytes) or GB (gigabytes) set as a unit.
innodb_flush_log_at_trx_commit = 0/1/2 this option determines what time to log information to a log file and what time these physical file to write (termed "synchronous") to the hard disk.
Set the value 0 means every second to write a log and synchronization, which can reduce the number of disk write operations, but may result in data loss; set the value 1 (set) mean in each perform a COMMIT
The command to write a log and synchronization, which can prevent data loss, but the hard disk write operations may be very frequent; set the value of 2 is usually a compromise, in which each perform a COMMIT command to write a log,
Every second of time synchronization.
The synchronous approach to innodb_flush_method = x InnoDB log file (only applicable to UNIX/Linux system). This option can be value has two kinds: fdatasync, fsync () function to synchronize; O_DSYNC,
Using O_SYNC () function to synchronize.
Innodb_log_archive = 1 to enable the InnoDB driver for archive (file) log, the log information is written into the ib_arch_log_n file. Do not enable this log function used in conjunction with the InnoDB and MySQL
Much meaning (MySQL enabled server binary log function is enough to use).

Set up and optimize the mysqld program --InnoDB-- buffer
The amount of RAM memory innodb_log_buffer_pool_size = n for InnoDB data table and its indexes and retention (the default is 8MB). This parameter has a significant impact on the speed, if the computer only operation
MySQL/The InnoDB database server, you should put all the memory of 80% for this purpose.
The maximum length of innodb_log_buffer_size = n transaction log file write buffer (the default is 1MB).
Innodb_additional_men_pool_size = n for various data structures used in the internal management of the distribution of the maximum length buffer (the default is 1MB).
innodb_file_io_threads = n I/The O operation (hard disk write operations) a maximum number of threads (default is 4).
Innodb_thread_concurrency = n InnoDB to drive the maximum number of threads to a program can use at the same time (default is 8).

The mysqld program -- the other options
Bind-address = IPADDR MySQL server IP address. If the MySQL server where the computer has multiple IP addresses, this option will be very important.
The default data type default-storage-engine = new type data table (the default is MyISAM). This setting can be set through the --default-table-type options.
Default-timezone = name set a geographical zone for the MySQL server (if it and geographical zone of the local computer is not the same).
The minimum word length of ft_min_word_len = n full-text index. The default setting of this option is 4, meaning not consider those consisting of 3 or fewer characters to construct words in creating index.
The maximum length of the data packet exchange between Max-allowed-packet = n client and server, this number at least should be greater than the client will have to deal with the maximum length of the BLOB block. The default setting is 1MB.
Sql-mode = model1, mode2, ... MySQL will run in which SQL mode. This option is the role of MySQL and other database system compatible with the greatest degree of. This option can be value including
ansi, db2, oracle, no_zero_date, pipes_as_concat.

Note: if an option is given in the configuration file is mysqld cannot be identified, the MySQL server will not start.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Harvey at December 09, 2013 - 5:37 PM