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

This article shares with you the content of a sample analysis of Mysql configuration files. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

[mysqld]

Port = 3306

Serverid = 1

Socket = / tmp/mysql.sock

Skip-locking

# avoid external locking of MySQL, reduce the probability of error and enhance stability.

Skip-name-resolve

Disable DNS parsing of external connections by MySQL, which eliminates the time it takes for MySQL to parse DNS. It should be noted, however, that if this option is turned on, all remote host connection authorizations must use IP addresses, otherwise MySQL will not be able to handle connection requests properly!

Back_log = 384

Specifies the number of possible connections to the MySQL. When the MySQL main thread receives a lot of connection requests in a very short period of time, this parameter takes effect, and the main thread takes a short time to check the connection and start a new thread.

The value of the back_log parameter indicates how many requests can be stored on the stack in a short period of time before MySQL temporarily stops responding to new requests. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. Different operating systems have their own limits on the queue size.

Trying to set the limit that back_log is higher than your operating system will not be valid. The default value is 50. It is recommended to set an integer less than 512 for Linux systems.

Key_buffer_size = 256m

# key_buffer_size specifies the size of the buffer used for indexing, and increasing it results in better index processing performance.

For servers with memory around 4GB, this parameter can be set to 256m or 384m.

Note: the excessive setting of this parameter value will reduce the overall efficiency of the server!

Max_allowed_packet = 4m

Thread_stack = 256K

Table_cache = 128K

Sort_buffer_size = 6m

The size of the buffer that can be used when sorting queries. Note: the allocated memory corresponding to this parameter is exclusive per connection! If there are 100 connections, the total sort buffer size actually allocated is 100x6 = 600MB. Therefore, it is recommended to set it to 6-8m for servers with about 4GB memory.

Read_buffer_size = 4m

The size of the buffer that can be used by read query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!

Join_buffer_size = 8m

The size of the buffer that can be used by the federated query operation is the same as that of sort_buffer_size, and the allocated memory corresponding to this parameter is exclusive per connection!

Myisam_sort_buffer_size = 64m

Table_cache = 512

Thread_cache_size = 64

Query_cache_size = 64m

Specifies the size of the MySQL query buffer. You can observe by executing the following command in the MySQL console:

Code:

# > SHOW VARIABLES LIKE'% query_cache%'

# > SHOW STATUS LIKE 'Qcache%'

If the value of Qcache_lowmem_prunes is very large, it indicates that insufficient buffering often occurs.

If the value of Qcache_hits is very large, it indicates that query buffering is used very frequently, and if the value is small and affects efficiency, then consider not query buffering; Qcache_free_blocks, if the value is very large, it indicates that there is a lot of fragmentation in the buffer.

Tmp_table_size = 256m

Max_connections = 768

Specifies the maximum number of connected processes allowed by MySQL. If there are frequent Too Many Connections errors when visiting the forum, you need to increase the value of this parameter.

Max_connect_errors = 10000000

Wait_timeout = 10

Specify the maximum connection time for a request, which can be set to 5-10 for servers with memory around 4GB.

Thread_concurrency = 8

The value of this parameter is the number of server logical CPU × 2. In this example, the server has two physical CPU, and each physical CPU supports H.T hyperthreading, so the actual value is 4 × 2 = 8.

Skip-networking

Turn on this option to completely turn off MySQL's TCP/IP connection mode, but do not turn on this option if the WEB server accesses the MySQL database server remotely! Otherwise, you will not be able to connect properly!

[common log]

Log = / var/log/mysql/mysql-sys.log

Log all connections and all SQL commands (generic query log); if no file parameter is given, MySQL will create a hostname.log file in the database directory as the log file (hostname is the hostname of the server).

Log-update = / var/log/mysql/mysql-update.log

The name of the log file that records 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.

Log-slow-queries = / var/log/mysql/mysql-slow.log

Log 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 a log file (hostname is the server hostname).

Long_query_time = n

The upper limit of the execution time for slow queries (the default setting is 10s).

Long_queries_not_using_indexs

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

Log-bin = / var/log/mysql/mysql-bin.log

Log all SQL commands that make changes to the data (that is, INSERT, UPDATE, and DELETE commands) 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).

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 the changes in a given database are recorded in the binary log file, and changes in other databases are not recorded. 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

Changes in a given database are not recorded in the binary log file.

Sync_binlog = n

After every n log writes, the log file is written to the hard disk (a synchronization of 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.

[master/slave]

Server-id = n

Assign a unique ID number to the server

Log-slave-updates

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

Master-host = hostname

Master-user = replicusername

Master-password = passwd

They are the host name or IP address of the master server, the user name used by the slave server to connect to the master server, and the password used by the slave server to connect to the master server. If a master.info file (mirror relationship definition file) exists on the secondary server, it ignores this option.

Master-port = n

The TCP/IP port used by the secondary 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 master.info file exists on the secondary server, it ignores this option.

Master-ssl-xxx = xxx

Configure SSL communication between master and slave servers.

Read-only = 0Pax 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 = 0Pax 1

1: 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

The meaning and usage of the-- replicate-do-table option are the same, 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

Only this database is mirrored.

Replicate-ignore-db = dbname

This database is not mirrored.

Replicate-ignore-table = dbname.tablename

This data table is not mirrored.

Replicate-wild-ignore-table = dbn.tablen

These tables are not mirrored.

Replicate-rewrite-db = db1name > db2name

The db1name database on the master database is mirrored as the db2name database on the secondary server.

Report-host = hostname

The hostname of the slave server; this information is only relevant to the SHOW SLAVE HOSTS command, which the master server can use 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, [... all]

Mirroring processing continues even if the error code is N1, N2, and so on (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.

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 (the default setting is 1MB).

Innodb_additional_men_pool_size = n

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

Innodb_file_io_threads = n

The maximum number of threads for the Icano operation (hard disk write operation) (the default is 4).

Innodb_thread_concurrency = n

The maximum number of threads that InnoDB drivers can use at the same time (the default setting is 8).

[InnoDB]

Skip-innodb

Do 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

Create 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 = n

The maximum number of files that the InnoDB datasheet driver can open at the same time (the default setting is 300). If you use the innodb-file-per-table option and need to open many tables at the same time, this number will probably need to be increased.

Innodb_data_home_dir = path

InnoDB home directory, all directories or file paths related to the InnoDB datasheet are relative to this path. By default, this home directory is the MySQL data directory.

Innodb_data_file_path = ts

The tablespace used to hold InnoDB as 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 are the tablespace files with the autoextend attribute enlarged at a time (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.

Innodb_fast_shutdown = 0Pax 1

Whether to close InnoDB as quickly as possible, the default setting is 1, which means that data cached in INSERT cache will not be written to the data table, and that data will be written again the next time the MySQL server starts (this is not risky 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.

[InnoDB log]

Innodb_log_group_home_dir = / var/log/mysql/innodb

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 = n

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.

Innodb_flush_log_at_trx_commit = 0Action1Comp2

This option determines 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 of 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

Enable 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).

[Others]

Bind-address = ipaddr

The IP address of the MySQL server. This option is important if the computer where the MySQL server is located has more than one IP address.

Default-storage-engine = type

The default data table type for the new data table (the default setting is MyISAM). This setting can also be set with the-- default-table-type option.

Default-timezone = name

Set 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 at least be greater than the length of the maximum BLOB block that the client program 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.

Thank you for reading! This is the end of this article on "sample analysis of Mysql configuration files". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.