MySQL configuration file My.ini detailed

Original link: http://blog.sina.com.cn/s/blog_718ac8bf0100pkvp.html

The following is a detailed configuration of the MySQL database server configuration file My.ini. Applications are InnoDB engines,2-core CPUs, and 32-bit SUSE.

[Client]

#password = Your_password

Port = 3306

Socket =/tmp/mysql.sock

# here follows entries for some specific programs

# The MySQL server

[Mysqld]

Port = 3306

Socket =/tmp/mysql.sock

#避免外部锁定(default is on ).

Skip-external-locking

#索引缓冲区(for myisam tables and temporary tables only ), which determines the speed of database index processing, instead of 10M

Key_buffer_size = 16K

#通信缓冲区的最大长度. the maximum size of a package or any generated/intermediate string. Can be changed to a larger size such as 32M

Max_allowed_packet = 1M

#所有线程打开的表的数目(one table uses 2 file descriptors ), the number of tables is larger. Increasing this value can increase the number of file descriptors required by mysqld. You can check the opened_tables (number of open tables ) state variables to see if you need to increase the table cache. Can be changed to 2048

Table_open_cache = 4

#每个排序线程分配的缓冲区的大小. Increasing this value can speed up the ORDER by or GROUP by operation and can be changed to 5M

Sort_buffer_size = 64K

#每个线程连续扫描时为扫描的每个表分配的缓冲区的大小, you may need to increase this value if you perform multiple successive scans, and you can change to 1M

Read_buffer_size = 256K

#当排序后按排序后的顺序读取行时, the row is read through the buffer to avoid searching the hard disk. Setting the variable to a larger value can greatly improve the performance of the ORDER by. However, this is the buffer allocated for each client, so you should not set the global variable to a larger value. Instead, change session variables only for clients that need to run large queries. Can be changed to 2M

Read_rnd_buffer_size = 256K

# Resets the communication buffer to this value between queries. If the statement exceeds that length, the buffer expands automatically until the max_allowed_packet byte.

Net_buffer_length = 2K

#每个线程的堆栈大小

#thread_stack = 128K #thread_stack = 194K

#mysql服务器安装目录

Basedir=f:/server/mysql

#mysql数据存储目录

Datadir=f:/server/mysql/data

#mysql数据库编码 (prevents garbled characters at the command line)

Character-set-server = GBK

#主从同步配置. The host is 1. The standby machine starts from 2.

Server-id = 1

#启动数据库更新二进制日志记录, the log filename prefix is mysql-bin

#log-bin=mysql-bin

# Binary Logging format-mixed recommended

#binlog_format =mixed

# causes updates to Non-transactional engines using statement format to be

# written directly to binary log. Before using this option make sure that

# There is no dependencies between transactional and non-transactional

# tables such as in the statement INSERT to T_myisam SELECT * from

# t_innodb; Otherwise, slaves may diverge from the master.

#binlog_direct_non_transactional_updates =true

# Uncomment the following if you is using InnoDB tables

#InnoDB do not create the directory yourself, you must use the operating system commands to create the appropriate directory yourself. Check that your MySQL service program has sufficient permissions to set up files in the DataDir directory (the MySQL user group owns ).

#这是The directory common settings for the InnoDB table. If not set,InnoDB will use the MySQL datadir directory as the default directory. If you set an empty string , you can set the absolute path in Innodb_data_file_path.

#innodb_data_home_dir = c:\\mysql\\data\\

#这里应当预先创建好10 2048M files, not yet created Innodb_data_file_path = ibdata1:2000m;ibdata2:2000m;ibdata3:2000m; Ibdata4:2000m;ibdata5:2000m;ibdata6:2000m;ibdata7:2000m;ibdata8:2000m;ibdata9:2000m;ibdata10:2000m:autoextend

#innodb_data_file_path = Ibdata1:10m:autoextend

#InnoDB the path to the log file. If not explicitly specified, two Ib_logfile0 and Ib_logfile1 files with a size of 5 MB will be established by default in the MySQL datadir directory .

#将日志文件与数据文件分别放在不同的物理硬盘中对提高性能通常是很有益的

#innodb_log_group_home_dir = c:\\mysql\\data\\

#表和索引数据的内存缓冲区, the greater the performance of the higher. But cannot exceed physical memory 50%. If os, this value can be larger. 32-bit os by 2g memory limitations, cannot set memory usage too high,  glibc  Will grow the process heap above the thread stack, which will cause the server to crash. The following close to or over  2g  will be dangerous: innodb_ Buffer_pool_size + key_buffer +    max_connections * (sort_buffer_size + record_buffer_size + binlog_cache_size) + max_connections * 2 MB

#每个线程将使用2MB (MySQL AB binary version is a KB) stack, in the worst-case scenario, Sort_buffer_size + record_buffer_size Additional memory is also used. Can be changed to 512M

#innodb_buffer_pool_size = 16M

#主要用来存储表结构和数据字典, the more tables require more memory, you can change to 16M

#innodb_additional_mem_pool_size = 2M

#日志组中的每个日志文件的大小(in megabytes). If n is the number of log files in the log group (Innodb_log_files_in_group), the ideal value is 1M to the buffer pool (innodb_log_buffer_size) size of 1/n. A larger value reduces disk I/O by reducing the number of times the buffer pool is flushed . But a large log file means that it takes longer to recover the data when it crashes. Can be changed to 256M

#innodb_log_file_size = 5M

#InnoDB the buffer size before the log is written to the log disk file. The ideal value is 1M to 8M. A large log buffer allows the transaction to run without having to save the log to disk and only to the transaction being committed (commit). Therefore, if you have large transactions, setting a large log buffer can reduce disk I/O.

#innodb_log_buffer_size = 8M

#1表示每次事务结束都写日志并刷新磁盘;2 indicates that each transaction writes the log but does not flush the disk (refreshed per second ),and 0 (the default ) indicates that the log is written every second and the disk is flushed. 0 indicates a loss of up to 1 seconds of data, but with the best performance.

#innodb_flush_log_at_trx_commit = 1

#InnoDB行锁导致的死锁等待时间(the default value is 50S), you can change

#innodb_lock_wait_timeout = [mysqldump]

Quick

Max_allowed_packet = 16M

[MySQL]

No-auto-rehash

# Remove The next comment character if you is not a familiar with SQL

#safe-updates

[Myisamchk]

Key_buffer_size = 8M

Sort_buffer_size = 8M

[Mysqlhotcopy]

Interactive-timeout

The following settings are for reference only:

[Mysqldump]

Quick

Max_allowed_packet = 16M

[MySQL]

No-auto-rehash

# Remove The next comment character if you is not a familiar with SQL

#safe-updates

[Isamchk]

Key_buffer = 20M

Sort_buffer_size = 20M

Read_buffer = 2M

Write_buffer = 2M

[Myisamchk]

Key_buffer = 20M

Sort_buffer_size = 20M

Read_buffer = 2M

Write_buffer = 2M

[Mysqlhotcopy]

Interactive-timeout