MySQL Database configuration file my.cnf detailed

Basedir = Path	Use the given directory as the root directory (installation directory).
Character-sets-dir = Path	Gives the directory where the character set is stored.
DataDir = Path	Reads the database file from the given directory.
Pid-file = filename	Specifies a file that holds the process ID for the MYSQLD program (for the unix/linux system only); The init-v script needs to use the process ID in this file to end the mysqld process.
socket = filename	Specifies a socket file for the local communication between the MySQL client and the server (for Unix/linux systems only; The default setting is typically/var/lib/mysql/mysql.sock files). In a Windows environment, if the MySQL client communicates with the server through named pipes, the –sock option gives the name of the named pipe (the default setting is MySQL).
Lower_case_table_name = 1/0	Whether the name of the new directory and datasheet is allowed to use only lowercase letters; The default setting for this option in the Windows environment is 1 (only lowercase letters are allowed).

MYSQLD Program: Language Settings

Character-sets-server = Name	The default character set for the new database or datasheet. In order to be compatible with earlier versions of MySQL, this character set can also be given with the –default-character-set option; But this option has become a bit outdated.
Collation-server = Name	The default sort method for the new database or datasheet.
Lanuage = Name	Displays an error message in the specified language.

MYSQLD Program: Communication, network, information security

Enable-named-pipes	Allows customers and servers in Windows 2000/XP environments to communicate using named pipes (named pipe). The default name for this named pipe is MySQL, but you can change it with the –socket option.
Local-infile [= 0]	Allows/disables the use of the load DATA Local statement to work with native files.
Myisam-recover [=opt1, Opt2, ...]	Automatically repairs all damaged MyISAM data tables at startup. The value of this option is 4: default, BACKUP, quick, and force; They act the same as the MYISAMCHK program's option.
Old-passwords	Use the old algorithms in the MySQL 3.23 and version 4.0 to encrypt the passwords in the MySQL database (the new encryption algorithm is introduced by default with MySQL version 4.1).
Port = N	Specify a TCP/IP communication port (usually port 3306) for the MySQL program.
Safe-user-create	Only users with insert permissions on the Mysql.user database table can use the grant command; This is an insurance mechanism (this user must also have grant permission to execute the grant command).
Shared-memory	Allows the use of memory (shared memory) for communication (Windows only).
Shared-memory-base-name = Name	Give a name to the shared memory block (the default name is MySQL).
Skip-grant-tables	Do not use the information in the MySQL database for access control (warning: This will allow any user to modify any database).
Skip-host-cache	The cache area is not used to hold the corresponding relationship between the host name and the IP address.
Skip-name-resovle	Does not resolve the IP address to the host name; All the checks associated with access control (Mysql.user datasheet) travel through the IP address.
Skip-networking	Local connections are allowed only through a socket file (Unix/linux system) or through Named pipes (Windows systems), and ICP/IP connections are not allowed; This improves security, but blocks external connections from the network and all Java clients (the Java client uses TCP/IP even in local connections).
user = Name	The MYSQLD program will be executed under the given Unix/linux account upon startup; Mysqld must be started from the root account to switch to another account after startup; The Mysqld_safe script will use the –USER=MYSQL option by default to start the MYSQLD program.

MYSQLD Program: Memory management, optimization, query buffer

