Разное

Как настроить mysql – Первоначальная настройка MySQL | MySQL

07.12.2020

Содержание

Что нужно настроить в mySQL сразу после установки? / Habr

Вольный перевод довольно старой статьи с MySQL Performance Blog о том, что лучше сразу же настроить после установки базовой версии mySQL.

Удивительно, сколько народу устанавливает mySQL на свои сервера и оставляют его с настройками по умолчанию.

Несмотря на то, что в mySQL существует довольно много настроек, которые Вы можете изменить, есть набор действительно очень важных характеристик, которые обязательно нужно оптимизировать под собственный сервер. Обычно после такой небольшой настройки производительность сервера заметно увеличивается.

  • key_buffer_size — крайне важная настройка при использовании MyISAM-таблиц. Установите её равной около 30-40% от доступной оперативной памяти, если используете только MyISAM. Правильный размер зависит от размеров индексов, данных и нагрузки на сервер — помните, что MyISAM использует кэш операционной системы (ОС), чтобы хранить данные, поэтому нужно оставить достаточно места в ОЗУ под данные, и данные могут занимать значительно больше места, чем индексы. Однако обязательно проверьте, чтобы всё место, отводимое директивой
    key_buffer_size     под кэш, постоянно использовалось — нередко можно видеть ситуации, когда под кэш индексов отведено 4 ГБ, хотя общий размер всех .MYI-файлов не превышает 1 ГБ. Делать так совершенно бесполезно, Вы только потратите ресурсы. Если у Вас практически нет MyISAM-таблиц, то key_buffer_size следует выставить около 16-32 МБ — они будут использоваться для хранения в памяти индексов временных таблиц, создаваемых на диске.
  • innodb_buffer_pool_size — не менее важная настройка, но уже для InnoDB, обязательно обратите на неё внимание, если собираетесь использовать в основном InnoDB-таблицы, т.к. они значительно более чувствительны к размеру буфера, чем MyISAM-таблицы. MyISAM-таблицы в принципе могут неплохо работать даже с большим количеством данных и при стандартном значении
    key_buffer_size    , однако mySQL может сильно «тормозить» при неверном значении innodb_buffer_pool_size. InnoDB использует свой буфер для хранения и индексов, и данных, поэтому нет необходимости оставлять память под кэш ОС — устанавливайте innodb_buffer_pool_size в 70-80% доступной оперативной памяти (если, конечно, используются только InnoDB-таблицы). Относительно максимального размера данной опции — аналогично key_buffer_size — не стоит увлекаться, нужно найти оптимальный размер, найдите лучшее применение доступной памяти.
  • innodb_additional_mem_pool_size — данная опция практически никак не влияет на производительность mySQL, однако рекомендую оставлять для InnoDB около 20 МБ (или чуть больше) под различные внутренние нужды.
  • innodb_log_file_size — крайне важная настройка в условиях баз данных с частыми операциями записи в таблицы, в особенности при больших объёмах. Большие размеры увеличивают быстродействие, однако будьте осторожны — увеличится и время восстановления данных. Я обычно выставляю значение около 64-512 МБ в зависимости от размера сервера.
  • innodb_log_buffer_size — стандартное значение данной опции вполне подойдёт для большинства систем со средним количеством операций записи и небольшими транзакциями. Если же в Вашей системе бывают всплески активности, или Вы активно работаете с BLOB-данными, то рекомендую немного увеличить значение
    innodb_log_buffer_size    . Однако не переусердствуйте — слишком большое значение будет пустой тратой памяти: буфер сбрасывается каждую секунду, поэтому Вам не понадобится больше места, чем требуется в течение этой секунды. Рекомендуемое значение — около 8-16 МБ, а для небольших баз — и того меньше.
  • innodb_flush_log_at_trx_commit — жалуетесь, что InnoDB работает в 100 раз медленнее MyISAM? Вероятно, Вы забыли про настройку
    innodb_flush_log_at_trx_commit    . Значение по умолчанию «1» означает, что каждая UPDATE-транзакция (или аналогичная команда вне транзакции) должна сбрасывать буфер на диск, что достаточно ресурсоёмко. Большинство приложений, в особенности ранее использовавшие таблицы MyISAM, будут хорошо работать со значением «2» (т.е. «не сбрасывать буфер на диск, только в кэш ОС»). Лог, однако, всё равно будет сбрасываться на диск каждые 1-2 секунды, поэтому в случае аварии Вы потеряете максимум 1-2 секунды обновлений. Значение «0» повысит производительность, но Вы рискуете потерять данные даже при аварийной остановке mySQL-сервера, в то время как при установке значение innodb_flush_log_at_trx_commit в «2» Вы потеряете данные только при аварии всей операционной системы.
  • table_cache — открытие таблиц может быть весьма ресурсоёмко. К примеру, MyISAM-таблицы помечают заголовки .MYI файлов как «используемые в текущий момент». Обычно не рекомендуется открывать таблицы слишком часто, поэтому лучше, чтобы кэш был достаточных размеров, чтобы держать все Ваши таблицы открытыми. Для этого используется некоторое количество ресурсов ОС и оперативной памяти, однако это обычно не является существенной проблемой для современных серверов. Если у Вас несколько сотен таблиц, то стартовым значением для опции table_cache может быть«1024» (помните, что каждое соединение требует свой собственный дескриптор). Если у Вас ещё больше таблиц или очень много соединений — увеличьте значение параметра. Я видел mySQL сервера со значением
    table_cache     равной 100 000.
  • thread_cache — создание/уничтожение потоков также является ресурсоёмкой операцией, которая происходит при каждой установке соединения и каждом разрыве соединения. Я обычно выставляю эту опцию равную 16. Если у Вашего приложения могут быть скачки количество конкурентных соединений и по переменной Threads_Created виден быстрый рост количества потоков, то стоит увеличить значение thread_cache. Цель — не допускать создания новых потоков в условиях нормального функционирования сервера.
  • query_cache_size — если Ваше приложение много и часто читает данные, и при этом у Вас нет кэша на уровне приложения, эта опция может очень помочь. Не ставьте здесь слишком большое значение, так как обслуживание большого кэша запросов будет само по себе затратным. Рекомендуемое значение — от 32 до 512 МБ. Не забудьте проверить, насколько хорошо используется кэш запросов — в некоторых условиях (при небольшом количестве хитов в кэше, т.е. когда практически не выбираются одинаковые данные) использование большого кэша может ухудшить производительность.

