MySQL configuration parameters _ MySQL

There are several default configuration templates after successful installation of MySQL, the list is as follows: my-hugecnf: For high-end product servers, including 1 to 2 GBRAM, mainly run mysqlmy-innodb-heavy-4Gini: for installation of innodb only, A maximum of 4 GBRAM is available. it supports large queries and several default configuration templates after MySQL is successfully installed. the list is as follows:
My-huge.cnf:High-end product servers, including 1 to 2 gb ram, mainly run mysql
My-innodb-heavy-4G.ini:It is used for installation of innodb only and has a maximum of 4 gb ram. it supports large queries and low traffic.
My-large.cnf:Used for medium-scale product servers, including approximately 512 M RAM
My-medium.cnf:Used for low-end product servers, including few memory (less than 128 M)
My-small.cnf:For servers with the lowest device, only a little memory (less than 512 MB)

The configuration of my. cnf is described as follows:

Basedir = path	Use the given directory as the root directory (installation directory ).
Character-sets-dir = path	Provides a directory for storing character sets.
Datadir = path	Reads database files from a given directory.
Pid-file = filename	Specify a file for the mysqld program to store the process ID (only applicable to UNIX/Linux systems); the Init-V script needs to use the process ID in the file to end the mysqld process.
Socket = filename	Specifies a socket file for the local communication between the MySQL client program and the server (only applicable to UNIX/Linux systems; the default setting is generally the/var/lib/mysql. sock file ). In Windows, if the MySQL client communicates with the server through the named pipe, The-sock option will give the name of the named pipe (the default value is MySQL ).
Lower_case_table_name = 1/0	Whether the name of the new directory and data table can only contain lower-case letters. this option is set to 1 by default in Windows (only lower-case letters are allowed ).

Mysqld program: language settings

Character-sets-server = name	The default character set of the new database or data table. To be compatible with earlier MySQL versions, you can use the-default-character-set option for this character set. However, this option is outdated.
Collation-server = name	The default sorting method of the new database or data table.
Lanuage = name	Displays error information in the specified language.

Mysqld program: communication, network, and information security

Enable-named-pipes	Allows customers and servers in Windows 2000/XP to communicate with each other using a named pipe (named pipe. The default name of this named pipeline is MySQL, but it can be changed using the-socket option.
Local-infile [= 0]	Allow/prohibit the use of the load data local statement to process LOCAL files.
Myisam-recover [= opt1, opt2,...]	All damaged MyISAM data tables are automatically repaired at startup. There are four optional values for this option: DEFAULT, BACKUP, QUICK, and FORCE; they are the same as those of the myisamchk program.
Old-passwords	Use the old algorithms in MySQL 3.23 and 4.0 to encrypt passwords in the mysql database (the new encryption algorithm introduced since MySQL 4.1 is used by default ).
Port = n	Specify a TCP/IP communication port (usually Port 3306) for the MySQL program ).
Safe-user-create	The GRANT command can be used only by users with the INSERT permission on the mysql. user database table. this is a double-insurance mechanism (this user must have the GRANT permission to execute the GRANT command ).
Shared-memory	Allows communication using the memory (shared memory) (only applicable to Windows ).
Shared-memory-base-name = name	Name 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 ing between host names and IP addresses.
Skip-name-resovle	Do not resolve the IP address to the host name. all checks related to access control (mysql. user data table) are performed through the IP address.
Skip-networking	Only one socket File (Unix/Linux system) or a named pipe (Windows system) is allowed for local connection, and ICP/IP connection is not allowed. this improves security, however, it blocks external connections from the network and all Java client programs (Java clients use TCP/IP even in local connections ).
User = name	After the mysqld program is started, it will be executed under a given UNIX/Linux account; after the mysqld program is started from the root account, it can be switched to another account for execution; the mysqld_safe script uses the-user = mysql option by default to start the mysqld program.

Mysqld program: memory management, optimization, and query cache

