Настраиваем сервер для LiveStreet. Часть IV. MySQL.

Предыдущие части:

Сначала генерируем и запоминаем пароль для MySQL-аккаунта root, который попросят во время установки:
pwgen -c -n 10 1


И запускаем установку:
apt-get install percona-server-server-5.5 percona-server-client-5.5


После установки, конфигурации для него по умолчанию не будет. Такова политика MySql начиная с версии 5.5. Поэтому нужно самим создать файл /etc/mysql/my.cnf:
vim /etc/mysql/my.cnf

следующего содержания:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
default-character-set = utf8

[mysqld]
#
# * Basic Settings
#
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
bind-address = 127.0.0.1

character-set-server = utf8
collation-server = utf8_general_ci

default-storage-engine = InnoDB
innodb_file_per_table = 1
innodb_file_format = barracuda
innodb_strict_mode = 1

innodb_buffer_pool_size = 1G
innodb_read_io_threads = 8
innodb_write_io_threads = 16
На последних 3-х остановимся подробно.
  • innodb_buffer_pool_size — по умолчанию MySQL установит как предел 80% доступной памяти и будет их неспешно отъедать надо оно ему или нет. Эта директива указывает ему потолок, в некоторой степени. Для LS c 1000 статей, 10000 комментариев и 1000 уников в день и 256M в общем, хватит. В основном, упирается в размеры вашей БД. Если, например, у вас сервер с 16Gb, то 80% под MySQL — может быть крайним расточительством. В сети можно найти запросы которые помогут понять сколько действительно нужно памяти для БД. Вот например: dba.stackexchange.com/a/27341. Можете по-разбираться, там всё элементарно. Боитесь — уберите директиву.
  • innodb_read_io_threads & innodb_write_io_threads — игры с параллелизмом. Если у вас VPS — убрать эти директивы из конфигурации обязательно. Если очень мощный, многопроцессорный сервер — можно поставить даже максимум: 64. В приведенном примере, просто компромиссный вариант.

После создания файла перестартовываем:
/etc/init.d/mysql restart


Замечание: тонкая настройка MySQL тема необъятного размера и сложности. Но, для небольших/средних сайтов можно вполне доверится настройкам по умолчанию. Однако, вот несколько полезных линков от господ ewden и ort :
https://github.com/rackerhacker/MySQLTuner-perl
http://www.day32.com/MySQL/
https://tools.percona.com/wizard
http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/introduction.html


Всё. Установили. Теперь нам нужен специфический аккаунт который можно использовать для обеспечения SSH tunneling, но которым вообще ничего нельзя делать через консоль и SFTP. Этот парень нужен нам чтобы работать с MySql over SSH, не открывая лишних возможностей.

Создаем аккаунт с именем, например, sqltunnel (имя лучше придумайте другое) без домашней директории и с хитрым shell:
useradd -g ssh -M -N -s /usr/sbin/nologin sqltunnel

Обычным образом генерируем и запоминаем пароль, подлиннее и посложнее:
pwgen -c -n -y 12 1

Задаём этот пароль нашему аккаунту:
passwd sqltunnel


Теперь нам надо разрешить этому пользователю ходить только к MySql который сидит на сервере на порте 3306. Для этого идем в конфигурационный файл /etc/ssh/sshd_config
vim /etc/ssh/sshd_config

и в самый конец файла (это важно!) добавляем следующее:
Match User sqltunnel
    PermitOpen localhost:3306
    AllowAgentForwarding no


После внесения настроек пере-запускаем sshd:
/etc/init.d/ssh restart


Теперь нам нужен MySql клиент.

Для начала качаем plink.exe и кидаем его туда же куда кинули putty.exe в первой части.

Качаем отсюда www.heidisql.com/download.php бесплатный и симпотичный HeidiSQL 7.0. Устанавливаем его на родимый Windows и запускаем:
heidisql #1
Задаём всё как на картинке, пока что в качестве пользователя используем root c тем паролем который сгенерировали в самом начале этой заметки. Переключаемся на закладку «SSH tunneling»:

Указываем путь к plink.exe. В синее поле задаём IP сервера, в красное — наш aккаунт для SSH-tunneling — sqltunnel. нажимаем Open — и всё. Соединитесь с MySQL:


Теперь нам нужно подготовить Базу данных для LiveStreet. Выполняем в HeidiSQL запрос для создания новой базы:
CREATE DATABASE mysuperlsdb;
И после этого там же создаем специальный MySQL аккаунт которой имеет права только к этой базе:
GRANT ALL ON mysuperlsdb.* TO newuser@localhost IDENTIFIED BY 'superpassword';

