MySQL5.0 my. cnf configuration instructions (1) _ MySQL

MySQL5.0 my. cnf configuration instructions (1) 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 subordinate 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 "%" are allowed in the database and database table names"