my.cnf

my.cnf — это файл конфигурации MySQL. Чтобы найти файл, используйте команду:

locate my.cnf

В Linux’e обычно он находится по такому пути:

/etc/my.cnf 
# либо 
/etc/mysql/my.cnf

Редактирование

Для редактирования необходимо открыть файл my.cnf в текстовом редакторе:

nano /etc/my.cnf

После внесения изменений в файл, необходимо делать перезапуск mysql:

/etc/init.d/mysql restart

Пример конфигурации

Это пример настроек Mysql для сервера с 1Гб оперативной памяти и двумя ядрами:

client]
port            		= 3306
socket          		= /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          		= /var/run/mysqld/mysqld.sock
nice           	 		= 0

[mysqld]
user            		= mysql
pid-file        		= /var/run/mysqld/mysqld.pid
socket          		= /var/run/mysqld/mysqld.sock
port            		= 3306
basedir         		= /usr
datadir         		= /var/lib/mysql
tmpdir          		= /tmp
language        		= /usr/share/mysql/english
old_passwords   		= 0
bind-address            	= 127.0.0.1

skip-external-locking

max_allowed_packet      	= 16M
key_buffer              	= 16M
[p:innodb_buffer_pool_size] 	= 512M
[p:innodb_file_per_table]   	= 1
[p:innodb_flush_method]     	= O_DIRECT
[p:innodb_flush_log_at_trx_commit]  = 0

thread_stack            	= 128K
[p:thread_cache_size]       	= 128
myisam-recover          	= BACKUP
max_connections         	= 128
table_cache             	= 32

query_cache_limit       	= 1M
[p:query_cache_size]    		= 4M

[p:slow_query_log]        		= /var/log/mysql/mysql-slow.log
long_query_time         	= 1

expire_logs_days        	= 10
[p:max_binlog_size]     		= 100M

[mysqldump]
quick
quote-names
max_allowed_packet      	= 16M

Если в вашем конфиге есть настройка thread_concurrency — уберите ее.

Стандартные конфигурации

Для удобства используйте одну из указанных конфигураций для вашего сервера:

Для 2Гб оперативной памяти

[client]
port                        = 3306
socket                      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket                      = /var/run/mysqld/mysqld.sock
nice                        = 0

[mysqld]
user                        = mysql
pid-file                    = /var/run/mysqld/mysqld.pid
socket                      = /var/run/mysqld/mysqld.sock
port                        = 3306
basedir                     = /usr
datadir                     = /var/lib/mysql
tmpdir                      = /tmp
language                    = /usr/share/mysql/english
old_passwords               = 0
bind-address                = 127.0.0.1

skip-external-locking

max_allowed_packet          = 16M
key_buffer_size             = 16M
innodb_buffer_pool_size     = 1024M
innodb_file_per_table       = 1
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit  = 0

max_connections             = 136

        query_cache_size            = 0

slow_query_log              = /var/log/mysql/mysql-slow.log
long_query_time             = 1

expire_logs_days            = 10
max_binlog_size             = 100M

[mysqldump]
quick
quote-names
max_allowed_packet          = 16M

Для 4Гб оперативной памяти

[client]
port                        = 3306
socket                      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket                      = /var/run/mysqld/mysqld.sock
nice                        = 0

[mysqld]
user                        = mysql
pid-file                    = /var/run/mysqld/mysqld.pid
socket                      = /var/run/mysqld/mysqld.sock
port                        = 3306
basedir                     = /usr
datadir                     = /var/lib/mysql
tmpdir                      = /tmp
language                    = /usr/share/mysql/english
old_passwords               = 0
bind-address                = 127.0.0.1

skip-external-locking

max_allowed_packet          = 16M
key_buffer_size             = 16M
innodb_buffer_pool_size     = 2048M
innodb_file_per_table       = 1
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit  = 0

max_connections             = 144

        query_cache_size            = 0

slow_query_log              = /var/log/mysql/mysql-slow.log
long_query_time             = 1

expire_logs_days            = 10
max_binlog_size             = 100M

[mysqldump]
quick
quote-names
max_allowed_packet          = 16M

Для 8Гб оперативной памяти

[client]
port                        = 3306
socket                      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket                      = /var/run/mysqld/mysqld.sock
nice                        = 0

[mysqld]
user                        = mysql
pid-file                    = /var/run/mysqld/mysqld.pid
socket                      = /var/run/mysqld/mysqld.sock
port                        = 3306
basedir                     = /usr
datadir                     = /var/lib/mysql
tmpdir                      = /tmp
language                    = /usr/share/mysql/english
old_passwords               = 0
bind-address                = 127.0.0.1