Как Вы можете видеть, это — глобальные настройки. Эти переменные зависят от «железа» сервера и используемых движков mySQL, в то время как сессионные переменные обычно настраиваются специально под конкретные задачи. Если Вы в основном используете простые запросы, то нет никакой необходимости увеличивать значение
sort_buffer_size, даже если у Вас есть лишние 64 ГБ оперативной памяти. Более того, большие значения кэшей могут только ухудшить производительность сервера. Сессионные переменные лучше оставить на потом, для тонкой настройки сервера.

PS: инсталляция mySQL идёт с несколькими предустановленными файлами my.cnf, рассчитанными под разную нагрузку. Если Вам некогда настраивать сервер вручную, то обычно лучше использовать их, чем стандартный конфигурационный файл, выбрав тот, что больше подойдёт под нагрузку Вашего сервера.

habr.com

Как включить MySQL параметры

Запуск необходимых параметров MySQL для веб-разработчика

От автора: одной установкой и созданием баз данных все не кончается. Для того, чтобы адаптировать под веб-разработку, необходимо внести изменения в MySQL параметры. Освоить все это методом тыка — настоящее испытание, которое может и не привести к успешному результату. Тем более, что дорожка давно проторена и уже сейчас можно найти массу мануалов, касательно конфигураций баз данных MySQL. Мы собрали несколько из них и создали статью, из которой вы узнаете о тонкой настройке, основных отличиях и преимуществах различных видов настройки.

Чем плохи умолчания?

Если говорить о веб-разработке, то реляционные MySQL были созданы не только для нее. Прежде всего необходима была систематизация файлов, включение их в одну архитектуру и прочие преимущества, которые мы сегодня наблюдаем. Тем не менее, MySQL уже настолько прочно укоренилась в мир веба, что даже ассоциируется с ним: наборы разработчиков обязательно включают одну из СУБД. Примечательно, что в большинстве случаев — это MySQL.

Запуск необходимых параметров MySQL для веб-разработчика

Но, стандартные настройки базы данных не только не дадут создать полноценный продукт, но и затруднят управление системой в принципе. К изначальной конфигурации необходимо добавить немало пунктов, которые сделают из ваших БД настоящий агрегатор данных, который можно использовать, как для массивных веб-приложений, так и для небольших решений, рассчитанных, в основном, на чтение с минимальным взаимодействием.

InnoDB или MyISAM?

Существует несколько наиболее распространённых систем хранения данных. Две из них встроены в MySQL: InnoDB и MyISAM. У каждой из них есть свои преимущества и недостатки, что определено применением. Существует очень простое объяснение того, когда следует использовать каждую их них. К примеру, некоторые разработчики считают, что MyISAM стоит включить только как систему для программ чтения и записи. В реальности все намного сложнее.

Запуск необходимых параметров MySQL для веб-разработчика

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

В процессе рассмотрения конфигураций MySQL мы еще не раз вернемся к вопросу о том, как настраивать каждую из подсистем. Мы покажем, насколько применимой может оказаться каждая их них. Проблема в том, что в большинстве мануалов из интернета информация устарела и уже не может рассматриваться, как релевантная. В итоге, все приходят к выводам о том, что новичку следует использовать MyISAM, а всем остальным — Inno. Необходимо рассмотреть основные отличия, чтобы прийти к объективным выводам:

транзакции. То, чего не хватает MyISAM. Это основной ресурс для работы платежных систем и не только. Любой веб-продукт, где будет осуществляться обмен информацией, нуждается в транзакционном движке. Как только в MySQL возникает ошибка, движок может совершить rollback и вернуть данные к предыдущему состоянию;

блокировки. В MyISAM они совершаются на уровне таблиц, в то время, как Inno блокирует отдельно каждую из строк;

deadlock. Подобных блокировок не стоит ожидать в ISAM, в то время, как в Inno они случаются время от времени;

count. Эти запросы выполняются быстрее именно в ISAM, несмотря на то, что ее даже не рассматривают, как вариант некоторые разработчики;

хранение таблицы. В Inno — это больше файлы, в которых насыпью находятся все данные. В ISAM для таблицы существует файл, у каждой свой.

И это далеко не весь перечень отличий. По сути, это две принципиально разные подсистемы, механизм работы которых имеет мало общих черт. Но, мы рассмотрим оптимизации MySQL для каждого из них.

Первоначальная настройка

До того, как мы перейдём непосредственно к расширению возможностей MySQL, нам необходимо произвести первичную настройку баз данных. Это короткий этап, который сделает работу более персонализированной и безопасной. Представим, что вы уже произвели установку пакетов для взаимодействия с БД, и теперь вас ждут первые шаги тонкой настройки. На этом этапе не стоит ждать высокоточного распределения ресурсов, но уже с этого момента можно начать создание базы. Если нет желания вдаваться в технические детали, то, по сути, на этом можно и остановиться.

Итак, серверная и клиентская стороны MySQL установлены, пора настроить их взаимодействие и запустить. Делаем это при помощи команды mysqld. Если всевозможные опции вам неизвестны (а это так), необходимо вызвать помощь, интуитивной командой — help. Новая информация отобразит и пути, с которыми взаимодействует СУБД.

Создание первой базы. Ей будет присвоено имя mysql. Стоит найти и директории, с которыми MySQL будет работать. Современные инсталляторы позволяют пропустить этот этап, но знание матчасти никому не повредит.

Теперь пришло время для ограничения привилегий. Сейчас любой желающий может войти в вашу БД с правами суперпользователя. Необходимо ограничить это круг и присвоить пароль для root-прав. Как включить эту конфигурацию? Очень просто:

После нашей команды, в MySQL открывается консоль пользователя, куда мы и забьём запрос на изменение пароля:

update user set password=PASSWORD(‘NEW-PASSWORD-HERE’) where User=’John’;

update user set password=PASSWORD(‘NEW-PASSWORD-HERE’) where User=’John’;

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

Попробуйте зайти в рут, при помощи других учетных данных, чтобы проверить работу защиты.

Для привилегированного пользователя существует ряд действий, которые он может выполнять с готовыми таблицами баз данных, а также создавать новые. Вы сами можете создать несколько типов пользователей с разными возможностями для редактирования. Обычный администратор должен иметь доступ только к одной БД, в то время, как для разработчика необходимы более широкие полномочия.

Необходимое логирование

Итак, в вашем распоряжении создание пользователей, которые будут модифицировать базу данных. Вы можете ограничить их функциональность, можете расширить ее до максимума. Но, в случае нарушения целостности или любых других угроз безопасности, винить будет некого, если не отслеживать все действия в MySQL. Это могут обеспечить логи, если включить их как следует. Единственный способ контроля, который не предусматривает логирование — это постоянный мониторинг, который мало кто может себе позволить. Слишком много времени уйдет на работы с низким коэффициентом полезного действия.

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

