Shulou(Shulou.com)05/31 Report--

This article mainly introduces the my.cnf configuration options of MySQL Database 5.0. in the daily operation, I believe that many people have doubts about the my.cnf configuration options of MySQL Database 5.0. the editor has consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "my.cnf configuration options of MySQL Database 5.0". Next, please follow the editor to study!

Overview of my.cnf configuration options for MySQL Database 5.0:

Mysqld programs-directories and files

Basedir = path uses 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 for the mysqld program to hold the process ID (for UNIX/ systems 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 local communication between the MySQL client and the server (for UNIX/Linux systems only; the default setting is generally / var/lib/mysql/mysql.sock file).

In a Windows environment, if the MySQL client and server communicate through a named pipe, the-- sock option will give the name of the named pipe (the default setting is MySQL).

Lower_case_table_name = 1 to 0 whether only lowercase letters are allowed for the names of new directories and datasheets; the default setting for this option is 1 in Windows environments (lowercase letters only are allowed).

Mysqld Program-language setting

Character-sets-server = name the default character set of the new or datasheet. To be compatible with earlier versions of MySQL, this character set can also be given with the-- default-character-set option; but this option is a bit out of date.

Collation-server = the default sort method for name new databases or data tables.

Lanuage = name displays the error message in the specified language.

D programs-communication, network, information security

Enable-named-pipes allows clients 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] allows / disables the use of LOAD DATA LOCAL statements to process local files.

Myisam-recover [= opt1, opt2,...] Automatically repairs all corrupted MyISAM tables at startup. There are four available values for this option: DEFAULT, BACKUP, QUICK, and FORCE;, which work the same as the myisamchk program's options of the same name.

Old-passwords uses the old algorithm in MySQL 3.23 and 4.0 to encrypt passwords in my (the default is the new encryption algorithm introduced in MySQL version 4.1).

Port = n specifies a TCP/IP communication port (usually port 3306) for the MySQL program.

Safe-user-create only users with INSERT privileges on mysql.user database tables can use the GRANT command; this is a double insurance mechanism (this user must also have GRANT privileges to execute GRANT commands).

Shared-memory allows the use of memory (shared memory) for communication (Windows only).

Shared-memory-base-name = name give the shared memory block a name (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 a cache to store the correspondence between hostnames and IP addresses.

Skip-name-resovle does not resolve IP addresses to hostnames; all checks related to access control (mysql.user data tables) travel through IP addresses.

Skip-networking only allows local connections through a socket file (Unix/Linux system) or named pipes (Windows system), but does not allow ICP/IP connections; this improves security, but blocks external connections from the network and all Java clients (Java customers use TCP/IP even in local connections).

The user= name mysqld program will be executed under a given UNIX/Linux account after startup; the mysqld must be started from the root account to switch to another account after startup; and the mysqld_safe script will use the-user=mysql option by default to start the mysqld program.

Mysqld programs-memory management, optimization, query cache

Bulk_insert_buffer_size = n the length of cache allocated by the INSERT command that inserts more than one new record at a time (the default setting is 8m).

Key_buffer_size = n is used to store the RMA value of the index block (the default setting is 8m).

Join_buffer_size = n the length of the cache allocated for the JOIN operation when the data column participating in the JOIN operation does not have an index (the default setting is 128K).

Max_heap_table_size = the maximum length of n HEAP tables (default is 16m); HEAP tables beyond this length will be stored in a temporary file instead of residing in memory.

Max_connections = the maximum number of database connections that the n MySQL server processes simultaneously (the default setting is 100).

Query_cache_limit = n the maximum length of query results that are temporarily stored in the query cache (the default setting is 1m).

Query_cache_size = n the maximum length of the query cache (the default setting is 0, no query cache is opened).

The working mode of the query cache: 0, disable the query cache; 1, enable the query cache (the default); and 2, "allocate on demand" mode, respond only to SELECT SQL_CACHE commands.

Read_buffer_size = n the length of the cache reserved for read operations that read data sequentially from the data table (the default setting is 128KB); the setting value of this option can be changed with the SQL command SET SESSION read_buffer_size = n if necessary.

Read_rnd_buffer_size = n is similar to the read_buffer_size option, but is for query results that are output in a specific order, such as queries that use the ORDER BY clause (the default setting is 256K).

Sore_buffer = n the length of the cache allocated for the sort operation (the default is 2m); if the cache 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 data table (the default is 32m); temporary data tables beyond this length will be converted to MyISAM data tables and saved to a temporary file.

Mysqld program-log

Log [= file] logs all connections and all SQL commands (generic query log); if no file parameter 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] logs query commands that take more than the value of the long_query_time variable to execute (slow log); if no file parameter is given, MySQL will create a hostname-slow.log file in the database directory as such log file (hostname is the server hostname).

