MySQL5.0 configuration of my. cnf _ MySQL

MySQL5.0 my. cnf configuration description MySQL database 5.0 my. cnf configuration options overview:

Mysqld program-directory and file

Basedir = path uses the given directory as the root directory (installation directory ).
Character-sets-dir = path indicates the directory where the character set is stored.
Datadir = path reads database files from a given directory.
Pid-file = filename specifies a file for the mysqld program to store the process ID (only applicable to UNIX/Linux systems). The Init-V script needs to end the mysqld process using the process ID in the file.
Socket = filename: specifies a socket file for local communication between the MySQL client program and the server (only applicable to UNIX/Linux systems). the default setting is usually/var/lib/mysql. sock file ).
In Windows, if the MySQL client communicates with the server through the named pipe, the -- sock option gives the name of the named pipe (the default value is MySQL ).
Lower_case_table_name = 1/0 whether only lowercase letters are allowed for the new directory and data table names; this option is set to 1 by default in Windows (only lowercase letters are allowed ).

Mysqld program-language settings

Character-sets-server = name 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 default sorting method for new databases or data tables.
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] allows/disables the use of the load data local statement to process local files.
Myisam-recover [= opt1, opt2,...] automatically fixes all damaged MyISAM data tables 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 uses 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 specifies a TCP/IP communication port (usually port 3306) for the MySQL program ).
Safe-user-create is only available in mysql. only users with the INSERT permission on the user database table can use the GRANT command. this is a double-insurance mechanism (this user must also have the GRANT permission to execute the GRANT command ).
Shared-memory allows the use of memory (shared memory) for communication (only applicable to Windows ).
Shared-memory-base-name = name indicates the name of the shared memory block (the default name is MySQL ).
Skip-grant-tables does not use the information in the mysql database for access control (Warning: This will allow any user to modify any database ).
Skip-host-cache does not use the high-speed cache area to store the ing between host names and IP addresses.
Skip-name-resovle does 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 allows a local connection through a socket file (Unix/Linux system) or a named pipe (Windows system), and does not allow an ICP/IP connection. 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 mysqld will be executed under a given UNIX/Linux account after the program is started; mysqld must be started from the root account before it can be switched to another account after the program is started; 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 is the length of the cache allocated by the INSERT command to INSERT multiple new records at a time (the default value is 8 MB ).
Key_buffer_size = n is used to store the RMA value of the index block (the default value is 8 MB ).
Join_buffer_size = n the cache length allocated for the JOIN operation when the data column involved in the JOIN operation does not have an index (the default value is 128 K ).
Max_heap_table_size = maximum length of the n HEAP data table (16 MB by default); the HEAP data table exceeding this length will be saved into 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 maximum length of the query cache (the default value is 0, and no query cache is opened ).
Query_cache_type = 0/1/2 query the working mode of the cache: 0, disable the query cache; 1, enable the query cache (default); 2, "pay-as-you-go" mode, only the SELECT SQL _CACHE command is returned.
Read_buffer_size = n indicates the length of the cache reserved for reading data from the data table in sequence (the default value is kb ); 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 is similar to the read_buffer_size option, but it is 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 indicates the length of the cache area allocated for sorting operations (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 maximum length of the temporary HEAP data table (32 MB by default); a temporary data table exceeding this length will be converted to the MyISAM data table and saved to a temporary file.

Mysqld program-Log

Log [= file] logs 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] logs the query command that exceeds the long_query_time 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 maximum time used to execute slow queries (10 s by default ).
Long_queries_not_using_indexs logs the slow query and query commands that do not use the index during execution (the rest are the same as the -- log-slow-queries option ).
Log-bin [= filename] records all SQL commands (INSERT, UPDATE, and DELETE commands) that modify data in binary format into logs (binary change logs, 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 ).
The index file name of the log-bin-index = filename binary log function. 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 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 records the changes in the given database 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 does not record changes in the given database to binary log files.
Sync_binlog = n writes the log file to the hard disk once every n log write operations (synchronize the log information 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 assigns a unique ID number 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 enables the binary log function. 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 records the changes in the given database to the binary log file/records the changes in the given database to the binary log file.

Mysqld program-image (backup storage)

Server-id = n: assign a unique ID to the server
Log-slave-updates enables the log function on the slave server so that this computer can be used to form an image chain (A-> B-> C ).
Master-host = host name the 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: 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 = password used by the passwd 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 TCP/IP port used by the slave server to connect to the master server (port 3306 is set by default ).
Master-connect-retry = n if the connection to the master server is not successful, wait for n seconds (s) before management (the default value is 60 s ). If the slave server has the 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 the slave server to execute SQL commands independently (default); 1: The slave server can only execute SQL commands from the master server.
Read-log-purge = 0/1 1: immediately delete the processed SQL command from the relay log file (default setting); 0: the processed SQL commands are not immediately deleted from the relay log file.
Replicate-do-table = dbname. tablename and -- replicate-do-table have the same meaning and usage, but wildcards "%" (for example, test %) are allowed in the names of databases and database tables. % -- image processing is performed on all database tables whose names start with "test ).
Replicate-do-db = name only processes images of this database.
Replicate-ignore-table = dbname. tablename does not mirror the data table.
Replicate-wild-ignore-table = dbn. tablen does not mirror these data tables.
Replicate-ignore-db = dbname does not perform image processing on this database.
Replicate-rewrite-db = db1name> db2name processes the db1name database image on the master database as the db2name database on the slave server.
Report-host = 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, slave server uses 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 not used

If the slave-skip-errors option is selected, the image operation on the slave server may be interrupted due to an error. manual participation is required after the interruption.
Mysqld -- InnoDB -- basic settings, tablespace files
Skip-innodb does 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 creates 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 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 is used to hold the tablespace where InnoDB is the data table: it may involve more than one file. The maximum length of each tablespace file must be in bytes (B), MB) the name of a tablespace file 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 use the name of the hard disk partition to define the tablespace. at this time, you must add the newraw keyword to the maximum initial length of the tablespace, and add the maximum extended length value of the tablespace.

The raw keyword is used as the suffix (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 MB for each increase in a tablespace file with the autoextend attribute (the default value is 8 MB ). This attribute does not involve specific data table files. the file size is relatively small.
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 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 determines whether to close InnoDB as quickly as possible. The default value is 1, which means that data cached in the INSERT cache is not written into the data table. The data will be stored in the MySQL server next time.

Write again at 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 indicates the number of log files used (default value: 2 ). 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.
The innodb_flush_log_at_trx_commit = 0/1/2 option determines when the log information is written to the log file and when the files are physically written (referred to as "synchronized") 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. 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 InnoDB log file synchronization method (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 (archive) log function of the InnoDB driver to write log information to the ib_arch_log_n file. This log feature is not enabled when InnoDB and MySQL are used together.

Significance (enabling the binary log function of the MySQL server is enough ).

Mysqld program-InnoDB-cache zone settings and optimization

Innodb_log_buffer_pool_size = n is 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 is the maximum cache length allocated for various data structures for internal management (1 MB by default ).
Innodb_file_io_threads = maximum number of threads for n I/O operations (hard disk write operations) (4 by default ).
Innodb_thread_concurrency = n maximum number of threads that can be used by the InnoDB driver at the same time (the default value is 8 ).

Mysqld program-other options

Bind-address = IP address of the ipaddr 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 ). 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 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 greater than the maximum BLOB block length that the customer program will process. The default value of this option is 1 MB.
SQL-mode = model1, mode2,... which SQL mode MySQL will 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 (for example, because of a stupid typing error), the MySQL server will not start