======Настройка Mysql======

Данная статья будет посвящена настройке Mysql для обычной работы.

Изменять конфигурационный файл my.cnf который находится по разному пути в зависимости от операционной системы.

Данные параметры носят рекомендательный характер и подходят большинству начинающих-средних сетей (100-2000) абонентов. В других случаях следует более детально подходить к параметрам оптимизации Базы данных и использовать специализированную литературу по настройке базы данных Mysql.

Ниже пример конфигурации MySQL 8 которая ставится при установке биллинга:
<tabs>
  * [[#tab-centos|Centos]]
  * [[#tab-debian|Debian]]
 
<pane id="tab-centos">
==== Конфигурация Centos 8 ====
<panel type="default" title="/etc/my.cnf">
<code>
[client]
port = 3306
socket=/var/lib/mysql/mysql.sock

 
[mysql]
loose-local-infile=1
 
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
 
[mysqld]
user = mysql
port = 3306
bind-address = 127.0.0.1
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/mysql.err
basedir = /usr
datadir = /var/lib/mysql
skip-external-locking
symbolic-links=0
skip-name-resolve
default-storage-engine=innodb
log_bin_trust_function_creators=1
skip-log-bin
sql-mode=''
optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,subquery_materialization_cost_based=off,use_index_extensions=off,condition_fanout_filter=off,derived_merge=off,use_invisible_indexes=off,skip_scan=off,hash_join=off'

#system
loose-local-infile=1
#query_cache_size = 256M
#query_cache_limit = 256M
join_buffer_size = 256M
max_join_size=256M
max_connect_errors = 1K
max_allowed_packet=16M
table_open_cache = 4096
max_heap_table_size = 1024M
tmp_table_size = 1024M
interactive_timeout = 120
wait_timeout = 120
connect_timeout = 120
thread_cache_size = 32
max_connections = 128
 
#Myisam
key_buffer_size = 8M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 2M
 
# change to ramdisk
tmpdir = /tmp/
 
# innodb
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size = 1024M
# default is 8 (or 1 if innodb_buffer_pool_size < 1GB)
innodb_buffer_pool_instances = 4
innodb_data_home_dir = /var/lib/mysql/
innodb_log_group_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 300
innodb_thread_concurrency = 0

[mysqldump]
quick
max_allowed_packet = 16M
 
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
 
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
 
[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
</code>
</panel>
</pane>

<pane id="tab-debian">
==== Конфигурация Debian 10 ====
<panel type="default" title="/etc/mysql/my.cnf">
<code>
[client]
port = 3306
socket=/var/run/mysqld/mysqld.sock

 
[mysql]
loose-local-infile=1
 
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
 
[mysqld]
user = mysql
port = 3306
bind-address = 127.0.0.1
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/mysql.err
basedir = /usr
datadir = /var/lib/mysql
skip-external-locking
symbolic-links=0
skip-name-resolve
default-storage-engine=innodb
log_bin_trust_function_creators=1
skip-log-bin
sql-mode=''
optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,subquery_materialization_cost_based=off,use_index_extensions=off,condition_fanout_filter=off,derived_merge=off,use_invisible_indexes=off,skip_scan=off,hash_join=off'

#system
loose-local-infile=1
#query_cache_size = 256M
#query_cache_limit = 256M
join_buffer_size = 256M
max_join_size=256M
max_connect_errors = 1K
max_allowed_packet=16M
table_open_cache = 4096
max_heap_table_size = 1024M
tmp_table_size = 1024M
interactive_timeout = 120
wait_timeout = 120
connect_timeout = 120
thread_cache_size = 32
max_connections = 128
 
#Myisam
key_buffer_size = 8M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 2M

# change to ramdisk
tmpdir = /tmp/
 
# innodb
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size = 1024M
# default is 8 (or 1 if innodb_buffer_pool_size < 1GB)
innodb_buffer_pool_instances = 4
innodb_data_home_dir = /var/lib/mysql/
innodb_log_group_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 300
innodb_thread_concurrency = 0
 
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
 
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
 
[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
</code>
</panel>
</pane>

</tabs>