Bulk_insert_buffer_size = n	The length of the cache allocated for the INSERT command that inserts multiple new records at a time (the default value is 8 MB ).
Key_buffer_size = n	The RMA value used to store the index block (the default value is 8 MB ).
Join_buffer_size = n	The cache length allocated for the JOIN operation when the data columns involved in the JOIN operation do not have indexes (the default value is 128 K ).
Max_heap_table_size = n	The maximum length of a HEAP data table (16 MB by default). a HEAP data table exceeding this length will be saved to a temporary file instead of resident in the memory.
Max_connections = n	The maximum number of database connections that the MySQL server processes simultaneously (100 by default ).
Query_cache_limit = n	The maximum length of the query result temporarily stored in the query cache (1 MB by default ).
Query_cache_size = n	The maximum length of the query cache area (the default value is 0, and no query cache area is opened ).
Query_cache_type = 0/1/2	The query cache mode is 0. the query cache is disabled. 1. the query cache mode is enabled (default). 2. the SELECT SQL _CACHE mode is used. only the SELECT SQL _CACHE command is returned.
Read_buffer_size = n	The length of the cache reserved for read operations that read data from data tables sequentially (KB by default ); when necessary, you can use the SQL command SET SESSION read_buffer_size = n to change the value of this option.
Read_rnd_buffer_size = n	Similar to the read_buffer_size option, but for the query results output in a specific ORDER (for example, query using the order by clause) (the default value is 256 K ).
Sore_buffer = n	The length of the cache area allocated for the sorting operation (2 MB by default); if the cache area is too small, a temporary file must be created for sorting.
Table_cache = n	The number of data tables opened at the same time (the default value is 64 ).
Tmp_table_size = n	The maximum length of a temporary HEAP data table (32 MB by default). a temporary data table that exceeds this length is converted to a MyISAM data table and saved to a temporary file.

Mysqld program: log

Log [= file]	Log all connections and all SQL commands (common query logs). If the file parameter is not provided, MySQL creates a hostname in the Database Directory. the log file is used as the log file (hostname is the host name of the server ).
Log-slow-queries [= file]	Log the query command that exceeds the long_query_time variable value during execution (slow query log). If the file parameter is not provided, mySQL creates a hostname-slow.log file in the database directory as this log file (hostname is the name of the server host ).
Long_query_time = n	The maximum execution time of slow queries (10 s by default ).
Long_queries_not_using_indexs	All query commands that do not use indexes during slow query and execution are recorded in the log (the rest are the same as the-log-slow-queries option ).
Log-bin [= filename]	All SQL commands (that is, the INSERT, UPDATE, and DELETE commands) that modify data are logged in binary format (binary change log, binary update log ). The log file name is filename. n or the 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, the index file and the binary log file have the same name, but the suffix is. index rather than. nnnnnn.
Max_binlog_size = n	The maximum length of the binary log file (1 GB by default ). Before the amount of information in the previous binary log file exceeds the maximum length, the MySQL server will automatically provide a new binary log file.
Binlog-do-db = dbname	Only the changes in the given data warehouse are recorded in the binary log file. Changes in other databases are not recorded. If you need to record changes in multiple databases, you must use multiple options in the configuration file to set one row for each database.
Binlog-ignore-db = dbname	Changes in a given database are not recorded in binary log files.
Sync_binlog = n	After n log writes, the log file is written to the hard disk once (the log information is synchronized once ). N = 1 is the safest practice, but the efficiency is the lowest. The default value is n = 0, which means that the operating system is responsible for synchronizing binary log files.
Log-update [= file]	Name of the log file that records the error (error log ). This log feature cannot be disabled. If the file parameter is not provided, MySQL uses hostname. err as the name of the log file.

Mysqld program: Image (master image server)

