MySQL Database configuration file my.cnf detailed

Basedir = Path	Use the given directory as the root directory (the 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	Specify a file that holds the process ID for the MYSQLD program (only for Unix/linux systems); The init-v script needs to use the process ID in this file to end the mysqld process.
socket = filename	Specify a socket file for local communication between the MySQL client and the server (only for unix/linux systems; The default setting is typically/var/lib/mysql/mysql.sock files). In a Windows environment, if the MySQL client communicates with the server through a named pipe, the –sock option gives the name of the named pipe (the default setting is MySQL).
Lower_case_table_name = 1/0	Does the name of the new directory and data table only allow lowercase letters; The default setting for this option in the Windows environment is 1 (lowercase letters only allowed).

MYSQLD Program: Language Settings

Character-sets-server = Name	The default character set for the new database or data table. To maintain compatibility 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 a 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 a Windows 2000/XP environment to communicate using named pipes (named pipe). The default name for this named pipe is MySQL, but it can be changed with the –socket option.
Local-infile [= 0]	Allow/disallow the use of the load DATA Local statement to process local files.
Myisam-recover [=opt1, Opt2, ...]	Automatically repairs all damaged MyISAM data tables at startup. There are 4 acceptable values for this option: default, BACKUP, quick, and force; They function identically to the same name as the MYISAMCHK program.
Old-passwords	Use the old algorithms in MySQL versions 3.23 and 4.0 to encrypt passwords in the MySQL database (the new encryption algorithm introduced by default with MySQL version 4.1).
Port = N	Specify a TCP/IP communication port (typically 3306 port) for the MySQL program.
Safe-user-create	Only a user with insert permission on a Mysql.user database table can use the grant command; This is a double-insurance mechanism (this user must also have grant permission to execute the grant command).
Shared-memory	Allows communication using memory (for 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	Do not use the cache area to store the host name and IP address correspondence.
Skip-name-resovle	The IP address is not resolved to the host name; The checks related to access control (mysql.user data table) are all traveling through an 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 it blocks external connections from the network and all Java client programs (Java customers use TCP/IP even in local connections).
user = Name	MYSQLD program will be executed under a given Unix/linux account after launch; Mysqld must be started from the root account in order to switch to another account after booting; 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 the INSERT command that inserts multiple new records at one time (the default setting is 8M).
Key_buffer_size = n	The RMA value used to hold the index chunk (the default setting is 8M).
Join_buffer_size = n	The buffer length allocated for the join operation when the data column participating in the JOIN operation is not indexed (the default setting is 128K).
Max_heap_table_size = n	Maximum length of the heap data table (default setting is 16M); More than this length of the heap data table 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 processes concurrently (the default setting is 100).
Query_cache_limit = n	The maximum length of the query result that is allowed to be temporarily stored in the query buffer (the default setting is 1M).
Query_cache_size = n	The maximum length of the query buffer (default setting is 0, does not open the query buffer).
Query_cache_type = 0/1/2	Query the operating mode of the buffer: 0, disable the query buffer; 1. Enable query buffers (default setting); 2, "On demand" mode, only responds to the Select Sql_cache command.
Read_buffer_size = n	The length of the buffer reserved for read operations that read data from the data table order (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 when necessary.
Read_rnd_buffer_size = n	Similar to the Read_buffer_size option, but for query results that are output in a particular order (for example, a query that uses an ORDER 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 the buffer is too small, you must create a temporary file to sort it.
Table_cache = n	The number of data tables that are open at the same time (the default setting is 64).
Tmp_table_size = n	The maximum length of the temporary heap data table (the default setting is 32M); A temporary data table over this length will be converted to a MYISAM data table and stored in a temporary file.

MYSQLD Program: Log

Log [= file]	Log all connections and all SQL commands (common query log); If the file parameter is not given, MySQL will create 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 executing (slow query log); If the file parameter is not given, MySQL will create a hostname-slow.log file in the database directory as the log file (hostname is the server hostname).
Long_query_time = n	Maximum execution time for slow queries (the default setting is 10s).
Long_queries_not_using_indexs	Log the slow query and query commands that do not use indexes at execution time (the rest of the same –log-slow-queries option).
Log-bin [= filename]	All SQL commands that modify the data (that is, the INSERT, update, and delete commands) are logged in binary format (binary change log, binary update logs). The file name for this log is FILENAME.N or the default HOSTNAME.N, where N is a 6-digit integer (the log file is sequentially numbered).
Log-bin-index = filename	The index file name of the binary logging feature. By default, this index file is the same name as the binary log file, 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 on the continuation.
Binlog-do-db = dbname	Only the changes in the given database are recorded in the binary log file, the changes in other databases are not recorded. If you need to record changes in multiple databases, you must use multiple this option in the configuration file to set one row per database.
Binlog-ignore-db = dbname	Do not log changes in a given database into a binary logfile.
Sync_binlog = n	Log files are written to the hard disk once per n logs (log information is synchronized once). 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) that describes the error condition. This logging feature cannot be disabled. If the file parameter is not 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 times the binary log enabled function.
Log-bin = Name	Enables the binary logging feature. The file name for this log is FILENAME.N or the default HOSTNAME.N, where N is a 6-digit integer (the log file sequence number).
Binlog-do/ignore-db = dbname	Only the changes in the given database are recorded in the binary log file/the changes in the given database are not logged into the binary log file.

MYSQLD Program: Mirroring (secondary mirror server)

Server-id = n	Assign a unique ID number to the server
Log-slave-updates	Enable the logging feature on the subordinate 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 (the mirror relationship definition file), it ignores this option.
Master-user = Replicusername	The user name that the secondary server uses to connect to the master server. If a mater.info file exists on the subordinate server, it ignores this option.
Master-password = passwd	The password that the secondary server uses to connect to the master server. If a mater.info file exists on the subordinate server, it ignores this option.
Master-port = n	The TCP/IP port that the secondary server uses to connect to the master server (the default setting is 3306 ports).
Master-connect-retry = n	If the connection to the master server is unsuccessful, wait for n seconds (s) before managing it (the default setting is 60s). If a subordinate server exists with a mater.info file, it ignores this option.
master-ssl-xxx = xxx	Configure the SSL communication between the master and slave servers.
Read-only = 0/1	0: Allow slave server to execute SQL command independently (default setting); 1: The secondary server can only execute SQL commands from the master server.
Read-log-purge = 0/1	1: Delete the processed SQL command immediately from the log file (default setting); 0: Do not remove the processed SQL commands from the log file immediately.
Replicate-do-table = Dbname.tablename	The meaning and usage of the –replicate-do-table option is the same, but the wildcard "%" is allowed in the database and database table names (for example: test%.%– mirrors the database tables in all databases whose names begin with "test").
REPLICATE-DO-DB = Name	This database is only mirrored.
Replicate-ignore-table = Dbname.tablename	Do not mirror this data table.
Replicate-wild-ignore-table = Dbn.tablen	These data tables are not mirrored.
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	The 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	The master and slave servers use a 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, the mirroring process continues regardless of any errors). If configured properly, the subordinate server should not have an error executing the SQL command (the SQL command executing the error on the master server will not be sent to the secondary server for mirroring); If you do not use the slave-skip-errors option, the mirror work on the subordinate server may be interrupted due to an error, and manual intervention is required to continue.

Mysqld–innodb: Basic settings, tablespace files

Skip-innodb	Do not load InnoDB datasheet driver – You can save some memory with this option if you do not need to innodb the datasheet.
Innodb-file-per-table	Create a tablespace file for each new data table instead of storing the data tables centrally in the central table space (the latter is the default setting). This option is seen in MySQL 4.1.
Innodb-open-file = n	InnoDB the number of files that the 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 need to be increased.
Innodb_data_home_dir = P	InnoDB The home directory, all directories or file paths related to the INNODB data table 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 data table: More than one file may be involved; The maximum length of each tablespace file must be given in bytes (B), megabytes (MB), or gigabytes (GB); The name of the tablespace file must be separated by semicolons; The last Tablespace 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 tablespace file ibdata1 is 1gb,ibdata2 maximum length is 1G, but it is allowed to expand to 2GB. In addition to the file name, you can also use the hard disk partition's setting name to define the table space, you must give the maximum initial length value of the tablespace plus the Newraw keyword suffix, the maximum extended length value of the table space plus the raw keyword suffix (such as/dev/hdb1:20gnewraw or/dev/ Hdb1:20graw); The default setting for MySQL version 4.0 and later is ibdata1:10m:autoextend.
Innodb_autoextend_increment = n	The tablespace file with the Autoextend property increases the number of megabytes per time (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 has not obtained the required resources after waiting for n seconds (s), use the rollback command to discard the transaction. This setting is important for discovering and handling deadlock conditions that are not recognized by the INNODB data table driver. The default setting for this option is 50s.
Innodb_fast_shutdown 0/1	Whether the InnoDB is turned off at the fastest speed, the default setting is 1, which means that the data cached in the insert buffer is not written to the data table, and those data will be written again at the next startup of the MySQL server (there is no risk because the insert buffer is an integral part of the tablespace, Data is not lost). Set this option to 0 negative because the InnoDB driver is likely not to have enough time to complete its data synchronization when the computer shuts down, and the operating system may forcibly end InnoDB before it completes the data synchronization, which results in incomplete data.

MYSQLD Program: innodb– Log

innodb_log_group_home_dir = P	The directory path used to hold the InnoDB log file (such as IB_LOGFILE0, Ib_logfile1, and so on). By default, the InnoDB driver uses the MySQL data directory as the location where you save the log files.
innodb_log_files_in_group = n	How many log files are used (the default setting is 2). The InnoDB data table driver will fill in these files in turn; When all log files are full, the log information that follows 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 log information is written to the log file and when the files are physically written (the term "synchronization") To the hard disk. Setting the value 0 means that the log is written and synchronized every second, which can reduce the number of hard disk writes, but may result in data loss; Setting the value 1 (setting) means that the log is written and synchronized each time a commit is executed, which prevents data loss, but the hard disk write operation can be very frequent; Setting a value of 2 is a general tradeoff, that is, each execution of a commit command writes a log of the next day, synchronizing every second.
innodb_flush_method = x	InnoDB Log file synchronization method (only applicable to Unix/linux systems). There are two desirable values for this option: Fdatasync, which is synchronized with the Fsync () function; O_dsync, use the O_sync () function to synchronize.
innodb_log_archive = 1	enables the archive (archive) log 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 (it is sufficient to enable the binary logging feature of the MySQL server).

MYSQLD program –innodb: Setting and optimization of buffers

Innodb_log_buffer_pool_size = n	The amount of RAM memory that is reserved for INNODB data tables and their indexes (the default setting is 8MB). This parameter has a considerable effect on speed, and if only the MYSQL/INNODB database server is running on the computer, 80% of all memory should be used for this purpose.
Innodb_log_buffer_size = n	Maximum length of the transaction log file write buffer (default setting is 1MB).
Innodb_additional_men_pool_size = n	The maximum length of the buffer allocated for the various data structures used for internal administration (the default setting is 1MB).
Innodb_file_io_threads = n	The maximum number of threads for I/O operations (hard disk write) (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 data table (the default setting is MyISAM). This setting can also be set by the –default-table-type option.
Default-timezone = Name	Set a geographic time zone for the MySQL server (if it is not the same as the local computer's geographic time zone).
Ft_min_word_len = n	Minimum word length work for full-text indexing. The default setting for this option is 4, meaning that when you create a full-text index, you do not consider those words that are constructed from 3 or fewer characters.
Max-allowed-packet = n	The maximum length of packets exchanged between the customer and the server, which should be at least greater than the length of the maximum blob block that the client program will process. The default setting for this option is 1MB.
Sql-mode = Model1, Mode2, ...	The SQL mode in which MySQL will run. The purpose of this option is to allow MySQL to maintain maximum compatibility with other database systems. The desirable values for this option include ANSI, DB2, Oracle, No_zero_date, Pipes_as_concat.

MySQL Database configuration file my.cnf detailed