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

This article mainly introduces the "Chinese description of the mysql database my.ini configuration file". In the daily operation, I believe that many people have doubts on the Chinese description of the mysql database my.ini configuration file. The editor consulted all kinds of materials and sorted out a simple and easy-to-use method of operation. I hope it will be helpful to answer the doubts of the "Chinese description of the mysql database my.ini configuration file". Next, please follow the editor to study!

Mysqld programs-directories and files

Quote

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 programs-language Settings

Quote

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

Quote

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 be executed under 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

Quote

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

Quote

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)

Quote

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)

Quote

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%.%- mirrors all database tables in all databases whose names begin with "test").

Replicate-do-db = name mirrors only this database.

Replicate-ignore-table = dbname.tablename does not mirror this data table.

Replicate-wild-ignore-table = dbn.tablen does not mirror these tables.

Replicate-ignore-db = dbname does not mirror this database.

Replicate-rewrite-db = db1name > db2name mirrors the db1name database on the master database as the db2name database on the secondary server.

Report-host = the hostname of the hostname slave server; this information is only relevant 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 communicate in a compressed format-if they all support this.

Slave-skip-errors = N1, N2, … Or all mirroring processing continues even if the error code is N1, N2, etc. (that is, mirroring processing continues no matter what error occurs). If configured properly, the secondary server should not have an error during the execution of the SQL command (the SQL command that went wrong on the master server will not be sent to the secondary server for mirroring processing); if the slave-skip-errors option is not used, the mirroring on the secondary server may be interrupted due to an error, and human involvement is required to continue after the interruption.

Mysqld-InnoDB- basic settings, tablespace files

Quote

Skip-innodb does not load the InnoDB datasheet driver-if you do not need an InnoDB datasheet, you can use this option to save some memory.

Innodb-file-per-table creates a tablespace file for each new table instead of centrally storing the tables in a central tablespace (the latter is the default). This option is available in MySQL 4.1.

Innodb-open-file = the maximum number of files that the InnoDB datasheet driver can open at the same time (the default setting is 300). If used

If you have the innodb-file-per-table option and you need to open many tables at the same time, this number will probably need to be increased.

Innodb_data_home_dir = p InnoDB home directory, all directories or file paths associated with the InnoDB datasheet are relative to this path. By default, this home directory is the MySQL data directory.

Innodb_data_file_path = ts is used to hold InnoDB as the tablespace of the 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 a semicolon; the last tablespace file can also have an autoextend attribute and a maximum length (max:n). For example, ibdata1:1G; ibdata2:1G:autoextend:max:2G means that the maximum length of the tablespace file ibdata1 is 1GB and the maximum length of ibdata 2 is 1G, but it is allowed to be extended to 2GB. In addition to the file name, the tablespace can also be defined with the setting name of the hard disk partition. At this time, the maximum initial length of the tablespace must be suffixed with the newraw keyword, and the maximum extended length of the tablespace must be suffixed with the raw keyword (for example, / dev/hdb1: 20Gnewraw or / dev/hdb1:20Graw); the default setting for MySQL 4.0 and later is ibdata1:10M:autoextend.

Innodb_autoextend_increment = n how many megabytes each time a tablespace file with the autoextend attribute is enlarged (the default setting is 8MB). This property does not involve specific datasheet files, which grow at a relatively small rate.

Innodb_lock_wait_timeout = n if a transaction does not get the required resources after waiting for n seconds (s), use the ROLLBACK command to discard the transaction. This setting is of great significance for discovering and handling deadlock conditions that are not recognized by the InnoDB datasheet driver. The default setting for this option is 50s.

The default setting is 1, which means that data cached in the INSERT cache will not be written to the data table, which will be written again the next time the MySQL server starts (there is no risk because the INSERT cache is an integral part of the tablespace and the data will not be lost). Setting this option to 0 is a negative risk, because when the computer is turned off, the InnoDB driver will probably not have enough time to complete its data synchronization, and the operating system may forcibly end the InnoDB before it completes the data synchronization, which can lead to incomplete data.

Mysqld Program-InnoDB- Log

Quote

Innodb_log_group_home_dir = p the directory path where the InnoDB log files are stored (such as ib_logfile0, ib_logfile1, etc.). By default, the InnoDB driver uses the MySQL data directory as the location where it saves the log files.

Innodb_log_files_in_group = how many log files are used (the default setting is 2). The InnoDB datasheet driver will fill in these files in turn; when all the log files are full, the subsequent log information will be written to the maximum length of the first log file (the default setting is 5MB). This length must be set with either MB (megabyte) or GB (gigabyte) as a single bit.

The option innodb_flush_log_at_trx_commit = 0swap 2determines when log information is written to log files and when these files are physically written (in terms called "synchronization") to the hard disk. Setting a value of 0 means that the log is written and synchronized every other second, which reduces the number of hard disk writes, but may result in data loss; setting a value of 1 (setting setting) means that the log is written and synchronized every time a COMMIT command is executed, which prevents data loss, but hard disk writes may be frequent Setting a value of 2 is a general compromise, that is, every time a COMMIT command is executed, a log is written and synchronized every other second.

Innodb_flush_method = x synchronization method for InnoDB log files (for UNIX/Linux systems only). There are two available values for this option: fdatasync, synchronized with the fsync () function, and O_DSYNC, synchronized with the O_SYNC () function.

Innodb_log_archive = 1 enables the archive log function of the InnoDB driver to write log information to the ib_arch_log_n file. Enabling this logging feature doesn't make much sense when InnoDB is used with MySQL (enabling the binary logging feature of the MySQL server is sufficient).

Mysqld Program-setting and Optimization of InnoDB- buffer area

Quote

Innodb_log_buffer_pool_size = n the amount of RAM memory reserved for the InnoDB data table and its indexes (the default setting is 8MB). This parameter has a considerable impact on speed, and if only the MySQL/InnoDB database server is running on the computer, 80% of the total memory should be used for this purpose.

Innodb_log_buffer_size = n the maximum length of the transaction log file write cache (default is 1MB).

Innodb_additional_men_pool_size = n the maximum length of cache allocated for various data structures for internal management (the default setting is 1MB).

Innodb_file_io_threads = the maximum number of threads for the n Imax O operation (hard disk write operation) (default is 4).

Innodb_thread_concurrency = the maximum number of threads that the InnoDB driver can use at the same time (the default is 8).

Mysqld Program-other options

Quote

Bind-address = the IP address of the ipaddr MySQL server. This option is important if the computer where the MySQL server is located has more than one IP address.

Default-storage-engine = the default data table type of the type new data table (the default setting is MyISAM). This setting can also be set with the-default-table-type option.

Default-timezone = name sets a geographic time zone for the MySQL server if it is different from the local computer's geographic time zone.

Ft_min_word_len = n the minimum word length of the full-text index. The default setting for this option is 4, which means that words built from 3 or fewer characters are not taken into account when creating a full-text index.

Max-allowed-packet = n the maximum length of packets exchanged between the client and the server, which should be at least greater than the length of the maximum BLOB block that the client will process. The default setting for this option is 1MB.

Sql-mode = model1, mode2, … Which SQL mode MySQL will run in. The purpose of this option is to make MySQL as compatible as possible with other database systems. The available values for this option are ansi, db2, oracle, no_zero_date, pipes_as_concat.

Note: if an option given in the configuration file is not recognized by mysqld, the MySQL server will not start

At this point, the study on the "Chinese description of mysql database my.ini configuration file" 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.