Настройка 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

DataLife Engine — Softnews Media Group

Ручная установка MySQL на Windows

Автор: genadie от 31-01-2014, 20:31, посмотрело: 154685

Для начала скачиваем zip-архив со страници загрузки http://dev.mysql.com/downloads/mysql/ .

Так как у меня 64 битная система, я выбрал: Windows (x86, 64-bit), ZIP Archive

Создаем каталог для установки, предположим C:\server\mysql-5.6.15, и в него распаковываем содержимое архива.

Открываем файл my-default.ini (в обычном Блокноте), и вносим изменения:

Удаляем все содержимое или закоментируем (в начале каждой строки ставим #).

Сохраняем и переименовываем в my.ini

Перед тем как осуществить пробный пуск, открываем входящий порт TCP 3306 в брандмауэре Windows.

Теперь необходимо выполнить пробный запуск:

Открываем директорию C:\server\mysql-5.6.15\bin

Жмем: файл, и выбираем «открыть командную строку как администратор»

Появится предупреждение, соглашаемся:

В командной строке вводим:

На экран будет выведено несколько строк. Если запуск прошел успешно, то последняя строка будет выглядеть примерно так:

Оставляем это окно открытым, и открываем еще одно окно командной строки, в котором вводим:

Если подключение прошло без ошибок, вы увидите приглашение командной строки: mysql>

Посмотрим какие базы у нас есть:

Точка с запятой в конце SQL-запроса ОБЯЗАТЕЛЬНА!

Теперь мы окончательно убеждены, что сервер работает.

Переходим к следующему этапу — необходимо установить пароль администратора MySQL (пользователь root). По умолчанию пароль не установлен, и это надо исправить. В том же окне MySQL вводим следующие команды:

Как видно на скриншоте колонка password заполнена, значит пароль установлен.

Перед выходом выполняем команду:

Теперь при подключении необходимо вводить пароль. Для этого используем ключ -p:

Для завершения работы MySQL выполняем:

Последний этап — запуск MySQL в качестве системной службы Windows.

Убедитесь, что в системе нет активных процессов MySQL.

В командной строке, запущенной от имени администратора, выполняем:

Для запуска службы, открываем «панель управления»,»система и безопасность», «администрирование», «службы», и вручную запускаем службу MySQL:

Или по проще: «управление компъютером», выделяем службы и запускаем:

Или еще проще, через командную строку открытую в директории установки:

Для остановки используем команду:

И последний вариант перезагружаемся.

Для удаления службы выполняем команду:

Если вдруг забыли пароль root:

Для начала Вам необходимо сбросить пароль и установить новый. Для этого нужно:
1.Перезапустить сервер в режиме —skip-grant-tables
2.Установить новый пароль администратора
3.Перезапустить сервер в обычном режиме
Теперь подробнее о каждом пункте. В режиме skip-grant-tables отключена проверка прав доступа и привилегий, иными словами, вы можете подключиться с пустыми логин/пароль и будете обладать при этом всеми возможными привилегиями.
Для запуска сервера в режиме skip-grant-tables проще всего временно добавить строчку skip-grant-tables в my.ini в секции [mysqld]

Затем перезапустить сервер.
С помощью MySQL Command Line (устанавливается вместе с MySQL) заходим в mysql без пароля (в место пароля просто нажимаем Enter) и вводим следующую команду:

MySQL — установка и настройка

Скачать дистрибутив базы данных MySQL можно тут: mysql-5.5.15-win32.msi. Новейшую версию сервера MySQL для Windows можно загрузить с официального сайта MySQL: mysql.com (Download -> MySQL Community Server -> Windows (x86, 32 или 64 bit), MSI Installer).

Установка MySQL 5.5 в Windows

Используя материалы этой статьи, можно без проблем установить сервер базы данных MySQL на компьютер под управлением Windows XP, Windows Vista, Windows 7.

Запустив файл дистрибутива MySQL нажимаем кнопку «Next», отмечаем флажок «I accept the terms in the License Agreement» (согласие с лицензионными правилами MySQL) и далее кнопку «Custom», для выбора параметров установки БД. Следующим шагом, практически в самом низу, справа, нажимаем кнопку «Browse. » для смены установочной директории по умолчанию на C:\MySQL5.5.15\ и далее «Next», потом «Install». После прохождения процесса инсталяции нужно еще пару раз нажать кнопку «Next», чтобы добраться до окошка «Completed the MySQL Server 5.5 Setup Wizard», в котором оставляем выбранное поле «Launch the MySQL Instance Configuration Wizard», чтобы запустить процесс первоначальной конфигурации сервера MySQL нажатием кнопки «Finish».

Теперь снова кнопка «Next», после чего оставляем как есть выбор «Detailed Configuration», чтобы иметь возможность выбирать параметры конфигурации и жмем «Next». Следующий выбор из 3-х возможных параметров:

• Developer Machine — малое выделение оперативной памяти компьютера для MySQL, подходит для рабочей станции, с установленными различными приложениями (Apache, PHP, Word, Excel, Игры и др.);
• Server Machine — среднее потребление памяти для компьютера, использующегося в качестве сервера, с не большим кол-вом установленных программ (например web-сервер, на котором планируется использовать только Apache, PHP и MySQL);
• Dedicated MySQL Server Machine — максимальное потребление операционной памяти, для использования компьютера в качестве выделенного (отдельного) сервера MySQL.

Тут необходимо выбрать первый (Developer Machine) или второй (Server Machine) вариант, в зависимости от планируемого использования компьютера.

На следующем этапе установки предлагается выбор из трех вариантов:

• Multifunctional Database — оптимизация скорости для различных типов таблиц MySQL: MyISAM и InnoDB;
• Transactional Database Only — таблицы InnoDB (тип таблиц, поддерживающий транзакции) оптимизируются для наивысшей скорости работы, тип таблиц MyISAM также доступен;
• Non-Transactional Database Only — доступен только тип таблиц MySQL MyISAM;

Оставляем выбранный по умолчанию пункт Multifunctional Database и жмем «Next». В следующем окне предлагается выбрать путь для хранения таблиц InnoDB — оставляем все без изменений и нажимаем «Next».

Теперь следует оставить, установленный по умолчанию пункт «Decision Support (DSS)/OLAP». Выбирая его, сервер MySQL конфигурируется на обработку до 20 одновременных подключений, чего, впринципе, вполне достаточно, для компьютера разработчика или домашнего сервера. При необходимости этот параметр можно будет изменить позднее, установив нужное кол-во одновременных подключений в конфигурационном файле MySQL my.ini. «Online Transaction Processing (OLTP)» — устанавливает ограничение в 500 одновременных подключений, «Manual Settitng» позволяет выбрать из списка необходимое кол-во одновременных коннектов.

На следующем этапе конфигурирования MySQL предлагается установить сетевые настройки БД MySQL:

• включить поддержку TCP/IP соединений (оставляем установленную по умолчанию галочку напротив «Enable TCP/IP Networking», что предоставит возможность подключаться к MySQL средствами PHP и, при необходимости, с других компьютеров по локальной сети/интернет);
• номер порта, который будет «слушать» сервер MySQL (по умолчанию его номер 3306 — оставляем без изменений);
• добавить выбранный номер порта в исключения штатного Брандмауера Windows (следует отметить поле «Add firewall exception for this port» даже если файрвол не используется — вдруг в будущем, когда-нибудь, он таки будет включен?);
• включить строгий режим соответствия SQL (поле «Enable Strict Mode» по умолчанию включено — оставляем как есть, за что отвечает это поле будет описано ниже, в разделе «Настройка MySQL»).

Далее предлагается выбрать кодировку баз данных по умолчанию:

• Standart Character Set — установить кодировку «Latin 1»;
• Best Support For Multilingualism — установить кодировку «UTF8»;
• Manual Selected Default Character Set / Collation — самостоятельный выбор кодировки по умолчанию из выпадающего списка (например, можно выбрать cp1251).

Если вы только начинаете осваивать MySQL лучшим выбором будет второй пункт — UTF8, так как эта кодировка поддерживает наибольшее число символов алфавитов множества стран мира.

Следующий шаг — выбор опций для Windows:

• Install As Windows Service — рекомендуемый параметр, оставляем пункт отмеченным;
• Service Name — название сервиса для Windows, выбираем «MySQL51» (можно оставить выбрраное по умолчанию название «MySQL», но если в будущем потребуется установить новую версию MySQL паралельно со старой — может возникнуть путаница с названиями, имя сервися нужно знать для ручного запуска сервера MySQL из коммандной строки: «net start mysql51» или «net start mysql «, если сервис назван как «MySQL»);
• Launch the Mysql Server automatically — автоматически запускать сервис MySQL при старте Windows, оставляем пункт отмеченным;
• Include Bin Directory in Windows PATH — занести путь к bin директории MySQL, в которой хранятся необходимые утилиты, в Windows PATH. По умолчанию этот пункт не отмечен, но он будет весьма полезен, при использовании утилит MySQL из коммандной строки — не прийдется заходить в директорию «bin» главного каталога MySQL — вызов утилиты будет доступен из любого каталога. Отмечаем этот пункт.

Предпоследний шаг — установка опций безопасности:

• Modify Security Settings — поле, отмеченное по умолчанию, позволяет ввести пароль для пользователя root. Root — пользователь, наделенный абсолютно всеми возможными правами в БД MySQL. Пароль для него вводить нужно обязательно, чтобы предотвратить взлом БД и компьютера в целом. Если же снять отметку с поля Modify Security Settings, логину root будет присвоен пустой пароль, что весьма не безопасно.
• Enable root access from remote machines — открыть доступ пользователю root из удаленных компьютеров — решить, отмечать это поле или нет, можно определившись, нужен ли будет удаленный доступ к серверу MySQL?
• Create An Anonymous Account — это поле отмечать не нужно, так как зачем на сервере MySQL открывать доступ для анонимных пользователей?

Последний шаг установки — нажатие кнопки «Exhibited», после чего MySQL будет установлена и запущена. Если в процессе возникла ошибка 1045, на шаге применения настроек безопасности (Apply security settings), смотрите как решить эту проблемму ниже.

Проверить, работает ли сервер MySQL можно зайдя в «Пуск» -> «Панель управления» -> «Администрирование» -> «Службы» и найдя в списке служб MySQL51. Если состояние службы: «работает», то все ОК.

Ошибка 1045 при установке MySQL

Ошибка 1045 на этапе применения настроек безопасности MySQL может возникнуть по двум причинам: закрытие порта MySQL (3306 по умолчанию) брандмауэром или несовпадению паролей root новой инсталяции со старой.

И если проблему с брандмауером можно легко и быстро решить, добавив в исключения порт слушаемый сервером MySQL (или просто отключив брандмауэр на некоторое время), то вот проблему со старым паролем MySQL стоит рассмотреть подробнее.

Все пароли для пользователей MySQL, в том числее и пользователя root, хранятся в базе данных MySQL. При удалении БД MySQL (старой версии или некорректно установленной) через «Пуск» -> «Панель управления» -> «Установка и удаление программ» или удалении каким либо другим образом установочных файлов — все старые базы данных, в том числе с логинами и паролями пользователй остаются. Хранятся они в папке C:\Documents and Settings\All Users\Application Data\MySQL. Более старые версии MySQL, например 5.0, хранили данные в C:\Documents and Settings\[Имя Пользователя]\Application Data\MySQL. Чтобы новая инсталляция завершилась успешно, а не ошибкой 1045 (The security setting could not be apllied to the database because the connection is failed with the following error.) необходимо удалить эту папку или вырезать и вставить в другую директорию.

Если зайдя в папку C:\Documents and Settings\All Users\, папка Application Data не наблюдается, необходимо включить отображение скрытых папок в Windows. Это можно сделать открыв папку C:\Documents and Settings\All Users\, сверху, в верхнем меню выбрать «Сервис» -> «Свойства папки», перейти на закладку «Вид», найти подраздел «Скрытые файлы и папки» и отметить пункт «Показывать скрытые файлы и папки».

Настройка MySQL

После установки можно провести некоторую донастройку сервера MySQL, хотя это не является обязательным. Файл настроек MySQL my.ini располагается в главной установочной директории сервера. Чтобы увидеть расширения файлов (по умолчанию в Windows расширения файлов не отображаются) нужно открыть любую папку и сверху, в верхнем меню выбрать «Сервис» -> «Свойства папки», перейти на закладку «Вид» и в первом контейнере настроек: «Файлы и папки» снять галочку с пункта: «Скрывать расширения для зарегистрированных типов файлов». Если точно соблюдались инструкции этой статьи, то папка с файлом my.ini называется C:\MySQL5.5.15\. Открываем my.ini в любом текстовом редакторе и вносим изменения.

Чтобы внесенные в конфигурационный файл изменения вступили в силу, необходимо остановить и вновь запустить сервер MySQL. Как это сделать, можно узнать ниже.

Смена типа таблиц по умолчанию: Innodb на MyISAM

Находим строку default-storage-engine=INNODB и заменяем «INNODB» на «MYISAM»: default-storage-engine=MYISAM . Теперь все вновь создаваемые таблицы будут иметь тип MYISAM.

SQL режимы (SQL mod)

При переносе работающих баз данных MySQL во вновь проинсталированную, может возникнуть множество ошибок, из за отличий в SQL режимах, называемых SQL mod. Чаще всего, ошибки возникают «благодаря» установленному рекомендуемому режиму STRICT_TRANS_TABLES. Эта проблема легко решается, путем настройки значений sql-mode=»значения» . Подробнее о режимах SQL mod, как их просматривать и устанавливать читайте тут: «Режимы SQL Mode в MySQL». Вкратце, отладка режимов SQL выглядит так: смотрим, какие режимы выставлены на работающем хостинге для MySQL и устанавливаем такие же, для вновь установленной версии MySQL.

Остановка и запуск сервера MySQL

Остановить и запустить сервис MySQL можно используя службы Windows: «Пуск» -> «Панель управления» -> «Администрирование» -> «Службы» и найдя в списке служб MySQL51 нажать на нее, после чего кнопками «Пуск» и «Стоп», можно, соответственно, запустить или остановить сервис MySQL.

Остановить и вновь запустить сервис MySQL можно также, используя коммандную строку Windows: «Пуск» -> «Выполнить» -> «cmd». Комманда net stop mysql51 останавливает сервер MySQL, запущенный как сервис Windows, net start mysql51 — запускает MySQL. Этот способ предпочтительнее, так как используя его, можно обнаружить возможные ошибки, при запуске сервиса.

My.ini — оригинальный* конфигурационный файл сервера MySQL

Конфигурационный файл сервера MySQL 5.5 для Windows my.ini, созданный инсталятором MySQL для Windows, можно просмотреть, перейдя по ссылке: конфиг MySQL — my.ini.

* — вверху файла внесены 2 строки-комментария от автора статьи.