skip-external-locking

max_allowed_packet          = 16M
key_buffer_size             = 16M
innodb_buffer_pool_size     = 4096M
innodb_file_per_table       = 1
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit  = 0

max_connections             = 160

        query_cache_size            = 0

slow_query_log              = /var/log/mysql/mysql-slow.log
long_query_time             = 1

expire_logs_days            = 10
max_binlog_size             = 100M

[mysqldump]
quick
quote-names
max_allowed_packet          = 16M

Для 16Гб оперативной памяти

[client]
port                        = 3306
socket                      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket                      = /var/run/mysqld/mysqld.sock
nice                        = 0

[mysqld]
user                        = mysql
pid-file                    = /var/run/mysqld/mysqld.pid
socket                      = /var/run/mysqld/mysqld.sock
port                        = 3306
basedir                     = /usr
datadir                     = /var/lib/mysql
tmpdir                      = /tmp
language                    = /usr/share/mysql/english
old_passwords               = 0
bind-address                = 127.0.0.1

skip-external-locking

max_allowed_packet          = 16M
key_buffer_size             = 16M
innodb_buffer_pool_size     = 8192M
innodb_file_per_table       = 1
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit  = 0

max_connections             = 192

        query_cache_size            = 0

slow_query_log              = /var/log/mysql/mysql-slow.log
long_query_time             = 1

expire_logs_days            = 10
max_binlog_size             = 100M

[mysqldump]
quick
quote-names
max_allowed_packet          = 16M

Для 32Гб оперативной памяти

[client]
port                        = 3306
socket                      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket                      = /var/run/mysqld/mysqld.sock
nice                        = 0

[mysqld]
user                        = mysql
pid-file                    = /var/run/mysqld/mysqld.pid
socket                      = /var/run/mysqld/mysqld.sock
port                        = 3306
basedir                     = /usr
datadir                     = /var/lib/mysql
tmpdir                      = /tmp
language                    = /usr/share/mysql/english
old_passwords               = 0
bind-address                = 127.0.0.1

skip-external-locking

max_allowed_packet          = 16M
key_buffer_size             = 16M
innodb_buffer_pool_size     = 16384M
innodb_file_per_table       = 1
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit  = 0

max_connections             = 256

        query_cache_size            = 0

slow_query_log              = /var/log/mysql/mysql-slow.log
long_query_time             = 1

expire_logs_days            = 10
max_binlog_size             = 100M

[mysqldump]
quick
quote-names
max_allowed_packet          = 16M

Для 64Гб оперативной памяти

[client]
port                        = 3306
socket                      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket                      = /var/run/mysqld/mysqld.sock
nice                        = 0

[mysqld]
user                        = mysql
pid-file                    = /var/run/mysqld/mysqld.pid
socket                      = /var/run/mysqld/mysqld.sock
port                        = 3306
basedir                     = /usr
datadir                     = /var/lib/mysql
tmpdir                      = /tmp
language                    = /usr/share/mysql/english
old_passwords               = 0
bind-address                = 127.0.0.1

skip-external-locking

max_allowed_packet          = 16M
key_buffer_size             = 16M
innodb_buffer_pool_size     = 32768M
innodb_file_per_table       = 1
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit  = 0

max_connections             = 384

        query_cache_size            = 0

slow_query_log              = /var/log/mysql/mysql-slow.log
long_query_time             = 1

expire_logs_days            = 10
max_binlog_size             = 100M

[mysqldump]
quick
quote-names
max_allowed_packet          = 16M

Для 128Гб оперативной памяти

[client]
port                        = 3306
socket                      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket                      = /var/run/mysqld/mysqld.sock
nice                        = 0

[mysqld]
user                        = mysql
pid-file                    = /var/run/mysqld/mysqld.pid
socket                      = /var/run/mysqld/mysqld.sock
port                        = 3306
basedir                     = /usr
datadir                     = /var/lib/mysql
tmpdir                      = /tmp
language                    = /usr/share/mysql/english
old_passwords               = 0
bind-address                = 127.0.0.1

skip-external-locking

max_allowed_packet          = 16M
key_buffer_size             = 16M
innodb_buffer_pool_size     = 65536M
innodb_file_per_table       = 1
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit  = 0

max_connections             = 640

        query_cache_size            = 0

slow_query_log              = /var/log/mysql/mysql-slow.log
long_query_time             = 1

expire_logs_days            = 10
max_binlog_size             = 100M

[mysqldump]
quick
quote-names
max_allowed_packet          = 16M

Этот текст был написан несколько лет назад. С тех пор упомянутые здесь инструменты и софт могли получить обновления. Пожалуйста, проверяйте их актуальность.