Общие логи — это любые действия с базами данных. Как правило, их оставляют отключенными. Слишком много ресурсов будет потреблять такой процесс. Куда интереснее природа медленных логов. В них будут записаны все операции, которые заняли больше времени, чем обычно. К примеру, вы устанавливаете допустимую задержку в пятнадцать секунд. Все, что заняло больше времени, будет записано. Таким образом можно отследить атаку на сервер с базами данных.

Запуск необходимых параметров MySQL для веб-разработчика

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Параметры для расширения

Теперь перейдём непосредственно к параметрам для расширения, которые необходимо провести на самом старте. Вы уже совершили все первоначальные настройки и пришло время для точных изменений. Важно знать, что выставление значений опций — сугубо индивидуальный процесс, который сильно зависит не столько от целей, для которых вы создаете базу данных, сколько от «железа», на котором происходит основной процесс. Мы рассмотрим те команды, которые являются наиболее глобальными и необходимы при создании большинства веб-проектов.

Начнем с опции для ISAM-таблицы. Хоть на них и любят поругаться профессионалы, участие в веб-разработке этих движков все еще велико. Тем более, что подходят они для 80% всего, что сейчас создают в сети — легковесный информационный сайт с небольшими динамическими элементами. В первую очередь, речь пойдет о key_buffer_size. В целом, значение можно ставить примерно в треть всей оперативной памяти, которая доступна на железе. Так как ISAM расходует кэш-память операционки, необходимо учесть и общую нагрузку на сервер. Оставьте также память и для того, чтобы хранить сами данные. Важным моментом является использование полного объема кэша. Поэтому, если ваши файлы занимают меньше места, уменьшите и память, отведенную под них.

Теперь похожую операцию с буфером можно провести, если вы собираетесь использовать в основном InnoDB. Параметр носит название innodb_buffer_pool_size. Дело в том, что, если ISAM может неплохо взаимодействовать и с теми настройками, что система несет сама по себе, то с Inno так не получится. Здесь необходимо внести изменения в любом случае. Иначе, все что вы создадите погрязнет во «фризах». Вы могли видеть последствия такой ошибки: веб-приложение явно не должно тратить много ресурсов, но постоянно тормозит даже на мощных машинах и с хорошим соединением.

Этот движок не использует кэш — у него свой буфер. Если вы работаете только с этим типом таблиц, значит оперативную память устройства можно «кушать» на 2/3. Можно поиграться со значениями, чтобы обеспечить быстродействие. Не стоит думать, что чем больше вы выставите, тем лучше заработает ваш продукт. Здесь важен не размер, а правильное соотношение.

Мы отредактируем место, которое выделено системой под запись всего, что происходило с базой данных в определенный момент. Начнем с опции innodb_log_file_size. Она необходима, если в таблицы происходит постоянная запись данных. Как только вы увеличите размер файлов, база данных начнет «летать». Не стоит просто выставлять размер, который вы считаете большим. Превалирующее число разработчиков считает, что 512 мб — это крайнее значение. Но, не ограничивайте себя этими рамками. Постепенно увеличивайте размер и тестируйте. Только так вы сможете выбрать идеальное соотношение. Не забывайте соблюдать прогрессию использования памяти.

Продолжаем! Теперь настал черед innodb_log_buffer_size. Если ваш проект — не социальная сеть и не стриминговый музыкальный портал, лучше оставить все как есть. Иначе вас ждет нестабильность и перерасход мощностей. Если же ваш веб-продукт и правда ожидает такая популярность и активность пользователей, можно немного увеличить объем. Рекомендуемое число — до шестнадцати мегабайт. Но, как мы и говорили, все переменные — индивидуальны. Придется подбирать и тестировать производительность.

Теперь пришло время приблизить быстродействие Inno к ISAM. Если ранее вы смотрели в сторону второго движка, то сейчас эта эпоха закончится. Необходимо внести изменения в опцию под названием innodb_flush_log_at_trx_commit. После установки ПО, значение параметра равно единице. Это значит, что все транзакции, которые производит движок, сбрасываются на диск устройства. Только представьте, что это значит для большого проекта. Есть две альтернативы. Первая — это значение ноль. Система начнет летать, но при остановке, либо другом нарушении, вся информация об изменениях будет утеряна. Если выставить значение «2», то данные будут храниться в кэше операционной. Для вас это значит, что база данных заработает быстрее, а серьезные последствия ждут только в том случае, если слетит сама ось.

Даже сам факт открытия таблицы может потреблять немало ресурсов, если не внести изменения в конфигурацию. С этой операцией можно совладать с помощью параметра table_cache. Он изменяет размер кэша до требуемых размеров. Вместо того, чтобы постоянно открывать новую таблицу, вы сможете держать их открытыми постоянно. Кстати, на этом примере видно, насколько прогрессивным решением является реляционная база данных, в сравнении с файлами.

Отличным решением для быстродействия является кэш у самого приложения. Но, такой подход не всегда работает — не всегда роскошь доступна. Однако, если считывание данных — частая операция для программы, а кеша нет, то query_cache_size и его изменение может помочь. Установите значение около 512 МБ и посмотрите, не увеличилось ли быстродействие. Если нет — уменьшите наполовину. Потом, увеличьте на столько же. Такой «метод тыка» бывает более быстрым, чем арифметическое сложение и вычитание.

Кстати, если вы не особенно хорошо разбираетесь в железе, значит можно попробовать действовать с помощью конфигурационных заготовок. Перед тем, как менять все подряд в системных файлах, можно попробовать те, которые подготовили разработчики СУБД. Несколько разных файлов my.cnf и один из вас может подойти под тот тип оборудования, который используете именно вы.

Когда применить MyISAM

Это вопрос, который мы задали в самом начале статьи и теперь дадим на него ответ. Но, сначала необходимо узнать об этом движке немного больше. Это система хранения данных, которая основана на одноименных принципах, разработанных, внимание, в 1963 году в компании IBM. Но, взирая на то, что математическим законам нет возраста, система работает до сих пор. Некоторые небольшие веб-проекты активно используют MyISAM и не видят надобности в переходе на InnoDB.

Однако, как только вы увидите, что проект нуждается в масштабировании в сторону «больше», ISAM станет непригоден к использованию. Эта система не поддерживает транзакции, а значит, не позволит участникам взаимодействовать друг с другом в полной мере. По сути, на основе этого движка можно разрабатывать лишь новостные информационные сайты. И даже они нуждаются в высоком уровне интерактивности, как показывают тренды современной веб-разработки.