Bulk_insert_buffer_size = n	The buffer length assigned to insert commands for inserting multiple new records at a time (the default setting is 8M).
Key_buffer_size = n	The RMA value used to hold the index block (the default setting is 8M).
Join_buffer_size = n	The buffer length allocated for the join operation when the data column that participates in the join operation is not indexed (the default setting is 128K).
Max_heap_table_size = n	The maximum length of the heap datasheet (the default setting is 16M); Heap data tables exceeding this length will be stored in a temporary file instead of residing in memory.
Max_connections = n	The maximum number of database connections that the MySQL server handles at the same time (the default setting is 100).
Query_cache_limit = n	Allows the maximum length of query results that are temporarily stored in the query buffer (the default setting is 1M).
Query_cache_size = n	Query the maximum length of the buffer (the default setting is 0, do not open the query buffer).
Query_cache_type = 0/1/2	Query buffer mode of operation: 0, disable query buffer; 1, enable query buffer (default settings); 2, "On demand" mode, only responds to the Select Sql_cache command.
Read_buffer_size = n	The length of the buffer cache reserved for read operations that read data sequentially from the datasheet (the default setting is 128KB); The setting value of this option can be changed with the SQL command set session read_buffer_size = n command, if necessary.
Read_rnd_buffer_size = n	Similar to the Read_buffer_size option, but is for query results that are output in a particular order (such as a query that uses an orders by clause) (the default setting is 256K).
Sore_buffer = n	The length of the buffer allocated for the sort operation (the default setting is 2M); If this buffer is too small, you must create a temporary file to sort.
Table_cache = n	The number of data tables opened at the same time (the default setting is 64).
Tmp_table_size = n	The maximum length of the temporary heap datasheet (the default setting is 32M); Temporary data tables exceeding this length will be converted to MyISAM data tables and stored in a temporary file.

MYSQLD Program: Log

Log [= file]	Log all the connections and all the SQL commands (common query log); If no file argument is given, MySQL creates a hostname.log file in the database directory as the log file (hostname is the hostname of the server).
log-slow-queries [= file]	Log query commands that exceed the value of the Long_query_time variable when executed (slow query log); If no file argument is given, MySQL creates a hostname-slow.log file in the database directory as the log file (hostname is the server hostname).
Long_query_time = n	Upper bound for slow query execution (the default setting is 10s).
Long_queries_not_using_indexs	Slow queries and query commands that do not use an index at execution time are all logged (with the remaining –log-slow-queries option).
Log-bin [= filename]	All SQL commands that modify the data (that is, insert, UPDATE, and delete commands) are logged in binary format (binary change log binary update log). The file name of this log is either FILENAME.N or default HOSTNAME.N, where N is a 6-digit integer (the log file is numbered sequentially).
Log-bin-index = filename	The index file name of the binary log feature. By default, this index file is the same as the binary log file name, but the suffix name is. index instead of. nnnnnn.
Max_binlog_size = n	The maximum length of the binary log file (the default setting is 1GB). Before the amount of information in the previous binary log file exceeds this maximum length, the MySQL server automatically provides a new binary log file to continue.
Binlog-do-db = dbname	Only the changes in a given database are recorded in binary log files, and the changes in other databases are not documented. If you need to document the changes in multiple databases, you must set up the configuration file with multiple options, one row per database.
Binlog-ignore-db = dbname	Do not record changes in a given database into binary log files.
Sync_binlog = n	The log file is written to the hard disk once every n the next day (log information is synchronized). N=1 is the safest approach, but the least efficient. The default setting is N=0, which means that the operating system is responsible for synchronizing the binary log files.
log-update [= file]	Log file name (error log) documenting the error situation. This logging feature cannot be disabled. If no file argument is given, MySQL uses Hostname.err as the name of the log file.

MYSQLD Program: Mirroring (Master mirror server)

Server-id = n	Assign a unique ID number to the server; The value range of n is 32 1~2 to enable the binary logging feature.
Log-bin = Name	Enable the binary logging feature. The file name of this log is either FILENAME.N or default HOSTNAME.N, where N is a 6-digit integer (log file sequence number).
Binlog-do/ignore-db = dbname	Only the changes in a given database are logged into the binary log file/not the changes in the given database are recorded in the binary log file.

MYSQLD Program: Mirroring (dependent mirror server)