В качестве «newuser» нужно, конечно, придумать что-то осмысленное, а пароль вообще сгенерировать тем же pwgen, например.

Замечание: Если уже есть полный dump базы (в примере ниже — mydb.sql), то самый быстрый и надежный способ создать из него базу — просто залить файл с дампом на сервер по SFTP и потом выполнить из консоли команду:
mysql --user=root --password=XXXXXXXX < mydb.sql


Всё :) Продолжение в следующей части:
Часть V. Apache

43 комментария

avatar
Вроде тот-же my.cnf надо сначала скопировать в /etc/mysql из /usr/share/perona-server-5.5.
Сколько помню, /etc/mysql всегда был без него. Хотя, pecrona-server-common (Как-то так) был поставлен.
avatar
Точно, глянул щас — /usr/share/mysql. Там конфиги. Плюс надо проверить, не лезет ли мускл голой попой в мир.
avatar
bind-address = 127.0.0.1
так это спасает от мира, или я не о том подумал?
avatar
Ага, не заметил.
avatar
Для настроек my.cnf у перконы есть сервис — tools.percona.com/wizard
  • ort
  • +6
avatar
Спасибо, отличный тюнер.
avatar
Отлично! ))
avatar
Спасибо, симпатичная штука.
avatar
Спасибо по больше бы таких мануалов ;)
avatar
Gmugra
mysql_secure_installation
Запустить так-же стоило бы наверно.
avatar
не вижу большого смысла на доступном только локально сервере, если честно.
avatar
Как минимум анонимные аккаунты удалить стоит. А оно это делает.
В любом случае лишним не будет.
avatar
Ну… кому надо информацию из комментария уже получил :). Я например еще mysql_tzinfo_to_sql тоже накатываю, но опять же не считаю что это нужно всем и всегда.
avatar
На домашнем компе с xubuntu не было изначально pwgen, если не хочется лишний раз устанавливать, можно воспользоватся штатными средствами :)

cat /dev/urandom | tr -d -c 'a-zA-Z0-9' | fold -w 8 | head -1
avatar
У меня VPS и там вместе с панелью от ISPserver автоматом была установлена mysql-server-5.1-5.1.61-0+squeeze1, да еще и без поддержки InnoDB.

Чтобы добавить поддержку, нужно эту снести и поставить новую версию? Хочется вариант чтобы минимально трогать сайт (он сейчас рабочий, но работает на Myisam)

буду признателен за совет.

P.S.
С учетом того, что уже анонсирована стабильная MySQL 5.6, установленная сейчас mysql-server-5.1-5.1.61-0+squeeze1 старовата?
avatar
Вообще-то поддержка InnoDB есть всегда, как минимум, встроенный вариант. Плагин InnoDB — лучше. fork этого плагина, под названием XtraDB, который и является основой Percona — ещё лучше. вы скорее всего просто базу создали не сказав что хотите InnoDB.

Совет такой — если работает нормально — ничего не трогайте. Черт его знает что случится с ISPserver когда вы там начнете софт курочить.
avatar
Спасибо!
Уже разобрался, что у них там это просто отключено в конфиге, нужно раскомментить в конфиге «skip-innodb» и перезапустить
avatar
Не уверен что всё так просто. Вас придётся конвертировать базу данных скорее.
avatar
базу конвертировать ес-но придется. Я говорил про то, как включить поддержку InnoDB, к сожалению я до этого не обратил внимания, что у них по умолчанию она отключена.
avatar
На всякий случайно привожу инструкцию по конвертации базы в InnoDb (у меня все сработало)

Конвертация

Все операции выполняются в консоле.

Создание исходного дампа базы:
mysqldump --opt -u USER -p DBNAME > dbname.sql

Изменяем тип движка для таблиц:

для MyISAM → InnoDB:
sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' dbname.sql > dbname.innodb.sql

Теперь удалим саму БД:
mysqladmin -u USER -p drop DBNAME

И создадим заново:
mysqladmin -u USER -p create DBNAME

Заливаем дамп обратно:
mysql -u USER -p DBNAME < dbname.innodb.sql
avatar
А вы уверены что у вас нет полнотекстовых индексов?
avatar
А что должно было произойти, если бы были (сейчас нет возможности просматривать все таблицы на их наличие)?
Мне вроде ошибок при таком алгоритме не выводилось.
avatar
глянул, перестанет работать то, что использовало Fulltext, но у меня вроде стандартная сборка + несколько плагинов, так что не должно ничего произойти.

хотя конечно можно все таблицы просмотреть и перепроверить.
avatar
Но к сожалению также выяснил, что установленное ими «mysql-server-5.1-5.1.61-0+squeeze1» старовата и все-таки желательно обновится на 5.5, иначе не работают опции

innodb_file_format = barracuda
innodb_strict_mode = 1
avatar
Выкиньте их из конфига, для начала. barracuda действительно только с начиная плагина работает. Ну будет у вас Antelope (которое по умолчанию) — не страшно, для начала.
avatar
по данному мануалу хайди вываливает ошибку
можно как то пофиксить?
Сервер в инете, я(клиент) за роутером
avatar
проверьте правильно ли вы сделали аккаунт для туннелинга. Просто в Putty им попробуйте залогинится. Если пустит и сразу выкинет — всё OK. Если начнет орать про невозможность создать соединение — что-то не так.
avatar
Хотелось бы добавит, что у root'a не должно быть прямого доступа по SSH (а ну его нафиг?):
Редактируем: /etc/ssh/sshd_config, добавляем строчки:
AllowUsers sqltunnel user_dlya_ssh
PermitRootLogin no

Можно заранее создать обычного пользователя user_dlya_ssh, у которого нет рут прав с другим паролем.
А потом просто вводим su и переходим в режим суперпользователя.
avatar
Аккаунт для туннелинра проверил.через Putty заходит.
С root'овским акк mysql то же все Ok.
Почему ругается?

PS. убираю пароль у root(настройки mysql) — входит на локальный сервер.
avatar
У меня по инструкции с первого раза HeidiSQL не завелся, была ошибка:

SQL Error (2013): Lost connection to MySQL server at 'reading initinal communication packet', system error: 0

Надо либо в настройках HeidiSQL на первой вкладке «Settings» в поле «Hostname / IP» вместо 127.0.0.1 указать localhost
либо на сервере в настройках ssh вместо localhost указать 127.0.0.1
вот здесь:
Match User sqltunnel
    PermitOpen 127.0.0.1:3306
    AllowAgentForwarding no
avatar
Спасибо, с localhost заработало! Я думаю стоит изменить в статье информацию.
avatar
У меня прекрасно работает с настройкой именно так, как описано в заметке. Почему возникает такой косяк — странно. localhost всё равно резолвится на 127.0.0.1, не должно быть никакой разницы использовать то или иное.
avatar
Ребят подскажите пожалуйста по необычному вопросу… Очень медленно работает мускуль, но при этом вообще не грузит процессор и не жрет память. Выполняю импорт (не LS) на другой, более слабой машине — все влетает раза в три быстрее, а тут никакой нагрузки нет, но импорт минут 5 выполняется.
avatar
Еще для более тонкой настройки конфига mysql советую такую вот маленькую утилиту, она не требует установки, просто качаем один файл:
wget mysqltuner.pl/
и запусткаем
perl index.html
он показывает статистику и дает советы по настройке mysql, примерно так:
avatar
wget http://mysqltuner.pl/
avatar
И еще, советую, для общего, так сказать, развития, глянуть вот эту конфигурацию, там все очень правильно расписано у человека в конфиге (первый пост), конечно цифры там для сервера с 48ГБ оперативы, но сам пример… В общем, для себя использовал как пример.
avatar
Потерянный я сегодня, вот ссылка — sqlinfo.ru/forum/viewtopic.php?id=6590
avatar
Ув. ТС, еще такой вопрос. Сейчас, когда уже базы созданы, сайты работают, если я в конфиге MySQL добавлю следующие директивы, не будет ли проблем с работой? В смысле, уже созданные базы будут работать с учетом этих конфигураций или уже только новые??

character-set-server = utf8
collation-server = utf8_general_ci

default-storage-engine = InnoDB
innodb_file_per_table = 1
innodb_file_format = barracuda
innodb_strict_mode = 1
avatar
Уже созданные — не будут. innodb_file_format, например не меняется налету, насколько я знаю. НО, вы можете просто пересоздать существующую базу — слить её в скрипт и создать, например, с новым именем. При создании новой базы из скрипта, все настройки для будут учтены.
avatar
После ввода пароля появилось вот такое сообщение.


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