Практически любое современное клиент-серверное приложение не обходится без СУБД и в большинстве организаций обслуживание серверов баз данных лежит исключительно на плечах сисадмина. Штатные инструменты позволяют решить только базовые задачи и их функций не всегда достаточно. Утилиты сторонних разработчиков сделают администрирование MySQL и клонов, очень простым.
Интерфейс администрирования
MySQL и клоны по умолчанию распространяются без графического интерфейса. В самых простых случаях с несколькими базами для управления достаточно командной строки, когда же количество серверов, баз и админов их обслуживающих переваливает за десяток необходимость в GUI становится более очевидной. Oracle предлагает свою разработку — единый инструмент для разработчиков баз данных и администраторов MySQL Workbench(mysql.com/products/workbench) для Windows, Linux, Mac OS X. Это мощная среда с большими возможностями позволяющая визуально проектировать, создавать и управлять базами данных. Доступны все инструменты для настройки серверов, администрирования учетных записей, бэкапа и восстановления, аудита и простого мониторинга состояния. Также с его помощью можно легко выполнить миграцию с других СУБД — MS SQL Server, Sybase ASE, PostreSQL и другие. Возможности к тому же можно расширить при помощи плагинов. Интерфейс не локализован. Функций очень много поэтому некоторое время придется потратить чтобы освоиться, хотя вообщем среда удобная и все понятно. Версия Community (OSS) Edition распространяется по лицензии GNU GPL. Установка проблем не вызывает, доступны пакеты под разные дистрибутивы Linux, Windows и Mac OS X. Для пользователей RedHat/CentOS лучше воспользоваться в EPEL, в Ubuntu APT репозитарием разработчика(dev.mysql.com/downloads/repo/apt).
$ wget -c http://dev.mysql.com/get/mysql-apt-config_0.2.1-1ubuntu14.04_all.deb $ sudo dpkg -i mysql-apt-config_0.2.1-1ubuntu14.04_all.deb $ sudo apt-get install mysql-workbench |
Очень популярен среди хостеров phpMyAdmin(phpmyadmin.net) позволяющий выполнить в интуитивной среде большинство операций по управлению базами данных, работе с таблицами, индексами, правами доступа, настройка репликации, экспорт информации, бэкап/восстановление, просматривать статистику и так далее. При этом остается возможность непосредственного ввода любых SQL запросов. Поддерживается управление несколькими серверами. Все достаточно интуитивно, и с администрированием может справиться пользователь без особой подготовки, с любого устройства где есть браузер. В Сети множество инструкций и примеров по использованию phpMyAdmin. Установка из репозитария пакетов проблем не вызывает, в качестве веб-сервера можно использовать не только Apache, но и более легкие nginx или lighttpd. Некоторые панели управления хостингом вроде cPanel и Plesk имеют поддержку phpMyAdmin.
Пользователи Windows наверняка оценят HeidiSQL(heidisql.com) поддерживающий управление MySQL, MS SQL и PostgreSQL (пока экспериментально) и распространяемый под OpenSource лицензией. Программа имеет очень удобный интерфейс, поддерживает подключение сразу к нескольким серверам, которые доступны в одном окне, это упрощает операции по экспорту/импорту данных. Доступно создание и редактирование баз, таблиц (и т.д.), управление привилегиями, экспорт таблиц (CSV, HTML, XML, SQL …), поиск, оптимизация, мониторинг. При написании запросов помогает автодополнение. Поддерживается командная строка, возможно подключение по SSH тоннелю. Есть Portable версия, используя Wine его можно запустить и в *nix/Mac OS X.
Для тех кому не подошли описанные продукты в интернет можно найти большое количество аналогов — SQLyog(code.google.com/p/sqlyog), dbForge Studio for MySQL(devart.com/ru/dbforge/mysql/studio), TOra(torasql.com), SQL Buddy(sqlbuddy.com) и другие.
Утилиты мониторинга
Как и любое приложение СУБД требует постоянного наблюдения за своей работой, чтобы в случае проблем легко можно было найти узкое место. Общую информацию о работу MySQL можно получить при помощи стандартного клиента mysqladmin, запросы вроде SLOW QUERY LOG, SHOW PROCCESSLIST, SHOW VARIABLES, SHOW GLOBAL STATUS, и другие редко дают четкую картину информацию, так как медленные запросы есть всегда, но они не обязательно влияют на работу сервиса. Есть еще утилита mysqldumpslow которая анализует данные slow.log и выводит самые частые медленные запросы.
Версия Enterprise предлагает специальный инструмент MySQL Enterprise Monitor, который предоставляет в реальном времени информацию о производительности и доступности всех баз данных MySQL. Кроме того для большинства систем мониторинга, включая OpenSource Nagios, Cacti, Zabbix, Ganglia доступны специальные плагины. Например, Nagios(nagios.com/solutions/mysql-monitoring). Каждый из плагинов должен быть правильно настроен, чтобы представить подробную информацию о том, что происходит в MySQL, а это не редко требует опыта. Разворачивать полноценную систему мониторинга в том случае когда задача только контроль СУБД, не всегда рационально. В этом случае на помощь приходят специализированные инструменты.
К сожалению весьма популярный MyTOP(github.com/jzawodn/mytop) уже более двух лет не развивается (хотя это не значит что его нельзя использовать), но у него есть прекрасная и более функциональная замена. Начиная с версии MySQL 3.23.41 в состав InnoDB входит InnoDB Monitor innotop(code.google.com/p/innotop), некий аналог юниксовского top для этой СУБД выводящая в удобном виде информацию по внутреннему состоянию InnoDB, которая может пригодиться при настройке производительности. Вариантов запуска утилиты много. Самый простой выполнить от имени определенного пользователя без параметров:
$ innotop -u root -p password |
По умолчанию подключение производится к локлаьному серверу, но можно указать любой узел или мониторить сразу несколько серверов. Список всех команд (Shift+клавиша) можно получить нажав клавишу вопроса. Например, Shift+Q выведет список всех текущих запросов. Параметр — write позволяет сохранить данные соединения в файл .innotop/innotop.conf:
$ innotop --write |
Единственное неудобство по указанным утилитам, они показывают информацию в реальном времени, а о сохранении и последующем анализе статистических данных следует позаботиться самому.
Появившийся в 2009 году проект mycheckpoint(code.openark.org/forge/mycheckpoint) за несколько лет стал фактически стандартным инструментом решение для мониторинга MySQL. Причина популярности уникальный подход — для хранения и запроса используется база данных, в которую (одна операция INSERT) собираются данные о метриках мониторинга, статистики и переменных MySQL, информации об ОС Linux (состояние ОЗУ и swap, нагрузка и т.п.). Также в базу попадают результаты пользовательских запросов. Собранную информацию легко просмотреть при помощи простых SELECT запросов. При этом администратор получает наглядные графики (на основе Google Chart API), отчеты и метрики, которые генерируются на лету. Для вывода HTML отчетов может использоваться собственный веб-сервер. Также могут быть настроены предупреждения отсылаемые по email. Возможен мониторинг удаленной системы и нескольких серверов.
Написан на Python, поэтому установка сложностей не вызывает. Разработчики предлагают deb, rpm и tar.gz пакет и сырцы.
В Ubuntu следует ввести следующие команды:
$ sudo apt-get install python-mysqldb $ wget -с https://mycheckpoint.googlecode.com/files/mycheckpoint-231-1.deb $ sudo dpkg -i mycheckpoint-231-1.deb |
После чего потребуется создать базу данных для хранения информации:
$ mysql -uroot -ppassword mysql> CREATE DATABASE mycheckpoint; mysql> GRANT ALL PRIVILEGES ON mycheckpoint.* TO 'user'@'localhost' IDENTIFIED BY 'password'; |
Для сбора и вывода данных используется утилита mycheckpoint, которую можно запускать вручную или через cron. Параметры подключения к MySQL и SMTP серверу указываются также в командной строке:
$ mycheckpoint --user=user --password=password --host=server --port=3306 |
Или записываются в конфигурационный файл (по умолчанию /etc/mycheckpoint.cnf)
*/5 * * * * mycheckpoint --defaults-file=/root/mycheckpoint.cnf |
Теперь можем просматривать собранную информацию и генерировать отчеты при помощи SQL запросов.
$ mysql mycheckpoint -e "SELECT html FROM sv_report_html_brief" --silent --raw > ./checkpoint_report.html |
Еще один полезный инструмент позволяющий контролировать, анализировать и при необходимости изменять обмен данными между MySQL сервером и клиентским приложением, называется MySQL Proxy(dev.mysql.com/downloads/mysql-proxy). Возможности у программы очень большие. Работает как под Windows, так и под *nix системами. Установка проблем не вызывает, в настройках необходимо указать порт MySQL (по умолчанию 3306) и PHP (строка mysql.default_port в php.ini). Все запросы формируются на языке Lua, документация здесь хорошо помогает.
Аудит MySQL
Одной из проблем связанных с эксплуатацией любого программного продукта является его неправильная настройка. После установки присутствуют лишние демонстрационные учетные записи, тестовые базы, сами пользователи могут использовать простые пароли, которые легко подобрать. Решить эти проблемы можно лишь при помощи постоянного аудита, который к тому же будет обязателен если производится обработка конфиденциальных/персональных данных (кредитные карты, медицинские записи, и т.д.), требуется создать среду соответствующую стандартам безопасности (SOX, HIPAA и т.д.), при расследовании инцидентов, устранении неполадок и т.д. После установки следует обязательно использовать mysql_secure_installation который обеспечивает минимальный набор позволяющих произвести коррекцию общих настроек безопасности.
Далее уже следует использовать инструменты и скрипты о которых ниже.
Задача аудита упрощается тем, что разработчики MySQL предлагают соответствующий API. Правда в MySQL плагин audit_log доступен только для версии Enterprise(dev.mysql.com/doc/refman/5.5/en/audit-log-plugin.html). Разработчики Percona Server предлагают GPL альтернативу(percona.com/doc/percona-server/5.5/management/audit_log_plugin.html) данному модулю, которую подходит для аудита MySQL и клонов. Две других альтернативы McAfee MySQL Audit Plugin(github.com/mcafee/mysql-audit) и MariaDB Audit Plugin for MySQL(mariadb.com/kb/en/mariadb-audit-plugin-117-release-notes) также справляются со своей задачей и позволяют производить аудит MariaDB, MySQL и Percona Server, но используют свой собственный формат журнала аудита, отличающийся от стандартного MySQL. Это потребует чуть больших первоначальных настроек. Применение плагинов простым назвать нельзя и подробно освещено в документации, которую все равно требуется прочитать, поэтому останавливаться не будем.
Кроме того Патриком Карлссоном (Patrik Karlsson) представлен(seclists.org/nmap-dev/2011/q2/att-814/mysql-audit.nse) набор тестов для сетевого сканера nmap, позволяющий протестировать сервер на наличие основных проблем безопасности. В Ubuntu после установки настройки хранятся в каталоге /usr/share/nmap/nselib/data и nmap/script, для проверки сервера необходимо указать параметры подключения и учетную запись.
$ nmap -p 3306 1.1.1.1 --script mysql-audit --script-args "mysql-audit.filename='/usr/share/nmap/nselib/data/mysql- cis.audit' ,mysql-audit.username='root',mysql- audit.password='password'" |
Хакер им воспользуется в любом случае, поэтому админ просто обязан периодически проверять сервера таким способом. Два пакета о которых следует рассказать отдельно также содержат инструменты аудита.
Percona Toolkit for MySQL
Штатные инструменты поставляемые с MySQL предоставляют лишь базовые возможности по администрированию, в результате многие операции приходится выполнять вручную. Это может быть проблемой, ведь уследить за всем очень сложно и часто потребуется определенный опыт, да и легко допустить ошибку. Пакет Percona Toolkit for MySQL(percona.com/software/percona-toolkit) собрал наработки двух проектов Maatkit и Aspersa и предоставляет скрипты позволяющие производить многие рутинные операции администрирования: проверить состояние репликации, аудит, собирать информацию, оптимизировать запросы, произвести тюнинг сервера, архивировать/восстанавливать данные и многое другое. Всего более 4000 тестов и настроек. Пакет доступен для основных дистрибутивов Linux (в Ubuntu пакет percona-toolkit). После установки получим 32 утилиты, имя которых начинается с pt-*, назначение часто понятно из названия. При запуске можно задавать различные фильтры и форматировать вывод. Поэтому документацию почитать все равно придется так как каждая утилита имеет большое количество параметров. Например, скрипт pt-summary выведет всю информацию по серверу собранную /proc/cpuinfo, /proc/meminfo, mount, df и другими утилитами, pt-show-grants — покажет все права пользователей СУБД, pt-query-digest позволяет строить отчеты основанные на анализе логов, обработанных сервером запросов, а также информации собранной processlist и tcpdump. Например, просмотр медленных запросов двумя способами:
$ pt-query-digest slow.log $ pt-query-digest --user=user --password=password --processlist --host=example.org |
Еще один популярный пакет Openark Kit(code.openark.org/forge/openark-kit) предлагает 14 утилит позволяющих провести тестирование СУБД: проверка установок, проверять пароли (пустые, одинаковые, слабые), блокировать аккаунты, прерывать запросы, фильтровать записи в журнале, выводить статус репликации, исправлять кодировки, и многое другое. Распространяется по лицензии BSD. Написан на Python и требует python-mysqldb. Для установки предлагается deb и rpm пакеты и сырцы, процесс стандартный и проблем не вызывает.
Самый популярный скрипт комплекта oak-security-audit предназначенный для периодического аудита аккаунтов, паролей, привилегий и прочих настроек сервера.
В общем случае его можно запустить без параметров. По умолчанию используется режим наибольшего уровня контроля системы (соотвествует —audit-level=strict):
$ oak-security-audit --socket=/var/run/mysqld/mysqld.sock --user=user --password=password |
В результате получим отчет, все что не помечено как Passed, требует пристального внимания.
По умолчанию утилиты из Openark Kit (включая и mycheckpoint о котором выше, того же разработчика) используют по умолчанию для подключения к СУБД сокет /var/run/mysqld/mysql.sock (в это вшито в сам скрипт) в Ubuntu файл называется mysqld.sock. Чтобы не задавать каждый раз путь можно использовать заранее подкготовленный файл с настройками подключения и указывать при помощи —defaults-file. Как вариант изменить настройки MySQL в my.cnf. Править скрипты неудобно, так как при обновлении они работать не будут.
Следующий скрипт oak-block-account очень популярен у разного рода хостеров, для временного отключения доступа к СУБД. Дело в том что стандартный механизм СУБД не позволяет отключать временно аккаунт (что-то вроде REVOKE login ON *.*) т.е. если учетная запись есть, пользователь всегда может подключаться к базе данных. Можно конечно использовать что-то вроде:
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USER'@'%'; |
Но в случае восстановления прав, придется помнить все настройки. И главное если посмотреть права при помощи:
mysql> SHOW GRANTS FOR 'USER'; GRANT USAGE ON *.* TO 'USER'@'%' IDENTIFIED BY PASSWORD ..... |
Мы убедимся, что такая команда не сбрасывает USAGE. Использование REVOKE USAGE фактически означает DROP USER. Проще изменить логин и пароль, но восстановление может быть проблемой. Вообщем возни и рисков много. В случае использования oak-block-account, учетная запись остается неизменной, ей просто задается временный пароль, поэтому подключиться с этой учетной записью нельзя.
$ oak-block-account --block --account-user=USER --account-host=example.org |
Дополнительный параметр —kill, позволить сразу сбросить все активные подключения. Восстановить работоспособность учетки также просто. Смотрим список аккаунтов и их статус:
$ oak-block-account --list |
И включаем учетную запись:
$ oak-block-account --release --account-user=USER --account-host=example.org |
Другие скрипты из Openark Kit позволяют упростить некоторые операции. Например oak-chunk-update дает возможность выполнить большие операции UPDATE/DELETE без длительных блокировок, разбив задачу на небольшие куски. Скрипт oak-show-replication-status выводит состояние репликации, oak-kill-slow-queries — удалить выполняющиеся запросы выполняющиеся уже долго время, oak-repeat-query — выполняет запрос пока не выполняется определенное условие (количество итераций, время).
Тюнинг MySQL
Оптимизация настроек очень тонкий процесс, ведь нужно на основании собранной статистики изменить только то что действительно повлияет на производительность. Самым известным инструментом для MySQL является Perl скрипт [mysqltuner](mysqltuner.com), который доступен в репозитариях большинства дистрибутивов Linux. Он читает текущие настройки сервера и установки MySQL, после чего выдает рекомендации (только рекомендации) по их изменению. Если установка производилась при помощи пакетов то достаточно ввести имя скрипта, иначе следует вызывать указывая интерпретатор:
$ perl mysqltuner.pl |
Далее будут запрошены логин и пароль администратора, после чего мы получим метрики системы и рекомендации. Кроме этого mysqltuner показывает информацию о индексах в таблицах и фрагментации, которые также влияют на скорость работы сервера. В случае необходимости получим рекомендации произвести перестановку индексов и дефрагментацию. Для более точно работы рекомендуется запускать скрипт после как минимум 24 часа аптайма.
Оригинальный скрипт написан под *nix, но на Codeplex(mysqltuner.codeplex.com) доступна адаптирована для Win.
Альтернативой можно назвать MySQL Performance Tuning Primer Script(day32.com/MySQL/tuning-primer.sh), который выдает не сколько наглядную информацию, но зато больше «разговорчивый» о проблемах.
Вывод
Это конечно далеко не все must have инструменты, которые должны быть под рукой у администратора баз данных. Но это наверное тот необходимый минимум которые следует изучить, кроме того в процессе знакомства начинаешь больше понимать в механизмах заложенных в MySQL.