Совсем недавно преимуществом ISAM был полнотекстовый поиск. Но, с выходом версии InnoDB 5.6.4, он доступен и на этом движке. Сегодня можно с уверенностью сказать, что ISAM — это базы данных начального уровня. Тем не менее, их довольно успешно комбинируют в реализациях социальных сетей, как например Facebook. Там почти весь массив информации хранится в Inno, в то время, как файлы для чтения размещены в MyISAM.

Database twist

Однозначно хорошей новостью можно назвать то, что базы данных можно бесконечно переносить с одного движка на другой. Делается это в уже знакомом нам файле my.cnf. Если вы работали с ISAM, то не забудьте убрать теги комментирования со строки «skip-innodb». Их блокируют для того, чтобы ускорить быстродействие базы данных.

Убедитесь в том, что правильно выставлен параметр innodb_data_file_path. Он должен выглядеть следующим образом:

innodb_data_file_path = ibdata1:100M:autoextend

innodb_data_file_path = ibdata1:100M:autoextend

Для любознательных: он определяет, каким будет наименьший объем файла, в котором хранятся таблицы. А также, позволяет ему увеличиваться в зависимости от хранимой информации. Не желательно, чтобы параметр был в максимальном значении. Далее, следует перезапустить MySQL.

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

mysqldump —opt -u USER -p DBNAME > dump.sql

mysqldump —opt -u USER -p DBNAME > dump.sql

Далее заменяем все на новый движок:

sed ‘s/ENGINE=MyISAM/ENGINE=InnoDB/g’ dump.sql > resultdump.sql

sed ‘s/ENGINE=MyISAM/ENGINE=InnoDB/g’ dump.sql > resultdump.sql

Или:

sed ‘s/ENGINE=InnoDB/ENGINE=MyISAM/g’dump.sql > resultdump.sql

sed ‘s/ENGINE=InnoDB/ENGINE=MyISAM/g’dump.sql > resultdump.sql

Все зависит от того, что на что вы собрались менять. Осталось только удалить старую базу данных. Создайте новую пустую базу данных и выгрузите в нее дамп.

Как видите, разнообразие настраиваемых параметров в MySQL способно удовлетворить всем требованиям, которые ставит перед СУБД веб-разработка. Несмотря на то, что существуют реляционные аналоги этого программного обеспечения, они остаются лидерами рынка. Кроме того, компания Oracle обеспечивает постоянное развитие и обновление, а AMP сборки все чаще делают свой выбор не в пользу альтернатив. Проверенный продукт остается на рынке и продолжает радовать разработчиков.

А у нас все! Экспериментируйте с конфигурациями и получите опыт работы с тонкой настройкой баз данных.

Запуск необходимых параметров MySQL для веб-разработчика

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

webformyself.com

Создание пользователя MySQL | Losst

После того, как вы установили и настроили MySQL, вам необходимо создать базы данных, таблицы и пользователей. Конечно, вы можете сделать это от имени  суперпользователя root, но это не безопасно. Да и большинство приложений не позволят вам такой вольности, например, Phpmyadmin не даст авториrзоваться от имени суперпользователя.

Поэтому для каждой базы данных нужно создавать отдельных пользователей и настраивать для них права. В этой статье мы рассмотрим, как выполняется создание пользователя mysql, а также настройка его прав.

Содержание статьи:

Создание пользователя mysql

1. Как создать пользователя MySQL

Предположим, что база данных уже создана и называется test_database. Нам нужно открыть клиент базы данных. Для этого наберите в терминале:

mysql -u root -p

Теперь можно работать. Для создания пользователя используется команда CREATE USER, её синтаксис такой:

CREATE USER ‘имя_пользователя’@’хост’ IDENTIFIED BY ‘пароль’;

Кроме имени пользователя, здесь нужно задать хост, с которого может авторизоваться этот пользователь. Здесь может быть доменное имя, IP-адрес, адрес подсети или знак «%», который означает все возможные хосты. Это очень удобно, потому что вы можете создать пользователя, к которому можно будет подключится только локально или настроить отдельно права для локального или удалённого пользователя.

Например, давайте создадим локального пользователя test_user с паролем password:

CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'password';

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

CREATE USER 'test_user'@'%' IDENTIFIED BY 'password';

Смотрим наших пользователей:

SELECT User,Host FROM mysql.user;

Все пользователи созданы.

2. Права пользователя MySQL

Создать пользователя — это ещё далеко не всё, теперь нужно назначить ему права. Для этого используется команда GRAT. У неё такой синтаксис:

GRANT привилегии ON база_данных . таблица TO ‘имя_пользователя’@’хост’;

Здесь можем дать определённые привилегии для опредёленного пользователя к базе данных или таблице. Если нужно дать права для всех баз данных или таблиц, используйте вместо названия звездочку — «*». Вот основные права пользователя MySQL, которые вы можете использовать:

  • ALL PRIVILEGES — все, кроме GRANT;
  • USAGE PRIVILEGES — никаких привилегий;
  • SELECT — делать выборки из таблиц;
  • INSERT — вставлять данные в таблицу;
  • UPDATE — обновлять данные в таблице;
  • DELETE — удалять данные из таблицы;
  • FILE — разрешает читать файлы на сервере;
  • CREATE — создавать таблицы или базы данных;
  • ALTER — изменять структуру таблиц;
  • INDEX — создавать индексы для таблиц;
  • DROP — удалять таблицы;
  • EVENT — обработка событий;
  • TRIGGER — создание триггеров.

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

  • GRANT — изменять права пользователей;
  • SUPER — суперпользователь;
  • PROCESS — получение информации о состоянии MySQL;
  • RELOAD — позволяет перезагружать таблицы привилегий;
  • SHUTDOWN — позволяет отключать или перезапускать базу данных;
  • SHOW DATABASES — просмотр списка баз данных;
  • LOCK TABLES — блокирование таблиц при использовании SELECT;
  • REFERENCES — создание внешних ключей для связывания таблиц;
  • CREATE USER — создание пользователей;

Чтобы дать права пользователю MySQL на обновление и добавление записей для базы данных test_database, выполните:

GRANT SELECT,UPDATE,INSERT ON test_database . * TO 'test_user'@'localhost';

 

Дальше дадим этому же пользователю все права над этой базой данных:

GRANT ALL PRIVILEGES ON test_database . * TO 'test_user'@'localhost';

Теперь посмотрим привилегии нашего пользователя:

SHOW GRANTS FOR 'test_user'@'localhost';

Мы видим, что для всех баз данных и таблиц привелегий нет, но зато есть все привилегии для базы данных test_database. Вот так это работает. После обновления прав пользователя необходимо обновить таблицу прав пользователей MySQL  в памяти. Для этого выполните:

FLUSH PRIVILEGES;

3. Удаление прав пользователя MySQL

Чтобы отозвать права у пользователя MySQL, используйте команду REVOKE вместо GRANT. Её синтаксис похож на GRANT:

REVOKE привилегии ON база_данных . таблица FROM ‘имя_пользователя’@’хост’;

Например, заберём все права на базу данных test_database у нашего пользователя:

REVOKE ALL PRIVILEGES ON test_database . * FROM 'test_user'@'localhost';

4. Создание суперпользователя MySQL

Если вам необходимо создать пользователя со всеми правами MySQL на замену для root, то можно использовать такую конструкцию:

GRANT ALL PRIVILEGES ON * . * TO 'test_user'@'localhost';

Даём все привилегии для пользователя test_user над всеми базами данными и всеми таблицами. Но наш пользователь не сможет давать права другим пользователям. Чтобы это исправить, нужно дать ему привилегию GRANT, а для этого используется такая команда:

GRANT ALL PRIVILEGES ON * . * TO 'test_user'@'localhost' WITH GRANT OPTION;

Теперь этот пользователь является суперпользователем для MySQL и, авторизовавшись от его имени в PhpMyAdmin, вы можете делать всё то же самое, что и с  помощью root.

Выводы

В этой статье мы рассмотрели, как создать пользователя MySQL в  Ubuntu или в любом другом дистрибутиве. Работа в командной строке с MySQL может показаться довольно сложной. Но сделать это надо всего один раз -после установки базы данных. А дальше можно выполнять все действия в Phpmyadmin.

losst.ru

Настройка MySQL + octopus / Habr

В данной статье я бы хотел показать как настроить MySQL для дальнейшего использования gem’a octopus, который используется для шардинга и репликации в Rails — приложениях.
Итак, представим, что перед нами стоит задача развернуть три сервера(на первом крутится Rails — приложение, второй нужен для Master’a, третий будет выступать в качестве Slave’a), настроить репликацию между серверами и сделать так, чтобы octopus работал.

Шаг первый — предварительная настройка серверов
Действия, описанные в данном шаге, необходимо проделать как на Master — машине, так и на Slave.

Установим MySQL.

sudo apt-get install mysql-server

Теперь зайдем в MySQL консоль:
mysql -u root -p

Создадим базу данных, а также пользователя, у которого будут все права на работу с данной базой:
create database rails_myapp;
GRANT ALL ON rails_myapp.* TO 'rails_myapp_user'@'localhost';
GRANT ALL ON rails_myapp.* TO 'rails_myapp_user'@'%';
FLUSH PRIVILEGES;
EXIT;

Шаг второй — настроим Master
Откроем файл конфигурации MySQL на Master — сервере.
sudo nano /etc/mysql/my.cnf

Внутри этого файла мы сделаем несколько изменений. Во — первых, найдем следующую строчку:
bind-address            = 127.0.0.1

Заменим стандартный IP адрес на 0.0.0.0, для того, чтобы Rails — приложение могло достучаться до сервера:
bind-address            = 0.0.0.0

Следующим нашим шагом будет изменение значения server-id, ищем server-id в [mysqld] секции файла конфига. Вы можете выбрать любое число в качестве server-id, но для простоты лучше указать 1, нужно лишь помнить, что данное число должно быть уникально для группы серверов, которые будут участвовать в репликации.

Еще раз убедитесь, что данная строка откомментирована.

server-id               = 1

Теперь перейдите на строку со значением log_bin. Slave будет копировать все изменения, которые будут регистрироваться в логе. Опять же, лучше просто откомментировать строчку с log_bin:
log_bin                 = /var/log/mysql/mysql-bin.log

В конце Вы должны указать имя базы данных, которая будет реплицирована Slave — сервером. Вы можете указать несколько баз, повторяя данную строку для всех баз, которые хотите репллицировать.
binlog_do_db            = rails_myapp

Все изменения сделаны! Можно сохранить и закрыть файл.

Перезапустим MySQL.

sudo service mysql restart

Откроем MySQL — шелл.
mysql -u root -p

Вы должны предоставить привилегии для Slave. Для этого используем следующую команду:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Далее Вы должны переключиться на свою базу:
USE  rails_myapp;

Заблокируем базу данных, чтобы посмотреть данные, которые пригодятся нам на следующих этапах:
FLUSH TABLES WITH READ LOCK;

Далее наберем:
SHOW MASTER STATUS;

Вы увидите примерно такую табличку, если наберете описанную ниже команду:
mysql> SHOW MASTER STATUS;

File Position Binlog_Do_DB
mysql-bin.000001 107 rails_myapp

1 row in set (0.00 sec)

Важно! Запишите или запомните название файла и номер позиции, данные значения будут использоваться позже.
Пока ваши базы данных заблокированы, нужно экспортировать базу rails_myapp. Откройте второй терминал в новом окне, убедитесь, что набираете данную команду в bash — шеле, а не в консоле MySQL.

mysqldump -u root -p --opt rails_myapp > rails_myapp.sql

Перейдите обратно в MySQL консоль, разблокируйте базы данных(разрешим запись).
UNLOCK TABLES;
QUIT;

Все! Мы закончили настройку Master’a.

Шаг третий — настроим Slave.
Зайдите на MySQL сервер, откройте MySQL и создайте базу данных(имя точно такое, как и на мастере):

CREATE DATABASE rails_myapp;
EXIT;

Перенесите файл с SQL — командами, который вы экспортировали на мастере и сделайте импорт.
mysql -u root -p rails_myapp< /path/to/rails_myapp.sql

Затем необходимо немного поправить файл конфигурации MySQL:
sudo nano /etc/mysql/my.cnf

Первое, что изменим будет server-id. Как Вы помните, номер должен быть уникальным в пределах группы(в конфиге Master’a мы указали 1).
server-id               = 2

Теперь допишем(или откомментируем) следующие три строки:
relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = newdatabase

Зададим новое значение bind-address:
bind-address            = 0.0.0.0

Перезапустим MySQL:
sudo service mysql restart

Следующим шагом будет непосредственно запуск репликации. Помните! Вместо MASTER_LOG_FILE и MASTER_LOG_POS Вы должны вписать те значения, которые записали до этого.
CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;
START SLAVE;

Как проверить, удачно ли прошел запуск Slave’a? Необходимо ввести комманду, описанную ниже и просмотреть лог:
SHOW SLAVE STATUS\G

Шаг четвертый — настроим octopus.
В Rails — приложении нужно добавить следующую строчку в Gemfile:

gem 'ar-octopus'