Long_query_time = the upper limit of the time it takes to execute a slow query (the default setting is 10s).

Long_queries_not_using_indexs logs slow queries and query commands that are executed without indexes (the rest are the same as the-- log-slow-queries option).

Log-bin [= filename] logs all SQL commands (that is, INSERT, UPDATE, and DELETE commands) that make changes to the data in binary format (binary change log, binary update log). The file name of this log is filename.n or the default hostname.n, where n is a 6-digit integer (log files are numbered sequentially).

Log-bin-index = the index file name of the filename binary logging feature. By default, this index file has the same name as the binary log file, but the suffix is .index instead of .nnnnnn.

Max_binlog_size = n the maximum length of the binary log file (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 connection.

Binlog-do-db = dbname only records the changes in a given database in the binary log file, not in other databases. If you need to record changes in multiple databases, you must use multiple this option in the configuration file, one row for each database.

Binlog-ignore-db = dbname does not record changes in a given database in the binary log file.

Sync_binlog = n writes the log file to the hard disk for every n log writes (synchronizes the log information). Number1 is the safest approach, but the least efficient. The default setting is nroom0, which means that the operating system is responsible for the synchronization of binary log files.

Log-update [= file] records the name of the log file for the error (error log). This logging feature cannot be disabled. If no file parameter is given, MySQL uses hostname.err as the name of the kind of log file.

Mysqld Program-Mirror (Master Mirror Server)

Server-id = n assigns a unique ID number to the server; the range of values for n is 1x 2 to the 32th power to enable binary logging.

Log-bin = name enables binary logging. The file name of this log is filename.n or the default hostname.n, where n is a 6-digit integer (log file sequential number).

Binlog-do/ignore-db = dbname only records changes in a given database in the binary log file / does not record changes in the given database in the binary log file.

Mysqld Program-Mirror (Secondary Mirror Server)

Server-id = n assign a unique ID number to the server

Log-slave-updates enables logging on the slave server so that this computer can be used to form a mirror chain (A-> B-> C).

Master-host = the hostname or IP address of the hostname host server. If a mater.info file (mirror relationship definition file) exists on the secondary server, it ignores this option.

Master-user = the user name used by the replicusername secondary server to connect to the master server. If a mater.info file exists on the secondary server, it ignores this option.

Master-password = the password used by the passwd secondary server to connect to the master server. If a mater.info file exists on the secondary server, it ignores this option.

Master-port = n the TCP/IP port used by the slave 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 not successful, wait n seconds (s) before managing mode (the default setting is 60s). If a mater.info file exists on the secondary server

It ignores this option.

Master-ssl-xxx = xxx configures SSL communication between master and slave servers.

Read-only = 0 SQL 1 0: allows the secondary server to execute SQL commands independently (the default); 1: the secondary server can only execute SQL commands from the master server.

Read-log-purge = 0 SQL 11: delete the processed SQL command from the relay log file immediately (the default setting); 0: do not delete the finished SQL command from the relay log file immediately.

Replicate-do-table = dbname.tablename has the same meaning and usage as the-- replicate-do-table option, but the wildcard "%" is allowed in database and database table names

(for example, test%.%-- compares all databases in all databases whose names begin with "test"

At this point, the study on "my.cnf configuration options for MySQL Database 5.0" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.