Настройка MySQL
Основное конфигурирование MySQL осуществляется с помощью файла /etc/my.cnf или /etc/mysql/my.cnf
Указание кодировок и collation
В секцию [mysqld] добавим строки:
character-set-server=utf8
collation-server=utf8_general_ci
init-connect=»SET NAMES utf8″
- character-set-server — кодировка для всего сервера;
- collation-server — порядок символов и строк на основе алфавитного порядка и классов эквивалентности;
- init-connect — строка, выполняемая для каждого клиента при соединении.
Ограничение количества, таймауты и источники соединений
bind-address=localhost
# Отключаем определение доменного имени для IP-адресов
skip-name-resolve
# Максимальное количество соединений
max_connections = 250
# Отключаем использованием symbolic-links
symbolic-links=0
# Таймауты
interactive_timeout=60
wait_timeout=60
Увеличение числа открытых файлов
В большинстве Linux-систем по умолчанию лимит открытия файловых дескрипторов установлен в 1024, для работы этого недостаточно.
Проверим текущие опции:
Внесем требуемые лимиты в /etc/security/limits.conf
Динамически изменим текущие лимиты:
Проверим soft limit:
Текущие лимиты в MySQL проверим SQL-запросом:
SHOW VARIABLES LIKE ‘%open_files%’
Оптимизация MySQL для MyISAM
Оптимизация параметров MySQL позволяет значительно увеличить производительность MyISAM.
Буферы
Основными параметрами являются key_buffer_size (буфер для работы с ключами и индексами) и sort_buffer (буфер для сортировки).
key_buffer_size = 64M
sort_buffer_size = 32M
При наличии 16Гб памяти и более, рекомендуется увеличить key_buffer_size до 128M-256M. Если Вы не используете MyISAM таблицы, рекомендуется установить размер key_buffer_size в 32Мб для хранения индексов временных таблиц.
Кэш запросов указывается в опции query_cache_size, ограничение на кэшируемый элемент в query_cache_limit, кэш открытых таблиц в table_open_cache.
table_open_cache = 2048
query_cache_limit = 2M
query_cache_size = 128M
query_cache_type = 1
thread_cache_size = 16
max_heap_table_size = 128M
tmp_table_size = 128M
Будьте внимательны при установке завышенного значения query_cache_size, т.к. это может привести к ожиданию блокировок (Be careful not to set the size of the cache too large. Due to the need for threads to lock the cache during updates, you may see lock contention issues with a very large cache). Мы не рекомендуем устанавливать значение больше 256M.
Параметр thread_cache_size указывает количество тредов (threads), уходящих в кеш при отключении клиента. При новом подключении тред не создается, а берется из кеша, что позволяет экономить ресурсы при больших нагрузках. При наличии 32Гб памяти и более рекомендуем увеличить thread_cache_size до 32, table_open_cache в диапазон 4096-8192, query_cache_size до 256M.
Перенос временных файлов MySQL в память
Проверяем наличие /dev/shm:
Настройки размещаются в /etc/fstab , рекомендуем указать размер, например, 1G:
none /dev/shm tmpfs defaults,size=1G 0 0
Если внесли изменения, то перемонтируем:
mount -o remount /dev/shm
В конфигурационном файле указываем:
В случае, если используется Apparmor, то внесите используемый путь (/dev/shm или /run/mysql) в конфигурационный файл /etc/apparmor.d/usr.sbin.mysqld, например:
service apparmor restart
Оптимизация MySQL для InnoDB
Стандартно все таблицы и индексы хранятся в одном файле, мы рекомендуем использовать опцию innodb_file_per_table для установки хранения каждой таблицы в отдельном файле. Дополнительно необходимо корректно рассчитать параметр innodb_open_files, до версии MySQL 5.6.6 он устанавливался в значение 300, с версии MySQL 5.6.6 рассчитывается автоматически и имеет значение по умолчанию -1.
Значение innodb_open_files и table_open_cache рассчитывается как количество таблиц во всех базах, умноженное на 2, ориентировочно рекомендуем устанавливать обе опции в 4096 или 8192.
innodb_file_per_table = 1
table_open_cache = 4096
innodb_open_files = 4096
При использовании только InnoDB часть опций требует корректировки:
key_buffer_size = 32M
max_allowed_packet = 1M
sort_buffer_size = 32M
read_buffer_size = 256K
read_rnd_buffer_size = 1M
thread_stack = 128K
query_cache_limit = 1M
query_cache_size = 0
query_cache_type = 1
thread_cache_size = 32
max_heap_table_size = 128M
tmp_table_size = 128M
Обратите внимание, при работе с InnoDB мы отключаем query_cache_size установкой его значения в 0, исключающее лишние действия по работе с кэшем, что особенно важно при активной работе с большими объемами данных. С версии MySQL 5.6 query_cache_size отключен, а с версии 8.0 удален.
Важнейшей настройкой MySQL при работе с InnoDB является innodb_buffer_pool_size, устанавливается по принципу «чем больше, тем лучше». Рекомендуется выделять до 70-80% оперативной памяти сервера под innodb_buffer_pool_size. Не забудьте провести расчет использования памяти Apache и дополнительным программным обеспечением для исключения сваливания системы в swap. Для сервера с 16Гб устанавливается в диапазоне 10-12G и разделяем его на 4 секции, для сервера с 32Гб устанавливаем значение в диапазоне 20-24G и разделяем его на 8 секций, :
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 4
Установка большого размера innodb_log_file_size может привести к увеличению быстродействия, при этом увеличится время восстановления данных, выберите от 258M до 1G.
innodb_log_file_size = 256M
innodb_log_buffer_size = 32M
При установке значения в 2 буфер не сбрасывается на диск, а только в кэш операционной системы. Установка значения в 0 увеличит быстродействие, однако возможна потеря последних данных при аварийном выключении mysql-сервера.
Количество потоков ввода/вывода файлов в InnoDB задается опциями innodb_read_io_threads, innodb_write_io_threads, обычно этому параметру присваивается значение 4 или 8, на быстрых SSD-дисках установите в 16. Значение innodb_thread_concurrency установите в количество ядер * 2.
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_thread_concurrency = 32
Для ускорения работы с INFORMATION_SCHEMA, SHOW TABLE STATUS или SHOW INDEX отключим обновление статистики при выполнении таких операций, с версии MySQL 5.6.6 innodb_stats_on_metadata отключено по умолчанию:
Для версии MySQL 5.7+ укажите дополнительные опции:
performance_schema = OFF
skip-log-bin
sync_binlog = 0
После применения изменений перезагрузите MySQL:
service mysqld restart
Динамическое изменение размера innodb_buffer_pool_size
С версии MySQL 5.7.5 допускается динамическое изменение размера innodb_buffer_pool_size, однако необходимо помнить, что размер innodb_buffer_pool_size должен быть пропорционален innodb_buffer_pool_chunk_size.
Запросим размер innodb_buffer_pool_chunk_size:
Получим значение, например, 128 мегабайт. Нам требуется динамически установить размер innodb_buffer_pool_size в 16G, соответсвенно 16G / 128M = 125, установим значение:
SET GLOBAL innodb_buffer_pool_size = (SELECT @@innodb_buffer_pool_chunk_size) * 125
IT-блог о веб-технологиях, серверах, протоколах, базах данных, СУБД, SQL, компьютерных сетях, языках программирования и создание сайтов.
MySQL сервер. Файл конфигураций my.ini. Настройка кодировки MySQL сервера. Таблицы перекодировок
Здравствуйте, уважаемые посетители моего скромного блога для начинающих вебразработчиков и web мастеров ZametkiNaPolyah.ru. Продолжим сегодня рубрику Заметки о MySQL, в которой я уже успел рассказать о том, как установить MySQL сервер и как настроить сервер баз данных. Сегодня я продолжу рассказывать о настройках сервера MySQL. В данной статье мы разберемся со следующими вопросами: куда устанавливается MySQL сервер, где найти базы данных MySQL сервера, как найти базы данных MySQL, для чего нужен файл my.ini, посмотрим примеры настройки сервера MySQL и где найти примеры настройки MySQL сервера.
Разберемся с кодировкой MySQL сервера. Какую кодировку лучше использовать. Разберемся с командами MySQL сервера для смены кодировки. Так же затронем вопрос о включение и выключение MySQL, обратите внимание, что MySQL сервер не перезагружается. Посмотрим, какие возможности есть у сервера MySQL. Разберемся где лежат таблицы перекодировок MySQL сервера и как их добавить. А также сделаем настройки файла my.ini.
MySQL сервер. Настройка MySQL сервера, файл конфигурации my.ini, примеры настройки MySQL сервера.
Как я уже говорил, с установкой и предварительной настройкой MySQL сервера мы разобрались, теперь давайте посмотрим, хотя бы поверхностно, из чего состоит MySQL сервер. Какие файлы за что отвечают. MySQL сервер, в моем случае был установлен по данному пути: c:\Users\Public\MySQL\, кто-то мог установить сам сервер в папку Program Files, обратите внимание, что этот путь не указывает, то место, где хранятся базы данных MySQL сервера, здесь находится сам сервер баз данных.
На скрине можно увидеть несколько файлов и папок сервера MySQL, нас собственно большая часть этих файлов не волнует.
Основной файл конфигурации MySQL сервера my.ini, это обычный текстовый файл, в который были вписаны настройки, которые мы вводили при установке MySQL сервера. Все остальные установленные файлы с расширением .ini – это всего лишь демонстрации настроек для my.ini, работает только my.ini, то есть, все остальные файлы существуют, как примеры для конфигурации и настройки MySQL сервера. Проще говоря, пример, как настроить MySQL сервер.
Программы MySQL сервера, какой файл для чего нужен
Где лежат примеры настроек MySQL сервера мы разобрались. Сами программы, предназначенные для обслуживания баз данных, находятся в папке bin.
Сам сервер MySQL – это mysqld.exe, d – означает демон, ну или служба. Про некоторые другие утилиты и программы мы поговорим в дальнейшем более подробно. Если у вас стоит win xp, то базы данных будут находиться c:\Documents and Settings\All Users\Application Data\MySQL\ MySQL Server 5.x\data, если вы пользуетесь total commander или любым другим нормальным файловым менеджером, то без труда найдете эту папку, если вы пользуетесь проводником от Windows, то обязательно укажите в настройках «Отображать скрытые файлы», так как данная папка скрыта.
На самом деле все это можно настроить в файле my.ini и реально эти папки могут находиться, там где вам будет удобнее. Файл my.ini нужно будет разобрать более подробно, и тема эта для отдельной статьи.
Кодировка MySQL сервера. SET NAMES — команда для смены кодировки. Кодировка командной строки Windows.
И так, теперь немного поговорим о кодировке сервера MySQL и кодировке командной строки Windows. Если вы будете пользоваться MySQL Command Lint Client, то проблем с кодировкой у вас возникнуть не должно.
Данный клиент установиться вместе с сервером и работа в нем не отличается от работы с командной строкой Windows. Для начала, вы вводите пароль, придуманный вами при установки MySQL сервера, а затем, не заморачиваясь с кодировками и командами типа SET Names, начинаете работать с базами данных: создавать новые базы данных, удалять базы данных, добавлять строки и столбцы к существующим таблицам баз данных, делать выборки из баз данных, создавать и удалять таблицы и многое-многое другое.
Но если вы решили управлять сервером MySQL через командную строку, то знайте, что кодировка командной строки Windows отличается от кодировки MySQL сервера, в командной строке – это cp866, MySQL сервер в моем случае работает с кодировкой UTF8. И это нужно исправить, кодировку командной строки поменять мы не можем. Остается менять кодировку, с которой работает сервер MySQL.
Как изменить кодировку MySQL сервера. Как получить доступ к серверу MySQL через командную строку Windows.
Тут у нас есть два способа. Первый из них, постоянно писать команду SET NAMES и указывать кодировку cp866. SET NAMES – это не одна команда, как считают некоторые, а целых три. Первая команда – установить кодировку ввода, то есть, с какой кодировки перекодировать данные, которые получает сервер. Вторая команда – установить кодировку вывода, то есть, в какой кодировки сервер MySQL будет отдавать данные. И третья команда – установить collation или по другому правила сравнения строк. И чтобы не набирать три команды сразу была придумана команда SET NAMES. И так, чтобы указать нужную кодировку следует написать SET NAMES, а затем в одинарных кавычках написать нужную кодировку.
Как найти файлы конфигурации MySQL, PHP и Apache
В этой статье мы узнаем несколько команд для поиска файлов конфигурации по умолчанию для сервера базы данных MySQL (my.conf), языка программирования PHP (php.ini) и HTTP-сервера Apache (http.conf), которые работают в одном стеке (наборе) вместе LAMP (Linux Apache Mysql / MariaDB PHP).
Файл конфигурации содержит параметры, связанные с системой или приложением. Это дает разработчикам и администраторам управлять работой этой системы или приложением.
Как системный администратор Linux, знание местоположения конфигурационных файлов или способов овладения ими является неоценимым навыком.
В структуре каталогов Linux, каталог /etc или его подкаталоги, хранят связанные с системой файлы или файлы конфигураций.
Хотя это основное расположение файлов конфигурации, некоторые разработчики предпочитают хранить другие файлы конфигурации в пользовательских каталогах.
Как найти файл конфигурации MySQL (my.conf)
Вы можете найти файл конфигурации MySQL с помощью инструмента командной строки mysql или mysqladmin.
Следующие команды будут отображать страницу справки mysql или mysqladmin, которая содержит раздел, где рассказывается о файлах (файлах конфигурации), из которых считываются параметры по умолчанию.
Дальше будет приведена команда grep, которая имеет параметр -A, он отображает NUM (кол-во) строк конечного контекста после сопоставления строк.
Как найти файл конфигурации PHP (php.ini)
PHP можно управлять с терминала с помощью утилиты командной строки php в сочетании с параметром -i, который позволяет показывать информацию и конфигурацию PHP. Также есть команда grep, она нужна, чтоб вы могли найти файл конфигурации PHP следующим образом:
Найдите файл конфигурации Apache http.conf / apache2.conf
Вы можете напрямую вызвать apache2 (что не рекомендуется в большинстве случаев) или администрировать с помощью интерфейса управления apache2ctl, как показано ниже, с параметром —V, который показывает параметры версии и сборки apache2:
Спасибо за уделенное время на прочтение статьи!
Если возникли вопросы, задавайте их в комментариях.
Подписывайтесь на обновления нашего блога и оставайтесь в курсе новостей мира инфокоммуникаций!
Чтобы знать больше и выделяться знаниями среди толпы IT-шников, записывайтесь на курсы Cisco от Академии Cisco, курсы Linux от Linux Professional Institute на платформе SEDICOMM University.