Внутри директории config/ нужно создать файл shards.yml, который будет отвечать за конфигурирование Slave — сервера. Помните! Файл database.yml отвечает за конфигурацию Master’a, а только что созданный shards.yml за конфигурацию Slave’ов,  не забываем, что в YML пробелы и табуляции имеют значение.

Файл shards.yml, после заполнения должен выглядеть примерно так:

 octopus:
  replicated: true
  fully_replicated: true
  environments:
    - development
    - test
  development:
    slave:
      adapter: mysql2
      encoding: utf8
      database: rails_myapp
      username: rails_myapp_user
      password: qwerty
      host: 130.111.11.111
      port: 3306
      pool: 10
  test:
    slave:
      adapter: mysql2
      encoding: utf8
      database: rails_myapp
      username: rails_myapp_user
      password: qwerty
      host: 130.111.111.111
      port: 3306
      pool: 10

Все! Осталось прописать в Ваших моделях, что они являются replicated_model(), и запустить Rails — приложение. Отправив POST — запрос, в консоли Вы должны увидеть примерно следующее:
 [Shard: slave]  OurModel Load (1.0ms)  SELECT  `model`.* FROM `model` WHERE `chats`.`id` = 'gkjhgfhd' LIMIT 1

Удачной настройки!

habr.com

Оптимизация производительности MySQL | Losst

MySQL — это одна из самых популярных реляционных систем управления базами данных, которая используется для обеспечения большинства веб-сайтов в интернете. От скорости записи и получения данных из таблиц зависит скорость работы сайта, в целом, так как, если на один запрос будет уходить больше секунды, то это будет тормозить работу php, а в следствии скоро накопиться столько запросов, что сервер не сможет их обработать.

В сегодняшней статье мы поговорим о том, как выполняется оптимизация производительности mysql. Какие программы для этого лучше использовать и как это работает.

Содержание статьи:

Скорость работы MySQL

Оптимизация без аналитики бессмысленна. Перед тем как переходить к оптимизации давайте посмотрим как работает база данных сейчас, есть ли запросы, которые выполняются очень медленно. Все настройки вашего сервиса mysql находятся в файле /etc/my.cnf. Чтобы включить отображение медленных запросов добавьте такие строки в my.cnf, в секцию [mysqld]:

log-slow-queries=/var/log/mariadb/slow_queries.log
long_query_time=5

Здесь первая строка включает запись лога медленных запросов, вторая указывает, что минимальное время запроса для внесения его в этот лог — две секунды. Еще можно включить в лог запросы, которые не используют индексы:

log-queries-not-using-indexes=1

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

systemctl restart mariadb

tail -f /var/log/mariadb/slow-queries.log

Мы можем видеть, что есть запросы, которые выполняются больше, чем 10 секунд. Это, например, запрос

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Можно его выполнить отдельно, в консоли mysql:

 

Здесь тоже измеряется время, и мы видим результат — три секунды. Это очень много. И еще ничего, если такие запросы приходят редко, если ваш сайт постоянно под нагрузкой, то тремя секундами вы не отделаетесь, количество необработанных запросов будет расти, а скорость ответа увеличиваться до нескольких минут. Можно пойти двумя путями — оптимизировать код, убрать сложные запросы, или же нужна оптимизация mysql на сервере.

Оптимизация MySQL

Конфигурация MySQL достаточно сложная, но, к счастью, вам не нужно в нее сильно углубляться. Есть специальный скрипт под названием MySQLTunner, который анализирует работу MySQL и дает советы какие параметры нужно изменить и какие значения для них установить. Скрипт поддерживает большинство версий MariaDB, MySQL и Percona XtraDB. Нам понадобится загрузить три файла с помощью wget:

wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

Первый из них — это сам скрипт, написанный на Perl, второй и третий — база данных простых паролей и уязвимостей. Они позволяют обнаружить проблемы с безопасностью. Дальше можно переходить к тестированию. Я использую сервер с настройками mysql по умолчанию, установленными панелью управления VestaCP.

perl ./mysqltuner.pl

Буквально за несколько минут скрипт выдаст полную статистику по работе MySQL. Количеству запросов, занимаемому объему памяти и эффективности работы буферов. Вы можете ознакомиться со всем этим, чтобы лучше понять в чем причина проблем. Проблемные места обозначены красными восклицательными знаками. Например, здесь мы видим, что размер буфера движка таблиц InnoDB (InnoDB buffer pool) намного меньше, чем должен быть для оптимальной работы:

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

Все параметры нужно добавлять в /etc/my.cnf. Еще раз замечу, что вы не копируете статью, а смотрите что вам выдала утилита. Начнем с query-cache.

query_cache_size=0
query_cache_type=0
query_cache_limit=1M

Скрипт рекомендует отключить кэш запросов. Query Cache — это кэш вызовов SELECT. Когда базе данных отправляется запрос, она выполняет его и сохраняет сам запрос и результат в этом кэше. И все бы ничего, но при использовании его вместе с InnoDB при любом изменении совпадающих данных кэш будет перестраиваться, что влечет за собой потерю производительности. И чем больше объем кэша, тем больше потери. Кроме того при обновлении кэша могут возникать блокировки запросов. Таким образом, если данные часто пишутся в базу данных — его надежнее отключить.

tmp_table_size=16M
max_heap_table_size=16M

Оба параметра устанавливают размер памяти, которая используется для внутренних временных таблиц MySQL. Утилита рекомендует использовать объем больше 16 мегабайт, просто установите это ваше значение для обоих переменных, если у вас достаточно памяти, то можно выделить 32 или даже 64. Но важно чтобы оба значения совпадали, иначе будет использоваться минимальное.

thread_cache_size=16

Этот параметр отвечает за количество потоков, которые будут закэшированны. После того, как работа с подключением будет завершена, база данных не разорвет его, а закэширует, если количество кэшированных потоков не превышает ограничение. Утилита рекомендует больше четырех, например, 16.

skip-name-resolve=1

Указывает, что не нужно пытаться определить доменное имя для подключений извне. Ускоряет работу, так как не тратится время на DNS запросы.

innodb_buffer_pool_size=800M

Этот параметр определяет размер буфера InnoDB в оперативной памяти, от этого размера очень сильно зависит скорость выполнения запросов. Значение зависит от размера ваших таблиц и количества данных в них. Если памяти недостаточно, запросы будут обрабатываться дольше. У меня используется стандартный объем 128, а нужно больше 652.

innodb_log_file_size=200M

Размер файла лога innodb должен составлять 25% от размера буфера. В случае 800 мегабайт это будет 200М. Но тут есть одна проблема. Чтобы изменить размер лога нужно выполнить несколько действий. Поскольку мы изменили все нужные параметры перейдем к перезагрузке сервера. Для нашего лога нужно остановить сервис:

systemctl stop mariadb

Затем переместите файлы лога в /tmp:

mv /var/lib/mysql/ib_logfile[01] /tmp

И запустите сервис:

systemctl start mariadb

Когда размер лога меняется сервис видит поврежденный лог, выдает ошибку и не запускается. Поэтому сначала нужно удалить старый. После этого смотрите есть ли сообщения об ошибках:

systemctl status mariadb

Тестирование результата

Готово оптимизация базы данных mysql завершена, теперь тестируем тот же запрос через клиент mysql:

mysql

> USE база_данных;
> SELECT option_name, option_value FROM wpfc_options WHERE autoload = 'yes';

Первый раз он выполняется долго, может даже дольше чем обычно, но все последующие разы буквально мгновенно. Результат с более 3 секунд до 0,15. А если брать статистику из slow-log, то от более 12. Если в выводе утилиты для вас были предложены и другие оптимизации, то их тоже стоит применить.

Выводы

Как видите, оптимизация mysql это достаточно просто благодаря такому скрипту, но, в то же время, такая операция может очень сильно помочь, особенно высоконагруженным проектам. Еще лучше ускорить работу может только оптимизация запросов mysql. Не забывайте время от времени проверять параметры, чтобы быть уверенным что все в порядке. Если у вас остались вопросы, спрашивайте в комментариях!

На завершение лекция про производительность MySQL от Percona:

Оцените статью:

Загрузка…

losst.ru

Установка и настройка MySQL сервера на Ubuntu » Инструкции

519x379MySQL — это быстрый, многопоточный, многопользовательский и устойчивый сервер SQL базы данных. Он предназначен как для ответственных сильнозагруженных производственных систем, так и для встраивания в массовое программное обеспечение.

Установка

Для установки MySQL запустите следующую команду из терминала:

sudo apt-get install mysql-server

 

Начиная с Ubuntu 12.04, MySQL 5.5 устанавливается по умолчанию. Несмотря на 100% совместимость с MySQL 5.1, при необходимости установить версию 5.1 (например в качестве зависимой базы к другим MySQL 5.1 серверам), вы можете заменить устанавливаемый пакет на mysql-server-5.1.

 

В процессе установки у вас запросят пароль для пользователя root под MySQL.

Как только установка завершится, сервер MySQL запустится автоматически. Вы можете использовать следующую команду в терминале для проверки, что сервер MySQL запущен:

sudo netstat -tap | grep mysql

Когда вы запустите эту команду, ввы сможете увидеть что-то похожее на следующую строку:

tcp        0      0 localhost:mysql         *:*                LISTEN      2556/mysqld

Если сервер не работает, вы можете набрать следующую команду для его запуска:

sudo service mysql restart

Настройка

Вы можете отредактировать файл /etc/mysql/my.cnf для настройки основных параметров — файл журнала, номер порта и пр. Например, чтобы настроить MySQL на ожидание подключений от компьютеров в сети, измените параметр bind-address на IP адрес сервера:

bind-address            = 192.168.0.5

 

Замените 192.168.0.5 на реальное значение адреса вашего сервера.

 

После изменений в /etc/mysql/my.cnf сервис MySQL нужно перезагрузить:

sudo service mysql restart

Если вам потребовалось сменить пароль пользователя root в MySQL, введите в терминале:

sudo dpkg-reconfigure mysql-server-5.5

Сервис MySQL будет остановлен и вас попросят ввести новый пароль.

Драйверы базы данных

Хотя конфигурация по умолчанию для MySQL, предоставляемая пакетами Ubuntu, имеет великолепную функциональность и работает достаточно хорошо, есть некоторые вещи, которые вы можете решить до того как продолжить.

MySQL разработан так, что позволяет хранить данные по-разному. Эти варианты относятся к драйверам (управляющим модулям — engines) как баз данных, так и хранилищ. Существует два основных драйвера, которые вам могут быть интересны: InnoDB и MyISAM. Драйверы хранилищ прозрачны (незаметны) конечным пользователям. MySQL управляет событиями по-разному на нижнем уровне, но независимо от того, какая система хранения данных используется, вы будете взаимодействовать с базой одним и тем же способом.

Каждый драйвер имеет свои преимущества и недостатки.

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

  1. MyISAM более старая из двух. Она может быть быстрее InnoDB при определенных обстоятельствах и предпочтительна при рабочей нагрузке, ориентированной на чтение данных. Некоторые интернет приложения настроены на использование именно MyISAM (однако это не означает, что они будут медленнее под InnoDB). MyISAM также поддерживает тип данныхFULLTEXT, который позволяет осуществлять очень быстрый поиск по большому количеству текстовых данных. Однако MyISAM поддерживает блокировку записи только на уровне таблиц. Это означает, что только один процесс может изменять данные в таблице в один момент времени. Поскольку некоторые приложения, использующие таблицу, могут масштабироваться (работать несколькими экземплярами — scales), это может стать серьезной помехой. Здесь также отсутствует журналирование, что может усложнить восстановление данных после сбоя. Следующая ссылка предоставляет некоторые соображения по использованию MyISAM в работающей базе данных.

  2. InnoDB — более современный драйвер, созданный по принципам ACID, что гарантирует надежную обработку транзакций базы данных. Блокировка записи производится на уровне одной записи в таблице. Это означает возможность нескольких изменений в одной таблице одновременно. Кэширование данных происходит также и в оперативной памяти внутри драйвера базы данных, позволяя кэшировать более эффективно чем на уровне блоков файлов. В соответствии с ACID все транзакции журналируются независимо от основных таблиц. Это позволяет намного более надежно восстанавливать данные при проверке целостности данных.

Начиная MySQL 5.5 InnoDB является драйвером по умолчанию и настоятельно рекомендуется вместо MyISAM, если только у вас нет специфических потребностей, уникальных для этого драйвера.

Расширенные настройки

 

Создание настроенного файла my.cnf

Существует ряд параметров, которые могут быть указаны в файле настроек MySQL, что со временем позволит вам повысить производительность вашего сервера. Для начальной настройки вам может пригодиться Percona’s my.cnf generating tool. Этот инструмент позволит вам создать файл my.cnf более оптимизированный под специфические возможности вашего сервера и ваши требования.

neoblogs.net

Как разрешить удаленные подключения к серверу базы данных MySQL

По умолчанию сервер MySQL прослушивает соединения только от localhost, что означает, что к нему могут обращаться только приложения, работающие на одном хосте.