Server-id = n	Assign a unique ID number to the server
Log-slave-updates	Enable logging on the secondary server so that this computer can be used to form a mirror chain (a->b->c).
Master-host = hostname	Host name or IP address of the master server. If a mater.info file exists on the secondary server (mirroring the relationship definition file), it ignores this option.
Master-user = Replicusername	The user name used by the secondary server to connect to the master server. If the Mater.info file exists on the secondary server, it ignores this option.
Master-password = passwd	The password used by the secondary server to connect to the master server. If the Mater.info file exists on the secondary server, it ignores this option.
Master-port = n	The TCP/IP port used by the secondary server to connect to the master server (the default setting is Port 3306).
Master-connect-retry = n	If the connection to the master server is unsuccessful, wait n seconds (s) before you manage it (the default setting is 60s). If the secondary server has a mater.info file, it ignores this option.
master-ssl-xxx = xxx	Configure SSL communication between the primary and the server.
Read-only = 0/1	0: Allow the subordinate server to execute SQL commands independently (default settings); 1: The secondary server can only execute SQL commands from the master server.
Read-log-purge = 0/1	1: Remove the processed SQL command from the log file immediately (default setting); 0: Do not delete the processed SQL command from the log file immediately.
Replicate-do-table = Dbname.tablename	The –replicate-do-table option has the same meaning and usage, but the database and database table names allow wildcard "%" (for example: test%.%– mirrors the database tables in all databases that begin with the name "test").
REPLICATE-DO-DB = Name	Mirroring this database is only handled.
Replicate-ignore-table = Dbname.tablename	This data table is not mirrored.
Replicate-wild-ignore-table = Dbn.tablen	Do not mirror these data tables.
Replicate-ignore-db = dbname	Do not mirror this database.
Replicate-rewrite-db = db1name > Db2name	Db1name database mirroring on the master database is processed as a db2name database on the subordinate server.
Report-host = hostname	Host name of the subordinate server; This information is only relevant to the show SLAVE hosts command-the master server can use this command to generate a list of subordinate servers.
Slave-compressed-protocol = 1	Master, from the server use compressed format for communication-if they all support this.
Slave-skip-errors = n1, N2, ... or all	Even if the error code is N1, N2, and so on, the mirroring process continues (that is, no matter what happens, the mirroring process continues to work). If properly configured, the secondary server should not have an error executing the SQL command (the SQL command that executes the error on the master server is not sent to the secondary server for mirroring); If you do not use the slave-skip-errors option, mirroring on the secondary server may be interrupted by an error that requires human involvement to continue.

Mysqld–innodb: Basic settings, table space files

Skip-innodb	Do not load the InnoDB datasheet driver – You can save some memory with this option if you do not need to innodb the data table.
Innodb-file-per-table	Create a table space file for each new datasheet instead of storing the data tables in a central tablespace (the default setting). This option is found in MySQL 4.1.
Innodb-open-file = n	The maximum number of files that the InnoDB data table driver can open at the same time (the default setting is 300). If you use the innodb-file-per-table option and you need to open many data tables at the same time, this number is likely to increase.
Innodb_data_home_dir = P	InnoDB The home directory, all the directories or file paths associated with the InnoDB datasheet are relative to this path. By default, this home directory is the MySQL data directory.
Innodb_data_file_path = ts	Table space used to hold InnoDB as a datasheet: More than one file may be involved; The maximum length of each tablespace file must be in bytes (B), megabytes (MB), or gigabytes (GB); The name of the tablespace file must be separated by semicolons; The last table space file can also take a Autoextend property and a maximum length (max:n). For example, ibdata1:1g; IBDATA2:1G:AUTOEXTEND:MAX:2G means: The maximum length of the table space file ibdata1 is the maximum length of the 1GB,IBDATA2 is also 1G, but allows it to expand to 2GB. In addition to the filename, you can define the table space with the set name of the hard disk partition, at which point the maximum initial length value of the tablespace must be appended with the Newraw keyword, and the maximum extended length value of the table space plus the raw keyword suffix (for example,/dev/hdb1:20gnewraw or/dev/ Hdb1:20graw); The default setting for MySQL 4.0 and later is ibdata1:10m:autoextend.
Innodb_autoextend_increment = n	The size of the tablespace file with the Autoextend property increases by the number of megabytes (the default setting is 8MB). This property does not involve specific data table files, and those files are relatively small in speed.
Innodb_lock_wait_timeout = n	If a transaction does not have the required resources after waiting for n seconds (s), the transaction is discarded using the rollback command. This setting is important for discovering and handling deadlock conditions that could not be identified by the InnoDB datasheet driver. The default setting for this option is 50s.
Innodb_fast_shutdown 0/1	Whether to turn off InnoDB at the fastest speed, the default setting is 1, meaning that the data cached in the insert buffer is not written to the data table, and that data will be written again at the next boot of the MySQL server (this is not a risk because the insert buffer is an integral part of the tablespace, Data is not lost). Setting this option to 0 is dangerous because when the computer shuts down, the InnoDB driver may not have enough time to complete its data synchronization, and the operating system may force the end of the InnoDB before it completes the data synchronization process, which can result in incomplete data.