Server-id = n	Assign a unique ID to the server. the value range of n is 1 ~ 2 to the power of 32 to enable the binary log function.
Log-bin = name	Enable the binary log feature. The log file name is filename. n or the default hostname. n, where n is a 6-digit integer (sequential log file 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 recorded in the binary log file.

Mysqld program: Image (subordinate image server)

Server-id = n	Assign a unique ID to the server
Log-slave-updates	Enable the log function on the slave server so that this computer can be used to form an image chain (A-> B-> C ).
Master-host = hostname	Host name or IP address of the master server. If the mater.info file (image link definition file) exists on the slave server, it ignores this option.
Master-user = replicusername	The username used by the slave server to connect to the master server. If the mater.info file exists on the slave server, it ignores this option.
Master-password = passwd	The password used by the slave server to connect to the master server. If the mater.info file exists on the slave server, it ignores this option.
Master-port = n	The slave server is used to connect the TCP/IP port of the master server (Port 3306 is set by default ).
Master-connect-retry = n	If the connection to the master server fails, wait for n seconds (s) before the management (60 s by default ). If the slave server has the mater.info file, it ignores this option.
Master-ssl-xxx = xxx	Configure SSL communication between the master and slave servers.
Reads-only = 0/1	0: allow the slave server to execute SQL commands independently (set by default); 1: The slave server can only execute SQL commands from the master server.
Read-log-purged = 0/1	1: delete the processed SQL command from the relay log file immediately (set by default); 0: Do not delete the processed SQL command from the relay log file immediately.
Replicate-do-table = dbname. tablename	The meaning and usage of the "-replicate-do-table" option are the same, but wildcards "%" (for example, test %) are allowed in database and database table names. %-image processing is performed on all database tables whose names start with "test ).
Replicate-do-db = name	Only image processing is performed for this database.
Replicate-ignore-table = dbname. tablename	Image processing is not performed on this data table.
Replicate-wild-ignore-table = dbn. tablen	Image processing is not performed on these data tables.
Replicate-ignore-db = dbname	Image processing is not performed on this database.
Replicate-rewrite-db = db1name> db2name	Process the db1name database image on the master database as the db2name database on the slave server.
Report-host = hostname	The host name of the SLAVE server. This information is only related to the show slave hosts command. the master server can use this command to generate a list of SLAVE servers.
Slave-compressed-protocol = 1	Master and slave servers use the compression format for communication-if they all support this.
Slave-skip-errors = n1, n2 ,... Or all	Even if the error code is n1 or n2, the image processing continues (no matter what error occurs, the image processing continues ). If properly configured, the slave server should not encounter an error when executing the SQL command (the SQL command that fails to be executed on the master server will not be sent to the slave server for image processing ); if you do not use the slave-skip-errors option, the image on the slave server may be interrupted due to an error. after the interruption, you must manually participate in the operation to continue.

Mysqld-InnoDB: basic settings, tablespace files

Skip-innodb	Do not load the InnoDB data table driver-if InnoDB data tables are not needed, you can use this option to save some memory.
Innodb-file-per-table	Create a tablespace file for each new data table instead of storing the data table in the central tablespace (the latter is set by default ). This option is started with MySQL 4.1.
Innodb-open-file = n	The maximum number of files that can be opened by the InnoDB data table driver at the same time (300 by default ). If the innodb-file-per-table option is used and many data tables need to be opened at the same time, this number may need to be increased.
Innodb_data_home_dir = p	The InnoDB main directory. all directories or file paths related to the InnoDB data table are relative to this path. By default, this main directory is the data directory of MySQL.
Innodb_data_file_path = ts	Table space used to hold InnoDB data tables: It may involve more than one file. The maximum length of each table space file must be in bytes (B), megabytes (MB), or gigabytes (GB) given in units; the names of tablespace files must be separated by semicolons; the last tablespace file can also contain an autoextend attribute and a maximum length (max: n ). For example, ibdata1: 1G; ibdata2: 1G: autoextend: max: 2G indicates that the maximum length of ibdata1 in a tablespace file is 1 GB, and the maximum length of ibdata2 is 1 GB, but it can be expanded to 2 GB. In addition to the file name, you can also define the table space by setting the name of the hard disk partition. at this time, you must add the newraw keyword to the maximum initial length value of the table space for the suffix, add the raw keyword to the maximum extended length of the tablespace (for example,/dev/hdb1: 20Gnewraw or/dev/hdb1: 20 Graw ); the default setting for MySQL 4.0 and later versions is ibdata1: 10 M: autoextend.
Innodb_autoextend_increment = n	The maximum size of MB for each tablespace file with the autoextend attribute (8 MB by default ). This attribute does not involve specific data table files. the file size is relatively small.
Innodb_lock_wait_timeout = n	If a transaction does not obtain the required resources after waiting for n seconds (s), use the ROLLBACK command to discard the transaction. This setting is important for discovering and processing deadlock conditions that are not recognized by the InnoDB data table driver. The default value of this option is 50 s.
Innodb_fast_shutdown 0/1	Whether to disable InnoDB as quickly as possible. The default value is 1, which means no data cached in the INSERT cache is written into the data table, the data will be written to the MySQL server at the next startup (there is no risk because the INSERT cache is an integral part of the tablespace and data will not be lost ). Setting this option to 0 is risky because the InnoDB driver may not have enough time to complete data synchronization when the computer is disabled, the operating system may forcibly end InnoDB before it completes data synchronization, which may result in incomplete data.

Mysqld program: InnoDB-Log

Innodb_log_group_home_dir = p	Directory used to store InnoDB log files (such as ib_logfile0 and ib_logfile1 ). By default, the InnoDB driver uses the MySQL data directory as the location for saving log files.
Innodb_log_files_in_group = n	Number of log files used (2 by default ). The InnoDB data table driver will enter these files in turn. when all the log files are full, the subsequent log information is written to the maximum length of the first log file (5 MB by default ). The length must be set in MB (MB) or GB (GB.
Innodb_flush_log_at_trx_commit = 0/1/2	This option determines when to write log information to log files and when to physically write these files (referred to as "synchronization") to the hard disk. The value 0 indicates that the log is written and synchronized every second, which can reduce the number of hard disk write operations, but may cause data loss. set the value 1 (set) this means that logs are written and synchronized every time a COMMIT command is executed, which can prevent data loss, but hard disk write operations may be frequent; setting value 2 is a general compromise, that is, every time a COMMIT command is executed to write a log, the log is synchronized every second.
Innodb_flush_method = x	How to synchronize InnoDB log files (only applicable to UNIX/Linux systems ). There are two optional values for this option: fdatasync, which is synchronized using the fsync () function; O_DSYNC, which is synchronized using the O_SYNC () function.
Innodb_log_archive = 1	Enable the archive log function of the InnoDB driver to write the log information to the ib_arch_log_n file. Enabling this log feature makes little sense when InnoDB and MySQL are used together (enabling the binary log feature of the MySQL server is enough ).

Mysqld program-InnoDB: cache zone settings and optimization

Innodb_log_buffer_pool_size = n	The amount of RAM memory reserved for the InnoDB data table and its indexes (8 MB by default ). This parameter has a considerable impact on the speed. if only MySQL/InnoDB database servers are running on the computer, 80% of the total memory should be used for this purpose.
Innodb_log_buffer_size = n	Maximum length of the cache for write operations on transaction log files (1 MB by default ).
Innodb_additional_men_pool_size = n	The maximum cache length allocated for various data structures for internal management (1 MB by default ).
Innodb_file_io_threads = n	Maximum number of threads for I/O operations (hard disk write operations) (4 by default ).
Innodb_thread_concurrency = n	The maximum number of threads that the InnoDB driver can use at the same time (the default value is 8 ).

Mysqld program: other options

Bind-address = ipaddr	The IP address of the MySQL server. This option is very important if the computer on which the MySQL server is located has multiple IP addresses.
Default-storage-engine = type	The default data table type of the new data table (the default value is MyISAM ). You can also use the-default-table-type option to set this setting.
Default-timezone = name	Set a geographic time zone for the MySQL server (if it is different from the geographic time zone of the local computer ).
Ft_min_word_len = n	The minimum word length of the full-text index. The default value of this option is 4, which means that words created by three or fewer characters are not considered during full-text indexing.
Max-allowed-packet = n	The maximum length of the data packet exchanged between the customer and the server. this number must be at least the length of the maximum BLOB block that the customer program will process. The default value of this option is 1 MB.
SQL-mode = model1, mode2 ,...	Which SQL mode does MySQL run in. This option is used to maximize compatibility between MySQL and other database systems. The optional values include ansi, db2, oracle, no_zero_date, and pipes_as_concat.

Note: If an option in the configuration file is not identified by mysqld, the MySQL server will not start.