Однако в некоторых ситуациях необходимо разрешить удаленные подключения. Например, когда вы хотите подключиться к удаленному серверу MySQL из вашей локальной системы, или когда вы используете многосерверное развертывание, когда приложение работает на компьютере, отличном от сервера базы данных.

В этой статье мы рассмотрим шаги, необходимые для разрешения удаленных подключений к серверу MySQL. То же самое относится и к MariaDB.

 

Первый шаг – настроить сервер MySQL на прослушивание определенного IP-адреса или всех IP-адресов на компьютере.

Если сервер MySQL и клиенты могут взаимодействовать друг с другом через частную сеть, то лучшим вариантом будет настроить сервер MySQL на прослушивание только по частному IP. В противном случае, если вы хотите подключиться к серверу через общедоступную сеть, настройте сервер MySQL на прослушивание всех IP-адресов на компьютере.

Для этого вам нужно отредактировать файл конфигурации MySQL и добавить или изменить значение опции bind-address. Вы можете установить один IP-адрес и IP-диапазоны. Если адрес есть 0.0.0.0, сервер MySQL принимает соединения на всех интерфейсах IPv4 хоста. Если в вашей системе настроен IPv6 0.0.0.0, используйте вместо ::.

Расположение файла конфигурации MySQL зависит от дистрибутива. В Ubuntu и Debian файл находится по адресу /etc/mysql/mysql.conf.d/mysqld.cnf, в то время как в дистрибутивах на основе Red Hat, таких как CentOS, файл находится по адресу /etc/my.cnf.

Откройте файл в текстовом редакторе :

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

 

Найдите строку, которая начинается с bind-address и установите в качестве значения IP-адрес, который должен прослушивать сервер MySQL.

По умолчанию установлено значение 127.0.0.1 (прослушивается только на локальном хосте).

В этом примере мы настроим сервер MySQL на прослушивание всех интерфейсов IPv4, изменив значение на 0.0.0.0
mysqld.cnf

bind-address           = 0.0.0.0
# skip-networking

 

Если есть строка, содержащая skip-networking, удалите ее или закомментируйте, добавив # в начале строки.

В MySQL 8.0 и выше директива bind-address может отсутствовать. В этом случае добавьте ее в разделе [mysqld].

После этого перезапустите службу MySQL, чтобы изменения вступили в силу. Только root или пользователи с привилегиями sudo могут перезапускать сервисы.

Чтобы перезапустить службу MySQL в Debian или Ubuntu, введите:

sudo systemctl restart mysql

 

В дистрибутивах на основе RedHat, таких как CentOS, для перезапуска службы выполните:

sudo systemctl restart mysqld

 

Следующим шагом является предоставление доступа к базе данных удаленному пользователю.

Войдите на сервер MySQL от имени пользователя root, введя:

sudo mysql

 

Если вы используете старый плагин аутентификации MySQL для входа в систему от имени пользователя root, введите команду ниже и введите пароль при появлении запроса:

mysql -uroot -p

 

Внутри оболочки MySQL используйте GRANTоператор для предоставления доступа удаленному пользователю.

GRANT ALL ON database_name.* TO user_name@'ip_address' IDENTIFIED BY 'user_password';

 

Где:

  • database_name – Имя базы данных, к которой будет подключаться пользователь.
  • user_name – это имя пользователя MySQL.
  • ip_address – это IP – адрес , с которого пользователь будет подключаться. Используйте, %чтобы позволить пользователю подключаться с любого IP-адреса.
  • user_password – это пароль пользователя.

Например, чтобы предоставить доступ к базе данных dbname пользователю с именем andreyex с паролем my_passwd с клиентского компьютера с IP 10.8.0.5, вы должны выполнить:

GRANT ALL ON dbname.* TO andreyex@'10.8.0.5' IDENTIFIED BY 'my_passwd';

 

Последний шаг – настройка брандмауэра для разрешения трафика через порт 3306( порт MySQL по умолчанию) с удаленных компьютеров.

 

Если вы используете iptables в качестве брандмауэра, команда ниже разрешит доступ с любого IP-адреса в Интернете к порту MySQL. Это очень небезопасно.

sudo iptables -A INPUT -p tcp --destination-port 3306 -j ACCEPT

 

Разрешить доступ с определенного IP-адреса:

sudo iptables -A INPUT -s 10.8.0.5 -p tcp --destination-port 3306 -j ACCEPT

 

UFW – это инструмент брандмауэра по умолчанию в Ubuntu. Чтобы разрешить доступ с любого IP-адреса в Интернете (очень небезопасно), выполните:

sudo ufw allow 3306/tcp

 

Разрешить доступ с определенного IP-адреса:

sudo ufw allow from 10.8.0.5 to any port 3306

 

FirewallD является инструментом управления брандмауэром по умолчанию в CentOS. Чтобы разрешить доступ с любого IP-адреса в Интернете (очень небезопасно), введите:

sudo firewall-cmd --permanent --zone=public --add-port=3306/tcpsudo firewall-cmd --reload

Чтобы разрешить доступ с определенного IP-адреса на конкретном порту, вы можете создать новую зону FirewallD или использовать расширенное правило. Хорошо создайте новую зону с именем mysqlzone:

sudo firewall-cmd --new-zone=mysqlzone --permanentsudo firewall-cmd --reloadsudo firewall-cmd --permanent --zone=mysqlzone --add-source=10.8.0.5/32sudo firewall-cmd --permanent --zone=mysqlzone --add-port=3306/tcpsudo firewall-cmd --reload

 

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

mysql -u user_name -h mysql_server_ip -p

 

Где user_name имя пользователя, которому вы предоставили доступ и mysql_server_ip, IP-адрес хоста, на котором работает сервер MySQL.

Если все настроено правильно, вы сможете войти на удаленный сервер MySQL.

Если вы получаете сообщение об ошибке, как показано ниже, либо порт 3306 не открыт, либо сервер MySQL не прослушивает IP-адрес.

ERROR 2003 (HY000): Can't connect to MySQL server on '10.8.0.5' (111)"

 

Приведенная ниже ошибка указывает на то, что пользователь, которому вы пытаетесь войти, не имеет прав доступа к удаленному серверу MySQL.

"ERROR 1130 (HY000): Host ‘10.8.0.5’ is not allowed to connect to this MySQL server"

 

MySQL, самый популярный сервер баз данных с открытым исходным кодом по умолчанию, прослушивает входящие соединения только на локальном хосте.

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

  1. Настройте сервер MySQL для прослушивания всего или определенного интерфейса.
  2. Предоставьте доступ удаленному пользователю.
  3. Откройте порт MySQL в вашем брандмауэре.

Если у вас есть вопросы, не стесняйтесь оставлять комментарии ниже.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

andreyex.ru

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *