MySQL parameters detailed and optimized

After the MySQL installation is complete, MySQL needs to be configured and optimized so that MySQL can run more efficiently.

vi/etc/my.cnf #打开配置文件

[mysqld]

Datadir=/data/mysqldata #在此位置读取数据

Socket=/tmp/mysql.sock #sock Communication settings

User=mysql #使用mysql启动

Symbolic-links=0 #是否支持快捷方式

log-bin=mysql-bin #开启 bin-log Log

Server-id=1 #mysql Service ID

Auto_increment_offset=1 #自增长字段从那个数开始, take value

Range: 1-65535

auto_increment_increment=2 #自增长每次递增的量, default 1, Fetch

Value range: 1-65535

######### you need to set both when #在做master-Master Sync

########## master:auto_increment_offset=1

########## auto_increment_increment=2

########## master:auto_increment_offset=2

########## auto_increment_increment=2

########## set the Auto_increment_offset to 1 and 2, respectively, to prevent the two servers from updating at the same time.

A conflict between the values of the ########## long field

Port = 3306

key_buffer = 384M #用于索引块的缓冲区大小, increase it to

Better processing of indexes (for all read and multiple writes).

The index block is buffered and shared by all threads, depending on the size of the memory

Table_cache = #为所有线程打开表的数量. Increasing this value can increase MySQL

The number of descriptors. avoid frequent opening of data tables to generate overhead

sort_buffer_size = 2M # A buffer that is ordered by each thread to speed up order by or

GROUP by operation. Note: The allocation size is exclusive for each connection ,

If there are 100 connections, it will occupy 2 * + = 600M

read_buffer_size = 2M # The buffer used by the query operation , per connection exclusive

query_cache_size = 32M #指定query result buffer size

read_rnd_buffer_size = 8M #在使用 After the pointer is sorted , random reads are used

myisam_sort_buffer_size = 64M # MyISAM buffer required for reordering when table changes

thread_concurrency = 8 # Maximum number of threads concurrent threads , Value:number of CPUs * 2, if

CPU supports h.t Hyper-threading, and then * 2

Thread_cache = 8 #缓存Number of reusable threads

Skip-locking #避免MYSQL的外部锁定 to reduce the chance of error increase stability

[Mysqld_safe] #mysql安全启动配置

Log-error=/var/log/mysqld.log #日志

Pid-file=/var/run/mysqld/mysqld.pid #PID文件

replicate-do-db=all #允许 slave sync which library

key_buffer_size #指定索引缓冲区的大小, it determines the index

The speed of processing, especially the speed of index reads. Typically 50% of memory

Show variables lik ' key_buffer_size ';

max_connection = #每个客户端连接最大的错误允许数量 to reach this limit,

Block connections until "FLUSH HOSTS" is executed or the service is restarted

innodb_buffer_pool_size #相当于key_buffer_size对于MyISAM The function of the table, using

This parameter specifies the memory size to buffer the data and index

# # # #对于单独的MySQL The server, you can set this value to the 80% of the physical memory

basedir = path # Use this directory as the root directory (installation directory)

DataDir = path # reads a data file from this directory

pid-file= Path # Specifies a file that holds the process ID for the MYSQLD program, only

Suitable for Unix/linux systems

[mysqldump]

Max_allowed_packet = 16M

MySQL parameters detailed and optimized