MySQL Common configuration

The size of the innodb_buffer_pool_size buffer, typically between 70% and 80% of memory, is used to cache table and index data, which is the primary cache space for lock-level data.

By viewing the show status like ' innodb_buffer_pool_% ';

Innodb_log_file_size log file size, the larger the better, you can reduce the frequency of buffer pool checkpoint cannot exceed 4G,

can set up multiple groups, such as 3 groups, each group size 300M

Innodb_log_buffer_size set the log buffer size, the default is 1M, the main function is to buffer the log data,

Improve the IO performance of Log, general 8M can be competent to show status like ' innodb_log% '; If Innodb_log_waits is 0,

Do not wait, the cache is large enough, if greater than 0, is the cache is not large enough to properly increase the innodb_file_per_table =1

You can have separate data and index file data for each table

Innodb_data_file_path not only specifies the path to all InnoDB data files,

Also specify the initial size allocation, the following is two hard disks

Innodb_data_file_path =/disk1/local/mysql/innodb1:1000m:autoextend:8m:max:1g;

Innodb_data_file_path =/disk2/local/mysql/innodb2:2000m:autoextend

If Innodb_data_home_dir is not specified, the value of Innodb_data_home_dir will be used directly.

If Innodb_data_home_dir is specified, then: Innodb_data_home_dir=/usr/local/mysql innodb_data_file_path=ibdata1:2g;ibdata2:2g; ibdata3:2g:autoextend:max:3g

It means that there are ibdata1,ibdata2,ibdata3 three files stored in the/usr/local/mysql under the

The last one can be automatically expanded, that is, when the IBDATA3 is greater than 2G, it will automatically increase to a maximum of 3G

============================

[Mysqld]

#general

DataDir =/usr/local/mysql socket =/usr/local/mysql/mysql.sock (should not be in the same location as the compiled installation directory)

Pid_file =/usr/local/mysql/mysql.pid (should not be in the same location as the compiled installation directory)

user = MySQL

Port =3306

Default_storage_engine =innodb

Expire_logs_days = 15

#innodb

Innodb_buffer_pool_size = 70-80% Memory value

Innodb_log_file_size = At least hundreds of M

Innodb_log_buffer_size = 1-8m/32-128m

innodb_file_per_table = 1

#innodb与文件系统相互作用, when set to 0_direct, you need to open innodb_file_per_table

#默认为 Fdatasync Innodb_flush_method = 0_direct (Unix-like, bypassing the operating system's cache)

#MYISAM

#键缓存, there is only one default, but you can set multiple, cache indexes only, and the data cache depends on the system

#查看总大小, should be a little bit smaller than this value, Unit B

# Select SUM (index_length) from information_schema.tables where engine= ' MyISAM ';

Key_buffer_size = 32M

#设置多个时

Key_buffer_1.key_buffer_size = value

Key_buffer_2.key_buffer_size = value

#用key_buffer_1来缓存t1, T2 table, index

Cache index t1,t2 in key_buffer_1;

########

#LOGGING

Log_error =/usr/local/mysql/mysql-error.log

Slow_query_log =/usr/local/mysql/mysql-slow.log

#OTHER

Tmp_table_size = 32m/or greater

Max_heap_table_size = 32M

Query_cache_type = 0

Query_cache_size = 0

Max_connections = 10000 (default is 100)

Open_file_limit = 65535 (the maximum number of open files, the more the Convention the better Point)

[Client]

Socket =/usr/local/mysql/mysql.sock

Port = 3306

MySQL Common configuration