MYSQLD Program: innodb– Log

innodb_log_group_home_dir = P	The directory path used to hold InnoDB log files (such as IB_LOGFILE0, Ib_logfile1, and so on). By default, the InnoDB driver uses the MySQL data directory as the location to save the log files for itself. How many log files are used by
innodb_log_files_in_group = n	(the default setting is 2). The InnoDB datasheet driver will fill in the files in turn; When all the log files are full, subsequent log information is written to the maximum length of the first log file (the default setting is 5MB). This length must be set in megabytes (megabytes) or GB (gigabytes).
innodb_flush_log_at_trx_commit = 0/1/2	This option determines when the log information is written to the log file and when the files are physically written (the term " Sync ") to the hard drive. Setting a value of 0 means writing a log every second and synchronizing, which can reduce the number of hard drive writes, but may result in data loss; Setting a value of 1 (setting settings) means writing a log and synchronizing each time a commit is executed, which prevents data loss, but hard disk writes can occur frequently; Setting a value of 2 is a general tradeoff, that is, each time a commit is executed, a log is written, and every second is synchronized.
innodb_flush_method = x	InnoDB Log file synchronization method (for the Unix/linux system only). The desirable values for this option are two: Fdatasync, synchronized with the Fsync () function; O_dsync, synchronize with the O_sync () function.
innodb_log_archive = 1	enables the archive (file) logging feature of the InnoDB driver to write log information to the Ib_arch_log_n file. Enabling this logging feature does not make much sense when InnoDB is used with MySQL (the binary logging feature of the MySQL server is enabled enough).

MYSQLD program –innodb: Settings and optimization of buffer

Innodb_log_buffer_pool_size = n	The amount of RAM memory reserved for INNODB data tables and their indexes (the default setting is 8MB). This parameter has a considerable effect on speed, and if you have a MYSQL/INNODB database server running on your computer, you should use 80% of all memory for this purpose.
Innodb_log_buffer_size = n	Maximum length for the transaction log file write operation buffer (the default setting is 1MB).
Innodb_additional_men_pool_size = n	The maximum length of buffers allocated for various data structures for internal management (the default setting is 1MB).
Innodb_file_io_threads = n	The maximum number of threads for I/O operations (hard Drive writes) (the default setting is 4).
Innodb_thread_concurrency = n	The maximum number of threads that the InnoDB driver can use at the same time (the default setting is 8).

MYSQLD Program: Other options

Bind-address = ipaddr	The IP address of the MySQL server. This option is important if the computer on which the MySQL server resides has multiple IP addresses.
Default-storage-engine = Type	The default data table type for the new datasheet (the default setting is MyISAM). This setting can also be set through the –default-table-type option.
Default-timezone = Name	Set a geographic time zone for the MySQL server (if it is different from the local computer's geographic time zone).
Ft_min_word_len = n	The minimum word length for full-text indexing work. The default setting for this option is 4, which means that when you create a Full-text index, you don't think about 3 or fewer characters to build the word.
Max-allowed-packet = n	The maximum length of packets exchanged between the client and the server, which should be at least greater than the maximum BLOB block that the client will process. The default setting for this option is 1MB.
Sql-mode = Model1, Mode2, ...	Which SQL mode MySQL will run in. The purpose of this option is to maintain MySQL to the fullest extent compatible with other database systems. The desirable values for this option include ANSI, DB2, Oracle, No_zero_date, Pipes_as_concat.

Note: If an option given in the configuration file is mysqld unrecognized, the MySQL server will not start